database/sql: One Interface, Bring Your Own Driver

How Go talks to a SQL database through the database/sql package: opening a pool, querying rows and scanning them, the sql.ErrNoRows and NULL traps that bite everyone once, placeholders that stop injection, prepared statements, and context for cancellation. Compiled and run against Go 1.26.5 with a real SQLite database.

The standard library ships a package for talking to SQL databases, and the first surprising thing about it is that it doesn’t know how to talk to any database. database/sql is an interface — a set of types and methods for querying, scanning, transactions, and connection pooling — with a deliberately empty space where the actual protocol lives. You fill that space with a driver: a separate package that knows how to speak Postgres, or MySQL, or SQLite, and registers itself with database/sql so the standard API can drive it. Learn the interface once and every database looks the same from your code.

Everything here runs against a real database. We use SQLite through modernc.org/sqlite, a pure-Go driver with no C dependency, so it compiles and runs anywhere Go does with nothing to install:

$ go get modernc.org/sqlite

Opening: a pool, not a connection

You wire in the driver with a blank import — imported only for its side effect of registering itself — and then open with the driver’s registered name:

package main

import (
	"database/sql"
	"log"

	_ "modernc.org/sqlite"
)

func main() {
	db, err := sql.Open("sqlite", "file:bookshop?mode=memory&cache=shared")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}
}

The _ in front of the import is the whole trick: you never call anything in the sqlite package directly, so a normal import would be an unused-import compile error. The blank import runs the package’s init, which calls sql.Register("sqlite", ...), and that name is the first argument to sql.Open.

Two things about Open catch people out. It does not connect — it validates arguments and prepares the pool, but the first real connection happens lazily on first use. That’s why db.Ping() exists: to force a connection now and surface a bad DSN early rather than three functions later. And the *sql.DB it returns is not a connection at all. It’s a pool of them, safe for concurrent use, meant to be opened once at startup and shared for the life of the program. Do not open one per request.

Insert, and the placeholder that saves you

Statements that don’t return rows go through Exec. Values are passed as separate arguments using ? placeholders, never glued into the SQL string:

res, err := db.Exec(
	`INSERT INTO books (title, author, price) VALUES (?, ?, ?)`,
	"The Go Programming Language", "Donovan & Kernighan", 39.99,
)
if err != nil {
	log.Fatal(err)
}
id, _ := res.LastInsertId()
n, _ := res.RowsAffected()
inserted id=1 rows=1

The ? is a parameter placeholder, and using it is not a style preference. The driver sends your SQL and your values over separate channels, so a value can never be parsed as SQL. This is what makes injection structurally impossible, and we’ll prove it in a moment. (Placeholder syntax is the one thing drivers disagree on: SQLite and MySQL use ?, Postgres uses $1, $2. The mechanism is identical.)

Querying many rows

Query returns an *sql.Rows you iterate with Next, pulling each row’s columns into variables with Scan. Three rules travel with every loop, and skipping any one is a real bug:

rows, err := db.Query(`SELECT id, title, price FROM books ORDER BY id`)
if err != nil {
	log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
	var (
		id    int
		title string
		price float64
	)
	if err := rows.Scan(&id, &title, &price); err != nil {
		log.Fatal(err)
	}
	fmt.Printf("%d  %-30s $%.2f\n", id, title, price)
}
if err := rows.Err(); err != nil {
	log.Fatal(err)
}
1  The Go Programming Language    $39.99
2  Learning Go                    $44.95
3  Concurrency in Go              $41.50

Scan takes pointers, because it writes into your variables; the order and count must match the SELECT columns. defer rows.Close() releases the underlying connection back to the pool — forget it and you leak a connection out of the pool on every call until the pool is empty and everything hangs. And the loop ends when Next returns false, which happens both when the rows run out and when iteration fails partway. Those two cases look identical from inside the loop, so rows.Err() after the loop is the only way to tell a clean finish from a broken one. It is the check everyone forgets first.

The single-row path, and sql.ErrNoRows

When you expect exactly one row, QueryRow skips the iteration and hands you something you call Scan on directly. But a query that matches nothing has to report that somehow, and here is the trap: it defers the “no rows” signal to Scan, as a specific error value.

var title string
err := db.QueryRow(`SELECT title FROM books WHERE id = ?`, 99).Scan(&title)
fmt.Printf("err=%v\n", err)
fmt.Printf("errors.Is(err, sql.ErrNoRows) = %v\n", errors.Is(err, sql.ErrNoRows))
id=1: title="Learning Go" err=<nil>
id=99: err=sql: no rows in result set
id=99: errors.Is(err, sql.ErrNoRows) = true

An empty result is sql.ErrNoRows, and you match it with errors.Is, exactly the sentinel pattern from the errors chapter. This matters because “no such row” is almost never a program failure — it’s a 404, a “user not found,” an ordinary branch in your logic. Treat it as one:

switch {
case errors.Is(err, sql.ErrNoRows):
	// no book with that id — a normal outcome
case err != nil:
	// a real database error
default:
	// found it
}

If you forget this and just check err != nil, every missing row becomes a 500.

NULL is not the zero value

SQL NULL is not an empty string or a zero. It’s the absence of a value, and Go has no plain type that carries “absent.” Scan a NULL column into a string and it fails outright:

var nick string
err := db.QueryRow(`SELECT nickname FROM members WHERE id = ?`, 1).Scan(&nick)
into string:      err=sql: Scan error on column index 0, name "nickname": converting NULL to string is unsupported

The fix is a nullable scan target. sql.NullString is a two-field struct — the value plus a Valid bool that is false when the column was NULL:

var ns sql.NullString
err := db.QueryRow(`SELECT nickname FROM members WHERE id = ?`, 1).Scan(&ns)
fmt.Printf("Valid=%v String=%q\n", ns.Valid, ns.String)
into NullString:  err=<nil>  Valid=false  String=""

No error now, and Valid=false tells you truthfully that the column was NULL rather than an empty string someone typed. The family — sql.NullInt64, sql.NullFloat64, sql.NullBool, sql.NullTime — covers the rest. Reach for them the moment a column can be NULL, which in practice is any column without a NOT NULL constraint.

Injection, disproven

Back to the placeholder claim. Bind a classic injection string as a value and it does nothing but fail to match:

evil := "Bodner'; DROP TABLE books;--"
var count int
db.QueryRow(`SELECT COUNT(*) FROM books WHERE author = ?`, evil).Scan(&count)
rows matching the injection string: 0
table still has 3 rows

The string was compared against the author column as one literal value. There was no SQL to inject into, because the query text and the data never met. Every value you take from a user goes through a placeholder. There is no exception to this rule, and there is no situation where string-concatenating SQL is the better choice.

Prepared statements and context

db.Prepare compiles a statement once so you can execute it many times with different arguments — worthwhile in a loop, where it saves the database re-parsing the same SQL every iteration. The returned *sql.Stmt needs closing, same as rows:

stmt, err := db.Prepare(`INSERT INTO books (title, author) VALUES (?, ?)`)
if err != nil {
	log.Fatal(err)
}
defer stmt.Close()

for _, b := range catalog {
	stmt.Exec(b.title, b.author)
}

Every method here has a Context sibling — QueryContext, QueryRowContext, ExecContext — and in a server you should use them, because they carry cancellation. When the request is cancelled or times out, the context is cancelled, and an in-flight query cancels with it instead of running to completion for a client that has already left:

ctx, cancel := context.WithCancel(context.Background())
cancel() // already cancelled, for illustration
err := db.QueryRowContext(ctx, `SELECT title FROM books WHERE id = ?`, 1).Scan(&title)
cancelled: err=context canceled
live:      title="Learning Go" err=<nil>

The cancelled context never reaches the database; the live one runs normally. In real code the context comes from the incoming *http.Request (r.Context()), so a client that disconnects automatically cancels the queries it triggered.

The pool has knobs

Because *sql.DB is a pool, it has limits worth setting explicitly rather than inheriting the defaults:

db.SetMaxOpenConns(10)              // hard ceiling on concurrent connections
db.SetMaxIdleConns(5)               // how many to keep warm
db.SetConnMaxLifetime(time.Hour)    // recycle connections older than this

SetMaxOpenConns is the important one in production: it’s the backpressure that stops a traffic spike from opening a thousand connections and toppling your database. Above that ceiling, callers wait for a connection to free up rather than piling on new ones. The default is unlimited, which is rarely what you want facing a real database server.

Transactions: all of it, or none of it

Some operations are only correct if they happen together. Move money between two accounts and you must debit one and credit the other; if the second write fails, the first must un-happen, or you have invented money. A transaction is the database’s guarantee for exactly this: a group of statements that commit as a unit or roll back as a unit, with nothing in between visible to anyone else.

You start one with db.Begin, which hands you a *sql.Tx. Every statement you run on that Tx — Exec, Query, QueryRow — happens inside the transaction, on one pinned connection, invisible to the rest of the world until you Commit. If anything goes wrong you Rollback, and the database discards the lot:

tx, err := db.Begin()
if err != nil {
	log.Fatal(err)
}
if _, err := tx.Exec(`INSERT INTO books (title) VALUES (?)`, "The Go Programming Language"); err != nil {
	tx.Rollback()
	log.Fatal(err)
}
if _, err := tx.Exec(`INSERT INTO books (title) VALUES (?)`, "Learning Go"); err != nil {
	tx.Rollback()
	log.Fatal(err)
}
if err := tx.Commit(); err != nil {
	log.Fatal(err)
}
after commit, rows = 2

Both inserts landed because Commit succeeded. Now the mirror image — the same two inserts, but we Rollback at the end instead of committing:

tx2, err := db.Begin()
if err != nil {
	log.Fatal(err)
}
tx2.Exec(`INSERT INTO books (title) VALUES (?)`, "Concurrency in Go")
tx2.Exec(`INSERT INTO books (title) VALUES (?)`, "Network Programming with Go")
// Read through the transaction itself — it sees its own uncommitted writes.
fmt.Println("inside tx, before rollback, rows =", countBooks(tx2))
tx2.Rollback()
fmt.Println("after rollback, rows =", countBooks(db))
inside tx, before rollback, rows = 4
after rollback, rows = 2

The row count inside the open transaction was 4 — the transaction can see its own uncommitted writes. But from outside, after the rollback, it’s back to 2: neither insert persisted. That is the whole promise of a transaction made concrete. (Note the reads: a query through tx2 sees the pending rows; a query through db uses a different pooled connection that can’t see them — so count through the handle whose view you actually want.)

The idiom you’ll see in real code puts the rollback on a defer:

tx, err := db.Begin()
if err != nil {
	return err
}
defer tx.Rollback() // safety net

if _, err := tx.Exec(...); err != nil {
	return err // defer rolls back on the way out
}
if err := tx.Commit(); err != nil {
	return err
}
return nil // defer still runs — but Rollback after Commit is a harmless no-op

This looks wrong the first time — you Commit, then a deferred Rollback fires anyway. It’s fine: rolling back an already-committed transaction returns sql.ErrTxDone and changes nothing. The payoff is that every early return between Begin and Commit — a failed Exec, a validation bail-out, a panic — is covered by that one deferred line. You cannot forget to roll back on an error path, because the defer catches them all.

One more form. In a server, begin with db.BeginTx(ctx, opts) so the transaction is tied to the request’s context — if the client disconnects, the in-flight transaction is cancelled and rolled back rather than holding a connection and locks for a caller who left. The options let you name an isolation level:

tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
after BeginTx commit, rows = 3

Prefer BeginTx with r.Context() in any handler, for the same reason you prefer QueryContext: a cancelled request should take its database work down with it.

Final thoughts

database/sql is one interface over every SQL database, with the driver supplied separately and wired in through a blank import. Open once and share the pool; Ping to fail fast. Query many rows with Query/Next/Scan, always deferring Close and always checking rows.Err(); take the single-row shortcut with QueryRow, and treat its sql.ErrNoRows as an ordinary branch, not a failure. Reach for sql.NullString and its siblings the instant a column can be NULL. Pass every user value through a ? placeholder, without exception. Group writes that must succeed together into a transaction with Begin/Commit/Rollback, and reach for the defer tx.Rollback() safety net so no error path can leave one dangling. Use the Context methods so a cancelled request cancels its queries, and set the pool’s limits before something else sets them for you at the worst moment.

Next: tests are table-driven — the testing tool that ships in the box, and the patterns that make Go tests read like data.

Comments