execute all migrations in one transaction

[wkalt]

Currently dbmate executes each migration in a new transaction. The potential issue with this is it can leave the database in an incompatible state with either version of the application, which is bad. Potential causes of this sort of failure can be difficult to detect in testing -- db server crash, oom, excessive temp files, tx id wraparound, etc.

I think we should change dbmate to execute all pending migrations in a single transaction. Thoughts?

[amacneil]

Is there a risk that we potentially apply a migration but don't record that in the migrations table, or just that we might end up with only half the migrations applied?

My initial feeling is that we shouldn't apply all transactions in one migration, because some transactions might not run inside a migration anyway, and I can't think of a case where it's a problem that the database has only some of the pending migrations applied, as long as each migration is either fully applied or not, and it's easy to determine which migrations have been applied.

Also note this won't make any difference for MySQL anyway since they don't support transactional DDL.

[wkalt]

Yeah, the nontransactional migrations are definitely a stick in the mud here, and the point about mysql is good. I'm not sure if this is worth doing given the special casing it'll require.

I'm imagining this would be useful in the following situation:
I have V1 of my application deployed, and need to run a series of migrations to support V2. I take my application down for the migration and the migration fails partway through for an unforeseen reason.

If the reason is server crash I think you're right, that the next step would just be to rerun migrate and catch up.

The reason could also be a subtle data related bug in the migration's logic, which maybe wouldn't be caught in dev -- some stray record introduced long ago could cause an unforeseen violation of a new constraint you're adding for example. If this happens you are now halfway between V1 and V2 with no version of the app that supports both, and if the migration was destructive you have no ability to rollback - need to restore from backup or fix your logic.

Thinking about it more it's probably not required to support. If you require this behavior, you can always collapse your pending migrations into a single file.

[amacneil]

Good points. I think in that case (if you're deploying an application with actual version numbers, and you're worried about ending up in some limbo state) I would recommend just merging the migrations into a single file before releasing to customers/operators. You could still do this in a way where it would be nondestructive for any developers who have the incremental migrations applied locally.