Enable out-of-order migrations by prefixing with timestamp instead of integer

[shanev]

In team environment where everyone is working on their own branch, the integer-based migration approach breaks down as it causes collisions.

One solution is to prefix migrations with a timestamp instead of integer. The version table can keep track of the timestamp instead of the version number.

[ernsheong]

Coming from Rails, your proposed solution is something I expected as well. But from experience the Rails approach has also caused a lot of pain in the past.

One possible suggestion (YMMV) is to fix your process: one person is in charge of migrations, and he/she has to approve it. This way one person is responsible for the DB schema, and can vet issues before going into production. This person is also responsible for determining which migration should go in first, and assign numbers to the person.

Not the painkiller though. Just a thought experiment in my head should my team actually scale.

[vmihailenco]

version is defined as bigint so as long as you properly pad month and day with zeroes it should be working fine, e.g. 20190419 is a valid version number and it should be working without any changes.

[haarts]

@ernsheong What pain are you referring to? I, too, come from Rails and have liked the timestamp based approach. But I've never worked with too big a projects.

[ernsheong]

@haarts The pain happens when there are migration conflicts, which can happen from time to time in a project with multiple engineers. Rare but possible. Admittedly the single integer increment will definitely conflict more with multiple people on the same project. Can check out https://github.com/robinjoseph08/go-pg-migrations for an alternative.

Has anyone tried, if using timestamps with this repo does it run migrations out of order?

[Janther]

I'm also trying out the timestamp approach and works well here but there's a bug when executing down.
Instead of going to the previous version, it just subtracts 1 from the current version.

[Janther]

I tried creating a migration on a previous date from the current version and the migration doesn't run.

Yes, this library does support having a large number as an ID for a migration.

But if 2 branches with different features add a migration, there shouldn't be any extra step when merging into master.

In most cases these migrations won't conflict when altering the schema or seeding data).
Just in the rare case when this happens, there should be a conflict solving step.

I know I come from the Rails world and thus I'm very opinionated in this case but I see the benefits.

What I see as behaviour here is the following:

• each migration has a timestamp as part of the file name.
  • the format is yyyyMMddHHmmss and it's automatically generated on the UTC time of the creation of the file.
• database entries don't need ID, just version (which is unique and works as the ID), created_at is a bonus.
• when executing up
  • migrations are read in order of this integer.
    • if there's an entry in the database with this migration ID, the migration is skipped.
    • otherwise the migration is run and the entry is added.
• when executing down
  • the database is read in order of succesfully run migrations from bigger to smaller.
    • down functionality is executed and the entry is removed.

[vmihailenco]

It looks like https://github.com/robinjoseph08/go-pg-migrations is working like that.

But I kinda like the fact that you need to manually resolve conflicts when there are migrations with the same id - it sounds more bullet proof in theory, e.g. when migrations try to rename the same column. Such extra safety should be worth small annoyance it creates...

[Janther]

Thank you @vmihailenco I had a look at it and changing from this solution to that solution was really easy.

[belden]

Within our work project, we've adopted a rule that all migrations simply are named [N]_migrate.tx.{up,down}.sql, where N is the next highest available integer in the migration sequence.

This is designed to deliberately cause merge conflicts whenever possible.

---

Previously, we were naming our migrations things such as:

19_add_foo_column_to_bar_table.tx.up.sql

but we ended up in a scenario where two separate branches both introduced a migration-19. The other one was something like

19_add_baz_column_to_bux_table.tx.up.sql

In a world where we have both of those migrations on a branch, eg integration, which incorporates both feature branches: then whichever migration happens to be merged to integration first is the actual claimant of the db at version 19. The other migration which also claims to be version 19 simply will not be run at all.

---

Forcing both branches to introduce the same file name (in this case: 19_migrate.tx.up.sql) means we end up with a merge conflict when the second branch is merged into integration. A merge conflict in this case is a good thing, because it indicates that the underlying db schema of the project is about to have two changes happen to it: and neither of the feature branches which introduce those changes is designed to work with the changes from the other branch.

---

Having the ability to do a timestamp-based ordering of the db schemas would solve all of this.