Day 51 — database/sql

Updated

July 30, 2026

Day 51 — database/sql

Stage VI · ~3h
Goal: Use Go’s portable database/sql correctly—connection pooling, context-aware queries, scanning/sql.Null*, and a small repository with tests (SQLite in-memory is fine; Postgres welcome).

Why this day exists

Stage V services forgot data when the process died. Real systems persist. database/sql is the stdlib façade over drivers:

  • You write SQL (or generate it later with sqlc)
  • The pool manages connections
  • Context cancels in-flight queries

ORMs are optional later; everyone needs to understand *sql.DB.

Theory 1 — Driver + Open

import (
    "database/sql"
    _ "github.com/jackc/pgx/v5/stdlib" // Postgres example
    // OR
    _ "modernc.org/sqlite" // pure Go SQLite
)

db, err := sql.Open("sqlite", "file:app.db?_pragma=foreign_keys(1)")
// Open does not verify connectivity — Ping does:
if err := db.PingContext(ctx); err != nil {
    return err
}

Pool settings

db.SetMaxOpenConns(10)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(30 * time.Minute)
db.SetConnMaxIdleTime(5 * time.Minute)

Defaults are not always production-safe; set them deliberately.

Setting Meaning
MaxOpenConns Cap concurrent DB connections
MaxIdleConns Keep warm conns
ConnMaxLifetime Recycle to avoid load balancer timeouts
ConnMaxIdleTime Drop idle sooner

Theory 2 — Query APIs

// single row
err := db.QueryRowContext(ctx,
    `SELECT id, name FROM items WHERE id = ?`, id,
).Scan(&item.ID, &item.Name)

// many rows
rows, err := db.QueryContext(ctx, `SELECT id, name FROM items`)
if err != nil {
    return err
}
defer rows.Close()
for rows.Next() {
    var it Item
    if err := rows.Scan(&it.ID, &it.Name); err != nil {
        return err
    }
    out = append(out, it)
}
return rows.Err()

Exec

res, err := db.ExecContext(ctx,
    `INSERT INTO items(id, name) VALUES(?, ?)`, id, name)
n, _ := res.RowsAffected()

Always prefer *Context variants.

Placeholders

Driver family Placeholder
SQLite / MySQL ?
Postgres $1, $2, …

pgx stdlib uses $n. Do not copy SQLite SQL blindly onto Postgres.

Theory 3 — Nullability

Go zero values cannot express SQL NULL for strings/bools cleanly.

var description sql.NullString
err := row.Scan(&description)
if description.Valid {
    s := description.String
    _ = s
}

Types: NullString, NullInt64, NullFloat64, NullBool, NullTime (and sql.Null[T] in newer Go).

Or scan into *string with driver support—be consistent.

Theory 4 — Transactions

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

if _, err := tx.ExecContext(ctx, `...`); err != nil {
    return err
}
return tx.Commit()

Use transactions for multi-statement atomicity (create order + line items).

Theory 5 — Repository pattern

type ItemRepo struct {
    db *sql.DB
}

func (r *ItemRepo) Get(ctx context.Context, id string) (Item, error) {
    var it Item
    err := r.db.QueryRowContext(ctx,
        `SELECT id, name, created_at FROM items WHERE id = ?`, id,
    ).Scan(&it.ID, &it.Name, &it.CreatedAt)
    if errors.Is(err, sql.ErrNoRows) {
        return it, ErrNotFound
    }
    return it, err
}

Map sql.ErrNoRows to domain ErrNotFound. Keep SQL in one layer.

Worked example — SQLite schema + CRUD

const schema = `
CREATE TABLE IF NOT EXISTS items (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL
);
`

func Migrate(ctx context.Context, db *sql.DB) error {
    _, err := db.ExecContext(ctx, schema)
    return err
}

func (r *ItemRepo) Create(ctx context.Context, id, name string) error {
    _, err := r.db.ExecContext(ctx,
        `INSERT INTO items(id, name, created_at) VALUES(?, ?, ?)`,
        id, name, time.Now().UTC().Format(time.RFC3339Nano),
    )
    return err
}

For tests:

db, err := sql.Open("sqlite", "file:test.db?mode=memory&cache=shared")
// or modernc DSN per driver docs

Lab 1 — Module setup

mkdir -p ~/lab/90daysofx/01-go/day51
cd ~/lab/90daysofx/01-go/day51
go mod init example.com/day51
# pick one:
go get modernc.org/sqlite
# or: go get github.com/jackc/pgx/v5/stdlib

Document driver choice in README.

Lab 2 — Repository CRUD

Implement:

  • Create
  • Get
  • List
  • Delete
  • Migrate

Table tests against in-memory SQLite (or testcontainers later on Day 61).

func TestItemRepo(t *testing.T) {
    db := openTestDB(t)
    repo := &ItemRepo{db: db}
    ctx := context.Background()
    if err := repo.Create(ctx, "1", "widget"); err != nil {
        t.Fatal(err)
    }
    it, err := repo.Get(ctx, "1")
    if err != nil || it.Name != "widget" {
        t.Fatalf("%+v %v", it, err)
    }
}

Lab 3 — ErrNoRows mapping

Get missing id → errors.Is(err, ErrNotFound). Never leak raw sql.ErrNoRows to HTTP layer without mapping (HTTP mapping is Day 53+; define domain error today).

Lab 4 — Pool / Ping

On startup path: PingContext with timeout. Unit test optional; manual check fine.

ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
defer cancel()
if err := db.PingContext(ctx); err != nil {
    log.Error("db", "err", err)
    os.Exit(1)
}

Lab 5 — Transaction transfer (stretch)

Two accounts table; Transfer(ctx, from, to, amount) in a transaction; test rollback on insufficient funds.

Common gotchas

Gotcha Fix
Forgetting rows.Close defer rows.Close()
Ignoring rows.Err Check after loop
Open without Ping Ping on startup
Holding transactions open during HTTP calls out Keep tx short
Wrong placeholders for driver Match driver
Scanning NULL into string sql.NullString
Unbounded MaxOpenConns Set pool caps
Using DB after Close Lifecycle ownership clear

Checkpoint

  • Migrate + CRUD repo works
  • All queries use Context
  • ErrNotFound mapping tested
  • Pool settings set intentionally
  • rows.Close + rows.Err correct
  • Driver choice documented

Commit

git add .
git commit -m "day51: database/sql repository pooling and CRUD"

Write three personal gotchas before continuing.

Tomorrow

Day 52 — migrations & tooling: versioned schema changes, and a deliberate sqlc vs ORM trade-off—not cargo-cult.