Make the migrations not suck

[vito]

Here are some annoyances with our current migration stack:

• They all have numbered filenames, where the number doesn't matter, and you have to remember to add it to the slice in the right place anyway. We've forgotten to add it to the slice in the past, and there's no way to notice (sometimes it's just an optimization).
• We're now hitting build failures on Windows because (we think) we have so many darn files or exported functions or something in that package. See bin-rc #893.
• There's no support for "down" migrations, making it impossible to downgrade Concourse.

Here are some things we have to get right:

• Migrations should never run concurrently. We have our own code to ensure this via database locking.
• Migrations should run transactionally. This is kind of obvious. We should decide what the bounds of the transaction should be though (i.e. per-migration-step or per-entire-migration).

Here are some goals for whatever we do to fix this:

• Down migrations. If possible. Being able to downgrade Concourse is always nice. How hard would this be for some of our existing migrations?
• Using an external library/package would be great.
• Not having to maintain a list of migrations in addition to the filenames.
• Some obvious way to make a new migration.
• It would be nice to be able to squash the migrations every now and then, so on initial start the whole schema is bootstrapped at once.
• It would also be nice to be able to see the schema all at once, as a developer. This could be related to the above point, if after running the migrations it generates a schema file that is then used to bootstrap, or something. (This could make merge conflicts more common though...)

[xtremerui]

Have we considered using goose or mattes/migrate

They both support down migrations and have lots of features we might need

[christophermancini]

https://github.com/mattes/migrate appears to be younger yet nearly have twice the contributors and more consistent activity. It also supports more than sql which might be useful down the road. This would be my vote between the two.

[vito]

Another thing to note: a lot of these tools just have you write .sql files, which is great and all since it's simple, but we've had some pretty hairy migrations in the past that require lots of Go code.

We should consider what we would have to do if all we could write was .sql. Maybe a simple migration that does the schema work, and then a batch operation that happens on startup, and we keep track of these batch operations separately?

[jwntrs]

Im not sure we can get away with running all the .sql migrations up front and then batch operations at startup. It seems like we would run into problems if we needed to do something like create a table, migrate data (with complicated logic that can't be done in sql), then drop a table. Or would the dropping be part of the batch operation? This then seems weird because we have schema changes in multiple places.

[christophermancini]

I think it is worth noting that the mattes/migrate project is considering the ability to have a shell migration driver. mattes/migrate#171

[jwntrs]

Yeah it looks like https://github.com/mattes/migrate is the winner. We're going to go with .sql only migrations for now and look at introducing batch operations if/when we need them.

We are also planning to collapse all pre-3.6 migrations into a bootstrap schema. This means all migrations from pre-3.6 deployments will have to migrate to 3.6 first.

[vito]

I didn't see anything for the "Some obvious way to make a new migration" goal, so I went ahead and added a script:

./scripts/create-migration some_migration_name

All this does is install the migrate cli and run create with the appropriate flags.

I haven't decided yet where to document this (CONTRIBUTING.md starts to feel unwieldy; maybe a wiki is a better approach?), but at least the script's there.

Accepting!