joplin-notes/joplin notion extension/Web Development/Go/databases.md

databases

sqlite https://www.sqlitetutorial.net/sqlite-go/connect/

connecting

func NewDB() *sql.DB {
	// err := errors.New("some error")

	db, err := sql.Open("sqlite", "./main.db")

	if err != nil {
		fmt.Println(err)
		return nil
	}
	fmt.Println("Connected to the SQLite database successfully.")


	fmt.Println("Table countries was created successfully.")

	return db
}

creating table

func CreateTable(db *sql.DB) (sql.Result, error) {

	sql := fmt.Sprintf(`CREATE TABLE IF NOT EXISTS  %v(
        id TEXT PRIMARY KEY,
        name     TEXT NOT NULL,
        price REAL NOT NULL,
        file TEXT 
    );`, TABLE_NAME)

	return db.Exec(sql)

}

findall

func FindAll(db *sql.DB) ([]types.Book, error) {

	sql := fmt.Sprintf(`select * from %v`, TABLE_NAME)

	rows, err := db.Query(sql)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var books []types.Book
	for rows.Next() {
		b := &types.Book{}
		err := rows.Scan(&b.Id, &b.Name, &b.Price, &b.Filename)
		if err != nil {
			return nil, err
		}

		books = append(books, *b)
	}

	fmt.Println("finall: ", books)

	return books, nil
}

we cant just use sql.Rows directly as it is stream so using Next() we read it row by row