Database Migration Strategies Cheat Sheet
Covers schema migration tooling, versioning, and safe rollout patterns like expand-contract for changing production database schemas without downtime.
2 PagesAdvancedMar 8, 2026
Migration Fundamentals
Core ideas behind versioned schema migrations.
- Versioned migrations- Each schema change is a numbered/timestamped file applied in order and tracked in a migrations table, giving a reproducible history
- Up / down migrations- The 'up' script applies a change; the 'down' script reverses it, enabling rollback
- Idempotent migrations- Migrations written so re-running them (e.g., after a partial failure) doesn't error or duplicate changes (IF NOT EXISTS guards)
- Backward-compatible change- A schema change that doesn't break code still running the previous version, essential when deploying app and DB changes separately
- Migration lock- Most migration tools take a lock so two deploys can't run migrations concurrently and corrupt the schema state
Expand-Contract Pattern
Rename a column safely across multiple deploys.
sql
-- Step 1 (expand): add the new column, nullable, deploy app that writes to BOTH columnsALTER TABLE users ADD COLUMN full_name TEXT;-- Step 2 (backfill): populate the new column from the old one in batchesUPDATE users SET full_name = first_name || ' ' || last_nameWHERE full_name IS NULL;-- Step 3: deploy app version that reads from full_name, still writes both-- Step 4 (contract): once all instances are on the new version, drop the old columnsALTER TABLE users DROP COLUMN first_name;ALTER TABLE users DROP COLUMN last_name;
Migration File (Knex.js)
A reversible migration with up and down functions.
javascript
exports.up = function (knex) { return knex.schema.alterTable('users', (table) => { table.string('full_name'); });};exports.down = function (knex) { return knex.schema.alterTable('users', (table) => { table.dropColumn('full_name'); });};// CLI usage:// npx knex migrate:make add_full_name_to_users// npx knex migrate:latest// npx knex migrate:rollback
Zero-Downtime Checklist
Practices that keep migrations safe under a rolling deploy.
- Avoid long locks- Adding a NOT NULL column with a default can rewrite the whole table on older Postgres versions; add nullable, backfill, then add a NOT NULL constraint with NOT VALID + VALIDATE
- Don't rename/drop in one step- Renaming a column or table breaks any code still deployed against the old name; do it in expand-contract stages instead
- Batch backfills- Update rows in small chunks (e.g., 1,000 at a time) with a short pause between batches to avoid long-running transactions and replication lag
- Create indexes concurrently- Use CREATE INDEX CONCURRENTLY (Postgres) or pt-online-schema-change (MySQL) to avoid locking writes during index creation
- Feature-flag risky changes- Gate new-column usage behind a flag so you can roll forward/back the app independent of the schema state
Pro Tip
Always assume old and new application code will run against the same database simultaneously during a rolling deploy — design every migration to be safe for both versions at once (expand-contract), never a single atomic cutover.
Was this cheat sheet helpful?
Explore Topics
#DatabaseMigrationStrategies#DatabaseMigrationStrategiesCheatSheet#Database#Advanced#MigrationFundamentals#ExpandContractPattern#MigrationFileKnexJs#ZeroDowntimeChecklist#Databases#CheatSheet#SkillVeris
