run pending migrations in a single transaction

[wkalt]

Currently dbmate issues one commit per pending migration. I think it should issue one commit at the end, after all migrations have been run.

Migrations are going to be run either before or after the code deploy. If run before, the old code must be compatible with both the new and old state of the database. If running after, the new code is compatible with both states.

Users of dbmate must ensure not only this, but also that the old/new code is compatible with all possible migration states between the first and last pending migration at which a failure may occur. I think this violates expectations - users may segment their migrations for various reasons like logical division or to avoid corrupting previous migrations while iterating in a dev environment. The manner in which pending migrations are split should not affect the end state of the database in case of failure.

If migrations are run in a single transaction, both options are safe:

• If migration occurs prior to code deploy, the migration fails, deployment doesn't happen, and no harm is done
• If migration occurs after code deploy, the code must have been compatible with the original database state, to which the database has been reverted.

Contrast with today:

• Migration occurs prior to deploy, fails midway. Database is now partially-migrated. Old code is hopefully compatible.
• Migration occurs after deploy, fails midway. Database is partially-migrated. Hopefully the new code is compatible. Note that failure isn't really necessary to demonstrate this - even without a failure, clients may see every intermediate state.

The only "drawback" I see is it can cause expensive, successful migrations to roll back, but I think that's probably what you want in the failure situation.

[wkalt]

this conflicts a bit with the transaction=false feature (at least for postgres). I think transaction=false migrations would need to error if more than one migration is pending.

[wkalt]

previous ticket, that I forgot about #99

[wkalt]

Nontransactional migrations will require more thought - it will be common in test code to execute the entire set of migrations at once, which would preclude the strategy described above.

[gregwebs]

dbmate cannot assume what a user wants here because there is no dependency information, and some migrations are not transactional.

There could be a flag --batch-transaction to apply all in a single transaction. This will run into issues where you only want a subset in that batch. In #434 I added a flag to specify a specific transaction. This could be expanded to specify multiple specific transactions to select a subset.

[gregwebs]

#434 supports specifying multiple migrations now, so if there was a --batch-transaction flag it would be possible to specify specific migrations to batch together.