write-database-migration

Write Database Migration

Version every schema change as a migration file committed alongside the code that requires it.

Why This Is Best Practice

Adopted by: Every major web framework ships a migration tool: Rails ActiveRecord Migrations, Django Migrations, Laravel Migrations. Flyway is used at Google, Zalando, Wix; Liquibase is used at 10,000+ enterprises. Schema-as-code is the universal default in 2024 — ad-hoc ALTER scripts are considered a smell in professional engineering teams. Impact: Teams using versioned migrations report near-zero schema drift incidents between environments (dev/staging/prod). Automated rollback capability reduces mean time to recovery from hours (restore from backup) to minutes (down migration). Every change is code-reviewed, auditable via git log, and exactly reproducible across all environments. Why best: Ad-hoc ALTER scripts run manually have no audit trail, can't be replayed, diverge between environments, and can't be rolled back predictably. A migration file is committed in the same PR as the application code that requires it — impossible to deploy code that references a column that doesn't exist.

Sources: Fowler & Sadalage, "Evolutionary Database Design" (martinfowler.com/articles/evodb.html); Rails Active Record Migrations guide; Flyway documentation (flyway.org)

Steps

1. One migration = one schema change

Never bundle unrelated alterations. Each migration should be independently deployable and independently rollbackable:

# Bad — one migration does too much
20240601_setup_users_and_orders.sql

# Good — separate concerns
20240601120000_create_users_table.sql
20240601120001_create_orders_table.sql
20240602090000_add_email_index_to_users.sql

2. Name with a timestamp prefix

Format: YYYYMMDDHHMMSS_<what_changed>.sql (or the equivalent in your framework).

The timestamp ensures deterministic ordering across branches and parallel development:

20240601120000_add_email_to_users.sql
20240601120001_create_sessions_table.sql
20240602090000_add_index_on_users_email.sql

3. Write both up and down

Every migration must have a reversal path:

-- up
ALTER TABLE users ADD COLUMN email VARCHAR(255);

-- down
ALTER TABLE users DROP COLUMN email;

If the rollback is destructive (data loss), document it explicitly rather than omitting it:

-- down (WARNING: drops all email data — backup required before rollback)
ALTER TABLE users DROP COLUMN email;

4. Test on production-like data before merge

Run the migration against a recent anonymized copy of the production database. Check:

• Migration completes without errors
• Application code works correctly after migration
• Down migration reverses cleanly
• Query performance is acceptable with production-scale data

5. Handle large tables safely (>1M rows)

Standard ALTER TABLE locks the table. On large tables, use non-locking patterns:

Adding a NOT NULL column with a default:

-- Step 1: Add nullable (no lock)
ALTER TABLE orders ADD COLUMN status VARCHAR(50);

-- Step 2: Backfill in batches (separate migration or script)
UPDATE orders SET status = 'pending' WHERE status IS NULL AND id BETWEEN 1 AND 10000;
-- ... repeat in batches

-- Step 3: Add NOT NULL constraint after backfill (fast — no data scan needed if all filled)
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

Adding an index: Use CREATE INDEX CONCURRENTLY (PostgreSQL) or equivalent. Never create indexes without CONCURRENTLY on live tables with significant traffic.

6. Never edit a merged migration

A merged migration has run in staging or production. Editing it creates a divergence between the recorded migration history and the actual database state.

For corrections: add a new migration that fixes the error.

# Bad — editing merged migration
git commit -m "fix typo in 20240601_add_email_to_users"  # already ran in prod

# Good — new corrective migration
20240603_rename_email_typo_to_correct_column.sql

Rules

• Migrations live in the same PR as the code that requires the schema change — never merge application code before its migration
• Never apply raw SQL directly to production databases — always run through the migration tool to maintain history
• Down migrations are required — "we'll never roll back" is an assumption that fails during incidents
• Seed data (test fixtures, reference data) belongs in seed files, not migrations — migrations model structure, not content
• All migrations must be idempotent when possible: CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS (PostgreSQL 9.6+)

Common Mistakes

Skipping the down migration. The first time you need to roll back at 2am, an absent down migration forces a manual database restore.

Bundling schema changes with data migrations. Schema changes (DDL) and data transformations (DML) have different risk profiles and should be separate migrations.

Locking production tables. A plain ALTER TABLE on a 50M-row table can lock writes for minutes. Always check whether your database's ALTER operation is non-locking.

Deploying code before its migration. Application code referencing users.email will crash if the migration hasn't run. Always run migrations before deploying the application code that requires them.

When NOT to Use

• Schema-less or document databases (MongoDB, DynamoDB): structure evolution is handled differently — use schema validation at the application layer and versioned document schemas
• One-off data corrections on production: use a documented, reviewed SQL script with a rollback plan — not a permanent migration that will run forever
• Seed/fixture data: use dedicated seed commands, not migration files