database/sql

database/sql

Overview

database/sql is the standard database access layer: connection pools, prepared statements, transactions, and context-aware queries. Drivers register themselves; your code stays mostly driver-agnostic.

Deeper pool and production notes: database/sql pool deep dive and Bookstore use in web databases.

Mental model

  sql.DB  = pool of connections (safe for concurrent use)
     │
     ├── QueryContext / QueryRowContext / ExecContext
     ├── PrepareContext → sql.Stmt
     └── BeginTx → sql.Tx → Commit / Rollback

• sql.DB is not a single connection. It is a pool.
• sql.Open may not dial until the first use — always PingContext at startup.
• Pass context.Context so cancel/timeout reaches the driver.

Open, configure, ping

import (
    "context"
    "database/sql"
    "time"

    _ "modernc.org/sqlite" // or pgx stdlib, etc.
)

func openDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("sqlite", dsn)
    if err != nil {
        return nil, err
    }
    db.SetMaxOpenConns(10)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(time.Hour)
    db.SetConnMaxIdleTime(15 * time.Minute)

    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil {
        _ = db.Close()
        return nil, err
    }
    return db, nil
}

Setting	Role
MaxOpenConns	Cap concurrent DB usage
MaxIdleConns	Keep warm connections
ConnMaxLifetime	Recycle to avoid server-side timeouts
ConnMaxIdleTime	Drop idle conns (Go 1.15+)

Query patterns

Many rows

rows, err := db.QueryContext(ctx,
    `SELECT id, title FROM books WHERE author = ? ORDER BY title`, author)
if err != nil {
    return nil, err
}
defer rows.Close()

var out []Book
for rows.Next() {
    var b Book
    if err := rows.Scan(&b.ID, &b.Title); err != nil {
        return nil, err
    }
    out = append(out, b)
}
return out, rows.Err() // always check rows.Err()

One row

var b Book
err := db.QueryRowContext(ctx,
    `SELECT id, title FROM books WHERE id = ?`, id,
).Scan(&b.ID, &b.Title)
if errors.Is(err, sql.ErrNoRows) {
    return Book{}, errNotFound
}
return b, err

Exec (insert/update/delete)

res, err := db.ExecContext(ctx,
    `INSERT INTO books (id, title) VALUES (?, ?)`, id, title)
if err != nil {
    return err
}
n, _ := res.RowsAffected()
_ = n

Always use placeholders (? or $1 depending on driver). Never concatenate user input into SQL.

Nullables

Database NULL is not Go zero. Use:

var maybeTitle sql.NullString
// Scan into maybeTitle; use maybeTitle.Valid && maybeTitle.String

Or pointer fields (*string) with some drivers/scanners. Be consistent.

Transactions

tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
if err != nil {
    return err
}
defer tx.Rollback() // no-op after successful Commit

if _, err := tx.ExecContext(ctx, `UPDATE accounts SET bal = bal - ? WHERE id = ?`, amt, from); err != nil {
    return err
}
if _, err := tx.ExecContext(ctx, `UPDATE accounts SET bal = bal + ? WHERE id = ?`, amt, to); err != nil {
    return err
}
return tx.Commit()

Keep transactions short. Do not hold a tx open across slow external HTTP calls.

Prepared statements

stmt, err := db.PrepareContext(ctx, `SELECT title FROM books WHERE id = ?`)
if err != nil {
    return err
}
defer stmt.Close()

var title string
err = stmt.QueryRowContext(ctx, id).Scan(&title)

Prepare when you run the same SQL many times in one process. For one-shot queries, QueryContext is fine.

Errors worth mapping

Condition	Typical HTTP / API mapping
sql.ErrNoRows	Not found
Unique constraint (driver-specific)	Conflict
context.DeadlineExceeded	Timeout / unavailable
Connection refused	Unavailable; log + retry policy

Log the real error; return a safe client message.

Rules of thumb

Do	Don’t
*Context methods + timeouts	Bare Query in request handlers without budget
Close rows (defer)	Forget rows.Err() after the loop
Parameterized SQL	String-build queries from user input
One *sql.DB per process/DSN	Open a new DB per request

Try next

1. Open SQLite in-memory, create a table, insert, list.
2. Force ErrNoRows and map it with errors.Is.
3. Run two concurrent QueryContext calls against one *sql.DB (pool is concurrent-safe).