---
name: managing-database-migrations
description: Writes and manages versioned database schema migrations with safe rollbacks and zero-downtime deployment patterns. Use when the user asks to write, create, review, or fix a migration, add or drop a column/table/index, rename a column safely, run a backfill, or deploy a schema change without downtime, or mentions Alembic, Prisma Migrate, Rails migrations, or Flyway. Do not use for designing the target schema itself (designing-database-schemas) or for one-off production data fixes.
---

<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# Managing Database Migrations

## When to use / when NOT to use
- **Use for:** authoring, reviewing, sequencing, or fixing schema migrations; zero-downtime schema changes; backfills tied to schema changes; rollback strategy.
- **Do NOT use for:** deciding what the schema should look like (that is `designing-database-schemas`), ad-hoc data corrections, or backup/restore work (`backing-up-databases`).

## Core rules

1. **One logical change per migration.** A migration that adds a table AND renames a column elsewhere cannot be partially rolled back.
   - ✅ `20260805_add_invoices_table.py` + `20260805_rename_users_phone.py`
   - ❌ `20260805_misc_schema_updates.py`
2. **Applied migrations are immutable.** Never edit a migration that has run anywhere beyond your machine — write a new one that corrects it.
3. **Every migration has a down path — or documents why not.** If `down` is impossible (data-destroying), state it in the migration header and require an explicit flag/confirmation to run.
4. **Use the project's framework-native tool.** Detect it before writing anything: `alembic.ini` → Alembic, `prisma/schema.prisma` → Prisma Migrate, `db/migrate/` → Rails, `flyway.conf`/`sql/V*.sql` → Flyway. Only fall back to raw SQL files + a version table if the project has no tool.
5. **Never mix schema changes and data changes in one migration.** Schema migrations run in DDL transactions; backfills are long-running DML. Split them: schema → backfill → schema.
6. **Zero-downtime changes follow expand → migrate → contract.** The app must work with both old and new schema between steps.
   - ✅ add nullable column → deploy code writing both → backfill → add `NOT NULL` → remove old column later
   - ❌ `ALTER TABLE users RENAME COLUMN phone TO phone_number;` in one release
7. **Long locks are outages.** On PostgreSQL: `CREATE INDEX CONCURRENTLY` (outside a transaction), add `NOT NULL` via `CHECK ... NOT VALID` + `VALIDATE CONSTRAINT`, batch backfills (see references).
8. **Test the rollback, not just the migration.** `up` then `down` then `up` again on a scratch database must succeed before review.

## Workflow

1. Detect the migration tool (rule 4) and read the two most recent migrations to match naming and style.
2. Classify the change: additive (safe), destructive (needs expand/contract), or data-touching (needs a separate backfill migration).
3. Write the migration(s) — smallest possible units, down paths included.
4. For destructive or locking changes, write the deployment sequence as comments at the top of the migration (which app version must be live before/after).
5. **Validate:** run `up` → `down` → `up` against a scratch/dev database and paste the tool's output. A migration is not done until this passes.

## Edge cases & failure modes
- **No scratch DB available** → say so explicitly and mark the migration untested; never claim validation that didn't run.
- **Migration already applied and wrong** → write a corrective follow-up migration; never edit history (rule 2).
- **Backfill on a large table** → batch by primary-key range with pauses (see references); a single `UPDATE table SET ...` locks the table.
- **Divergent heads (two branches added migrations)** → merge with the tool's mechanism (e.g. `alembic merge`), never renumber existing files.
- **MySQL** → no transactional DDL: a failed migration leaves partial state; make each statement idempotent (`IF EXISTS`/`IF NOT EXISTS`).

## References
Deeper recipes and lock-safe SQL: see [references/patterns.md](references/patterns.md)