Skipped migration versions in versioned workflow

[plutino]

Hi there.

We are evaluating a DB schema management workflow based one Atlas versioned migrations. One particular scenario we try to resolve is when two migrations are merged out-of-order. For example, two teams work on two different migrations, version 3 and version 4. The PR with version 4 is merged first, and applied to all database in different environments. The PR with version 3 is then merged.

Since Atlas keep full migration history in the versions table, we have expected that it can pick up that a migration is missing and apply it. This could lead to inconsistencies between databases, but it seems fine since, in practice, out-or-order schema changes are usually independent.

This, however, seems not to be the case with Atlas. atlas migrate status and atlas migrate apply don't seem detect the missing migration before the current version, or we have missed something.

What's the recommended practice for this scenario? We don't want to change migration version during merge because we also want to support the use case that a migration can be applied to some environment before it's merged to master. A version change in this case would require changes on the specific environment to be rolled back, which seems harder to managed than allowing migrations to be applied out-of-order.

Thanks and any comments and suggestions are appreciated.

[a8m]

Hey, @plutino 👋

Atlas Sum was created excatly for this use case. Since both Atlas and its users cannot rely on the fact that out-of-order schema changes are independent, having this file (+ enabling migration linting/validation in CI) enforces a true linear migration history when changes are merged into the master branch. Applying migration files in their correct order maintains predictable and replayable migration history that can be executed anywhere, regardless of the actual version the target database is currently on (e.g., a common use case when you ship software and do not control its deployment).

If migration linting is enabled in CI, a user cannot merge their changes into the master branch if a new migration file was added that they are not aware of (because atlas.sum has been updated). In this case, the user needs to rebase their branch on the master branch and execute migrate hash. There's one caveat, though: the linter does not enforce that only the last line (in atlas.sum) can be updated, and users can still change the history if they really want to - we plan to add a check for it soon.

Attached are two nice screenshots on this subject from a presentation I did some time ago:

[plutino]

Thanks @a8m , this is very helpful.

We would rely on the hashes to ensure a branch is up-to-date with master before merging. Unfortunately, we are not able to use the linter due to this bug. The hashes by themselves cannot prevent out-of-order versions being merged since the re-hashed checksums are still valid. This leaves the space for that potential that a migration may be skipped in prod, which is the reason why we are looking for a new solution to replace go-migrate. We have hoped that migration tools like Atlas that stores the full migration history would be able to detect the skipped migrations. In our use case, it's better to run migrations out of order than skip one.

Here is an example of the scenario I described:

The current atlas.sum:

h1:R+g+3CNeFy+dX1LtmPZnYsheypCsjKvdPWuXauvwv6I=
20230330000000_init_import_exts.sql h1:rFe+aWxdLWLh5lw76hkV94/+F855jzN3lZ2v6devtcc=
20230330000010_init_import_tables.sql h1:5fWPYP8kSGI486PrT2+12VA9ycRvriT4P29o9KDaE2k=
20230330000020_init_import_funcs.sql h1:aiaD76+0tgx3AFqXhAV3ZXn0qjysau+jKiyifiesgks=
20230424150049_test_add_fables_table.sql h1:2IxbTModrcierP64GSFpfla5ZILEzWAZjUOmyNvH38s=
20230424151047_create_wolfs_table.sql h1:QpHSLXLSTA9vq4iR3HBHjmpYeDzkqmRM0mLRkS2qcNE=
20230424161329_create_ducks_table.sql h1:wDMUr7Owg8rcL88oQjdF9fYtdwXydFKV6vFa7BVADsQ=
20230424181813_create_horses_table.sql h1:keyNWPLPtCH4DtJhBs5zX0Na1j9RPKsURF5yJuxJPsM=
20230425155632_create_donkeys_table.sql h1:E6gOzXjN6Re02RY68DHHeU1mfpZSkERsLh+fmJOT84w=

And the output of atlas migrate status:

Migration Status: OK
  -- Current Version: 20230425155632
  -- Next Version:    Already at latest version
  -- Executed Files:  7
  -- Pending Files:   0

There are 8 migration versions as indicated by the atlas.sum file, and the db status shows OK with only 7 versions run.

Another related issue for us is that we would like to support a workflow in which migrations might be applied to one of the shared dev environments before they are merged to master. If we enforce a strict linear version rule at merging time, this means that the same migration might have a different version when it's applied in a feature branch from when it's applied in master. We would need to either support migration rollback to get the database in the dev environment to its old state before the migration, or allow dirty migrations so that duplicated changes from different versions might be ok. What is your recommended practice in this case?