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 |
Expand / contract
For a breaking change on a live system, the pattern is to do it in phases: first expand (add the new without removing the old: new column, dual write), then migrate readers, finally contract (remove the old). Each deploy is compatible with the previous one, and a rollback never meets a schema the code doesn't know. A column rename, in this model, is: add the new column, copy the data, write to both, migrate reads, drop the old. 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. A tool that classifies the changes takes the "I don't think this breaks anything" out of the review.