I ran across an article about state v migration based development for Snowflake. While I think that this article is a bit slanted because the author is building a tool, it does present some good reasons why people worry about migration-based approaches. This article also references a work from Alex Yates on the two approaches.

Everything we do in relational databases, and in some other platforms like Snowflake, is a migration script. If you run SQL Compare and get the differences, you are generating a migration script. It might contain lots of changes, but it's still a migration script. I wish I could write CREATE OR ALTER TABLE scripts, but that's not a part of the SQL specification (yet).

The concerns in the article are valid, though some are a bit outdated. I know that I am biased a bit as my employer, Redgate Software, sells tools in this area. However, over the years, we've supported and worked on both approaches. We do a lot of research, and I get to provide feedback on the good and bad of how we approach the problem. Ultimately, I think the world is very chaotic and we should be as flexible as possible with developers.

I find that most developers like the state approach. Even if they want to write code themselves, having an easy way to capture the state of objects and keep track of them is useful. When we see a table difference, we want to see a view of the old table and new table in a CREATE TABLE statement: the state approach.

For deployments, we always need migration scripts, but we want to build them as appropriate for the situation. Cherry-picking out specific changes is often what we need when urgent hot fixes need to be deployed, or a piece of work isn't tested. In those cases, we might need to deploy some changes and not others, which often require us to put a subset of changes into a migration script(s). That's a hassle, and these situations are often where we find humans making mistakes.

Ultimately I prefer state sometimes and migrations others. If I had to pick only one, it would be migrations, but I'd ensure I had a state view of the schema using a tool like SQL Compare. I want to be able to see history and capture changes. in a human-readable format.

One approach isn't better, and both have advantages and disadvantages. We are often under pressure to be more like DevOps software developers, adaptive and agile. We can use either state or migrations to do this. The important thing is that your whole team understands the approach we are using and the potential limitations. They also need to work together to smooth out your database deployments.