Document migration order behaviour.

[kevincox]

I can see that the migration ID is the current date by default which leads me to believe that migrations are applied in order. However there is a lack of documentation of what happens if an out-of-order migration is identified.

For example image that my DB is at version 1. Then two developers make feature branches and add migrations 2 and 3. If 3 merges and migrates first what happens when migration 2 gets merged?

It seems that there are 2 major options here:

1. Reject migration 2 ("Error: Out of order migration 2 for DB version 3.")
2. Apply migration 2.

I think option 2 is generally preferable however it does cause complications if the two migrations conflict. If you are lucky you will get an error on conflict, however the rare case could be that you get a different result applying 3 then 2 as opposed to 2 then 3. This would lead in the production DB having a different schema than developer machines which could be a source of bugs.

1 is annoying upfront but can be fixed by manually renaming migration 2 to come after migration 3. However depending on the rate of change of your project this Compare and Set style work can quickly become tedious.

Either way the current behaviour should be documented.

[amacneil]

Correct - dbmate's behavior is that we will apply all unapplied migrations in numeric order (your option 2). We intentionally do not error out here, because the situation you describe is extremely common (developers working on separate PRs which land out of order), and 9 times out of 10 applying them in a different order is a non-issue. Also, in the unlikely event that there is a conflict, it will usually result in an error rather than an out of sync production db (e.g. if two developers try to rename the same column).

In my experience this works fine for teams up to a few dozen developers working on a single project, and beyond that you probably want a dedicated release engineer/manager anyway.

1 is annoying upfront but can be fixed by manually renaming migration 2 to come after migration 3. However depending on the rate of change of your project this Compare and Set style work can quickly become tedious.

It's also worth noting that renaming migrations in this fashion will mess up the local development databases of your other team members (since we would try to apply migrations that have already been applied), which depending on the size of your team can be equally painful. Hence why it's easier to simply allow migrations to be applied out of order.

Thanks for the comment, I will document this behavior.

[kevincox]

Thanks, the described behaviour is what I would want for my use cases. Just was missing that detail when evaluating the project.

[amacneil]

Great!

I'll leave a note here for future readers that if anyone really does desire the first option (reject out of order migrations), it would be easy enough to add a --strict-order flag or something. Happy to accept a PR.