Your Migration Tests Run in Your Head
Write it down, so the database checks your guesses and you’re free to find the gaps.
A migration with one job
customer.status has been free text for years: 'ACTIVE', 'INACTIVE', whatever the app happened to write. It's moving to a lookup table, customer_status, with ACTIVE and INACTIVE. When the migration is done, every customer must have a status_id, and the old text column is gone. This is the step that fills it in:
-- V3__fill_customer_status_id.sql
UPDATE customer
SET status_id = s.id
FROM customer_status s
WHERE s.code = upper(customer.status);What could go wrong?
The migration already answers the obvious case: somewhere, someone wrote 'active' in lowercase, hence the upper. It looks careful, and in CI, on an empty database, there's nothing to get wrong. On a customer's database, the next migration, the one that makes status_id NOT NULL, blew up:
Failed to execute script V4__drop_customer_status_text.sql
SQL State : 23502
Message : ERROR: column "status_id" of relation "customer" contains null values
Location : db/migration/V4__drop_customer_status_text.sql
Line : 2The error names V4, but V3 is the culprit. One customer’s status was 'Active ' - with a trailing space - which upper turns into 'ACTIVE ': still no match. V3 left that customer without a status_id. The NOT NULL in V4 wasn't put there to check V3 - the app needs it - but it's the only reason this failed loudly. Without it, the customer would have silently lost their status. And it failed in production, after Flyway had already committed V3 and the step before it - each migration runs in its own transaction - on a database you can't log into. The deployment is half done, and the fix starts with a support call.
The test you already ran
If you paused at that question, think about what you did. You imagined rows that could be sitting in that column — a lowercase 'active', maybe a NULL - and ran V3 against them in your head. Whoever wrote V3 did the same; that's where the upper came from. That's a test: seed data, an action, an expected result.
The trouble is the test runner. A head doesn’t execute SQL; it guesses what the SQL would do. And it holds only a few cases at a time. Here’s the same kind of test, written down, using jdbscript for the database part:
@Test
public void every_customer_keeps_its_status() {
migration.migrateBefore("2"); // stop right before V2
migration.before().insertDB(db -> { // seed in the old shape
db.customer().id(1).name("Acme").status("ACTIVE");
db.customer().id(2).name("Globex").status("INACTIVE");
db.customer().id(3).name("Initech").status("active");
db.customer().id(4).name("Umbrella").status("Active ");//trailing space
});
migration.migrateTo("4"); // run V2, V3, V4, and nothing after
migration.after().assertDBHas(db -> { // assert in the new shape
db.customer_status().id(1).code("ACTIVE");
db.customer_status().id(2).code("INACTIVE");
db.customer().id(1).status_id(1);
db.customer().id(2).status_id(2);
db.customer().id(3).status_id(1);
db.customer().id(4).status_id(1);
});
}(full class: link)
Run it against the V3 above and it fails exactly the way production did — same error, same line of V4 — but on a laptop, before anything is merged. (The fixed V3, and a test for customers without a known status, are in the full example.) Writing it down helps with both — the guessing and the forgetting — though not in the same way.
Any head is a flawed SQL engine
Even when you do imagine the right row, you still have to guess what the SQL does with it, and that guess comes from whichever database you learned on. On SQL Server, 'ACTIVE ' = 'ACTIVE' is true: trailing spaces are ignored in comparisons, and the default collation ignores case as well. On MySQL, it depends on the collation: the current default ignores case but not the trailing space; older defaults ignore both. On PostgreSQL, both count. And then there's Oracle, where an empty string is NULL. You know that. It still gets you.
Get Yury Altukhou’s stories in your inbox
Join Medium for free to get updates from this writer.
So a developer who pictures 'Active ' and thinks "that matches" may be perfectly right - for the database they know. On SQL Server, this V3 maps'Active ' correctly; on PostgreSQL, it doesn't. The case was imagined; the answer came from the wrong engine.
A written test removes this weakness completely. Nothing is left to guess: the database runs V3 on the rows and says what happened. That’s also why the test should run on every DBMS you ship to, configured the way production is. Anything else tells you about a different database.
Any head has limited space
The other weakness is harder, and a test doesn’t fix it for you: it can’t contain a row nobody thought of. The trailing space wasn’t in the first version of this example either; it came up later, as a what-if. Not because trailing spaces are exotic, but because a head juggles only a few cases at once. Think of the lowercase one, then the trailing space, then NULL, and by the time you check the third, the first has slipped away.
Write them down, and you no longer have to hold them. Your head is free to search instead of remember, and each new idea is compared against a visible list rather than a fading one. Upper case is there, lower case is there — what about mixed? A trailing space? No status at all? The test doesn’t produce the missing row. It frees you to find it.
The list isn’t limited to one head, either. Anyone can add a row: the colleague who asks “what about customers with no status?”, or an AI agent listing the oddities that might lurk in a free-text column. Their ideas get checked instead of trusted.
If you have a copy of production, it’s a good place to look for cases: SELECT DISTINCT status shows values nobody would think to invent. Just don't let it replace the test. Running the migration on a copy only tells you that it ran - not that every customer ended up with the right status.
Why we skip these
Three reasons, mostly.
The setup is tedious. By hand, getting the database to “right before this migration” means configuring Flyway with a target of the previous version, cleaning the database between tests, and writing the rows as raw INSERT strings - for every test. That tedium is what jdbscript's migration support was built to remove. migrateBefore("2") stops right before the migration, using its own version as the marker (with Liquibase, a changeset id or tag). The rows go in through typed interfaces of the old schema instead of raw SQL, so a typo in a column name is a compile error. And migrateTo("4") runs exactly the migrations under test.
The test is disposable. Once every database has run the migration, the test has nothing left to protect, and you can delete it. That’s no reason to skip it: it does its job before the migration ships, while a mistake is cheap to fix.
It has no obvious home. Every other test in the project starts from the latest schema: whatever sets up its database,@SpringBootTest or a shared base class, runs all the migrations first. This test needs the database as it was in the past, so it can't share that setup. It needs a database to itself, one it can wipe, schema and all, before every run.
What it costs, and when it’s worth it
Every test rebuilds the database from scratch, up to the migration under test. On the closed-source project where these tests already run, with about 390 changesets, that takes around 7 seconds per test on PostgreSQL and 27 on Oracle — over 90% of it just getting to the starting point. (H2 does it in about 1 second, but that only tells you what the migration does on H2.) These tests are few, though — a handful per data migration — and they can live in their own suite. Then compare that with the alternative: a half-applied migration on a database you can’t reach.
Not every migration needs one. If reviewing it made you imagine rows, write them down. If it didn’t — a new table, a nullable column, a constant default — you’re probably right that there’s nothing to test.
The refactorings we put off
That free-text status column survived for years because fixing it meant a data migration you couldn't check, on databases you might never see. Once you can check it, the fix is just work: a few migrations, a test, a review.
Full example, including what’s not shown here: github.com/cyberdruid-dev/articles/tree/main/untested-migration/example.
