database/sql
database/sql is not a database driver: it is the pool, the statement cache and the row scanner on top of one. A program imports a driver for its database, opens a handle with the driver name and a connection string, and from then on uses the same three calls whatever the database is. The driver here is modernc.org/sqlite v1.62.0, a pure-Go SQLite that the module proxy fetched when the module was created, so the programs and their output are real.
Open and the first row
sql.Open checks the driver name and the DSN but does not connect; the first Exec or Query does. The DSN :memory: is a database that lives in this process, which is why the program pins the pool to one connection: a second connection would see a second empty database. The insert uses a placeholder, never a formatted string, and the id comes back from LastInsertId.
package main
import (
"database/sql"
"fmt"
_ "modernc.org/sqlite"
)
func main() {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
panic(err)
}
defer db.Close()
db.SetMaxOpenConns(1)
if _, err := db.Exec(`CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)`); err != nil {
panic(err)
}
res, err := db.Exec(`INSERT INTO notes (title) VALUES (?)`, "buy milk")
if err != nil {
panic(err)
}
id, err := res.LastInsertId()
fmt.Println("inserted id:", id, "err:", err)
var title string
err = db.QueryRow(`SELECT title FROM notes WHERE id = ?`, id).Scan(&title)
fmt.Println("title:", title, "err:", err)
}
go build -o app . && ./app
inserted id: 1 err: <nil>
title: buy milk err: <nil>
The blank import registers the driver, which is the one place Go uses an import for its side effect. The ? placeholder is a parameter of the statement, so a title with a quote in it is data and not SQL; the driver and the database supply the escaping, and a program never builds a query with fmt.Sprintf.
Query and rows
Query returns a cursor: Next advances, Scan copies the current row into the pointers, and the loop ends at the last row. rows.Err after the loop is not optional, because a connection that failed halfway is reported there and not by Next. The deferred Close releases the connection the moment the function returns.
package main
import (
"database/sql"
"fmt"
_ "modernc.org/sqlite"
)
func main() {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
panic(err)
}
defer db.Close()
db.SetMaxOpenConns(1)
if _, err := db.Exec(`CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)`); err != nil {
panic(err)
}
for _, title := range []string{"buy milk", "write lesson", "read book"} {
if _, err := db.Exec(`INSERT INTO notes (title) VALUES (?)`, title); err != nil {
panic(err)
}
}
rows, err := db.Query(`SELECT id, title FROM notes ORDER BY id`)
if err != nil {
panic(err)
}
defer rows.Close()
for rows.Next() {
var id int
var title string
if err := rows.Scan(&id, &title); err != nil {
panic(err)
}
fmt.Printf("%d %s\n", id, title)
}
fmt.Println("rows error:", rows.Err())
}
go build -o app . && ./app
1 buy milk
2 write lesson
3 read book
rows error: <nil>
Three rows in the order the query asked for, and a nil error at the end. Scan converts the driver's values to the types of the pointers, so an integer column into an int and a text column into a string; a nullable column needs the next section.
NULL and sql.NullString
A column that may be NULL cannot scan into a string: the driver has nothing to put there and Scan returns a conversion error. sql.NullString holds the value and a Valid flag, and the program checks the flag instead of comparing with an empty string, because an empty string is a real value.
package main
import (
"database/sql"
"fmt"
_ "modernc.org/sqlite"
)
func main() {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
panic(err)
}
defer db.Close()
db.SetMaxOpenConns(1)
var title sql.NullString
err = db.QueryRow(`SELECT NULL`).Scan(&title)
fmt.Println("valid:", title.Valid)
fmt.Printf("value: %q\n", title.String)
fmt.Println("error:", err)
}
go build -o app . && ./app
valid: false
value: ""
error: <nil>
The scan succeeded and the column is NULL, which is exactly what Valid says. A program that writes a value back uses either title.String or nil depending on the flag, and a JSON response wants null rather than the empty string in that case, so the handler maps the pair.
A table that does not exist
The driver passes the database's error through, with the text the database wrote. The program below asks for a table nobody created and prints the message; a service maps the class of the error to a status, and a migration runner treats a missing table as "not migrated yet" rather than as a crash.
package main
import (
"database/sql"
"fmt"
_ "modernc.org/sqlite"
)
func main() {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
panic(err)
}
defer db.Close()
db.SetMaxOpenConns(1)
_, err = db.Exec(`SELECT * FROM missing`)
fmt.Println("error:", err)
}
go build -o app . && ./app
error: SQL logic error: no such table: missing (1)
The text and the code come from SQLite through the driver, and the shape is a plain error, not a typed one. A program that needs to tell a missing table from a syntax error either matches on the driver's type or wraps its own sentinel at the call site, which is the layer the next lesson builds its transactions on.
The connection pool
The handle is a pool. Two calls can use it at once, up to the limit; a call beyond the limit waits. A connection that sits idle is closed after the lifetime, which is how a program survives a database that restarts underneath it. The stats below show the limit that was set and the one connection the Ping opened.
package main
import (
"database/sql"
"fmt"
"time"
_ "modernc.org/sqlite"
)
func main() {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
panic(err)
}
defer db.Close()
db.SetMaxOpenConns(4)
db.SetMaxIdleConns(2)
db.SetConnMaxLifetime(time.Minute)
if err := db.Ping(); err != nil {
panic(err)
}
stats := db.Stats()
fmt.Println("max open:", stats.MaxOpenConnections)
fmt.Println("open:", stats.OpenConnections)
}
go build -o app . && ./app
max open: 4
open: 1
Ping opened the first connection, so one is open. A pool too small serialises the handlers and a pool too large exhausts the database's own connection limit; the number belongs in the configuration, next to the timeout and the DSN, and a busy service measures it rather than guessing.
SQL in a Go program
The database/sql contract is small and the discipline around it is not. Every query uses placeholders. Every rows value is closed. Every error is checked, including the one after the loop. The handle is created once and shared. A schema change goes through a migration, never through a CREATE TABLE in a handler. And a transaction, a prepared statement and a context on every call are the next lesson, because a query without a context keeps running after the request it belonged to has gone.
The API capstone replaces this in-memory table with a file database through the same calls, and its tests use a temporary file per test: the driver, the placeholders and the Scan stay exactly as they are here.
Exercises
- Remove SetMaxOpenConns(1) and run the create and the insert. What happens to the table, and why does :memory: behave that way?
- Scan a NULL into a plain string and read the error. What does the conversion error say, and which type should the column use?
- Insert a title with a single quote in it with a placeholder and then with fmt.Sprintf. Which one fails, and what does the failure teach about SQL injection?
Questions
A new set of questions in a new order every time. Choose one answer for each, then check.
-
What does sql.Open do?
Answer
Checks the driver and the DSN without connecting
The first Exec or Query connects.
-
Why is modernc.org/sqlite imported with a blank name?
Answer
Its init registers the driver with database/sql
The package is used for its side effect.
-
What does db.QueryRow(...).Scan do?
Answer
Runs a query that returns one row and copies it into the pointers
A missing row is sql.ErrNoRows.
-
Why check rows.Err after the loop?
Answer
A failure during iteration is reported there
Next stops silently on an error.
-
What does sql.NullString add to a string?
Answer
A Valid flag for NULL
An empty string is not a NULL.
-
How is a value put into a query?
Answer
With a ? placeholder and an argument
The driver escapes the value.
-
What does :memory: mean for SQLite?
Answer
A database in this process, one per connection
That is why the pool is pinned to one connection.
-
What does SetMaxOpenConns bound?
Answer
The connections the pool opens at once
A call beyond the limit waits.
-
What does the no such table error come from?
Answer
The database, passed through the driver
The text is the database's.
-
Why create the database handle once?
Answer
It owns the pool and the statement cache
A handle per request defeats the pool.
The code on this page was run with go 1.26.10 on Alpine Linux 3.24.3 (x86_64) in a Docker container.
Article author: Arthur Isaev