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.