Migrations sequential consistency problem

[AnatolyRugalev]

The problem

Our team faced a problem with the way go-migrate keeps track of migrations. Let me give an example to make the problem apparent.

Developer A and developer B add migrations in separate branches in time 10 and 20, so they have these directory structures:

Developer A:

- migrations
  - 1_first_migration.down.sql
  - 1_first_migration.up.sql
  - 10_dev_a_migration.down.sql
  - 10_dev_a_migration.up.sql

Developer B:

- migrations
  - 1_first_migration.down.sql
  - 1_first_migration.up.sql
  - 20_dev_b_migration.down.sql
  - 20_dev_b_migration.up.sql

They both create pull requests, but developer B manages to merge it first and deploy it to production which makes his migrations to execute.

Developer A rebases his branch to master in order to merge it next and in CI there's no sign of problem because we create clean database in CI every time we run tests.

After a while Developer A manages to merge his branch as well, but migrate does not detect it as a new migration as it assumes that migration 10 had already been run.

Solutions

I see two approaches in solving this:

1. Maintain all applied migration names in the table
   • breaks compatibility with all migrate installations
   • it becomes much harder to squash migrations and force version
2. Make dependency chain between migrations. E.g. in order to run 20 we need migration 10 before that. This way we can detect a problem in CI so it doesn't gets merged unnoticed: migrate can detect multiple dependencies from the same migration and also detect deleted migrations
   • migrate needs a way to specify a dependency and an extra option to enforce dependency checks (something like -- migrate:after 1_first_migration)

I can invest some time to solving this, but I want to have your opinion on it first

[AnatolyRugalev]

Duplicate of #179, will move there