I work with a lot of customers that are trying to find better ways to build and deploy database code. Some of them use a state or model method with SQL Compare or another Redgate product, and some want to use Flyway and its hybrid migration-based framework. It's always interesting to me how customers lean one way or the other, for various reasons.

No matter which choice people make, we know that using migrations is more effort than a state-based approach. However, I think that it's a better approach, especially because no matter how simple or smooth you think your development process is, at some time there will be a situation that needs a more complex approach. a migrations-based framework allows you to handle the complexity smoothly, without depending on a person to sort out the issues.

It's not just me, but many developers feel the same way. There's an interesting article from a developer that gives some examples of why using migrations work well in different situations. It also acknowledges that the effort is high, but by using migrations you can accomplish complex tasks across time. something I try to get more developers to do. Don't big-bang-deploy-everything, but break things into chunks.

It's not just a simple change. There are a few reasons that migrations are hard, one of which I loved. The author compares migrations to working with live electrical wires. Imagine hanging a ceiling lamp and wiring it up without turning off the power (note: don't try that).

One of the best reasons I have for migrations is that lots of work isn't done at the same time, especially in a team. If each team or each piece of work is broken into separate migrations, choosing which ones to move over from developer is much easier than if all the changes are captured in a state-ful model. You still have to be aware of dependencies between migrations, but having them all separate at the beginning (and hopefully tagged to pieces of work) makes this easier.

The other reason to choose migrations is that I can stage future changes. Imagine I need to make a change to add something in one migration and remove it later. Suppose we add a new tax column and then after Jan 1, we remove the old tax column. With migrations I can pre-write the removal and create a pull request with the "Tax Removal - DO NOT MERGE UNTIL 2024 JAN 1" title, which should be obvious to anyone. I like keeping things simple, which helps when I never know who might need to handle this work in the future.