David R. MacIver recently tweeted that “Database migrations need tests: They’re one of the most sensitive and error prone things you could possibly do to your production data” (later clarifying that he meant design mistakes). I answered that I thought this wasn’t usually necessary, since I tend to develop against a subset of the production database, allowing me to fix almost all problems before doing them on the actual production database, and that migrations are a prime target for code reviews, since they’re usually on the shorter side, and don’t occur often.
David answered that yes, so far manual testing and dry runs had been sufficient for them as well, but he dreads the day when they aren’t enough.
So, should you test your migrations?
My answer is: No, probably not.
Now let me qualify my answer: So far, all web apps that I’ve developed so far that used a database had a fairly clear pattern as far as migrations are concerened: There are a lot of them in the intial phase, where most of the database is in heavy flux as the app is developed. Also, any amount of production data is basically testing data (e.g. test user accounts, sample entries, and so on). And I always use PostgreSQL, which allows DDL statements to be wrapped in transactions.
Then, the app is publicly launched, and while there are still migrations that will be run against the database, they occur much less compared to the early days of development, and tend to only add new columns or tables, not transform or delete anything.
This means two things:
- Most migrations are run before the database is filled with “real”, user-generated data. And:
- Any migrations run fter the public launch only add stuff, so they don’t touch any of the old data
If your app fits these two properties (and doesn’t run on MySQL), I’ll say that no, with a few exceptions, you don’t need to test your migrations.
You should definitley have them reviewed by your team if possible and do a dry run before allowing migrations to run against produciton data, though. And, if possible, work with a copy or at least a subset of your production database, since made-up entries will never be as good as the real thing.
But your app might not be like mine
Not all apps are web apps. Some apps require more complex migrations, that transform or delete columns and their data. And some people use MySQL.
Note: This is not an anti-MySQL post, but if you use MySQL, you should be aware of the fact that it cannot wrap multiple DDL statements into a transcation. This means if a migration fails halfway through, you might have a database in an inconsitent state that you need to roll back by hand, or somehow take the half-failed (or half-successful?) transaction into account.1
For example, iOS applications can use the CoreData framework and SQLite3 to store their data. Given that the data is stored on the devices themselves, and not in a central location that is under your control, you will definitly want to do everything in your power to make sure the migration runs without problems on all of the devices.
Or you need to transform or delete a large amount of data. Even with backups it is a pain in the arse to undo such an action, so you will want to be sure that your migration does The Right Thing.
The Answer
As so often, there is only one answer to “Should you test your migrations?”: It depends. On the type of app you’re working on, on the database you’re using, and on the kind of migrations you need to run. Use your own good judgement, and when in doubt, more testing will hurt you far less in the long run than not enough testing.
And keep in mind that it is easier to remove restrictions than to impose them, so when designing your database layout and types, try to make everything as restrictive as possible.
Tweet
