Day 20 — database/sql, Migrations & REST Design

Updated

July 30, 2025

Day 20 — database/sql, Migrations & REST Design

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.

Day 52 — migrations & SQL tooling

Stage VI · ~3h
Goal: Manage schema with versioned migrations, understand sqlc vs ORM trade-offs, and apply migrations cleanly in app startup and tests.

Why this day exists

CREATE TABLE IF NOT EXISTS is fine for Day 51 toys. Production schemas evolve:

  • Add columns, indexes, constraints
  • Backfill data
  • Coordinate multi-instance deploys

Without migrations you get “works on my machine” drift. Without a tooling philosophy you either drown in ORM magic or copy-paste SQL forever. Today freezes the mental model you will use through the capstone.

Theory 1 — Migration principles

  1. Forward versioned files — 0001_init.up.sql, 0002_add_email.up.sql
  2. Expand/contract for zero-downtime when needed (add nullable → backfill → constrain)
  3. Never edit applied migrations in shared environments—add a new one
  4. Down migrations optional; many teams prefer forward-fix only
  5. Run migrations as a separate step or gated startup with locking

Minimal migration table

CREATE TABLE IF NOT EXISTS schema_migrations (
  version TEXT PRIMARY KEY,
  applied_at TEXT NOT NULL
);

Record each applied version. The version string is usually the filename prefix (0001_init).

Expand / contract in one paragraph

Ship a migration that adds a nullable column while old code ignores it; deploy new code that writes both old and new; backfill; then a later migration enforces NOT NULL. Dropping columns requires the reverse: stop reading → deploy → drop. This is how two binary versions coexist during a rolling deploy.

Theory 2 — Lightweight DIY migrator (lab-friendly)

type Migration struct {
    Version string
    SQL     string
}

func Apply(ctx context.Context, db *sql.DB, migrations []Migration) error {
    if err := ensureTable(ctx, db); err != nil {
        return err
    }
    for _, m := range migrations {
        ok, err := applied(ctx, db, m.Version)
        if err != nil {
            return err
        }
        if ok {
            continue
        }
        tx, err := db.BeginTx(ctx, nil)
        if err != nil {
            return err
        }
        if _, err := tx.ExecContext(ctx, m.SQL); err != nil {
            _ = tx.Rollback()
            return fmt.Errorf("migration %s: %w", m.Version, err)
        }
        if _, err := tx.ExecContext(ctx,
            `INSERT INTO schema_migrations(version, applied_at) VALUES(?, ?)`,
            m.Version, time.Now().UTC().Format(time.RFC3339),
        ); err != nil {
            _ = tx.Rollback()
            return err
        }
        if err := tx.Commit(); err != nil {
            return err
        }
    }
    return nil
}

func ensureTable(ctx context.Context, db *sql.DB) error {
    _, err := db.ExecContext(ctx, `
CREATE TABLE IF NOT EXISTS schema_migrations (
  version TEXT PRIMARY KEY,
  applied_at TEXT NOT NULL
)`)
    return err
}

func applied(ctx context.Context, db *sql.DB, version string) (bool, error) {
    var n int
    err := db.QueryRowContext(ctx,
        `SELECT COUNT(1) FROM schema_migrations WHERE version = ?`, version,
    ).Scan(&n)
    return n > 0, err
}

Embed SQL with //go:embed migrations/*.sql for shippable binaries.

//go:embed migrations/*.sql
var migrationFS embed.FS

Production often uses golang-migrate, goose, atlas, or pressly/goose—pick one for real services; DIY teaches the model.

Theory 3 — sqlc vs ORM

Approach What you write Pros Cons
Raw database/sql SQL strings Full control, no codegen Boilerplate Scan
sqlc SQL → generated Go Type-safe, explicit SQL, fast Codegen step; SQL skill required
ORM (GORM, ent, …) Struct tags / DSLs Speed of CRUD Hidden queries, magic, migrations coupling

Expert recommendation for this volume

  • Prefer explicit SQL (raw or sqlc-style).
  • Use ORM only if you already know its failure modes.
  • Capstone default: database/sql + migrations + thin repo; sqlc welcome.

sqlc mental model

-- name: GetItem :one
SELECT id, name, description FROM items WHERE id = ?;

Generates GetItem(ctx, id) (Item, error) with correct types. You review SQL in PR diffs—good for teams.

ORM footguns (awareness)

  • N+1 queries via lazy loading
  • Auto-migrate in production by accident
  • Unbounded Find without limits
  • Struct tag soup
  • “Repository” that is really the ORM session leaking

Theory 4 — Migration + app lifecycle

deploy:
  1. migrate job (or init container)
  2. start N app replicas

local:
  go run ./cmd/migrate
  go run ./cmd/service

Avoid racing multiple app instances each trying complex non-transactional migrations without locks. SQLite DIY in a single process is fine for labs; multi-writer Postgres needs advisory locks or a dedicated migrate step.

// optional startup gate for single-instance tools
if os.Getenv("RUN_MIGRATIONS") == "1" {
    if err := migrate.Apply(ctx, db, all); err != nil {
        return err
    }
}

Theory 5 — Data migrations vs schema

  • Schema: add column, index, constraint
  • Data: UPDATE ... SET backfill

Sometimes split for safety (schema first, data in a controlled job). Always backup mindset on prod (even if lab skips). Never SELECT * assumptions after schema changes—name columns in app SQL.

Theory 6 — Ordering and naming

migrations/
  0001_init.sql
  0002_add_description.sql
  0003_items_name_unique.sql
  • Zero-pad so lexicographic order == apply order
  • One logical change per file when possible
  • Include index creation in the migration that needs it for correctness

Worked example — two-step migration set

migrations/0001_init.sql:

CREATE TABLE items (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL
);

migrations/0002_add_description.sql:

ALTER TABLE items ADD COLUMN description TEXT NOT NULL DEFAULT '';

Repo update: Scan description. Tests apply all migrations from empty DB.

func Load(fs embed.FS) ([]Migration, error) {
    entries, err := fs.ReadDir("migrations")
    if err != nil {
        return nil, err
    }
    sort.Slice(entries, func(i, j int) bool {
        return entries[i].Name() < entries[j].Name()
    })
    var out []Migration
    for _, e := range entries {
        if e.IsDir() || !strings.HasSuffix(e.Name(), ".sql") {
            continue
        }
        b, err := fs.ReadFile("migrations/" + e.Name())
        if err != nil {
            return nil, err
        }
        ver := strings.TrimSuffix(e.Name(), ".sql")
        out = append(out, Migration{Version: ver, SQL: string(b)})
    }
    return out, nil
}

Lab 1 — Setup

mkdir -p ~/lab/90daysofx/01-go/day52/migrations
cd ~/lab/90daysofx/01-go/day52
go mod init example.com/day52

Carry repo from Day 51 or reimplement a minimal items table + database/sql access.

Lab 2 — Implement Apply

  1. Embed or read migration files sorted by name.
  2. Apply to empty DB.
  3. Re-run Apply → no-op (idempotent).
  4. Test that schema_migrations has expected versions.
go test ./migrate/ -count=1 -race

Lab 3 — Evolve schema

Add description migration; update Create/Get; tests pass on fresh DB.

Simulate “old code” vs “new code” discussion in README: why expand/contract matters when two versions run together. One short paragraph is enough.

Lab 4 — Tool choice writeup

In NOTES.md or README section (≤1 page):

  1. Would you use sqlc, raw SQL, or ORM for a 5-table service? Why?
  2. One risk of auto-migrate.
  3. One benefit of SQL in PRs.

No wrong answer if reasoned.

Lab 5 — Optional sqlc hello

If you want codegen experience:

# install sqlc; write sqlc.yaml; sqlc generate

Generate GetItem only. Not required for gate. If you skip, write one sentence why raw SQL is enough for this lab week.

Common gotchas

Gotcha Fix
Editing old migrations Add new version
Non-deterministic file order Zero-pad numeric prefixes
DDL outside transaction (some DBs) Know engine limits
Auto-migrate prod casually Review + backup
App assumes schema without migrating Startup check / migrate job
Ignoring partial failure Transaction per migration when possible
SQLite vs Postgres placeholders ? vs $1; abstract or dual files
Forgetting indexes until prod hurts Add with the query that needs them

Checkpoint

  • Versioned migrations apply idempotently
  • Second migration evolves schema + code
  • Tests start from empty DB + migrations
  • Written sqlc vs ORM trade-off note
  • Can explain expand/contract in one paragraph
  • go test -race green on migrate package

Commit

git add .
git commit -m "day52: versioned migrations and sql tooling notes"

Write three personal gotchas before continuing.

Tomorrow

Day 53 — REST design: resource modeling, status codes, versioning, and a stdlib mux JSON API over your repository.

Day 53 — REST design

Stage VI · ~3h
Goal: Design a versioned JSON REST API on stdlib ServeMux, backed by your repository—clear resources, status codes, pagination basics, and consistent response envelopes.

Why this day exists

CRUD handlers without REST thinking become ad-hoc RPC-over-HTTP:

  • Verbs in paths (/getItem)
  • Always HTTP 200 with error in body
  • No resource identifiers
  • Breaking changes without versioning

Today you align HTTP semantics with domain operations so Day 54 auth and Day 55 errors attach cleanly.

Theory 1 — Resources, not actions

Prefer Avoid
GET /v1/items GET /v1/getItems
GET /v1/items/{id} GET /v1/item?id= only
POST /v1/items POST /v1/createItem
DELETE /v1/items/{id} POST /v1/items/delete

Collections vs items:

  • Collection: /v1/items
  • Member: /v1/items/{id}
  • Subresource: /v1/items/{id}/tags

Nouns scale; verb paths invent a private RPC dialect per team.

Theory 2 — Status code cheat sheet

Code When
200 OK with body
201 Created (often + Location header)
204 OK no body (delete/update)
400 Malformed / validation failed
401 Unauthenticated
403 Authenticated but not allowed
404 Missing resource
409 Conflict (duplicate id)
415 Unsupported Content-Type
422 Semantic validation (optional; some teams use 400)
429 Rate limit
500 Unexpected server bug
503 Dependency down

Do not invent “200 OK + {"success":false}” as the primary pattern. Clients, load balancers, and caches all understand status codes better than inventing a second protocol inside JSON.

Theory 3 — Versioning strategies

Strategy Example Notes
URL prefix /v1/items Simple, explicit—use this today
Header Accept: application/vnd.myapp.v1+json Flexible, harder to explore
No version /items OK until first break—still learn v1 habit

Breaking change → /v2. Additive fields are usually non-breaking if clients ignore unknown JSON keys. Removing or renaming fields is breaking.

Theory 4 — Response shapes

Success

{
  "id": "01H...",
  "name": "widget",
  "created_at": "2026-07-27T12:00:00Z"
}

List + pagination (basic)

{
  "items": [ ... ],
  "next_cursor": "abc",
  "limit": 20
}

Cursor pagination beats large OFFSET for big tables—mention even if you implement only limit today.

limit := 20
if v := r.URL.Query().Get("limit"); v != "" {
    n, err := strconv.Atoi(v)
    if err != nil || n < 1 {
        writeErr(w, http.StatusBadRequest, "bad_request", "invalid limit")
        return
    }
    if n > 100 {
        n = 100
    }
    limit = n
}

Errors (preview Day 55)

{
  "error": {
    "code": "not_found",
    "message": "item not found"
  }
}

Stay consistent across handlers—copy one helper, not six shapes.

Theory 5 — Idempotency & methods

Method Idempotent? Safe?
GET yes yes
PUT yes no
DELETE yes no
POST no (usually) no
PATCH often conditional no

PUT can mean “replace resource at id” (client-supplied id). POST “create under collection” (server id). Pick and document.

Idempotency keys for POST (stretch): client sends Idempotency-Key; server stores request hash → response for replay. Awareness is enough today.

Theory 6 — DTO vs domain vs DB

HTTP JSON (DTO)  ↔  domain/service  ↔  SQL row

Do not leak DB column names or internal flags to clients without intent. Map explicitly:

type ItemDTO struct {
    ID        string `json:"id"`
    Name      string `json:"name"`
    CreatedAt string `json:"created_at"`
}

func toDTO(it store.Item) ItemDTO {
    return ItemDTO{
        ID: it.ID, Name: it.Name,
        CreatedAt: it.CreatedAt.UTC().Format(time.RFC3339),
    }
}

Worked example — wiring repo to HTTP

func (s *Server) routes() {
    s.mux.HandleFunc("GET /v1/items", s.listItems)
    s.mux.HandleFunc("POST /v1/items", s.createItem)
    s.mux.HandleFunc("GET /v1/items/{id}", s.getItem)
    s.mux.HandleFunc("DELETE /v1/items/{id}", s.deleteItem)
    s.mux.HandleFunc("GET /healthz", s.health)
}

func (s *Server) getItem(w http.ResponseWriter, r *http.Request) {
    id := r.PathValue("id")
    it, err := s.repo.Get(r.Context(), id)
    if err != nil {
        if errors.Is(err, store.ErrNotFound) {
            writeErr(w, http.StatusNotFound, "not_found", "item not found")
            return
        }
        s.log.Error("get item", "err", err, "id", id)
        writeErr(w, http.StatusInternalServerError, "internal", "internal error")
        return
    }
    writeJSON(w, http.StatusOK, toDTO(it))
}

func (s *Server) createItem(w http.ResponseWriter, r *http.Request) {
    var req struct {
        Name string `json:"name"`
    }
    if err := readJSON(r, &req); err != nil {
        writeErr(w, http.StatusBadRequest, "bad_request", err.Error())
        return
    }
    if strings.TrimSpace(req.Name) == "" {
        writeErr(w, http.StatusBadRequest, "validation", "name is required")
        return
    }
    id := newID()
    if err := s.repo.Create(r.Context(), id, req.Name); err != nil {
        s.log.Error("create item", "err", err)
        writeErr(w, http.StatusInternalServerError, "internal", "internal error")
        return
    }
    w.Header().Set("Location", "/v1/items/"+id)
    writeJSON(w, http.StatusCreated, ItemDTO{ID: id, Name: req.Name})
}

Helpers (readJSON, writeJSON, writeErr) should live once in the api package.

Lab 1 — Compose service

mkdir -p ~/lab/90daysofx/01-go/day53
cd ~/lab/90daysofx/01-go/day53
go mod init example.com/day53

Integrate: migrations (Day 52) + repo (Day 51) + HTTP (Day 45 style) + slog. Prefer real SQLite file or :memory: with migrations applied at startup for local demo.

Lab 2 — REST compliance checklist

For each endpoint, document in README:

  • Method + path
  • Request body schema
  • Success status + body
  • Error statuses

Implement list with ?limit= (default 20, max 100).

Lab 3 — Tests as contract

httptest tables:

  • Create → 201 + Location
  • Get → 200
  • Get missing → 404 + error code
  • Invalid JSON → 400
  • List limit enforcement (101 → clamped or 400—document which)
go test ./api/ -race -count=1

Lab 4 — Conflict path

Unique name constraint (optional unique index migration). Second create → 409 with code=conflict. Map store.ErrConflict carefully; never return raw SQL UNIQUE text to clients.

Lab 5 — Curl script

scripts/smoke.sh exercising happy path. Runnable after go run:

#!/usr/bin/env bash
set -euo pipefail
base=${1:-http://127.0.0.1:8080}
curl -sf "$base/healthz" >/dev/null
id=$(curl -sf -X POST "$base/v1/items" -H 'Content-Type: application/json' \
  -d '{"name":"smoke"}' | jq -r .id)
curl -sf "$base/v1/items/$id" | jq .
echo OK

Common gotchas

Gotcha Fix
RPC path names Resource nouns
200 for errors Proper status codes
Leaking SQL errors to clients Map to stable codes
Unbounded list limit/cursor
Mixing v0 unversioned breakages /v1 prefix now
Wrong method semantics Follow table
Forgetting Location on 201 Set header
Scanning into DTO from SQL directly Map at boundary

Checkpoint

  • Versioned routes under /v1
  • Status codes match cheat sheet
  • Repo errors mapped to HTTP
  • Pagination limit enforced
  • Contract tests green
  • README endpoint table

Commit

git add .
git commit -m "day53: versioned REST JSON API over repository"

Write three personal gotchas before continuing.

Tomorrow

Day 54 — auth basics: protect routes with sessions or JWT—authentication vs authorization, middleware, and safe defaults.