Latest / Software Testing with Fexingo: QA, Automation, and Reliable Software Engineering / How to Test Database Migrations Without Losing Data
Transcript
- Lucas: So there's this thing that keeps happening in software teams — someone writes a database migration, runs it on staging, everything looks fine, then it hits production and suddenly half the rows are gone or the app won't start. Luna: Right, that 'it worked on my machine' syndrome, but for schemas. I've seen it cause a 45-minute outage at a previous company because a migration dropped a column that was still being referenced in code. Lucas: Exactly. And the thing is, migrations are often treated as an afterthought in testing. Teams will unit test the application code, integration test the API, but the migration script itself gets maybe a manual run on a developer's local database. That's not enough. Luna: So what's the proper approach? I've heard people talk about 'migration dry runs' in CI. How does that work? Lucas: It's surprisingly straightforward. You set up a fresh database in your CI pipeline — ideally using something like a containerized database — then apply the migration against it. But the key is the data: you need a representative data set. An empty database will pass any migration. You want a copy of production data, or at least a synthetic set that matches production volume and variety. Luna: And that catches things like missing indexes or constraint violations? Because those only show up with enough rows. Lucas: Exactly. I remember one case where a migration added a foreign key constraint — it passed on staging because staging had only a few rows. In production, there were orphaned records that violated the constraint, and the migration failed halfway through, leaving the schema in an inconsistent state. Luna: Ouch. So they needed a rollback plan. That's another thing — do you test the rollback too? Lucas: Absolutely. Every migration should have a corresponding rollback script, and you should run both in your CI. Apply the migration, validate the schema and data, then roll it back and verify everything returns to the original state. That way you know you can safely revert if something goes wrong. Luna: But rollbacks aren't always trivial. Some changes — like dropping a column — aren't easily reversible if data was removed. What then? Lucas: That's where the 'expand migrate contract' pattern comes in. Instead of dropping a column in one migration, you do it in phases. First, expand: add the new column, migrate data from the old to new, keep both columns. Then migrate: update all application code to use the new column. Finally, contract: in a later deployment, drop the old column. Each step is reversible. Luna: That requires coordination with the development team. But it does prevent those irreversible mistakes. Are there tools that enforce this kind of discipline? Lucas: Flyway and Liquibase are the big ones. They version-control your migrations and provide built-in checksums to detect if a migration script has been modified after it was applied. They also support rolling back — though with limitations depending on the database. Luna: Do they integrate into CI workflows easily? Because the key is that this happens automatically on every build, not just when a developer remembers. Lucas: Yeah, they have Maven plugins, Gradle tasks, and command-line tools. You can run them in a pipeline step that spins up a temporary database — say using Testcontainers or a Docker image — applies the migration, runs a validation query, and tears down. If it fails, the build fails. Luna: That sounds like a lot of infrastructure. But I suppose the cost of not doing it is higher. One bad migration can wipe out hours of customer data. Lucas: Exactly. And the test itself is fast — once you have the setup scripted, it runs in under a minute. The biggest challenge is getting a good copy of production data that's anonymized and sized appropriately. Luna: Anonymization is important. You don't want PII in CI. But using a subset of production — say the most common schemas and a few million rows — can be enough to catch performance regressions, like missing indexes. Lucas: Right. Another technique is to test the migration against a snapshot of production taken just before the release. Some teams use database cloning tools to spin up a read-only copy quickly. Luna: I'm thinking about the organizational side. In many teams, database migrations are written by developers, but QA is responsible for testing them. How do you bridge that? Lucas: Good point. Ideally, the migration script itself should be reviewed just like code — by a peer who understands the database. And QA should have access to the migration tool output. They can include migration validation in their test plan for the release. Luna: And they should also verify that the migration doesn't cause any application-level failures. For example, a migration might rename a column but code still references the old name — that's a runtime error. Lucas: Yes, that's why integration tests that run against the migrated database are critical. You want to deploy the application code that will be used in production, with the migration applied, and run your full API test suite. Luna: That's essentially a canary test for the schema. Okay, so we've got: automated dry runs with representative data, rollback testing, expand migrate contract for dangerous changes, and integration tests after migration. Anything else? Lucas: One more thing: test the migration against multiple environments. Staging might have a different database version than production. For example, if you're running MySQL 5.7 in staging but production is on 8.0, a migration might behave differently. Luna: Environment parity — always the dream. So to wrap it up, the takeaway is: treat database migrations with the same rigour as any code change, and automate the testing. If today's conversation gave you something usable, consider supporting the show at buy me a coffee dot com slash fexingo. It keeps us ad-free and focused on real testing practices. Lucas: Absolutely. And that's a small way to help us keep delivering deep dives like this. So next time you're about to run a migration, remember: dry run, rollback script, and representative data. It might save your weekend. Luna: And your customers' data. Thanks for listening — now go test those migrations.