diff options
| -rw-r--r-- | docs/superpowers/plans/2026-04-07-sql-migrations.md | 231 |
1 files changed, 231 insertions, 0 deletions
diff --git a/docs/superpowers/plans/2026-04-07-sql-migrations.md b/docs/superpowers/plans/2026-04-07-sql-migrations.md new file mode 100644 index 0000000..60b2848 --- /dev/null +++ b/docs/superpowers/plans/2026-04-07-sql-migrations.md @@ -0,0 +1,231 @@ +# SQL Migrations Implementation Plan + +> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking. + +**Goal:** Replace the inline `CREATE TABLE IF NOT EXISTS` block in `internal/db/db.go` with versioned SQL files managed by `golang-migrate/migrate`, applied automatically at startup. + +**Architecture:** Migration files live in `internal/db/migrations/` and are embedded into the binary via `embed.FS`. `db.Open` calls `migrate.Up()` after opening the database, ensuring the schema is always current before any caller uses `*DB`. golang-migrate tracks applied versions in a `schema_migrations` table it manages automatically. + +**Tech Stack:** `github.com/golang-migrate/migrate/v4`, `database/sqlite` (modernc.org/sqlite adapter), `source/iofs` (embed.FS source) + +--- + +## File Map + +| File | Action | Purpose | +|---|---|---| +| `internal/db/migrations/000001_init.up.sql` | Create | Full current schema | +| `internal/db/migrations/000001_init.down.sql` | Create | Drop all tables in reverse order | +| `internal/db/db.go` | Modify | Add `//go:embed`, wire up migrate, remove inline `Exec` block | +| `go.mod` / `go.sum` | Modify | Add golang-migrate packages | + +--- + +### Task 1: Add golang-migrate dependency + +**Files:** +- Modify: `go.mod`, `go.sum` + +- [ ] **Step 1: Fetch the packages** + +```bash +go get github.com/golang-migrate/migrate/v4 +go get github.com/golang-migrate/migrate/v4/database/sqlite +go get github.com/golang-migrate/migrate/v4/source/iofs +``` + +- [ ] **Step 2: Verify** + +```bash +go list -m github.com/golang-migrate/migrate/v4 +``` + +Expected output: `github.com/golang-migrate/migrate/v4 v4.x.x` + +- [ ] **Step 3: Commit** + +```bash +git add go.mod go.sum +git commit -m "chore: add golang-migrate dependency" +``` + +--- + +### Task 2: Create migration SQL files + +**Files:** +- Create: `internal/db/migrations/000001_init.up.sql` +- Create: `internal/db/migrations/000001_init.down.sql` + +- [ ] **Step 1: Create the up migration** + +`internal/db/migrations/000001_init.up.sql`: + +```sql +CREATE TABLE pages ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + path TEXT NOT NULL UNIQUE, + html_path TEXT NOT NULL, + title TEXT NOT NULL DEFAULT '', + date TEXT DEFAULT '', + tags TEXT DEFAULT '[]', + updated_at DATETIME DEFAULT CURRENT_TIMESTAMP +); +CREATE INDEX idx_pages_path ON pages(path); +CREATE INDEX idx_pages_date ON pages(date); + +CREATE TABLE posts ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + slug TEXT NOT NULL UNIQUE, + title TEXT NOT NULL DEFAULT '', + date TEXT DEFAULT '', + tags TEXT DEFAULT '[]', + draft INTEGER NOT NULL DEFAULT 0, + blocks TEXT NOT NULL DEFAULT '[]', + updated_at INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') * 1000000 as integer)) +); +CREATE INDEX idx_posts_slug ON posts(slug); +CREATE INDEX idx_posts_date ON posts(date); + +CREATE TABLE redirects ( + from_slug TEXT PRIMARY KEY, + to_slug TEXT NOT NULL +); + +CREATE TABLE settings ( + key TEXT PRIMARY KEY, + value TEXT NOT NULL DEFAULT '' +); + +CREATE VIRTUAL TABLE pages_fts USING fts5( + path UNINDEXED, + title, + content, + tokenize = 'porter unicode61' +); +``` + +Note: `IF NOT EXISTS` is intentionally absent — golang-migrate guarantees each migration runs exactly once. + +- [ ] **Step 2: Create the down migration** + +`internal/db/migrations/000001_init.down.sql`: + +```sql +DROP TABLE IF EXISTS pages_fts; +DROP TABLE IF EXISTS settings; +DROP TABLE IF EXISTS redirects; +DROP TABLE IF EXISTS posts; +DROP TABLE IF EXISTS pages; +``` + +- [ ] **Step 3: Commit** + +```bash +git add internal/db/migrations/ +git commit -m "feat: add initial schema migration files" +``` + +--- + +### Task 3: Wire golang-migrate into db.Open + +**Files:** +- Modify: `internal/db/db.go` + +- [ ] **Step 1: Run existing tests to confirm baseline** + +```bash +go test ./internal/db/... -v +``` + +Expected: all tests pass. + +- [ ] **Step 2: Replace db.go** + +Full new content of `internal/db/db.go`: + +```go +package db + +import ( + "database/sql" + "embed" + + "github.com/golang-migrate/migrate/v4" + sqlitedriver "github.com/golang-migrate/migrate/v4/database/sqlite" + "github.com/golang-migrate/migrate/v4/source/iofs" + _ "modernc.org/sqlite" +) + +//go:embed migrations +var migrationsFS embed.FS + +type DB struct { + db *sql.DB +} + +func Open(path string) (*DB, error) { + sqldb, err := sql.Open("sqlite", path) + if err != nil { + return nil, err + } + // SQLite works best with a single writer; also ensures in-memory DBs + // (used in tests) share one connection across the migrate driver and + // all application queries. + sqldb.SetMaxOpenConns(1) + + if err := runMigrations(sqldb); err != nil { + sqldb.Close() + return nil, err + } + return &DB{db: sqldb}, nil +} + +func runMigrations(sqldb *sql.DB) error { + src, err := iofs.New(migrationsFS, "migrations") + if err != nil { + return err + } + driver, err := sqlitedriver.WithInstance(sqldb, &sqlitedriver.Config{}) + if err != nil { + return err + } + m, err := migrate.NewWithInstance("iofs", src, "sqlite", driver) + if err != nil { + return err + } + if err := m.Up(); err != nil && err != migrate.ErrNoChange { + return err + } + return nil +} + +func (d *DB) Close() error { return d.db.Close() } + +// RawDB returns the underlying *sql.DB for advanced queries. +func (d *DB) RawDB() *sql.DB { return d.db } +``` + +- [ ] **Step 3: Run tests** + +```bash +go test ./internal/db/... -v +``` + +Expected: all tests pass (the `openTestDB` helper uses `:memory:`, `SetMaxOpenConns(1)` ensures the migrate driver and app queries share the same connection). + +- [ ] **Step 4: Build to confirm no compile errors** + +```bash +go build ./cmd/iblog +``` + +Expected: exits with code 0, no output. + +- [ ] **Step 5: Commit** + +```bash +git add internal/db/db.go +git commit -m "feat: wire golang-migrate into db.Open, remove inline schema" +``` |