Day 52 — migrations & SQL tooling

Updated

July 30, 2026

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.