Hash signatures shall be checked on `migrate` call

[PizzaPartyInc]

Feature description

This is mostly a repeat of what was described in Discord chat, but duplicating here as well for better readability.

Currently, when migrate command is executed, it only checks hashes on the level of database constraint whenever a new migration is applied. But some cases might benefit from comparing existing migration file hashes against hashes stored in database.

Motivating example

Let's say that we have a project with 15 migration files. This state is in branch-1

Now, a refactoring change arrived and we decided to pretty-up existing migration files by combining and re-generating them. We combine some migrations, resulting in 10 different migration files, drop the old database and re-create the database with new migrations. The resulting schema is still the same, we are just using different migration files. Let's say this state is in branch-2.

If we have branch-1 checked out with 15 original migrations applied, switch to branch-2, and run migrate (without running db-reset beforehand) - we get Already up to date message and no errors.
The way I see it, it happens because we have 15 migrations applied, and migrate command cannot find 16-th migration to apply, thinking that all is good.

I would have expected to get an error, stating that there is some sort of mismatch between hashes that are stored in graphile_migrate.migrations table and the files that migrate command uses, but we get no errors.

I could have argued about this, that perhaps this can be checked explicitly by the calling code before migrate command is called, but a reversed case actually does what I would expect.

If we have branch-2 checked out with 10 migrations applied, and switch to branch-1 and try to run migrate command - we get an error insert or update on table "migrations" violates foreign key constraint "migrations_previous_hash_fkey", because migration 11 apply attempt fails because of mismatched hash.

Context of our case: we execute migrate from the code on app startup. During deployment, it was expected for service to fail to start, giving an indication that migrations are wrong... but it started and worked fine 🙂

Supporting development

I [tick all that apply]:

• am interested in building this feature myself
• am interested in collaborating on building this feature
• am willing to help testing this feature before it's released
• am willing to write a test-driven test suite for this feature (before it exists)
• am a Graphile sponsor ❤️
• have an active support or consultancy contract with Graphile