database/sql

Updated

September 13, 2026

database/sql

database/sql is the standard-library interface for relational databases. It defines types and functions that every driver implements. The boring default is: open one sql.DB per process, reuse it everywhere, and always close *sql.Rows before moving on. The package is intentionally database-agnostic; this chapter uses modernc.org/sqlite — a pure-Go SQLite driver that requires no C compiler.

Mental model

sql.DB is a connection pool, not a single connection. The pool opens and closes underlying connections automatically based on load. Calls to QueryRow, Query, and Exec borrow a connection, do the work, then return it to the pool.

Three query methods cover almost every case:

Method Returns Use when
db.Exec sql.Result INSERT / UPDATE / DELETE
db.QueryRow *sql.Row exactly one row expected
db.Query *sql.Rows zero or more rows

*sql.Rows holds an open connection until you close it. Forgetting defer rows.Close() exhausts the pool. Forgetting rows.Err() hides scan-loop errors silently.

A prepared statement (db.Prepare) sends the SQL to the database once and re-executes with different arguments. This avoids repeated parsing overhead and prevents SQL injection.

A transaction (db.BeginTx) runs a group of statements atomically. Call tx.Commit() on success and tx.Rollback() on any error.

Worked examples

All examples share this module definition. Place it in a go.mod file in your working directory and run go mod tidy once:

module desk

go 1.27

require modernc.org/sqlite v1.37.1
go mod tidy

Case 1: Open, create table, insert, query one row

Save as desk_ticket.go.

// desk_ticket.go
package main

import (
    "database/sql"
    "fmt"
    "log/slog"
    "time"

    _ "modernc.org/sqlite"
)

type Ticket struct {
    ID      int64
    Subject string
    Status  string
    Created time.Time
}

func main() {
    // :memory: creates a private, in-process SQLite database.
    // No file is written to disk.
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        slog.Error("open", "err", err)
        return
    }
    defer db.Close()

    // Verify the database is reachable.
    if err := db.Ping(); err != nil {
        slog.Error("ping", "err", err)
        return
    }

    // Create the tickets table.
    const ddl = `
        CREATE TABLE tickets (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            subject TEXT    NOT NULL,
            status  TEXT    NOT NULL DEFAULT 'open',
            created TEXT    NOT NULL
        );`
    if _, err := db.Exec(ddl); err != nil {
        slog.Error("create table", "err", err)
        return
    }

    // Insert one ticket.
    created := time.Now().UTC().Format(time.RFC3339)
    res, err := db.Exec(
        `INSERT INTO tickets (subject, status, created) VALUES (?, ?, ?)`,
        "Keyboard missing from desk 4", "open", created,
    )
    if err != nil {
        slog.Error("insert", "err", err)
        return
    }
    id, _ := res.LastInsertId()
    fmt.Printf("inserted ticket id=%d\n", id)

    // Query one row by primary key.
    var t Ticket
    var createdStr string
    row := db.QueryRow(
        `SELECT id, subject, status, created FROM tickets WHERE id = ?`, id,
    )
    if err := row.Scan(&t.ID, &t.Subject, &t.Status, &createdStr); err != nil {
        slog.Error("scan", "err", err)
        return
    }
    t.Created, _ = time.Parse(time.RFC3339, createdStr)

    fmt.Printf("ticket #%d: %q  status=%s  created=%s\n",
        t.ID, t.Subject, t.Status, t.Created.Format(time.DateTime))
}

Run:

go run desk_ticket.go

Output:

inserted ticket id=1
ticket #1: "Keyboard missing from desk 4"  status=open  created=2026-09-09 04:47:00

sql.Open validates the driver name and DSN format but does not open a connection. db.Ping() forces the first real connection. The blank import _ "modernc.org/sqlite" registers the driver’s init function with database/sql.

Case 2: Query multiple rows

Save as desk_list.go. This program inserts several tickets and lists only the open ones.

// desk_list.go
package main

import (
    "database/sql"
    "fmt"
    "log/slog"
    "time"

    _ "modernc.org/sqlite"
)

func mustExec(db *sql.DB, query string, args ...any) {
    if _, err := db.Exec(query, args...); err != nil {
        slog.Error("exec", "query", query, "err", err)
        panic(err)
    }
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        slog.Error("open", "err", err)
        return
    }
    defer db.Close()

    mustExec(db, `CREATE TABLE tickets (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        subject TEXT    NOT NULL,
        status  TEXT    NOT NULL DEFAULT 'open',
        created TEXT    NOT NULL
    )`)

    now := time.Now().UTC().Format(time.RFC3339)
    mustExec(db, `INSERT INTO tickets (subject, status, created) VALUES (?, ?, ?)`,
        "Printer jam on floor 2", "open", now)
    mustExec(db, `INSERT INTO tickets (subject, status, created) VALUES (?, ?, ?)`,
        "Chair broken at desk 7", "open", now)
    mustExec(db, `INSERT INTO tickets (subject, status, created) VALUES (?, ?, ?)`,
        "Monitor flickering", "closed", now)
    mustExec(db, `INSERT INTO tickets (subject, status, created) VALUES (?, ?, ?)`,
        "Coffee machine fault", "open", now)

    // Query multiple rows. Defer rows.Close immediately after checking err.
    rows, err := db.Query(
        `SELECT id, subject FROM tickets WHERE status = ? ORDER BY id`, "open",
    )
    if err != nil {
        slog.Error("query", "err", err)
        return
    }
    defer rows.Close() // Always. Releases the connection back to the pool.

    fmt.Println("open tickets:")
    for rows.Next() {
        var id int64
        var subject string
        if err := rows.Scan(&id, &subject); err != nil {
            slog.Error("scan", "err", err)
            return
        }
        fmt.Printf("  #%d  %s\n", id, subject)
    }

    // Check for errors that occurred during iteration.
    // rows.Next() swallows iteration errors; rows.Err() surfaces them.
    if err := rows.Err(); err != nil {
        slog.Error("rows iteration", "err", err)
    }
}

Run:

go run desk_list.go

Output:

open tickets:
  #1  Printer jam on floor 2
  #2  Chair broken at desk 7
  #4  Coffee machine fault

The pattern is always: rows, err := db.Query(...) → check err → defer rows.Close() → for rows.Next() → rows.Scan(...) → check rows.Err(). Skipping rows.Err() means a network hiccup or cursor error will silently produce a truncated result set.

Case 3: Prepared statement for repeated inserts

A prepared statement sends the SQL template to the database once. Subsequent calls pass only the argument values. This avoids re-parsing the same SQL on every execution — important when inserting hundreds or thousands of rows.

Save as desk_bulk.go.

// desk_bulk.go
package main

import (
    "database/sql"
    "fmt"
    "log/slog"
    "time"

    _ "modernc.org/sqlite"
)

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        slog.Error("open", "err", err)
        return
    }
    defer db.Close()

    if _, err := db.Exec(`CREATE TABLE tickets (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        subject TEXT    NOT NULL,
        status  TEXT    NOT NULL DEFAULT 'open',
        created TEXT    NOT NULL
    )`); err != nil {
        slog.Error("create table", "err", err)
        return
    }

    // Prepare parses the SQL once. stmt is reused for every insert below.
    stmt, err := db.Prepare(
        `INSERT INTO tickets (subject, status, created) VALUES (?, ?, ?)`,
    )
    if err != nil {
        slog.Error("prepare", "err", err)
        return
    }
    defer stmt.Close() // Return statement resources when done.

    now := time.Now().UTC().Format(time.RFC3339)
    subjects := []string{
        "Desk lamp out",
        "Broken locker key",
        "Wi-Fi drops in meeting room A",
        "Whiteboard marker missing",
        "Reception phone faulty",
    }

    // Insert 100 tickets using the same prepared statement.
    // The SQL is parsed only once regardless of how many rows we insert.
    for i := range 100 {
        subject := subjects[i%len(subjects)]
        if _, err := stmt.Exec(subject, "open", now); err != nil {
            slog.Error("stmt exec", "i", i, "err", err)
            return
        }
    }

    var count int
    if err := db.QueryRow(`SELECT COUNT(*) FROM tickets`).Scan(&count); err != nil {
        slog.Error("count", "err", err)
        return
    }
    fmt.Printf("inserted %d tickets via prepared statement\n", count)
}

Run:

go run desk_bulk.go

Output:

inserted 100 tickets via prepared statement

With an ad-hoc db.Exec inside the loop, the database re-parses the SQL string every iteration. With db.Prepare, parsing happens once. For a remote database such as PostgreSQL, this difference is measurable at hundreds of rows. For SQLite it is smaller but the habit remains correct.

Case 4: Transaction — close a ticket atomically

A transaction bundles multiple statements into one atomic unit. Either every statement commits, or none of them do. Use db.BeginTx (not the older db.Begin) to attach a context.Context for cancellation.

Save as desk_close.go.

// desk_close.go
package main

import (
    "context"
    "database/sql"
    "errors"
    "fmt"
    "log/slog"
    "time"

    _ "modernc.org/sqlite"
)

func seed(db *sql.DB) (openID, closedID int64, err error) {
    now := time.Now().UTC().Format(time.RFC3339)
    r1, err := db.Exec(
        `INSERT INTO tickets (subject, status, created) VALUES (?, ?, ?)`,
        "Standing desk motor broken", "open", now,
    )
    if err != nil {
        return 0, 0, err
    }
    r2, err := db.Exec(
        `INSERT INTO tickets (subject, status, created) VALUES (?, ?, ?)`,
        "Headset cable frayed", "closed", now,
    )
    if err != nil {
        return 0, 0, err
    }
    id1, _ := r1.LastInsertId()
    id2, _ := r2.LastInsertId()
    return id1, id2, nil
}

// closeTicket moves a ticket to closed and records the resolver.
// It returns an error if the ticket does not exist or is already closed.
func closeTicket(ctx context.Context, db *sql.DB, id int64, resolver string) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin tx: %w", err)
    }
    // Rollback is a no-op once Commit has been called.
    // Defer ensures resources are released on every return path.
    defer tx.Rollback()

    // Lock the row and check its current status.
    var status string
    err = tx.QueryRowContext(ctx,
        `SELECT status FROM tickets WHERE id = ?`, id,
    ).Scan(&status)
    if errors.Is(err, sql.ErrNoRows) {
        return fmt.Errorf("ticket %d not found", id)
    }
    if err != nil {
        return fmt.Errorf("select status: %w", err)
    }
    if status == "closed" {
        return fmt.Errorf("ticket %d is already closed", id)
    }

    // Update status inside the same transaction.
    _, err = tx.ExecContext(ctx,
        `UPDATE tickets SET status = 'closed' WHERE id = ?`, id,
    )
    if err != nil {
        return fmt.Errorf("update: %w", err)
    }

    // Record the resolver in a separate audit row.
    _, err = tx.ExecContext(ctx,
        `INSERT INTO audit (ticket_id, action, actor, ts) VALUES (?, 'closed', ?, ?)`,
        id, resolver, time.Now().UTC().Format(time.RFC3339),
    )
    if err != nil {
        return fmt.Errorf("audit insert: %w", err)
    }

    // Commit makes both the UPDATE and INSERT permanent atomically.
    return tx.Commit()
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        slog.Error("open", "err", err)
        return
    }
    defer db.Close()

    for _, ddl := range []string{
        `CREATE TABLE tickets (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            subject TEXT    NOT NULL,
            status  TEXT    NOT NULL DEFAULT 'open',
            created TEXT    NOT NULL
        )`,
        `CREATE TABLE audit (
            id        INTEGER PRIMARY KEY AUTOINCREMENT,
            ticket_id INTEGER NOT NULL,
            action    TEXT    NOT NULL,
            actor     TEXT    NOT NULL,
            ts        TEXT    NOT NULL
        )`,
    } {
        if _, err := db.Exec(ddl); err != nil {
            slog.Error("ddl", "err", err)
            return
        }
    }

    openID, closedID, err := seed(db)
    if err != nil {
        slog.Error("seed", "err", err)
        return
    }

    ctx := context.Background()

    // Success: close an open ticket.
    if err := closeTicket(ctx, db, openID, "alice"); err != nil {
        slog.Error("close open ticket", "err", err)
    } else {
        fmt.Printf("ticket #%d closed successfully\n", openID)
    }

    // Error path: attempt to close an already-closed ticket.
    if err := closeTicket(ctx, db, closedID, "bob"); err != nil {
        fmt.Printf("expected error: %v\n", err)
    }

    // Verify audit table has exactly one row.
    var auditCount int
    db.QueryRow(`SELECT COUNT(*) FROM audit`).Scan(&auditCount)
    fmt.Printf("audit rows: %d\n", auditCount)
}

Run:

go run desk_close.go

Output:

ticket #1 closed successfully
expected error: ticket #2 is already closed
audit rows: 1

The deferred tx.Rollback() after a successful tx.Commit() is a no-op. It exists so that any early return — from a failed SELECT, UPDATE, or audit INSERT — automatically undoes partial work. This is the standard Go transaction pattern.

The trap

Forgetting defer rows.Close() leaks a connection for every un-closed *sql.Rows. The pool has a finite number of connections (db.SetMaxOpenConns). Each leaked *sql.Rows pins one connection until the garbage collector finalises it — which may never happen in a tight loop. db.Stats().WaitCount rises as callers queue waiting for a free connection.

Save as desk_leak.go.

// desk_leak.go
package main

import (
    "database/sql"
    "fmt"
    "log/slog"

    _ "modernc.org/sqlite"
)

func leakyQuery(db *sql.DB) {
    // BUG: rows is never closed.
    // The connection is held until the GC collects the *sql.Rows value,
    // which may be arbitrarily late.
    rows, err := db.Query(`SELECT id FROM tickets LIMIT 1`)
    if err != nil {
        slog.Error("query", "err", err)
        return
    }
    if rows.Next() {
        var id int64
        rows.Scan(&id) // result discarded intentionally to focus on the leak
    }
    // Missing: defer rows.Close()
}

func fixedQuery(db *sql.DB) {
    rows, err := db.Query(`SELECT id FROM tickets LIMIT 1`)
    if err != nil {
        slog.Error("query", "err", err)
        return
    }
    defer rows.Close() // The fix. Always the first line after the error check.

    if rows.Next() {
        var id int64
        rows.Scan(&id)
    }
    _ = rows.Err()
}

func main() {
    db, err := sql.Open("sqlite", ":memory:")
    if err != nil {
        slog.Error("open", "err", err)
        return
    }
    defer db.Close()

    // Cap the pool at 3 connections to make the symptom visible quickly.
    db.SetMaxOpenConns(3)

    if _, err := db.Exec(`CREATE TABLE tickets (id INTEGER PRIMARY KEY)`); err != nil {
        slog.Error("ddl", "err", err)
        return
    }
    for i := range 5 {
        db.Exec(`INSERT INTO tickets VALUES (?)`, i+1)
    }

    // Call the leaky version several times.
    for range 10 {
        leakyQuery(db)
    }
    fmt.Printf("after leaky queries  — open conns: %d  wait count: %d\n",
        db.Stats().OpenConnections, db.Stats().WaitCount)

    // Call the fixed version the same number of times.
    for range 10 {
        fixedQuery(db)
    }
    fmt.Printf("after fixed queries  — open conns: %d  wait count: %d\n",
        db.Stats().OpenConnections, db.Stats().WaitCount)
}

Run:

go run desk_leak.go

Output:

after leaky queries  — open conns: 3  wait count: 7
after fixed queries  — open conns: 1  wait count: 7

After the leaky calls the pool is fully occupied and callers had to wait. After the fixed calls the pool returns to a single idle connection. The WaitCount accumulator does not reset between runs — it shows the cumulative damage the leak already caused.

The boring rule

  • Always defer rows.Close() on the line immediately after checking the error from db.Query.
  • Always check rows.Err() after the rows.Next() loop.
  • Use db.QueryRow when you expect exactly one row. It calls Close internally.
  • Use transactions for any operation that touches more than one row or table.
  • Use prepared statements when the same SQL executes in a loop.
  • Call db.SetMaxOpenConns and db.SetMaxIdleConns explicitly in production; the unlimited default can exhaust database server connection limits.
  • Register the driver with a blank import (_ "modernc.org/sqlite") in main or TestMain, never in library packages.

Try this

  1. desk_list.go: Add a second status filter so the query returns both "open" and "in-progress" tickets. Use a SQL IN (?, ?) clause and pass both values as arguments.
  2. desk_bulk.go: Wrap the 100 prepared-statement inserts inside a single transaction. Time the wall-clock difference with time.Now() before and after the loop. Observe that SQLite is significantly faster when inserts share one transaction instead of auto-committing each row.
  3. desk_close.go: Add a reopenTicket function that mirrors closeTicket — it checks that the ticket is currently "closed", sets it back to "open", and writes a "reopened" audit row, all in one transaction.
  4. desk_leak.go: Remove db.SetMaxOpenConns(3) and re-run. Observe that WaitCount stays at zero because the unlimited pool never blocks. Add it back and lower the cap to 1 to see the leak starve all queries.