Weekly Review — Month 4 · Week 2 (Days 092–098)

Journal index · Roadmap › this week

📅 The Week in One Line

Everything behind the HTTP handler: connecting to Postgres and pooling (database/sql, pgx/pgxpool), type-safe queries (sqlc), schema versioning (golang-migrate), and structuring it all behind a repository with atomic, context-aware transactions.

✅ What I Completed

• Day 092 — database/sql basics: drivers, lazy Open+Ping, the *sql.DB pool, Scan, rows.Err, placeholders, NULLs
• Day 093 — pgx & pgxpool: native vs database/sql modes, Acquire/Release vs direct pool calls, DSN, pool sizing
• Day 094 — sqlc: write-SQL/generate-Go, :one/:many/:exec annotations, compile-time safety, no ORM
• Day 095 — golang-migrate: ordered up/down files, schema_migrations, idempotence, embed.FS, the dirty-state trap
• Day 096 — repository pattern: consumer-side interface, domain errors, return copies, testable fakes
• Day 097 — transactions & context: BeginTx, the deferred-Rollback WithTx helper, isolation levels, cancellation
• Day 098 — review + closed-book recall
• Examples: pool, migrate, repository, txn (stdlib-only, runnable)
• Exercises solved: 3 (migrationplan, inmemrepo, txstore)

💡 Lessons Learned

• sql.Open does NOT connect — it's lazy; PingContext to verify connectivity at startup.
• A *sql.DB / pgxpool.Pool is a long-lived, concurrency-safe POOL; create once, share, and cap its size below Postgres's max_connections.
• After a rows.Next() loop you must still check rows.Err() — Next()==false covers both end-of-rows and a mid-iteration error.
• sqlc is a code generator, not an ORM: real SQL in, type-safe Go out, with errors caught at generate-time.
• Never edit an applied migration; append a new one. A half-failed migration goes "dirty" and must be fixed then forced.
• Define repository interfaces on the consumer side ("accept interfaces, return structs"), translate driver errors into domain ErrNotFound/ErrConflict, and return value copies — not pointers into the store.
• defer tx.Rollback() right after BeginTx is the safety rule: it no-ops after a successful Commit and undoes everything on any error/panic/timeout. Always check Commit's error.
• Run statements on tx, not db, or they hit different pooled connections and aren't atomic.

💪 Strengths (what clicked)

• The WithTx(ctx, db, fn) deferred-Rollback pattern — it finally feels automatic.
• Mapping driver errors to domain sentinels at the repository boundary and matching with errors.Is.
• The five-layer mental model: migrate → sqlc → pgx/pool → repository → transactions, all threaded by context.

🧩 Weaknesses (what's still fuzzy)

• Retrying serialization failures (40001) under Repeatable Read / Serializable — number of attempts and backoff.
• Dynamic SQL with sqlc (optional filters, variable-length IN); when to drop to hand-written queries.
• Connection poolers (PgBouncer) and which pgx features (prepared statements, session state) break in transaction-pooling mode.

🔁 Spaced-Repetition Re-quiz (topics from earlier weeks)

1. Q: (M1) What does string(65) produce and why?
   A

   "A" — int→rune to code point U+0041, not "65". Use strconv.Itoa for the digits.
2. Q: (M2) What does %w in fmt.Errorf do?
   A

   Wraps an error so errors.Is/errors.As can unwrap and match it — the basis for this week's domain-error translation.
3. Q: (M3 W2) Why must wg.Add precede go?
   A

   Add inside the goroutine races with Wait, which could return early on a still-zero counter.
4. Q: (M4 W1) Set Content-Type before or after WriteHeader?
   A

   Before — headers freeze once the body starts flushing; setting it after is silently ignored.
5. Q: (M4 W2) Does sql.Open establish a connection?
   A

   No — it's lazy; the first connection happens on first use. Call db.PingContext to verify eagerly.

🎯 Action Items

• Write a generic retryTx wrapper that retries on Postgres 40001 with bounded attempts + jittered backoff.
• Rebuild the Week 1 widgets API's storage layer on a pgxpool.Pool + a repository, swapping the in-memory store with no handler changes.
• Add embedded embed.FS migrations run at startup, and verify idempotence by booting twice.
• Spike sqlc's sqlc.slice() / = ANY($1) for variable-length IN filters.

🚀 Next Week Goals

• Assemble the pieces into a real service: configuration, structured logging, and observability around the data layer.
• Test the data layer: in-memory fakes for unit tests, and integration tests against a throwaway Postgres.
• Wire request context end-to-end: HTTP handler → service → repository → transaction, with timeouts and cancellation.

📊 Metrics

Hours	Days hit	Exercises	Commits	Avg confidence
9	7/7	3	7	3.⅗

Suggested commit: docs(journal): month 4 week 2 review