Safe Database Migration Patterns
debt(d7/e7/b7/t7)
Closest to 'only careful code review or runtime testing' (d7). While tools like gh-ost and pt-online-schema-change exist for online schema changes, they don't automatically detect unsafe migration patterns in your migration files. Laravel migrations or similar frameworks don't warn you about adding NOT NULL columns without defaults to large tables. Detection typically requires careful code review of migration files against table sizes, or discovering the problem during deployment when table locks occur.
Closest to 'cross-cutting refactor across the codebase' (e7). Fixing an unsafe migration pattern often requires the expand-contract approach: add nullable column, deploy code changes, backfill data, add constraints, deploy again, then remove old column. This spans multiple deployments, requires coordinating code changes with schema changes, and may involve multiple migration files plus application code changes. The quick_fix describes a multi-step process across separate deploys.
Closest to 'strong gravitational pull' (b7). Migration practices apply broadly to all database contexts (web, cli) and every future schema change must follow these patterns to avoid downtime. Once a codebase has large tables, every migration becomes a potential incident. The choice to use expand-contract patterns affects deployment pipelines, release processes, and how every developer writes migrations going forward. It's not quite architectural (b9) but significantly shapes development workflow.
Closest to 'serious trap' (t7). The misconception explicitly states that developers believe 'running migrations as part of deployment is always safe.' This contradicts the expected behavior because what works fine on a dev database with 100 rows becomes catastrophic on production with millions of rows. The intuitive approach (add NOT NULL column, deploy) is exactly wrong for large tables. Developers coming from small-scale applications will consistently guess wrong about migration safety.
Also Known As
TL;DR
Explanation
Database migrations must be safe to run against the old code (before deploy) and backward-compatible with the new code after deploy. The expand-contract pattern: Step 1 (Expand) — add the new column as nullable, keep old column; Step 2 — deploy new code that writes to both columns; Step 3 — backfill old data into new column; Step 4 (Contract) — remove old column once fully migrated. Never: rename a column in one deploy (code and DB diverge), drop a column without a deploy that stops using it first, or add a NOT NULL column without a default. Large table changes: use pt-online-schema-change (MySQL) or pg_repack (PostgreSQL) to avoid long table locks. Tools: Phinx, Doctrine Migrations, Laravel migrations.
Diagram
flowchart LR
subgraph Unsafe - Breaking
M1[Rename column - breaks old app]
M2[Drop column - breaks old app]
M3[Change type - breaks old app]
end
subgraph Safe - Expand Contract
S1[Add new column nullable]
S2[Deploy app reading both columns]
S3[Backfill data]
S4[Deploy app using new column only]
S5[Drop old column]
S1 --> S2 --> S3 --> S4 --> S5
end
style M1 fill:#f85149,color:#fff
style M2 fill:#f85149,color:#fff
style M3 fill:#f85149,color:#fff
style S1 fill:#238636,color:#fff
style S5 fill:#238636,color:#fff
Common Misconception
Why It Matters
Common Mistakes
- Adding a NOT NULL column without a default to a large table — locks the table while backfilling.
- Dropping a column that application code still references — deploy order matters.
- Irreversible migrations with no down() — prevents rollback when a deploy fails.
- Running migrations inside the deploy step rather than as a separate, pre-deploy step.
Code Examples
// Dangerous migration — locks large table:
public function up(): void {
Schema::table('orders', function($t) {
$t->string('tracking_code')->notNull(); // Locks table while adding NOT NULL to 10M rows
});
}
// Safe — add nullable first, backfill, then add constraint:
// Step 1: $t->string('tracking_code')->nullable();
// Step 2: backfill existing rows
// Step 3: $t->string('tracking_code')->notNull()->change();-- Safe: add nullable column (backward compatible with old code)
ALTER TABLE users ADD COLUMN display_name VARCHAR(100) NULL;
-- Safe: rename via expand-contract (3 deploys)
-- Deploy 1: add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);
-- Deploy 2: write both columns, read from old one
-- Deploy 3 (after backfill): read from new column, then drop old
ALTER TABLE users DROP COLUMN name;
-- UNSAFE: rename in one migration (breaks running old code)
-- ALTER TABLE users RENAME COLUMN name TO full_name;
-- Large table: use pt-online-schema-change (MySQL) — no table lock
$ pt-online-schema-change --alter 'ADD INDEX (email)' D=myapp,t=users --execute