I wrote a tool for that in our company (in Java, but could be written in any language) like 10 years ago or so, which is still in heavy use as of today in multiple projects, migrating tens of thousands of (SQLServer and PostgreSQL) databases each year.
Its key feature is probably the usage of a schema hash, which seems to be pretty unique among DB migration tools (at least the common open source equivalents to what I wrote don't seem to do this). It works like this:
- The tool can hash the current schema found in a live database that it's connected to
- It also knows hashes for all schemas ever created for the application (these are versioned and usually originate largely from an ORM generator, but can also mix generated parts with manually-added ones)
- In addition to knowing these "full schemas" with their respective versions, it knows forward (and optionally backward) migrations between the versions (these are entirely created by developers). Migrations usually perform DDL stuff, but may optionally also convert data either with pure SQL or procedurally (as migrations are effectively stored as Java code, they can do practically anything)
- When run against a database, it can either initialize it to any version of the software using one of the full schemas, or migrate it from any known schema version to any other version to which it can find a migration path (pathfinding is a cheap Dijkstra implementation on the migration graph). It automatically detects at which version a schema is by hashing it and comparing the hash with all known schema versions and their hashes (with the ability to fall back to metadata of past migrations also stored in the database in case two versions result in a hash collision). Before and after every migration step, the hash is built from the current state of the database and checked against the stored values for before and after the migration step in question, to ensure the step finds everything as expected and resulted in the correct final state.
- If a backward migration path can be built, migrating "down" is also possible (but I've never seen this actually used in practice, hence most teams today just skip writing backwards migration steps).
- The hashes are also used to prevent accidents that might happen if someone runs a migration against a database on which some person manually modified anything schema-relevant. In such case, the hash won't match any version, and the migration will not even start. Whatever manual modification has been done must be undone first in order to guarantee that the migration steps can find the exact state that they were written for and thus yield the expected results (this actually saved our asses multiple times).
- Finally, a variant of the tool was integrated in the CI process, which means that all migration steps currently checked in are tested against an actual database with each build and can fail the build if not working. Since the current schema can be generated from an object model, the tests also generate this automatically and check its hash against whatever the currently highest versioned migration would produce, failing the test if these are not equal. Effectively this results in developers being immediately notified if someone did change the database schema (usually indirectly via an object model change) but did not check in the necessary migrations to get old versions migrated upwards.