database/sql: never ignore errors

"Life is all about sharing. If we are good at something, pass it on." - Mary Berry

database/sql: never ignore errors

2019-10-16

I’m reading Building RESTful Web Services with Go. And in chapter 4, there is an example to play around with SQLite:

 1db, err := sql.Open("sqlite3", "./books.db")
 2if err != nil {
 3	log.Fatal(err)
 4}
 5
 6statement, err := db.Prepare("CREATE TABLE IF NOT EXISTS books (id INTERGER PRIMARY KEY, isbn INTEGER, author VARCHAR(64), name VARCHAR(64) NULL)")
 7if err != nil {
 8	log.Fatal(err)
 9} else {
10	log.Println("Created table books successfully")
11}
12statement.Exec()
13
14statement, err = db.Prepare("INSERT INTO books (name, author, isbn) VALUES (?, ?, ?)")
15if err != nil {
16	log.Fatal(err)
17}
18statement.Exec("Life is a joke", "The Javna brothers", 123456789)
19log.Println("Inserted first book into db")
20rows, err := db.Query("SELECT id, name, author FROM books")
21var tempBook Book
22for rows.Next() {
23	rows.Scan(&tempBook.id, &tempBook.name, &tempBook.author)
24	log.Printf("ID: %d, Book: %s, Author: %s\n", tempBook.id, tempBook.name, tempBook.author)
25}

Run this and I got:

12019/10/16 09:59:06 Created table books successfully
22019/10/16 09:59:06 Inserted first book into db
32019/10/16 09:59:06 ID: 0, Book: , Author:

Why the ID is zero and name and author is empty?

Checking db from the command line I saw this:

1❯❯❯❯ sqlite3
2SQLite version 3.28.0 2019-04-15 14:49:49
3Enter ".help" for usage hints.
4Connected to a transient in-memory database.
5Use ".open FILENAME" to reopen on a persistent database.
6sqlite> .open books.db
7sqlite> SELECT * FROM books;
8|123456789|The Javna brothers|Life is a joke

So, there is no id. Looking carefully at the schema:

1sqlite> .schema
2CREATE TABLE books (id INTERGER PRIMARY KEY, isbn INTEGER, author VARCHAR(64), name VARCHAR(64) NULL);

There is a typo: id should be INTEGER, not INTERGER.

How can I prevent this kind of errors?

Scan (or almost all operations with database/sql) return an error as the last value. You should always check that. Never ignore them.

1if err := rows.Scan(&tempBook.id, &tempBook.name, &tempBook.author); err != nil {
2	log.Fatal(err)
3}

then you can quickly found what the error is:

12019/10/16 10:11:12 Created table books successfully
22019/10/16 10:11:12 Inserted first book into db
32019/10/16 10:11:12 sql: Scan error on column index 0, name "id": converting NULL to int is unsupported

Tags: golang restful sqlite3

Edit on GitHub