SQL schema diff: spotting breaking changes before deploy
Comparing two versions of a schema by hand is error-prone exactly where it
hurts most: the column that disappeared, the type that narrowed, the NOT NULL
that appeared. A text diff (git diff on the dump) helps little
because formatting noise and column order hide what matters. What counts is the
semantic diff: what actually changed and what it breaks.
Breaking vs non-breaking
| Change | Breaks? | Why |
|---|---|---|
| New nullable column (or with a DEFAULT) | no | old writes stay valid |
| Column removed or renamed | yes | every SELECT/INSERT that names it fails; a rename is drop + add to readers |
| Type widened (INT to BIGINT, VARCHAR(50) to 120) | usually no | existing values fit; watch for overflow on consumers |
| Type narrowed or swapped (VARCHAR to INT) | yes | existing data may not fit or convert |
| NOT NULL added without a DEFAULT | yes | old INSERTs that omit the field start failing |
| UNIQUE added | depends | fails if there's already a duplicate; changes the write contract |
Two of the "yes" rows destroy data, not just compatibility: a dropped column takes its values with it, and a narrowed type truncates or refuses what's already stored. Those deserve a louder alarm than the rest, a failed INSERT pages someone; silently lost data doesn't.
The rename ambiguity
A diff of two schema versions can't tell a rename from a drop-plus-add.
phone VARCHAR(20) gone and mobile VARCHAR(20) new is
either "we renamed it" (migration: ALTER TABLE ... RENAME COLUMN,
data preserved) or "we dropped one field and added an unrelated one"
(migration: DROP + ADD, data on the old one gone).
Same diff, opposite migrations. Any tool that auto-applies one of the two
readings is guessing with your data; the honest output is "this looks like a
rename, confirm it", which is exactly why Schemint marks the pattern uncertain
instead of generating the destructive version quietly.
Expand / contract, step by step
For a breaking change on a live system, do it in phases so every deploy is compatible with its neighbors. The column rename, done safely:
-- V12, expand: add the new column, backfill, dual-write via app code
ALTER TABLE customers ADD COLUMN mobile VARCHAR(20);
UPDATE customers SET mobile = phone WHERE mobile IS NULL;
-- (deploy app version that writes both, reads mobile)
-- V13, contract: nothing reads phone anymore
ALTER TABLE customers DROP COLUMN phone;The NOT NULL variant of the same dance: add the column nullable with a
DEFAULT, backfill, deploy code that always writes it, then
ALTER TABLE ... SET NOT NULL as the contract step. At every point
a rollback of the application meets a schema it understands, and a rollback of
the migration meets code that still works. Tedious? It is. But it's the tedium
that doesn't take down production at 3am.
Automate the detection
The point of reviewing the schema diff in the PR is to catch a breaking
change before the deploy, not after the incident. Eyeballing two DDL files
fails quietly, the reviewer's eye slides over a VARCHAR(180) that
became VARCHAR(120), and "I don't think this breaks anything"
carries the deploy. A tool that classifies every change (added, removed,
narrowed, NOT NULL'd, possibly-renamed) turns that sentence into a checklist:
each breaking item either gets an expand/contract plan or an explicit "no
readers exist, accepted" in the PR description. The diff also drafts the
forward migration, which is the part worth generating; the judgment calls
above are the part worth a human.