Tool idea: graph-based DB migrations

We use Phinx to manage our DB migration scripts in RelayRobin. Just like every other migration tool under the sun, this allows for a predictable list of changes to build the schema from nothing to the latest master state.

But there is always the problem of VCS merges creating gaps in the change list, stepping on each other, etc. And another big enterprise-specific problem is bullet-proofing rolling cluster node upgrades - it would be great if the running application code could compare the version of the schema in the DB against the version it expects. This way out-of-date nodes could gracefully refuse service on schema that is too new or too old.

The way I would love to deal with migrations is to apply a Git-like model. Each migration step is a way to change schema from one state to another, like a Git commit that is a representation of the delta between repo snapshots. The "down" part of any migration step is the reverse of the schema delta changes. The app should declare which schema state (identified by some kind of symbolic ID, likely) it requires to persist itself. Kind of like the "HEAD" or "master" commit pointer in a Git repo pointing to the latest state in that daisy-chain of deltas.

So, when two developers independently change schema, what will happen is that the "HEAD" pointer in the codebase will trigger a merge conflict. And whoever is resolving the conflict will have to create a "merge" migration - if the schema forked from common state A into states B and C, this migration will know how to apply changes to bring them both back to common state D.

The big win in this approach is also presence of that "HEAD" schema state pointer. It allows the application to detect and fail fast when it is talking to a stale database state (during a cluster upgrade, failed migration, etc). And that prevents many subtle bugs.

This is just a half-baked concept of a graph-driven approach to DB migrations, and has some issues as well. But it would be nice to get closer to resolving the problems with existing DB migration management tools.