The core problem

A schema migration that runs while the application is live can break the running code. A full downtime migration is simple but unacceptable for production services.

Expand-contract pattern

  1. Expand — add the new column/table alongside the old. Both old and new code work.
  2. Migrate — backfill data from old to new. Dual-write period where both are maintained.
  3. Contract — once all code uses the new column, drop the old one.

Renaming a column safely

-- Step 1 (deploy with old code still running)
ALTER TABLE orders ADD COLUMN customer_id INT;
UPDATE orders SET customer_id = user_id;

-- Step 2 (deploy new code that writes to customer_id)
-- Run after new code is fully deployed

-- Step 3 (cleanup migration, no code change needed)
ALTER TABLE orders DROP COLUMN user_id;

Tooling

  • Flyway — SQL-based, version-numbered migration files. Simple and reliable.
  • Liquibase — XML/YAML/SQL, more flexible rollback support.
  • golang-migrate, Alembic (Python), Laravel migrations — language-native options.