> The command in array index n upgrades the schema from version n-1 to n.
> Thus, no matter which version is found, the software can bring the
> database to the required schema version. In fact, if an uninitialized
> database is found (for example, in a testing environment),
> it might loop through dozens of schema changes until it gets to the
> newest version.
1. Once you've accumulated years of migrations, it's unnecessarily slow to bring up a new dev/test db. The tool is potentially running many ALTERs against each new table, vs a declarative approach which just runs a single CREATE with the desired final state.
2. The migration tool does not have a way of handling databases that aren't in one of the well-defined versioned states. This can happen easily on dev databases, where a developer just wants to experiment before landing on the final desired migration.
3. Most of these tools require a developer to code (in a specific programming language, or a proprietary DSL) the migration logic, as well as its rollback. There's plenty of room for human error there, especially since most of these tools don't automatically validate that the rollback actually correctly reverts the migration.
In contrast, a declarative approach simply tracks the final desired state (a repo containing a bunch of CREATE statements), and the tool figures out how to properly transition from any arbitrary state to the new desired state. This permits the state to be expressed in pure SQL, with no need for a DSL or custom programming, nor any need to define rollback logic.
A declarative tool can also be used in an automated reconciliation loop, which can handle situations like a master failing mid-schema-change in a sharded environment. This is analogous to other declarative tools, such as k8s.
There are several recent open source projects using the declarative approach for schema management. I'm the author of one, Skeema [1], for MySQL/MariaDB. Others include migra [2] for Postgres, and sqldef [3] for MySQL or Postgres.
Although declarative schema management is only recently picking up adoption, it isn't a new approach. Facebook has been handling their schema changes using a declarative approach for over 8 years now.
[1] https://github.com/skeema/skeema