State-Based vs. Migration Based
Great insights.
Yes, there are two ways to tackle database version control: state-based (declarative) and migration-based (imperative). However, I think it’s worth shining more light on the qualities of the two, especially as they apply to DevOps.
The migration-based (imperative) is the best solution for teams looking to safely automate and accelerate the database release process. Leaders like Martin Fowler make the point in depth. ( see article: https://www.martinfowler.com/articles/evodb.html) The issue of multiple developers and parallel development efforts leading to Frankenscripts is only an issue when there is no governance. It’s a matter of making the process change to check in all code – including database code – into source code control. As a further step, you can treat database code just like app code and manage change deployments to databases using automation tools (tools built to specifically automate and manage database deployments are out there – go out and find the one that’s right for you) – effectively eliminating undocumented, out-of-band changes.
If everyone has to check in code, and if automation is the only way to make changes, you eliminate the Frankenscript scenario. And let’s be honest: a process change to apply source code control to database code is a relatively simple fix; if you are really struggling to ask developers to check in database code, you have issues much worse than your database release process.
There are two fundamental reasons why the migration-based approach is the indisputable preference for bringing DevOps to the database release process. With migrations, the change itself is a first-class artifact, enabling the “build once, deploy often” philosophy, and enabling predictability, consistency, and automatability. The production release is not going to be a surprise if you are deploying the same artifact to it as you did to the test server. Next, the migration-based approach promotes small, incremental changes – another central tenant of Agile software development. The state-based approach doesn’t meet either of these requirements and necessitates manual review and oversight in addition to its other shortcomings.
For more complex database environments, the state-based approach is actually substantially worse than the migration-based approach. Let’s be serious: you really want or expect everyone who needs to work with the database to contend with the full declarative state model of a large, complex database? And yet somehow asking developers to check in code is hard?
And are you really going to rely on a tool to generate a script to get from an already complicated state to the future state you have declared without having it trip over potential data migrations and the scores of views, procedures, and functions that are interleaved in a complex web of dependencies? And when something inevitably goes wrong, such as the simple case of splitting a table, are you going to force developers to hand craft the solutions with a complex state representation? Honestly, who are you kidding?! A state-based approach to large, complex databases is impractical unless you are an academic employing a team full of data architects. For those of us software developers in the real world, the small, incremental changes with a migration-based approach is the only practical way to for us to work with large, complex database systems in an Agile/DevOps environment.