Why must a migration script generated by diffing the mapping against the last known schema be reviewed before it runs?
answer
- two snapshots, no edit history
- intent is not in the diff
- a rename looks like drop plus add
- structure only, never the data
- generated output is a draft
basics
~20 sA diff compares two end states, not the edit that produced them, so it cannot see intent. A renamed field reaches the script as a dropped column plus an added one, which destroys the data unless a human rewrites it.
solid answer
~40 sDiff generation is a good drafting tool and a bad author. It takes the shape the mapping now requires, compares it with the last known schema, and emits statements that close the gap. What it never sees is **what you did** — only where you started and where you ended. So a rename appears as one column missing and another appearing, and the honest way to close that gap is `DROP COLUMN` plus `ADD COLUMN`: structurally correct, and it throws the values away. The same blindness turns a split field into a drop plus two adds and a type narrowing into a silent data-losing change. Treat the output as a draft: read every statement, rewrite the ones whose intent the tool could not infer, and keep the edited script as the artifact of record.
code
sql · 6 lines-- emitted by the diff: two end states, no intent
ALTER TABLE customer DROP COLUMN full_name;
ALTER TABLE customer ADD COLUMN display_name VARCHAR(200);
-- what the change actually was, after review
ALTER TABLE customer RENAME COLUMN full_name TO display_name;go deeper
Remember that a generated migration is a draft. The tool sees the old schema and the new mapping and nothing in between, so it cannot know a column was renamed rather than replaced.
Explain the mechanism: set difference over two snapshots. Then name the edits it provably cannot infer — rename, split, move, narrowing — and what happens to the data in each.
Show the workflow you enforce: generate to a file, review, rewrite, keep the edited script authoritative, and prove data survival against representative volumes rather than an empty database.
Argue about where authority sits. A generated statement is an unsigned change; the review step is what turns schema evolution into something with an author, a record and a rollback story.
## What the generator actually compares A schema diff has exactly two inputs: **the shape the current mapping requires** and **the last known state of the schema** — a snapshot, a development database, or the result of replaying the existing scripts. It computes the set difference between them and emits statements that turn the second into the first. That is a comparison of two photographs. It contains no record of the edits between them. The generator does not know that a field was renamed, that two fields were merged, or that a column moved to another table; it only knows which names are present on each side. Every failure below follows from that single limitation. ## The rename is the canonical failure A field renamed in the mapping presents to the diff as *a column that is gone* and *a column that has appeared*. The statements that close that gap are structurally correct and destroy the data: ```sql -- what the diff emits ALTER TABLE customer DROP COLUMN full_name; ALTER TABLE customer ADD COLUMN display_name VARCHAR(200); ``` Run against an empty development database this looks perfect: afterwards the schema matches the mapping exactly, and every test passes. Run against a populated table it silently discards a column of real values. Nothing in the tool is wrong; the information needed to do better was never available to it. A reviewer who knows the change was a rename replaces those two statements with one that preserves the values, or, when the column must stay readable by code that is still running, with an add, a copy and a later removal — the sequencing of that is a schema-evolution question in its own right. ## The other edits a diff cannot infer - **Splitting or merging a field** — one drop and two adds, or two drops and one add, with no statement that redistributes the values. - **Moving a column to another table** — a drop here and an add there, and no transfer between them. - **Narrowing a type or a length** — emitted as a straightforward alteration, with no thought for rows that no longer fit. - **Tightening nullability** — emitted as an alteration that will simply fail, or succeed and lock, depending on what the rows already contain. - **Naming churn** — objects the previous generator named automatically reappear as drops and adds when the generation rules change. - **Phantom differences** — the two sides describe the same thing in different vocabularies (a synonym for a type, an implicit default, an index the key already implies), and the tool proposes a change where none is needed. - **Cost blindness** — the generator has no idea whether a table holds a thousand rows or a billion, and emits the same statement either way. And it emits **structure only**. Populating a new column, or reshaping values into it, never appears in the output; that work has to be authored deliberately alongside the script. ## Making the workflow safe 1. **Generate into a file, never straight onto a database.** The output is a draft, and a draft has to be readable before it is runnable. 2. **Read every statement and ask what edit produced it.** Any drop next to an add is a rename or a move until proven otherwise. 3. **Rewrite what the tool could not infer** — preserve values, split a destructive statement across releases, add the data work the generator omitted. 4. **Keep the edited script as the artifact of record.** From then on the script, not the generator, is what ran. 5. **Regenerate afterwards as a check.** Diffing the mapping against the schema the edited script produced should come back empty; if it does not, the two have already diverged. ## The other direction: reverse-engineering The same tooling usually runs backwards as well, producing mapping classes from an existing database. That is the natural starting point when the database predates the application or is shared with other consumers. It carries a mirror-image caveat: the generated mapping reflects the schema's vocabulary, including naming that was never meant for code, and regenerating it later overwrites whatever a human refined in between. In both directions the rule is identical — **generate a draft, review it, and let the reviewed artifact be the one that counts**.
- If the generated script has to be reviewed anyway, what is the generator still worth?It finds everything, which people do not. A mapping change touches columns, keys, indexes and constraints that are easy to forget by hand, and the generator lists them all. It is a completeness aid; judgment about intent and cost stays with the reviewer.
- How would you catch the case where someone skipped the review and shipped a drop-plus-add?Make the generated draft an input to review rather than an output of the build, so a person signs the script that ships. Then, in a pre-release environment holding representative data, run the chain and assert the column's values survive it — an empty database cannot show that failure.
- What does an empty regenerated diff after applying the script actually prove?Only that the structure the mapping requires now exists. It says nothing about whether the values are correct, whether the change was cheap to apply at volume, or whether anything the mapping does not model was disturbed.
Comparing yesterday's and today's photograph of a shelf shows one labelled jar missing and a new one present. It cannot tell you the jar was relabelled rather than thrown away and replaced.
saying these in an interview costs you the question
- Says the generator tracks renames because it read the mapping
- Applies generated statements straight to a database with no review
- Believes a green test suite on an empty database proves the script is safe
- Expects the generator to emit statements that move existing values
- Treats every proposed difference as real rather than a vocabulary mismatch