# DB Migrate

> Generate safe, reversible SQL migrations with proper up/down statements, transactional wrappers, and rollback safety checks. Supports Postgres, MySQL, SQLite.

- Skill: `kasimmj/db-migrate` (Agent Skill)
- Install (CLI): `npx skillmds@latest add kasimmj/db-migrate`
- Raw SKILL.md: https://api.skillmd.com/api/skills/kasimmj/db-migrate/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- License: MIT
- Author: kasimmj (https://skillmd.com/u/kasimmj)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/kasimmj/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.

```sql
-- 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.