--- name: "database_migrations" description: "Write, test, and apply reversible database schema migrations: up/down migration anatomy, numbering conventions, apply order, rollback strategy, expand/migrate/contract zero-downtime patterns, and migration testing. Use when asked to design a migration, rename a column safely, roll back a schema change, or review a schema-change plan." metadata: { "includeInPrompt": true } --- Differs from agent-git because: agent-git versions *code* with commits; this skill versions *schema* with ordered up/down change sets tracked in a `schema_migrations` table. Differs from etl-pipelines because that moves *data between systems*; this changes the *shape of one system's tables* with reversible, ordered, tested steps. ## Purpose Ship database schema changes safely: write one-change migrations with matching up/down halves, number them so apply order is deterministic, apply them in order with a tracking table, test them against fresh and seeded copies before production, and roll back or roll forward deliberately when something breaks. ## Workflow ### 1. Anatomy of a migration One migration = one logical change (add a column, rename a column, add an index). Two halves: ```sql -- up (apply: moves schema forward) ALTER TABLE users ADD COLUMN handle TEXT; UPDATE users SET handle = username WHERE handle IS NULL; -- down (rollback: restores the previous schema) ALTER TABLE users DROP COLUMN handle; ``` - Name files `_.sql` where `` sorts chronologically. - The runner below splits on `^-- up$` / `^-- down$` markers. ### 2. Numbering conventions - **Timestamps** (`20260922081031_create_users.sql`): default for teams; collisions across branches are rare and merges never need renumbering. - **Sequential** (`0001_create_users.sql`): simpler to read, but two branches adding `0004` will collide and need a manual fix at merge time. - Either way, the version is recorded in a `schema_migrations` table so each migration applies exactly once; apply order = filename order. ### 3. Write → review → test → apply → verify 1. **Write** the up and down halves. If you can't write a sensible down half, the change is destructive — see rule 4. 2. **Review** the down half as carefully as the up half. An untested rollback is a wish, not a plan. 3. **Test on a copy**: fresh DB from scratch, plus a seeded copy with realistic data. Check both. 4. **Apply in order**, one environment at a time (dev → staging → prod), verifying after each step. 5. **Verify**: row counts, a canary query against the changed table, and `SELECT version FROM schema_migrations` matches what you expected. ### 4. Rollback strategy: down vs roll forward - **Roll back** when the migration hasn't shipped to production yet, or the down half is loss-free (adding a column, creating an index). - **Roll forward** (write a new migration that fixes it) when production data has already flowed through the new schema and a down half would destroy rows — e.g. a dropped column can't be un-dropped with its data. Losing data to make a schema "clean" is never a rollback. - **Destructive-change rules**: never drop a column or table in one step. Deprecate it first (stop writing, stop reading, observe), then drop in a later migration. A down half that says `DROP TABLE users` with no recovery path is a red flag. ### 5. Zero-downtime pattern: expand → migrate → contract When old and new app code run simultaneously during a deploy, a single rename migration breaks one of them. Split it across three migrations, deployed with the app changes interleaved: 1. **EXPAND** — add the new thing without removing the old: `ALTER TABLE users ADD COLUMN handle TEXT;` then backfill from `username`. App code: write to both, read from old. 2. **MIGRATE** — backfill any rows written since expand (`UPDATE users SET handle = username WHERE handle IS NULL;`), then add the constraint (`handle NOT NULL`). App code: write to both, read from new. 3. **CONTRACT** — remove the old: `ALTER TABLE users DROP COLUMN username;`. App code: new only. A fully worked example (rename `username` → `handle`, verified below) ships with the runner. ### 6. Testing migrations - **From scratch**: apply every migration to an empty database; the final schema must match expectations. - **On a seeded copy**: copy a production-shaped database, apply, confirm row counts and spot-check data. - **Idempotency**: re-running apply must be a no-op (the tracking table prevents double-apply). - **Backfill for new NOT NULL columns**: add the column nullable → backfill existing rows → then add the constraint. Adding NOT NULL to a populated table in one step fails or locks, depending on the database. (This runner caught exactly that bug during testing: rows inserted between the expand and migrate steps had NULL handles, so the migrate step re-backfills before enforcing NOT NULL.) - **Down paths**: roll back each destructive-adjacent migration at least once on the seeded copy and confirm data survives. ## Output Contract When this skill is invoked, produce (in this order): 1. The migration list: version, short name, one-line description, destructive or not. 2. The up/down SQL (or change sets) for each, ready to save as `_.sql`. 3. The apply plan: order, which environments, and what the verification query is per step. 4. The rollback decision: down vs roll forward, per migration, with the reason. 5. A testing checklist: from-scratch result, seeded-copy result, idempotency check, backfill check, down-path check — mark each done or skipped (skipped needs a reason). ## Operating Rules 1. One logical change per migration. Never bundle an unrelated alter into the same file. 2. Every migration has a down half, or an explicit written reason why rolling forward is the only option. 3. Never drop a column or table in the same migration that removes its last reader — deprecate first, drop later. 4. Timestamp-based versions by default; sequential only if the team's repo history already uses it. 5. Apply order is filename order, tracked in `schema_migrations`. Never hand-edit the tracking table to skip a migration. 6. Test from scratch AND on a seeded copy before touching production. Untested rollbacks don't count as rollbacks. 7. Don't invent tool commands. Ecosystem tools, described generically (confirm exact commands against your installed version): - **Alembic** (Python/SQLAlchemy): Python migration scripts with `upgrade()`/`downgrade()` functions, autogenerate support. - **Flyway** (JVM): versioned SQL files (`V1__create_users.sql`), checksum validation, repeatable migrations. - **golang-migrate** (Go): paired `_.up.sql` / `.down.sql` files, per-database drivers. - **prisma migrate** (Node): schema-declared models, generated SQL migration history under `prisma/migrations/`. - **Django migrations** (Python): auto-generated from model changes, `makemigrations`/`migrate`, dependency graph. 8. When no migration tooling exists yet, use the dependency-free runner below — it was executed end-to-end (Python 3.12, stdlib `sqlite3` only, sqlite3 CLI 3.45.1 for inspection). ### Inlined runner (dependency-free) Save as `run_migrations.py` in a directory containing a `migrations/` folder, then run it. ```python #!/usr/bin/env python3 """Dependency-free migration runner (stdlib only): apply / rollback / status. Migration files live in ./migrations/ and are named _.sql, where sorts chronologically (timestamps like 20260922081031). Each file has -- up and -- down sections of plain SQL. """ import os, re, sqlite3, sys DB = os.environ.get("DEMO_DB", "/tmp/db-migrate-demo/demo.db") MIGRATIONS = os.path.join(os.path.dirname(os.path.abspath(__file__)), "migrations") def split_sections(text): parts = re.split(r"(?im)^\s*--\s*(up|down)\s*$", text) sec = {} for i in range(1, len(parts), 2): sec[parts[i].lower()] = parts[i + 1] if i + 1 < len(parts) else "" return sec def conn(): c = sqlite3.connect(DB) c.execute("CREATE TABLE IF NOT EXISTS schema_migrations (version TEXT PRIMARY KEY, applied_at TEXT DEFAULT (datetime('now')))") return c def applied(c): return [r[0] for r in c.execute("SELECT version FROM schema_migrations ORDER BY version")] def files(): return sorted(f for f in os.listdir(MIGRATIONS) if f.endswith(".sql")) def cmd_status(): c = conn() done, pending = applied(c), [] for f in files(): if f[:-4] not in done: pending.append(f) print("applied:", done if done else "(none)") print("pending:", pending if pending else "(none)") c.close() def cmd_apply(): c = conn(); done = set(applied(c)); n = 0 for f in files(): v = f[:-4] if v in done: continue sql = split_sections(open(os.path.join(MIGRATIONS, f)).read()).get("up", "") c.executescript(sql) c.execute("INSERT INTO schema_migrations (version) VALUES (?)", (v,)) c.commit(); n += 1 print("applied", v) print(f"{n} migration(s) applied") c.close() def cmd_rollback(): c = conn(); done = applied(c) if not done: print("nothing to roll back"); return v = done[-1] sql = split_sections(open(os.path.join(MIGRATIONS, v + ".sql")).read()).get("down", "") c.executescript(sql) c.execute("DELETE FROM schema_migrations WHERE version = ?", (v,)) c.commit() print("rolled back", v) c.close() if __name__ == "__main__": cmd = sys.argv[1] if len(sys.argv) > 1 else "status" {"status": cmd_status, "apply": cmd_apply, "rollback": cmd_rollback}[cmd]() ``` ### Worked example: rename `username` → `handle` (expand/migrate/contract) `20260922081031_create_users.sql`: ```sql -- up CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT NOT NULL, created_at TEXT DEFAULT (datetime('now')) ); -- down DROP TABLE users; ``` `20260922081245_add_handle_column.sql` (EXPAND): ```sql -- up ALTER TABLE users ADD COLUMN handle TEXT; UPDATE users SET handle = username WHERE handle IS NULL; -- down ALTER TABLE users DROP COLUMN handle; ``` `20260922081356_not_null_handle.sql` (MIGRATE): ```sql -- up -- Re-backfill FIRST: rows created between expand and migrate may have NULL handle. UPDATE users SET handle = username WHERE handle IS NULL; -- SQLite cannot add NOT NULL to an existing column in place: recreate the table. PRAGMA legacy_alter_table = ON; ALTER TABLE users RENAME TO users_old; CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT NOT NULL, created_at TEXT DEFAULT (datetime('now')), handle TEXT NOT NULL ); INSERT INTO users (id, username, created_at, handle) SELECT id, username, created_at, handle FROM users_old; DROP TABLE users_old; -- down ALTER TABLE users RENAME TO users_new; CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT NOT NULL, created_at TEXT DEFAULT (datetime('now')), handle TEXT ); INSERT INTO users (id, username, created_at, handle) SELECT id, username, created_at, handle FROM users_new; DROP TABLE users_new; ``` `20260922081502_drop_username.sql` (CONTRACT): ```sql -- up ALTER TABLE users DROP COLUMN username; -- down ALTER TABLE users ADD COLUMN username TEXT NOT NULL DEFAULT ''; UPDATE users SET username = handle; ``` ### Real run (verbatim) Fresh DB, migrations 1–2 applied, two rows seeded (`zuckbot`, `mikey`), then 3–4 applied: ``` === apply remaining (backfill runs here) === applied 20260922081356_not_null_handle applied 20260922081502_drop_username 2 migration(s) applied === data: handle backfilled from username === 1|zuckbot 2|mikey === rollback contract step (username restored) === rolled back 20260922081502_drop_username id username handle -- -------- ------- 1 zuckbot zuckbot 2 mikey mikey === re-apply === applied 20260922081502_drop_username 1 migration(s) applied === final: contract complete, tracking table === 1|zuckbot 2|mikey 20260922081031_create_users 20260922081245_add_handle_column 20260922081356_not_null_handle 20260922081502_drop_username ``` Rollback of the migrate step (table-recreation down path) also verified: data survived. Re-running apply after everything is applied prints `0 migration(s) applied` — idempotent via `schema_migrations`.