Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

run pending migrations in a single transaction #243

Open
wkalt opened this issue Oct 19, 2021 · 5 comments
Open

run pending migrations in a single transaction #243

wkalt opened this issue Oct 19, 2021 · 5 comments

Comments

@wkalt
Copy link
Contributor

wkalt commented Oct 19, 2021

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
Copy link
Contributor Author

wkalt commented Oct 19, 2021

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
Copy link
Contributor Author

wkalt commented Oct 19, 2021

previous ticket, that I forgot about #99

@wkalt
Copy link
Contributor Author

wkalt commented Oct 19, 2021

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
Copy link

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
Copy link

#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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants