database/sql in Go: NullString, Contexts, and Connection Lifecycle

• Book: The Complete Guide to Go Programming
• Also by me: Hexagonal Architecture in Go — the companion book in the Thinking in Go series
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

---

You've shipped a Go service that talks to Postgres. The queries are
fast. The tests pass. Then a marketing email goes out, traffic
triples for ten minutes, and your logs fill with the same line over
and over: sql: database is closed or context deadline exceeded or,
the confusing one, pq: sorry, too many clients already. The
database has plenty of headroom. Your service is starving it of
connections anyway.

database/sql is one of the oldest packages in the standard library,
and it hides a connection pool behind an API that looks like a thin
driver wrapper. Three parts of that API decide whether your service
degrades gracefully or falls over: how you scan nullable columns, how
you thread contexts through queries, and how you size the pool. None
of them are hard. All of them are easy to get subtly wrong.

sql.Null types: the column can be NULL, your string cannot

A string in Go has a zero value of "". A SQL column has a fourth
state on top of every value it can hold: NULL. Scan a NULL into a
plain string and you get an error at runtime, not compile time.

var name string
err := row.Scan(&name)
// if the column is NULL:
// sql: Scan error on column index 0: converting
// NULL to string is unsupported

The Scan failed. Your handler returns a 500 for a row that exists
and is perfectly valid. The fix is the sql.Null* family. Go 1.22
rounded it out with generics, so as of 1.22+ you have both the old
named types and a generic one.

import "database/sql"

var name sql.NullString
err := row.Scan(&name)
if err != nil {
    return err
}
if name.Valid {
    fmt.Println("name:", name.String)
} else {
    fmt.Println("name is NULL")
}

NullString is a two-field struct: String string and Valid bool.
Valid is false when the column was NULL. You check Valid
before you trust String. There's NullInt64, NullFloat64,
NullBool, NullTime, and since 1.22 a generic sql.Null[T]:

var age sql.Null[int32]
err := row.Scan(&age)
if age.Valid {
    fmt.Println("age:", age.V)
}

The generic version stores the value in .V and works for any type
the driver can scan, which saves you from writing a custom Scanner
for every nullable numeric width.

One trap worth naming: sql.NullString marshals to JSON as
{"String":"x","Valid":true}, not as a bare string or null. If the
value goes straight into a JSON response, wrap it in your own type
with a MarshalJSON method, or convert to a *string before you
serialize. Don't let the pool's internal representation leak into your
API contract.

QueryContext: the non-context calls are a trap in disguise

database/sql gives you two of every method. Query and
QueryContext. Exec and ExecContext. Begin and BeginTx. The
short names are the old ones. They call the context version internally
with context.Background(), which means they can never be cancelled
and never respect a deadline.

That's the whole trap. A request comes in with a 2-second budget. The
client hangs up after 2 seconds. Your query, launched with plain
Query, keeps running against the database because nothing told it to
stop. The connection stays checked out of the pool. Under load, every
abandoned request holds a connection hostage, and the pool empties
even though no query is doing useful work.

Always take a context and always pass it down:

func findUser(
    ctx context.Context,
    db *sql.DB,
    id int64,
) (string, error) {
    const q = `SELECT name FROM users WHERE id = $1`

    var name sql.NullString
    err := db.QueryRowContext(ctx, q, id).Scan(&name)
    if err != nil {
        return "", err
    }
    if !name.Valid {
        return "", nil
    }
    return name.String, nil
}

QueryRowContext passes ctx all the way to the driver. If the
context is cancelled or its deadline passes while the query is in
flight, database/sql tells the driver to cancel, and the connection
goes back to the pool instead of hanging. Whether the query on the
database side actually stops depends on the driver, but the
connection is reclaimed either way, and that's what saves the pool.

For multi-row reads the same rule applies, plus a discipline that
catches everyone at least once.

Rows.Close and Rows.Err: the two lines everybody forgets

A *sql.Rows holds a connection for as long as it's open. You iterate
with rows.Next(), and the loop ends when Next returns false. The
catch: Next returns false both when you've read every row and when
iteration hit an error partway through. If you only check the loop
condition, you can't tell "done" from "broke."

func listNames(
    ctx context.Context,
    db *sql.DB,
) ([]string, error) {
    const q = `SELECT name FROM users ORDER BY id`

    rows, err := db.QueryContext(ctx, q)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var names []string
    for rows.Next() {
        var n sql.NullString
        if err := rows.Scan(&n); err != nil {
            return nil, err
        }
        if n.Valid {
            names = append(names, n.String)
        }
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return names, nil
}

Two lines do the load-bearing work. defer rows.Close() returns the
connection to the pool even if you bail early with an error mid-loop.
Without it, an early return inside the loop leaks the connection
until the garbage collector eventually runs a finalizer, which is not
a schedule you want your pool depending on. And rows.Err() after the
loop is the only way to see an error that ended iteration. Skip it and
a network blip mid-scan looks identical to a clean end of results.
You return a truncated list and call it success.

Close is safe to call more than once, so defer rows.Close() right
after the error check is the pattern. Put it there every time, before
you even write the loop.

Connection lifecycle: the pool has four knobs

sql.DB is not a connection. It's a pool of them, created lazily and
reused. Out of the box the pool has MaxIdleConns set to 2 and
MaxOpenConns set to unlimited. Those defaults are why services fall
over under load: unlimited open connections means a spike opens
hundreds of them until the database refuses new clients with too many
clients already.

Four methods size the pool. Set them once, right after you open.

db, err := sql.Open("pgx", dsn)
if err != nil {
    return err
}

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(2 * time.Minute)

SetMaxOpenConns(25) caps total connections. When all 25 are busy,
the next QueryContext blocks until one frees up or the context's
deadline fires. That backpressure is what you want. A bounded pool
turns "database overwhelmed" into "requests wait," and waiting
requests respect their context deadline and shed load cleanly.

SetMaxIdleConns controls how many idle connections stay warm.
Setting it equal to MaxOpenConns avoids a churn pattern where
connections open, serve one query, close, and reopen on the next
request. If idle is lower than open, a burst opens connections that
get closed seconds later, and you pay the TCP-plus-TLS handshake cost
over and over.

SetConnMaxLifetime caps how long a single connection lives before
the pool retires it. This matters behind a load balancer or a managed
database that rotates backends: a connection pinned for hours can
outlive the server it's talking to. Five minutes is a common starting
point. SetConnMaxIdleTime (Go 1.15+) retires connections that have
been idle too long, so a pool sized for peak traffic shrinks back down
when the peak passes.

Sizing rule of thumb: MaxOpenConns across all your service replicas
must stay under the database's connection limit, with headroom for
migrations and admin tools. Ten replicas at 25 connections each is 250
connections. If your Postgres max_connections is 100, you've
oversubscribed the database by 2.5x, and the eleventh replica to
scale up will start getting rejections. Count replicas times pool
size against the server limit before you pick the number.

Verifying the pool actually works

db.Stats() returns a snapshot you can log or export to metrics. It's
the fastest way to confirm your tuning is real and not aspirational.

s := db.Stats()
log.Printf(
    "open=%d inuse=%d idle=%d waited=%d",
    s.OpenConnections,
    s.InUse,
    s.Idle,
    s.WaitCount,
)

WaitCount is the number that tells the story. If it climbs under
load, requests are queuing for a connection, and either your pool is
too small or your queries are too slow. If InUse sits pinned at
MaxOpenConns while WaitCount grows, the pool is the bottleneck.
Export these four and you can see a connection starvation problem
before it becomes a context deadline exceeded in the logs.

The three-line checklist

Grep the codebase you already have:

1. \.Scan( — every nullable column needs a sql.Null* target or a pointer, not a bare string/int.
2. db.Query( / db.Exec( without Context — swap for the context versions and thread ctx from the handler down.
3. sql.Open — confirm a SetMaxOpenConns call lives near it. If there isn't one, your pool is unbounded.

None of these three is a rewrite. Each is a small edit that changes
how your service behaves the next time traffic triples. The
database/sql API looks thin, and that thinness is the point: it
hands you a pool and trusts you to size it. Take it up on the offer.

---

If this was useful

database/sql is a small package that rewards knowing exactly what
sits under the API: the pool, the driver contract, the difference
between a checked-out and an idle connection. The Complete Guide to
Go Programming covers the standard library at that depth, including
how the runtime and the pool interact under load. Hexagonal
Architecture in Go shows how to keep this behind a repository port so
your domain code never sees a sql.NullString or a pool knob, and the
database stays a swappable adapter.