Locking the table before migration

[kszafran]

Firstly, thank you for this wonderful library! We've been happily using it for over 2 years.

I have a feature request. Sometimes we have multiple instances of our application start a the same time, and they all try to run the same DB migration. Suffice to say, only one instance is successful and the others often crash. Worst case scenario, they could interfere with each other and break the migration.

I was thinking it would be useful to have an option to lock the schema_migration table in order to avoid such issues (or even do it by default). This would be the order of operations:

1. lock schema_migration
2. select version from the DB
3. plan migrations, apply migrations
4. insert new version
5. unlock schema_migration

This way if multiple applications try to run migrations at the same time, only one will get the lock. After the migration completes, the other ones can then get the lock, select versions, and realize that there's nothing to migrate anymore.

I'm thinking of either implementing locking in our applications or contributing the feature here, but I don't know when I'll have the time to do it. Perhaps someone else would like to implement it? For reference, we're using the postgres driver.

[F21]

Hey thanks for opening this issue! Really glad you found it useful 😄

I think this is a good idea, especially for databases that can do table-level locking, so that it doesn't block other instances of the app when running the migrations on a hot database. I don't have enough bandwidth to work on this myself, but I am definitely happy to look at a PR if you're able to contribute it to the library.

[kszafran]

Thanks. It's one of many improvements we want to make to our backend. We might get to it at some point, but I can't promise when that'll happen.

Would you expect the contribution to add it to all possible drivers, or would adding it just to postgres also be acceptable?

[F21]

If possible, I'd love to see this added to all drivers, but if it's too work, I am definitely happy for it to be implemented for just the postgres driver at the movement. I think once we have 1 implementation, it should be easy to copy the implementation and tests to the other drivers and get them working with some minor adjustment.