Database Migrations: A Practical Guide
A migration changes a schema without losing data. Learn the safe patterns, the zero-downtime approach, and how to roll back.
By Velloreth
What a migration is
A migration is a versioned script that changes the schema. The tool records which migrations have been applied, so every environment reaches the same state.
migrations/
├── 001_create_users.sql
├── 002_add_email_index.sql
└── 003_add_orders_table.sql
The version is recorded in a table, so the tool knows what to apply.
The migrations run in order, so the schema is deterministic.
Never edit an applied migration. The applied version is recorded, so an edit is not re-run, and the environments diverge. Add a new migration.
The expand and contract pattern
A destructive change breaks the old code. The fix is to split it into phases, so both versions work.
The goal: change the schema while the old code is still running, then deploy the new code, then remove the old schema.
Renaming a column
The naive approach breaks the old code immediately.
ALTER TABLE users RENAME COLUMN name TO full_name;
The expand and contract approach:
Phase 1 — expand. Add the new column, and write to both.
ALTER TABLE users ADD COLUMN full_name TEXT;
The application writes to both name and full_name, and reads from name.
Phase 2 — backfill. Copy the existing data.
UPDATE users SET full_name = name WHERE full_name IS NULL;
Do it in batches, so a large table is not locked.
Phase 3 — switch. The application reads from full_name and writes to both.
Phase 4 — contract. Stop writing to name, and drop it.
ALTER TABLE users DROP COLUMN name;
Each phase is a separate deploy, and the old and new code both work at every point.
Adding a NOT NULL column
The naive approach fails on a table with rows.
ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
The safe approach:
- Add the column as nullable.
- Backfill the value.
- Add the constraint.
ALTER TABLE users ADD COLUMN role TEXT;
UPDATE users SET role = 'user' WHERE role IS NULL;
ALTER TABLE users ALTER COLUMN role SET NOT NULL;
In PostgreSQL 11 and later, adding a column with a constant default is instant, because the default is stored in the metadata rather than written to every row. In an older version, it rewrites the table.
Locking
A migration that takes a lock blocks the application.
Adding an index locks the table for writes in some databases. Use a concurrent index build.
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
CONCURRENTLY builds the index without blocking writes, at the cost of taking longer and not running inside a transaction.
Adding a column with a default rewrites the table in an older version.
Changing a column type rewrites the table, and it may fail if the data does not fit.
Adding a foreign key validates every row, which takes a lock. Add it as NOT VALID and validate separately.
ALTER TABLE orders ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;
Set a lock timeout, so a migration that cannot get a lock fails rather than blocking the application.
SET lock_timeout = '5s';
Backfilling
A backfill on a large table should be batched, so it does not hold a long transaction.
-- Instead of one statement over a million rows
UPDATE users SET role = 'user' WHERE role IS NULL;
-- Batch it
UPDATE users SET role = 'user'
WHERE id IN (
SELECT id FROM users WHERE role IS NULL LIMIT 1000
);
Repeat until no rows remain. Each batch is a short transaction, so the lock is brief and the replication lag stays low.
Add a small sleep between batches to let replication catch up.
Reversibility
A migration should be reversible, or the rollback should be a forward migration.
A reversible migration has a down step.
exports.up = (db) => db.schema.createTable('orders', (t) => {
t.increments('id');
t.integer('user_id').references('users.id');
});
exports.down = (db) => db.schema.dropTable('orders');
A destructive migration is not truly reversible. A DROP COLUMN loses the data, and the down step cannot restore it. The safer approach is a forward migration that re-adds the column and backfills from a backup, which is a manual operation.
The rule: a migration that loses data should be a deliberate, reviewed decision, and the rollback plan should be written down.
Testing a migration
Run it against a copy of production data. A migration that works on an empty table may fail on a table with a million rows, or with a value that violates a new constraint.
Test the rollback. A down step that has never been run is a down step that does not work.
Test with the old code running. The expand phase must work with the old application, which is the point of the pattern.
Measure the time. A migration that takes 30 seconds on a test database may take 30 minutes on production.
Check the lock. A migration that takes an exclusive lock on a busy table blocks the application.
Running a migration in production
Run it as a separate step, not from the application’s startup. A failed migration should not prevent the application from starting, and a migration that runs on every instance is a race.
Run it once, from a single job, before the new code is deployed.
Have a rollback plan. Know how to revert the code and the schema.
Watch the replication lag. A large backfill can lag a replica, which affects a read from the replica.
Do it at a low-traffic time if the migration takes a lock, even with the expand and contract pattern.
Practical guidance
- Version every migration and run them in order.
- Never edit an applied migration.
- Use expand and contract for a destructive change.
- Add a column as nullable, backfill, then add the constraint.
- Use
CONCURRENTLYfor an index on a large table. - Batch a backfill.
- Set a lock timeout.
- Test against production-like data.
- Test the rollback.
- Run the migration as a separate step.
For the related guides, see database-indexing-explained and sql-joins-explained. For the broader context, ci-cd-pipeline-basics and twelve-factor-app-explained.
Quotable summary
A migration is versioned and applied in order, so every environment reaches the same schema, and an applied migration must never be edited because the applied version is already recorded. A destructive change should be split into expand and contract phases, so the old and the new code both work at every point, which is what makes a zero-downtime migration possible. Adding a column with a default can lock a large table depending on the database version, and adding an index should use CONCURRENTLY to avoid blocking writes. A migration should be reversible, or the rollback should be a forward migration that undoes it, and a migration that loses data should be a deliberate, reviewed decision with a written rollback plan.