database_migrations v1.0.0

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 <version>_<short_name>.sql where <version> 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 <version>_<name>.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 <version>_<name>.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.

1. 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 <version>_<name>.sql,
where <version> 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.