Database Migration Safety
Claude CodeSKILL.mdCoding
Ship schema changes without downtime using expand-contract: backwards-compatible steps, safe locking, and a rollback plan written before running anything.
Use when: Use when writing or reviewing a schema migration, especially renames, type changes, NOT NULL additions, or anything touching a large or hot table.
Get this skill
Add the skill file with the CLI, by hand, or over MCP.Save this to your project or home directory so Claude Code can load it.
npx goodprompts add database-migration-safetySaves to
~/.claude/skills/database-migration-safety/SKILL.mdnpx goodprompts add database-migration-safetySaves to
~/.claude/skills/database-migration-safety/SKILL.mdSkill file
SKILL.md3,356 characters# Database Migration Safety
During a deploy, old code and new code run against the same database at the same time. Every migration must be compatible with **both**, or someone gets paged.
## The golden rule
Never ship a migration and the code that requires it in a step where either could break the other. Split into deploys where each step is safe with the code before and after it.
## Expand-contract (the only safe way to rename/restructure)
Renaming `users.fullname` → `users.display_name`:
1. **Expand:** add `display_name` as nullable. Deploy code that writes both columns, reads old.
2. **Migrate:** backfill `display_name` from `fullname` in batches (1k–10k rows per transaction, sleep between batches — one giant `UPDATE` locks the table and bloats the WAL).
3. **Switch reads:** deploy code reading `display_name`, still writing both. Verify with a count of rows where the columns disagree: must be zero and stay zero.
4. **Stop writing old:** deploy code that ignores `fullname`.
5. **Contract:** after a full release cycle with no rollback, drop `fullname` in its own migration.
The same shape covers type changes (new column of new type), table splits, and moving data between services.
## Operation safety table (Postgres-flavored)
- **Safe:** add nullable column; add column with default (PG 11+ — metadata only); create index `CONCURRENTLY` (must run outside a transaction); drop index `CONCURRENTLY`.
- **Dangerous, has safe recipe:**
- NOT NULL on existing column → add `CHECK (col IS NOT NULL) NOT VALID`, then `VALIDATE CONSTRAINT` (takes only a light lock), then set NOT NULL.
- Foreign key on big tables → add `NOT VALID`, `VALIDATE` separately.
- Plain `CREATE INDEX` → blocks writes for the whole build; always `CONCURRENTLY` on non-empty tables.
- **Never in one step on a live table:** rename column/table (breaks running old code instantly); change column type requiring a rewrite; drop a column code still reads. All of these go through expand-contract.
- Set a `lock_timeout` (e.g. 5s) at the top of every migration so a blocked DDL fails fast instead of queueing behind a long transaction and stalling all traffic.
## Rollback plan — written before running
For each migration, write down:
- **Down path:** the reverse migration, tested against a copy. If the change is destructive (drop column, lossy type change), there is no down path — which is exactly why destructive steps come last, after the code rollback window has passed.
- **Code rollback compatibility:** "If we roll the app back one release, does this schema still work?" Steps 1–4 above: yes. Step 5: only the previous release, which no longer reads the column.
- **Data restore story for destructive steps:** snapshot/backup point-in-time confirmed *before* running, and how long restoring takes.
## Checklist before merge
- [ ] Migration runs cleanly on a copy with production-scale row counts; duration recorded.
- [ ] Old app version works against the new schema (the actual deploy-window condition).
- [ ] Long operations are batched; DDL has `lock_timeout`; indexes are `CONCURRENTLY`.
- [ ] Destructive steps are in a separate, later migration — never bundled with the expand step.
- [ ] Rollback plan written in the PR description, including the no-down-path cases.
- [ ] Backfill is idempotent (safe to re-run after a crash mid-way).