Db migrate

DB Migrate Skill

You are generating a database migration. Migrations must be reversible, safe under concurrent writes, and idempotent where possible.

Step 1 — Detect the migration tool

Look for these markers (in order):

• migrations/ or db/migrate/ directory
• Tool config files: knexfile.*, alembic.ini, migrate.yaml, prisma/schema.prisma, supabase/migrations/
• ORM clues in package.json, requirements.txt, go.mod

If you can't tell, ask before writing.

Step 2 — Compute the next filename

Standard formats:

• YYYYMMDDHHmmss_<slug>.sql (Supabase, Knex)
• 001_<slug>.sql (sequential)
• <n>_<slug>.{up,down}.sql (separate files)

Use git log and the existing migrations dir to determine the convention. Never break the existing pattern.

Step 3 — Write the migration

For each operation, follow these safety rules:

Adding a column

• ✅ ADD COLUMN ... NULL DEFAULT <value> — safe
• ❌ ADD COLUMN ... NOT NULL without default — breaks on large tables
• ✅ Two-step: add nullable → backfill in batches → add NOT NULL constraint

Removing a column

• 🚨 Two-deploy approach required:
  1. Deploy code that stops reading/writing the column
  2. Then drop it in a follow-up migration
• Document this in a comment block.

Renaming a column

• 🚨 Three-step approach:
  1. Add new column, copy data
  2. Update code to use new column
  3. Drop old column

Adding an index

• ✅ CREATE INDEX CONCURRENTLY on Postgres for tables > 1M rows
• ❌ Regular CREATE INDEX will lock the table

Foreign keys

• Use ON DELETE strategy explicitly. Never default.

Step 4 — Provide rollback

For each up, write the inverse down. If the down would lose data, say so explicitly in a comment.

-- up
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;

-- down
-- ⚠️ This is destructive — deleted rows can't be recovered.
ALTER TABLE users DROP COLUMN email_verified;

Step 5 — Wrap in a transaction (where possible)

• Postgres/SQLite: wrap in BEGIN; ... COMMIT;
• MySQL: most DDL is implicit-commit; warn the user.
• Statements that cannot be in a transaction (e.g., CREATE INDEX CONCURRENTLY) go in their own migration file.

Step 6 — Hand off

Print:

• The file path created
• The exact command to apply (npm run migrate, alembic upgrade head, supabase migration up)
• The exact rollback command

When NOT to use

• Seeding test data (use a fixture skill instead)
• ORM-driven schema changes that are auto-generated (Prisma migrate, ActiveRecord)
• Stored procedure-only changes (use stored-proc skill if available)

Failure modes

• ⚠️ Backfills against tables > 10M rows should be done outside the migration in a chunked job. Suggest this when relevant.
• ⚠️ Always recommend running migrations on a staging copy first.