Day 108 — Project: Repository & Migrations

Month 4 · Week 4 · ⬅ Day 107 · Day 109 ➡ · Journal index

🎯 Learning Objective

Implement the persistence layer of the capstone: a SQL-backed repository that satisfies the service's Repo interface, plus versioned, forward-only migrations to create the schema.

📚 Topics

• database/sql with QueryRowContext / ExecContext and context
• Mapping driver errors to domain errors (sql.ErrNoRows → ErrNotFound)
• Versioned migrations (golang-migrate / goose) vs hand-rolled runner
• Unique constraints, RETURNING id, and conflict handling

📖 Reading / Sources

• database/sql package docs
• Go wiki — database/sql best practices (avoiding common pitfalls)
• golang-migrate · pressly/goose
• jackc/pgx — the recommended Postgres driver

📝 Notes

• The repository is the only package that imports a driver. It implements the service's Repo interface, so business logic never sees SQL → [[dependency-inversion]].
• Always use the ...Context methods (QueryRowContext, ExecContext, BeginTx) and pass the request's ctx, so a cancelled HTTP request also cancels the query → [[context-first-param]].
• Translate at the boundary: if errors.Is(err, sql.ErrNoRows) { return ErrNotFound }. A Postgres unique-violation (23505) becomes ErrConflict. Callers stay driver-agnostic → [[error-wrapping]].
• Use RETURNING id (Postgres) to get the generated key in one round trip instead of a second SELECT.
• Migrations are forward-only and versioned: numbered NNNN_name.up.sql / .down.sql files applied in order, recorded in a schema_migrations table so each runs once. Never edit an applied migration — add a new one → [[migrations]].
• *sql.DB is a pool, not a single connection; it's safe for concurrent use and you keep one for the app's lifetime. Configure SetMaxOpenConns/SetConnMaxLifetime (Day 093) → [[connection-pool]].
• Every Rows must be Close()d (defer it) and you must check rows.Err() after the loop, or you leak connections and miss late errors.
• sqlc generates type-safe Go from SQL; golang-migrate runs the schema. They are third-party tools — the concepts are modelled stdlib-only in examples/month-04/repository and examples/month-04/migrate.

💻 Code Examples

// Postgres adapter implementing the service's Repo interface (needs pgx/stdlib).
func (r *PostgresRepo) Get(ctx context.Context, id int) (User, error) {
    const q = `SELECT id, email FROM users WHERE id = $1`
    var u User
    err := r.db.QueryRowContext(ctx, q, id).Scan(&u.ID, &u.Email)
    switch {
    case errors.Is(err, sql.ErrNoRows):
        return User{}, fmt.Errorf("get %d: %w", id, ErrNotFound) // domain error, not driver error
    case err != nil:
        return User{}, fmt.Errorf("get %d: %w", id, err)
    }
    return u, nil
}

// 0001_users.up.sql — a forward migration, applied once and recorded.
// CREATE TABLE users (
//     id    BIGSERIAL PRIMARY KEY,
//     email TEXT NOT NULL UNIQUE
// );

Stdlib analogues you can run: examples/month-04/repository and examples/month-04/migrate.

🏋️ Exercises / Practice

Exercise	Status	Link
Service over the Repo interface (fake adapter)	✅	exercises/month-04/week-4/service
(Concept) repository + migrate runner	✅	examples/month-04/repository · examples/month-04/migrate

🐛 Mistakes Made

• Returned sql.ErrNoRows straight from the repo; the handler then couldn't map it cleanly. Wrapped it as ErrNotFound at the boundary.
• Forgot defer rows.Close() in a List query and leaked pooled connections under load. Added the defer and a rows.Err() check.

❓ Open Questions

• Embed migrations with //go:embed and run them on startup, or run them as a separate CI step? (Leaning embed + explicit migrate up command, not auto-run on boot.)

🧠 Active Recall (answer without looking)

1. Q: Why must the repository translate sql.ErrNoRows before returning it?

A

So callers depend on a stable domain error (`ErrNotFound`) instead of a driver detail. Swapping Postgres for SQLite then doesn't ripple into the service or handler.

1. Q: What makes a migration "forward-only and versioned," and why never edit an applied one?

A

Each migration has a unique increasing version recorded in `schema_migrations` and runs exactly once. Editing an applied migration would diverge environments that already ran the old version — add a new migration instead.

🪶 Feynman Reflection

The repository is a power adapter: the service speaks one plug shape (the Repo interface and domain errors), and the adapter converts that to whatever the wall socket (Postgres, SQLite, a map) provides. Migrations are the building's wiring history — an append-only log of changes so any site can be brought up to the current schema by replaying the steps it hasn't seen.

🕳️ Knowledge Gaps

• pgx native interface vs database/sql compatibility layer — performance/feature trade-offs. Revisit before choosing in production.

✅ Summary

I implemented a context-aware SQL repository that hides the driver behind domain errors, and set up forward-only versioned migrations for the schema.

⏭️ Next Steps / Prep for Tomorrow

• Day 109: integration tests that boot the whole app against a real database with testcontainers.

---

Time spent	Difficulty	Confidence
90 min	🟦🟦⬜⬜⬜	🟦🟦🟦⬜⬜

Suggested commit: feat(project): SQL repository and migrations (day 108)