What are the most effective schema migration strategies?

Question

I recently came across a migration system that relies on incremental numbers, so the scripts are executed in ascending order. A folder containing a few scripts might look like:

Script1.sql
...
Script1009.sql
Script1010.sql
Script1011.sql

The process of adding new scripts means checking different branches in version control to find the latest number, incrementing it by 1 and praying you don't raise a file conflict because someone did the same thing before you could push to the repo.

The only other way I know about is a dependency method: each script specifies a script it's dependent on, so the scripts are linked together in a hierarchy... which is great until someone mistakenly creates a circular dependency.

So: what other methods are there, and why are they (in)effective?

It should also be noted this is a theory question, I'm not looking for software.

Answer 1

I'm considering this issue as well.

Another way to migrate schemas would be to use the meta-data on the RBDMS to get the difference between a source database (migrated, up-to-date) to a target database. There are "sql diff" libraries for this.

A potential advantage of this over the migration strategies you list is that typical implementation migration change code is then SQL. The implementations I've seen of the numbered file/dependency-tracking file strategies you list above those are in application language code that will generate the SQL, but the SQL updates themselves is not versioned. This might make it opaque for database administrators. Another advantage of the sql diff approach might having this SQL diff might it easier to test on a staging database from the standpoint of DBA concerns like query performance, DDL locks, etc.