Lesson 05 — SQLite, migrations, and repositories

Outcome

Persist Projects/Tasks safely in SQLite, survive restart, and establish migration discipline.

Why this comes now

We now have real state worth persisting. Adding SQLite after domain rules lets storage conform to the model rather than define it accidentally.

Understand

SQLite is an in-process transactional database, but database/sql may use multiple connections. Connection-scoped PRAGMAs must therefore be configured correctly for every connection (the driver DSN is the preferred route here). Starting defaults: foreign keys ON, WAL, synchronous NORMAL, and a reasonable busy timeout.

Migrations are permanent release history. Repository methods translate between storage rows and domain values; they do not decide business transitions.

Build the real project

1. Add modernc.org/sqlite.
2. Implement DB open and XDG data-path helper.
3. Add embedded ordered migrations and schema_migrations.
4. Create initial Project/Task/Acceptance Criterion tables with foreign keys.
5. Configure SQLite pragmas through the DSN/driver-supported mechanism.
6. Add repository methods and transaction helpers.
7. Add migration idempotence, foreign-key, and restart persistence tests.
8. Decide whether to introduce sqlc now or after a few hand-written queries; either is acceptable if SQL remains explicit.

Completion gate

A Task created through the application/repository survives DB close/reopen. Foreign-key enforcement is proven in a test. Re-running migrations changes nothing. go test ./... is green.

Pitfalls to avoid

Do not run one PRAGMA foreign_keys=ON on a pooled connection and assume all connections are configured. Do not edit an already-released migration later. Do not put raw provider transcripts in SQLite.

References

Required: modernc.org/sqlite, SQLite WAL, SQLite foreign keys. Optional: sqlc SQLite tutorial.

Checkpoint

Advance to M2. Note any storage constraint that exposed a flaw in the domain model and fix the canonical docs now, not later.