Reading a generated migration script before you run it

Any tool that compares two schemas can generate the SQL to reconcile them. The generation is the easy part. The valuable part is the review, and a generated script is only as safe as the attention it gets before it runs.

This is the checklist we use. It takes about five minutes and catches the failures that actually happen.

1. Check the direction

The single most expensive mistake is comparing the right two schemas in the wrong order. A script that makes production look like an old development database is perfectly valid SQL and completely wrong. Read the header comments and confirm which endpoint is the source and which is the target before reading anything else.

2. Find everything destructive

Search the script for DROP, TRUNCATE and MODIFY. The first two are obvious. MODIFY is the one that catches people out, because narrowing a type silently discards data that does not fit:

1-- Truncates every value longer than 64 characters, without warning.2ALTER TABLE `customers`3  MODIFY COLUMN `notes` varchar(64) NOT NULL;4 5-- Fails outright if any existing row holds a value outside the new list.6ALTER TABLE `orders`7  MODIFY COLUMN `status` enum('draft','paid') NOT NULL;

Before applying either, check the data. A single SELECT answers it:

SELECT COUNT(*) FROM `customers` WHERE LENGTH(`notes`) > 64;SELECT DISTINCT `status` FROM `orders`;

3. Check NOT NULL on an existing table

Adding a NOT NULL column to a table that already has rows requires a default, otherwise MySQL supplies an implicit one — zero, an empty string, or the zero date. That usually is not what you want, and it is much harder to detect afterwards than to prevent now.

4. Look at the size of the tables being altered

ALTER TABLE on a large InnoDB table can rebuild it. On a table of a few thousand rows this is instant. On a hundred million rows it can run for hours and hold locks the application cares about. The statement is identical in both cases; only the table tells you which one you have.

5. Confirm foreign key ordering

A correctly generated script drops the constraints that would block its own changes and recreates them afterwards. Confirm that it does. If constraints are recreated before the columns they reference are final, the script fails halfway — which is recoverable, but only if you took a backup first.

6. Read the transaction boundary

Check that the script is wrapped in a transaction, and understand what that does and does not buy you in MySQL. Individual DDL statements are atomic in 8.0, but a sequence of them is not fully transactional — several statements cause an implicit commit. The transaction is worth having; it is not a substitute for a backup.

The habit that matters most

Run the script against a copy of the target first — a restored backup, or a staging database that genuinely resembles production. Every item above is a way of predicting what will happen. Applying it to a copy is how you find out. Five minutes of review plus one rehearsal removes almost every deployment surprise worth worrying about.

SchemaSync generates these scripts with destructive statements commented out by default, which makes step 2 a matter of reading rather than remembering. See generating migration SQL.