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

Bust of Developocritus, the Forgotten Greek Philosopher
Bust of Developocritus, the Forgotten Greek Philosopher

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: I work 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.

Previous Post
3 Key Points from My Upcoming Talk, “DevOps: What, Who, Why, and How?”
Next Post
New Free Videos on SQL Prompt, SQL Compare, and SQL Doc with Redgate’s SQL Change Automation

Related Posts

No results found

2 Comments. Leave new

  • I wish it was that easy; we have like a gazillion objects in DBs and some software simply crashes when trying to analyze the DB… not giving names here ;-).

    Reply
    • LOL, no worries, my feelings aren’t hurt. If you’re talking about a Redgate product and have the time to open a support case, we would appreciate it — always want to discover where things aren’t working well and identify ways to fix it.

      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu