State vs Migration for Database Source Control - decide based on one question

One controversial topic in database development is how to properly store and deploy database changes.

This is generally described as choosing between two options, which are approximately as easy to understand as Greek philosophy:

• State based - A Platonic view of the database as a snapshot of a set of forms in a given state at a point in time
• Migrations - An Aristotelian view of the database defined as a series of scripts which generate change over time

Yeah, I never really liked arguing about philosophers, either.

Scratch all that. There’s a simple question that you can use to decide whether a state-based or migrations-based approach is right for a given database.

Here’s that question:

Do you want at least 95% of your database changes to deploy using auto-generated code?

There you go, that’s it.

Look back on your life-long experience with Clippy, with Siri, with every automated telephone system you’ve called in the past, including those where you ended up yelling “CANCEL MY ACCOUNT!” or “OPERATOR! PLEASE CAN I SPEAK TO AN OPERATOR?!?!” into the line because you were stuck in a loop.

Think about Alexa, who may get into conversations with your television sometimes. Think about the driving directions from Waze that have you make four left turns in a row. Think about those times when you wonder if Google Maps is sending you somewhere simply so it can pick up traffic info, or if it really thought that was a good idea.

If you think about those examples and want to use un-modified computer generated database change scripts 95% of the time, a primarily state-based approach is right for you.

That’s because a state-based approach looks at the current state of the in source control, compares it against a live database representing the desired state, and generates the shortest “directions” to get from one to the other.

If you want to give custom directions 5% of the time or more, a primarily migrations based approach is right for you.

Why 95%?

There are 50 ways to leave your lover, and there are definitely more than two ways to store and deploy your database changes.

That’s because vendors have created hybrid versions between state and migration approaches that allow you to primarily use one approach, and occasionally use a bit of the other. (Disclaimer: At the time I wrote this article, I worked for one of those vendors. Here’s a whitepaper where we talk about state-first vs migration-first, and why we offer hybrids rather than absolutes.)

But these two approaches are difficult to unify, so even a “hybrid approach” has a strong side.

Do you mean migrations-first tools don’t help me write SQL?

Nope. Migrations based database deployment tools may have very nice engines to auto-generate changes for you, too.

The differences are that a migrations-first tool generally makes it much easier for humans to review change code and customize those scripts – making it easier for you to control exactly what happens, and how many steps are taken to make it happen.