search

db-migrate / node-db-migrate

Database migration framework for node
Other
2.32k stars 360 forks source link

Question: Batch updates & transactions #713

Closed ltamrazov closed 3 years ago

ltamrazov commented 3 years ago

Hi,

We are looking for a migration tool that would allow us to also perform data migrations (like a bulk update on a table). Of course the best way to run these types of updates is through batches so as not to lock the table for a significant amount of time. I was wondering if this is something that is possible with this tool? Specifically, I'm wondering if each migration is automatically wrapped into a transaction and if we have control over this at all? Its my understanding if the entire thing will be wrapped into a transaction, no matter the batches the table will still be locked until the transaction is committed, correct?

Additionally, is it possible to maintain two instances of this on a single DB? the use case is that we would have one set of migrations that are required schema changes that would run on start up of the app and would block the app until they are complete, and a second set of migrations that are non blocking (like data changes) and can be kicked off separately after the app has started. However both of these would be on the same database.

Any insight would be really appreciated!

wzrdtales commented 3 years ago

Not yet, see spec https://github.com/db-migrate/node-db-migrate/issues/687 for rough overview of how the future design will look like. And yes every migration is wrapped in a transaction, that is troublesome for big updates of course, but you can deactivate the transactions per migration and pretty much do the batching yourself right now, but there will be support for that with the upcoming version.

Additionally, is it possible to maintain two instances of this on a single DB? the use case is that we would have one set of migrations that are required schema changes that would run on start up of the app and would block the app until they are complete, and a second set of migrations that are non blocking (like data changes) and can be kicked off separately after the app has started. However both of these would be on the same database.

Sure just adjust the migration folder and the migration table and you're good to go.

ltamrazov commented 3 years ago

Awesome, makes sense. Thank you for the clarification!