---
name: database-migrations
description: >
  Plan and write relational schema changes that deploy without downtime or data loss in any back-end stack — expand/contract sequencing, lock-safe DDL per engine, backfills over large tables, online index builds, migration tooling (EF Core, Prisma, Drizzle, Flyway, Liquibase, Alembic), and what rollback actually means once data has moved. Use this whenever the user writes or changes a migration, adds or drops a column, renames anything, adds a NOT NULL or a constraint to an existing table, adds an index to a large table, backfills or rewrites data, sees a migration lock up production or time out, plans the deploy order of schema and code, or asks whether a schema change is safe to ship. This skill owns migration safety; the stack skills own the ORM idioms.
metadata:
  technology: Backend (general)
  type: development
---

# Database Migrations
> **Targets:** PostgreSQL, SQL Server, MySQL · EF Core 10, Prisma 7, Drizzle, Flyway, Liquibase, Alembic · **Verified:** 2026-09 against https://www.postgresql.org/docs/current/sql-altertable.html and https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/

During a rolling deploy, old code and new code run against the same database at the same time. That single fact drives every rule here: a schema change is safe only if the version of the application that is still running can survive it. The database is also the one part of the system with no undo — code rolls back, deleted columns do not. Sequence every change so that each step is compatible with the code on both sides of it, and treat a migration that takes a lock on a large table as a production incident waiting for enough rows.

## 1. Decide first

| Question | Default | Change when |
|---|---|---|
| Schema and code in one deploy | No — expand, deploy, migrate, contract across separate deploys | Only for a brand-new table nothing reads yet |
| Renaming a column or table | Never in place: add new, dual-write, backfill, switch reads, drop later | A table created in the same release and never deployed |
| Adding a column | Nullable, or with a default the engine applies without a rewrite | Adding `NOT NULL` with no default requires the expand/contract dance |
| Adding an index to a big table | `CREATE INDEX CONCURRENTLY` (Postgres) / `ONLINE = ON` (SQL Server, edition permitting) | Small table, or a maintenance window you actually have |
| Backfill strategy | Batched, throttled, resumable, outside the migration transaction | A single `UPDATE` only when the table is genuinely small |
| Destructive step (drop column/table) | A later release, after the old code is gone everywhere | Never in the same deploy as the code that stopped using it |
| Migration runner | The stack's tool, applied as an explicit step before the app starts | Automatic migrate-on-startup is unsafe with multiple instances |
| Rollback plan | Forward fix, plus a tested restore path for data loss | A down-migration only where it is genuinely lossless |

## 2. Expand / contract

The only sequence that survives a rolling deploy. Renaming `users.name` to `full_name`:

1. **Expand** — add `full_name`, nullable. Deploy. Old code ignores it.
2. **Dual-write** — deploy code that writes both columns and reads `name`.
3. **Backfill** — copy `name` into `full_name` in batches for existing rows.
4. **Switch reads** — deploy code that reads `full_name` and still writes both.
5. **Stop writing the old column** — deploy.
6. **Contract** — drop `name`, in a later release, once every instance runs the new code.

Steps 2–5 are separate deploys. Collapsing them is what turns a rename into an outage: between the migration and the last instance restarting, the old code is selecting a column that no longer exists.

The same shape covers type changes, splitting a column and moving data between tables. Per-change recipes: `references/change-recipes.md`.

## 3. Lock-safe DDL

What a statement locks, and for how long, decides whether it is safe:

- **Postgres** — `ADD COLUMN` with a non-volatile default is metadata-only in current versions; `ALTER TYPE`, adding `NOT NULL` and most constraint additions rewrite or scan the table. Build indexes with `CREATE INDEX CONCURRENTLY` (outside a transaction, and check for an `INVALID` index afterwards). Add constraints as `NOT VALID`, then `VALIDATE CONSTRAINT` separately.
- **SQL Server** — `ONLINE = ON` for index operations where the edition supports it; `ALTER COLUMN` is generally a size-of-data operation; `WITH (ONLINE = ON, RESUMABLE = ON)` helps on long index builds.
- **MySQL/InnoDB** — most `ALTER TABLE` supports `ALGORITHM=INPLACE, LOCK=NONE`, but not all; state the algorithm explicitly so it fails fast rather than silently copying the table.

Always set a **lock timeout** (and a statement timeout) in the migration session. Without one, a DDL statement blocked behind a long-running query queues every subsequent query on that table — the classic "one migration took the whole site down". With one, it fails and you retry.

Engine-by-engine detail and the safe form of each operation: `references/lock-safe-ddl.md`.

## 4. Backfills

- Batch by primary key range, commit per batch, and make it **resumable** — record progress so a restart does not start over.
- Throttle: sleep between batches, and watch replication lag rather than only CPU. A backfill that outruns replication breaks read replicas.
- Run it **outside** the migration. A migration that holds a transaction open for an hour blocks DDL, bloats WAL and cannot be interrupted safely.
- Make it idempotent (`WHERE full_name IS NULL`), so re-running is free.
- For very large tables, a backfill is a job with monitoring, not a script someone runs in a terminal.

## 5. Tooling

| Tool | Apply step | Watch out for |
|---|---|---|
| EF Core | `dotnet ef migrations bundle` → run the bundle in the pipeline | `EnsureCreated` is not migrations; auto-migrate on startup races across instances |
| Prisma | `prisma migrate deploy` in the pipeline | `migrate dev` is for development only; it can reset the database |
| Drizzle | `drizzle-kit generate` then apply the SQL | Generated SQL is a starting point — read it before shipping |
| Flyway / Liquibase | Versioned scripts applied by the pipeline | Never edit an applied migration; add a new one |
| Alembic | `alembic upgrade head` | Autogenerate misses data changes and some constraint edits |

Whatever the tool: migrations are applied as an explicit deployment step, by one runner, before the new code starts — not by every application instance on boot.

## 6. Rollback

Rolling back code is easy; rolling back a schema is mostly a fiction. Once a column is dropped or data is transformed, the previous state exists only in a backup.

- Plan **forward fixes**. Keep each step small enough that rolling *forward* is quick.
- Write a down-migration only where it is genuinely lossless (dropping a column you just added).
- Before any destructive step, confirm the restore path: a tested backup, and a stated recovery point objective.
- The contract step is the dangerous one, and it is also the one under least time pressure — leave it until you are sure.

## Deliverable format

```
## Change — what is changing and why
## Sequence — numbered steps, each marked as migration or deploy, in order
## DDL — the statement for each step, with lock and timeout settings
## Backfill — batching, throttling, resumability, and how progress is observed
## Compatibility — what the currently-running code does at each step
## Rollback — forward fix per step; restore path for anything destructive
## Verification — what to check after each step, including on a replica
```

## Checklist

- [ ] Old and new code can both run against the schema at every step.
- [ ] Nothing is renamed or dropped in the same deploy as the code change.
- [ ] Every destructive step is in a later release than the code that stopped using it.
- [ ] Index creation on large tables is concurrent/online.
- [ ] Constraints are added unvalidated, then validated separately, where the engine allows it.
- [ ] A lock timeout and statement timeout are set in the migration session.
- [ ] Backfills are batched, throttled, resumable and idempotent, and run outside the migration.
- [ ] Replication lag is watched during the backfill.
- [ ] Migrations are applied by one explicit step, not by application startup.
- [ ] The rollback story is written down, and the restore path for destructive steps is tested.
- [ ] The migration was run against a realistic data volume, not an empty dev database.

## Anti-patterns

| Anti-pattern | Why it hurts | Fix |
|---|---|---|
| Rename column + change code in one deploy | Old instances query a column that no longer exists | Expand/contract across separate deploys |
| `ADD COLUMN NOT NULL` with no default on a big table | Table rewrite under an exclusive lock | Add nullable, backfill, then set `NOT NULL` (validate separately) |
| `CREATE INDEX` without `CONCURRENTLY` on a hot table | Blocks writes for the duration of the build | `CONCURRENTLY` / `ONLINE = ON`, then verify the index is valid |
| Backfill as one `UPDATE` inside the migration | Long transaction, lock escalation, no way to stop it safely | Batched, resumable job outside the migration |
| Migrate on application startup | Several instances race; a failed migration takes the app down with it | One explicit pipeline step before the new version starts |
| No lock timeout | One long query turns a fast DDL into a site-wide stall | `lock_timeout`/`SET LOCK_TIMEOUT` in the migration session |
| Editing an already-applied migration | Environments diverge silently; checksums break | Add a new migration |
| Trusting a down-migration for a destructive change | The data is already gone | Forward fix, plus a tested restore path |
| Testing only on an empty dev database | Everything is fast on zero rows | Rehearse on production-like volume |

## Go deeper

- `references/change-recipes.md` — step-by-step sequences for rename, type change, NOT NULL, split/merge column, moving data between tables, and adding a foreign key.
- `references/lock-safe-ddl.md` — per-engine lock behaviour, the safe form of each operation, timeout settings and how to verify an online index build.
- Sibling skills: `messaging-patterns` when a change must be published as an event; `backend-code-review` for reviewing someone else's migration; `service-operability` for the monitoring a long backfill needs.