# Database Migrations Without Fear > What a migration actually is (git for your schema), the golden pattern for changing live data safely, and the dangerous migrations that lock tables or break the running app - so you can change a production schema without losing data or taking downtime. --- # Database Migrations Without Fear There's a particular flavor of dread that comes with changing a database that real users are hitting right now. The code you can roll back in seconds. The *data* - the orders, the accounts, the thing your whole company runs on - you can't un-spill. So a schema change that felt trivial on your laptop turns into a held breath in the deploy channel: *did that just lock the table? is the app still up? did I lose anything?* That dread comes from not knowing what the change is actually doing to the live system. This guide fixes that: the mental model of what a migration *is*, the one pattern that lets you change live data without downtime, and the specific migrations that bite people - so you see them coming instead of discovering them in an incident. ## How to read this - **About to ship a scary schema change right now?** Skip to [Phase 3: The Dangerous Migrations](03-the-dangerous-migrations.md) and check the cheat-card at the top against what you're about to run. - **Want migrations to finally make sense?** Read in order - each phase builds on the last. Phase 1 gives you the mental model, Phase 2 the safe pattern, Phase 3 the landmines. ## The phases 1. **[What a Migration Is](01-what-a-migration-is.md)** - a versioned, ordered change to your schema, checked into source control and applied identically in every environment. The "git for your schema" mental model, up/down (apply/rollback), and an annotated example migration. 2. **[Doing It Safely on Live Data](02-doing-it-safely-on-live-data.md)** - the golden pattern: additive changes first, backfill the data, then switch. The expand/contract (parallel-change) approach that keeps the running app working *during* a rename or type change. 3. **[The Dangerous Migrations](03-the-dangerous-migrations.md)** - the ones that bite: long locks on big tables, dropping or renaming columns the running app still uses, and non-nullable columns without defaults. Plus the two things you always want first: a rollback plan and a backup. > Database-engine specifics (how Postgres vs. MySQL differ on locking, online-DDL tools like > `pg_repack`, `gh-ost`, or `pt-online-schema-change`) are deliberately deferred. This guide teaches > the patterns that hold across engines; the engine-specific tooling is a follow-up once the patterns > are second nature. **Related:** [Relationships and Keys](/guides/relationships-and-keys) · [Transactions and ACID](/guides/transactions-and-acid) --- # What a Migration Is If you've ever pulled a teammate's branch, run the app, and gotten a `column "phone_number" does not exist` error, you've felt the problem migrations solve. Your *code* changed when you pulled - Git made sure of that. But your local database didn't. The schema and the code drifted apart, and the app fell into the gap. A migration is the fix for that drift. Here's the one idea the whole topic rests on. ## The mental model: git for your schema **What a migration actually is.** A migration is a small, versioned, *ordered* change to your database structure, written down as a file and checked into source control next to your code. Not a click in a GUI, not a one-off `ALTER TABLE` you typed into production and forgot - a recorded step that any environment can replay. The reason this matters is the same reason Git matters for code. Think about what Git gives you: a history of ordered changes, the same history on every machine, and the ability to move forward (apply commits) or backward (revert them). Migrations give your *schema* exactly those properties. ```mermaid flowchart LR subgraph CODE[CODE - git] c1[commit 1: add login route] --> c2[commit 2: add phone field to form] --> c3[commit 3: add SMS sender] end subgraph SCHEMA[SCHEMA - migrations] m1[0001 create users table] --> m2[0002 add phone_number column] --> m3[0003 create messages table] end CODE -. same ordered history on every machine .- SCHEMA ``` So a migration file is to your database what a commit is to your code: one ordered, replayable step. Run them all in order, and any empty database becomes the exact structure your app expects - on your laptop, on a teammate's, in CI, in staging, in production. Same steps, same result, every time. 💡 **Key point.** The value isn't any single migration - it's that the *ordered set* of them is the single source of truth for what your schema looks like. Nobody has to remember "oh, and you also need to add this index by hand." It's in the list. You replay the list. ## Up and down: apply and roll back **What it actually is.** Most migration tools split each migration into two halves: - An **up** (or *apply*) step: the change you want - "add the `phone_number` column." - A **down** (or *rollback* / *revert*) step: the exact reverse - "drop the `phone_number` column." 📝 **Terminology.** *DDL* (Data Definition Language) is the subset of SQL that changes structure: `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, `CREATE INDEX`. Migrations are mostly DDL. *DML* (Data Manipulation Language) - `INSERT`, `UPDATE`, `DELETE` - changes the *rows*. Some migrations do both (create a column, then fill it in), which becomes important in [Phase 2](02-doing-it-safely-on-live-data.md). **What it does in real life.** When you deploy, the tool looks at which migrations the database has already run, finds the new ones, and runs their *up* steps in order. If a deploy goes wrong, the *down* step is your scripted way back - instead of improvising a fix under pressure, you run the reverse you already wrote. ⚠️ **Gotcha - down is a comforting story, not a guarantee.** A `down` cleanly reverses *structure*. It does **not** bring back *data*. If your `up` dropped a column, the `down` that re-adds the column gives you an empty column - the old values are gone. Rollback is real for "add a table, oops, remove it"; it is a trap for anything that destroyed data. That asymmetry is the entire reason Phase 2 and Phase 3 exist. (And this is why we lean on a real backup, not just `down`, in Phase 3.) ## How migration tools work, at a concept level Every framework has one - Rails has Active Record migrations, Django has its migrations, Laravel has its own, and standalone tools like Flyway and Liquibase do the same job for any stack. They differ in syntax, but underneath they all do the same three things: 1. **Keep migrations as ordered files** - usually named with a number or timestamp so the order is unambiguous (`0001_…`, `0002_…`, or `20260619_…`). 2. **Track what's been applied** - in a little bookkeeping table inside your database (often called `schema_migrations` or similar). That table is how the tool knows migration `0007` ran but `0008` hasn't. 3. **Apply the pending ones in order** - on command, or as part of your deploy. Let's see that bookkeeping table, because it demystifies the whole thing: ```console $ psql -c "SELECT version FROM schema_migrations ORDER BY version;" version -------------- 0001 0002 0003 (3 rows) ``` *What just happened:* You asked the database which migrations it has recorded as run. It says `0001` through `0003`. When you deploy a branch that adds `0004_add_phone_number`, the tool will see `0004` is *not* in this table, run its `up` step, and then insert `0004` here. Next deploy, it sees `0004` is present and skips it. That's the entire trick - a checklist the database keeps about itself. ## An annotated example migration Here's a complete, ordinary migration in plain SQL - the kind a tool would run as one step. We'll annotate every line so nothing is mysterious: ```sql -- 0004_add_phone_number.sql -- == UP (apply) == ALTER TABLE users ADD COLUMN phone_number text; -- new, nullable column. Existing rows get NULL here. CREATE INDEX idx_users_phone_number -- so lookups by phone don't scan the whole table ON users (phone_number); -- == DOWN (rollback) == DROP INDEX idx_users_phone_number; -- reverse the index first… ALTER TABLE users DROP COLUMN phone_number; -- …then the column. Reverse order of the up. ``` *What just happened:* The **up** adds a nullable `phone_number` column to `users` and an index to make lookups on it fast. Because the column is nullable, every existing row gets `NULL` - nothing breaks, no value is required. The **down** undoes both, in reverse order (drop the index before the column it sits on). This is a *safe* migration: purely additive, asks nothing of existing rows, and its rollback genuinely restores the prior structure with no data loss - because there was no data in the new column to lose yet. That "purely additive" quality isn't an accident - it's the property Phase 2 deliberately engineers for, because it's what makes a change safe to run while real users are hitting the table. ## Recap 1. A **migration** is a versioned, ordered, source-controlled change to your schema - *git for your database structure*. The ordered set is the source of truth. 2. Migrations have an **up** (apply) and usually a **down** (rollback). Down reverses *structure*, not *data* - it can't resurrect dropped values. 3. **Migration tools** keep migrations as ordered files, track which have run in a bookkeeping table, and apply the pending ones in order - the same way in every environment. 4. Most migrations are **DDL**; the safe ones are **additive** and ask nothing of existing rows. --- # Doing It Safely on Live Data On your laptop, a schema change is one command and you're done. There's no traffic, so it doesn't matter that for a moment the column existed but was empty, or that old code and new code disagreed about the table shape. On a live system, those moments are exactly where things break, because the *old* version of your app is still running and serving users the instant your migration lands. The fear here is real but the cure is a single discipline: **never make a change that requires the app and the schema to switch over in the same instant.** Let's build that into a repeatable pattern. ## Why the naive way bites: the deploy gap Picture the obvious approach to renaming a column from `name` to `full_name`: write one migration that renames it, deploy it alongside the code that uses the new name. The problem is timing. A deploy is never instantaneous - for a window of seconds or minutes, you have a mix: ```mermaid flowchart TD S1["schema: column is name"] -->|RENAME runs mid-deploy| S2["schema: column is full_name"] App["old app code still live, reads name"] --> S2 S2 --> Err["old code now queries name
✗ column gone → errors"] ``` *What just happened:* The rename took effect while old app instances were still live. Those instances keep asking for `name`, the column no longer exists, and every one of those requests throws an error until the deploy finishes. Even a "fast" rename causes a burst of 500s - the schema and the code tried to switch in the same instant, and they couldn't. The fix is to stop trying to switch in one instant. ## The golden pattern: add, backfill, switch Almost every safe live migration follows the same three-beat shape. Hold this and you can reason your way through most changes: ```mermaid flowchart LR A["1. ADD
create the new structure additively
old app keeps working, untouched"] --> B["2. BACKFILL
copy/compute data into it
in safe batches, no rush"] B --> C["3. SWITCH
deploy app code that uses
the new structure"] C --> D["4. CLEAN UP (later)
remove the old structure
once nothing reads it"] ``` The genius of this order is that **every step is safe on its own.** Adding nullable structure breaks nothing. Backfilling only writes data; readers don't care. Switching the app happens *after* the new structure already exists and is full. Nothing has to line up to the second. 💡 **Key point.** Each step can be its own deploy, hours or days apart. You're trading one scary all-at-once change for a few boring, individually-safe ones. Boring is the goal. ## Expand/contract: keeping the app alive through a rename That golden pattern has a name when you apply it to renames and type changes: **expand/contract**, also called **parallel change**. The idea is to run the old and new shapes *side by side* for a while, so there's never a moment when the app can only use one of them. 📝 **Terminology.** *Expand* = add the new thing without removing the old (the schema temporarily holds both). *Contract* = once everything uses the new thing, remove the old. The safe middle is the period where both exist. Let's rename `users.name` to `users.full_name` on a live table, step by step. ### Step 1 - Expand: add the new column (additive, safe) ```sql -- migration: 0010_add_full_name.sql (UP) ALTER TABLE users ADD COLUMN full_name text; -- nullable; existing rows get NULL ``` *What just happened:* You added `full_name` alongside the still-present `name`. The running app doesn't know or care that this column exists - it's still reading and writing `name`. Zero impact on traffic. This is a deploy you can ship in the middle of a Tuesday. ### Step 2 - Write to both columns (dual-write) Deploy app code that writes to **both** `name` and `full_name` whenever a user is created or updated. Reads still come from `name`. ```mermaid flowchart LR W[app writes] --> N["name (old, still source of truth for reads)"] W --> FN["full_name (new, kept in sync from now on)"] R[app reads] --> N ``` *What just happened:* From this deploy onward, any new or changed row keeps the two columns identical. You've stopped the new column from falling further behind. What's left is the rows that existed *before* this code shipped - they still have `full_name = NULL`. That's the backfill's job. ### Step 3 - Backfill the old rows (in batches) Now copy the existing data across. The instinct is one big `UPDATE`; resist it (Phase 3 explains why a single huge `UPDATE` is dangerous). Do it in bounded batches: ```sql -- backfill: copy name into full_name where it hasn't been set yet UPDATE users SET full_name = name WHERE full_name IS NULL AND id BETWEEN 1 AND 10000; -- one batch; repeat with the next id range ``` ```console UPDATE 10000 ``` *What just happened:* You filled in 10,000 rows where `full_name` was still empty. Run this repeatedly over successive `id` ranges (a loop, or a small script) until no rows remain with `full_name IS NULL`. Batching keeps each statement short, so it never holds locks long or strains the database while real users are working. Because Step 2 is already keeping new rows in sync, the backfill only has to catch up the *old* rows - once it's done, the two columns match everywhere. ### Step 4 - Switch reads to the new column Now - and only now - deploy app code that **reads** from `full_name`. The data is all there, so this flips cleanly. ```mermaid flowchart LR W[app writes] --> N[name] W --> FN[full_name] R[app reads] --> FN FN -.->|switched| R ``` *What just happened:* The app now treats `full_name` as the real column. `name` is still being written (by the dual-write from Step 2) but nothing reads it anymore. The rename is, for all user-facing purposes, complete - and at no single moment did the app ask for a column that wasn't ready. ### Step 5 - Contract: drop the old column (later, deliberately) After the new code has been running long enough that you're confident you won't need to roll back to the old reads, remove the leftovers: ```sql -- migration: 0014_drop_name.sql (UP) ALTER TABLE users DROP COLUMN name; ``` *What just happened:* You removed the old column and the dual-write code that fed it (in the same deploy). The expansion is contracted; the table is now in its clean, final shape. This is the *only* destructive step, and you took it last, on purpose, well after the switch - when nothing reads `name` and you've stopped needing it as a safety net. ⚠️ **Gotcha - don't contract too soon.** The whole safety of expand/contract comes from the overlap period. If you drop `name` in the same deploy that switches reads to `full_name`, you've recreated the deploy-gap problem from the top of this phase *and* thrown away your fallback. Let the dust settle between switch and contract. A day is cheap; an incident is not. ## The same shape works for type changes Changing a column's type (say, an `integer` id to a `bigint`, or a `varchar` to `text`) is the same dance: add a new column of the new type, dual-write, backfill, switch reads, drop the old. You don't mutate the column in place while the app depends on it - you stand up its replacement beside it and move over calmly. ## Recap 1. The danger in live migrations is the **deploy gap** - old app code running against a schema that already changed. Don't make the app and schema switch in the same instant. 2. The **golden pattern** is *add → backfill → switch* (then clean up later). Each step is safe on its own and can be a separate deploy. 3. **Expand/contract** (parallel change) applies that to renames and type changes: add the new column, **dual-write** to both, backfill old rows in **batches**, switch reads, then drop the old column last. 4. **Contract late.** The overlap period is your safety net and your rollback path - don't collapse it early. --- # The Dangerous Migrations Read this *before* you run the thing that's making you nervous. Most migration disasters aren't exotic - they're a handful of the same mistakes, made by people who didn't know the migration was dangerous until it was. Here they are, named, so they can't surprise you. ## The danger cheat-card > **About to run a migration? Check it against this first. If it matches a row, read that section > before you ship.** | What you're about to do | The danger | The calm move | |---|---|---| | `ALTER TABLE` / add index on a **big** table | Can lock the table; writes freeze for minutes (§1) | Add columns nullable; build indexes concurrently; check your engine (§1) | | **Drop or rename** a column | Old app code still using it → errors (§2) | Use expand/contract from [Phase 2](02-doing-it-safely-on-live-data.md); drop last (§2) | | Add a `NOT NULL` column **without a default** | Fails outright, or locks while it backfills (§3) | Add nullable → backfill → add the constraint (§3) | | One giant `UPDATE` to backfill | Long lock + huge transaction → bloat, replication lag (§4) | Batch it (§4) | | Anything **destructive** at all | If it goes wrong, data is gone | Rollback plan + a tested backup, first (§5) | --- ## 1. Long locks on a big table **What actually happens.** Many schema changes need a lock on the table to do their work. On a tiny table that lock is held for a blink and nobody notices. On a table with millions of rows, the same operation can hold it for *minutes* - and while it's held, queries queue up behind it. To your users, the feature (or the whole app) is frozen. ```mermaid flowchart TD M["migration: ALTER TABLE holds a lock
(minutes on a big table)"] --> L[(table lock held)] W1[user write 1] --> L W2[user write 2] --> L W3[user write 3] --> L L --> F["every write queues behind the lock
app looks frozen / times out"] ``` The classic trap is building an index. A plain `CREATE INDEX` on a large, busy table blocks writes for the entire build: ```sql CREATE INDEX idx_orders_customer_id ON orders (customer_id); -- blocks writes while it builds ``` Postgres offers a non-blocking variant that builds the index without holding that write lock: ```sql CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id); ``` *What just happened:* `CONCURRENTLY` tells Postgres to build the index while still allowing reads and writes - it takes longer overall and can't run inside a transaction, but it doesn't freeze your users. (source: PostgreSQL docs, "Building Indexes Concurrently" - https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY) ⚠️ **Gotcha - locking behavior is engine- and version-specific.** Which operations lock, and for how long, differs between Postgres and MySQL and changes across versions. Don't assume an `ALTER` is cheap because it was cheap on your 50-row dev table - check your engine's docs for that specific operation, and test against a realistically-sized copy first. 💡 **Key point.** "It ran instantly in dev" tells you nothing about production - cost scales with row count and live traffic, neither of which your laptop has. ## 2. Dropping or renaming a column the app still uses This is the deploy-gap from [Phase 2](02-doing-it-safely-on-live-data.md), worth stating on its own because it's the most common self-inflicted outage. The moment you drop or rename a column, any still-running app instance that references the old name starts erroring: ```console $ # app log, seconds after a "rename" migration deployed: ERROR: column "name" does not exist LINE 1: SELECT id, name, email FROM users WHERE id = $1 ^ ``` *What just happened:* The migration removed `name`, but old app instances mid-deploy were still running `SELECT … name …`. Every such query fails until the new code fully rolls out. A "quick rename" became a wave of errors. The fix is the entire reason Phase 2 exists: **never drop or rename in place under live traffic.** Use expand/contract - add the new column, dual-write, backfill, switch reads, and drop the old column as a separate, *later* migration once you've confirmed nothing references it. ## 3. A non-nullable column without a default Say you need a required column - every user must have a `status`: ```sql ALTER TABLE users ADD COLUMN status text NOT NULL; ``` ```console ERROR: column "status" of relation "users" contains null values ``` *What just happened:* The table already has rows. The instant you declare the column `NOT NULL`, every existing row violates it - they have no `status` yet - so the database refuses the change. Even when you *do* supply a default, on some engines and versions adding a `NOT NULL` column with a default has to write that value into every existing row, which on a big table means a long lock (§1). The safe path is the golden pattern from Phase 2, applied to a constraint: ```sql -- 1. add it nullable - instant, breaks nothing ALTER TABLE users ADD COLUMN status text; -- 2. backfill in batches (see §4) until no NULLs remain UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 10000; -- …repeat over id ranges… -- 3. now that every row has a value, add the constraint ALTER TABLE users ALTER COLUMN status SET NOT NULL; ``` *What just happened:* You added the column nullable (safe), filled in every existing row in batches (safe), and only *then* told the database "from now on this can't be null." By the time the constraint goes on, there are no nulls to object to, so it succeeds - you never asked the database to do an impossible or table-locking thing in one shot. ## 4. The one giant UPDATE Backfilling with a single `UPDATE users SET …` across millions of rows is its own hazard, for two reasons. First, it can lock a lot of rows for a long time, blocking other writes (§1). Second, it's one enormous transaction - on databases like Postgres that generates a large amount of row churn to clean up afterward, and on replicated setups it can make replicas fall behind while they apply that one massive change. Batching, as in Phase 2 and §3, sidesteps both: each batch is a small, short transaction. ```sql UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 10000; -- bounded; commits; then do the next range ``` *What just happened:* You updated a bounded slice and committed it, keeping locks brief and each transaction small. Looping over ranges processes the whole table without any single step being heavy. ## 5. Always: a rollback plan and a backup Two habits turn "we have a problem" into "we have a plan": **A rollback plan.** Before you run a migration, know your way back. For additive changes, your `down` migration is genuinely enough. For anything destructive, remember the hard truth from [Phase 1](01-what-a-migration-is.md): **a `down` restores structure, not data.** A `down` that re-creates a dropped column gives you an empty one. So "we'll just roll back" is a real plan for expand-style changes and a *false comfort* for destructive ones - exactly why expand/contract keeps the old column around as your true fallback, and why destructive steps come last. **A real backup - and the DDL-transaction caveat.** Before any destructive migration, take a backup (or confirm a recent one exists *and* that you've actually restored from one before - an untested backup is a guess). A transaction helps on some engines: Postgres can run most DDL inside a transaction, so a failed migration rolls back as a unit. But MySQL implicitly **commits** before and after many DDL statements, so it can't wrap them in a transaction the way you'd expect - a multi-statement migration can fail half-done with no automatic undo. > ⏭️ If "commit", "rollback", and "atomic" are fuzzy - or you want to know exactly what your engine > wraps in a transaction and what it doesn't - read [Transactions and ACID](/guides/transactions-and-acid). > It's the foundation under every "can I undo this?" question in this phase. ⚠️ **Gotcha - never test a destructive migration for the first time on production.** Run it against a recent restore of production data (a staging copy sized like the real thing) and watch the lock time, the run time, and the result. Surprises are free there and expensive in prod. ## Recap The five dangers map straight onto the cheat-card at the top: big-table locks, drop/rename under live traffic, `NOT NULL` without a default, one giant `UPDATE`, and anything destructive without a rollback plan and a tested backup. Each has the same shape - add nullable, backfill in batches, switch, drop last - and `down` restores structure, never data. When in doubt about what your engine wraps in a transaction, see [Transactions and ACID](/guides/transactions-and-acid).