I’m not anything like a database specialist or guru. But I’ve run into a few tips and tricks over the years that make SQL migrations easier to handle. And as obvious as some of them seem to me now, I’m frequently reminded when discussing these tips that they often are anything but obvious to others, and in fact at one point they were not obvious to me, either.

I make no claim that these are all still “best practice” (whatever that even means), or that this is anything like a complete list. But here goes…

1. Make your database migrations idempotent. In other words, it should be safe to re-run the same migration multiple times, with no change in outcome.
2. If your upgrade isn’t idempotent, wrap it in a transaction. In other words, if you can’t write an upgrade that is safe to re-run, at least put it in a transaction. Then if it fails, your data is not left in an inconsistent state.
3. Don’t write downgrade scripts. Most db migration tools allow you to specify both upgrade and downgrade scripts, which are expected to mirror each other. This allows a clean roll-back, in theory. Instead, I prefer to make my upgrade scripts idempotent (see above tips), then downgrading should never be necessary.
4. Run large changes in the background. If you’re converting 10 million columns from an INT to a CHAR, for example, don’t do this in an upgrade script. Instead, have your upgrade add the new CHAR column, then have a background task handle the data migration from the old to new column. Then drop the old column some time later (see next tip).
5. Isolate destructive changes. The destructive changes (such as DROP COLUMN, ALTER COLUMN, or DELETE WHERE...) are actually impossible to downgrade anyway (aside from restoring a backup). But that doesn’t mean we can actually avoid them. Sometimes they’re necessary. So I always keep them separate. That is, I never do add a column and drop one (sometimes known as a rename) in the same upgrade script. Instead, add your new column in one migration, and preserve all destructive changes for a separate upgrade, ideally one that runs days, weeks, or even months later, when you’re highly confident it won’t need to be rolled back.

That’s all for today. What tips do you have to make database migrations easier?