Flyway: running multiple migrations in a single transaction

4
votes

I've been looking at Flyway as a database migration tool.

The one thing that I have been unable to find a definite answer for is the following:

Can I force Flyway to run all as-of-yet unapplied migrations in a single transaction, instead of having each migration be its own transaction?

In a dev environment it's not an issue, but in a production environment where you would potentially perform multiple migrations from one update to the next, one of the migrations failing would leave the database in a 'half-migrated' state, where some migrations were committed and some not - quite a bad thing.

A workaround would be to simply cram all the SQL required in a single file, but there are issues with that:

  • The production migrations and dev migrations would end up being performed differently, since you cannot know in advance what will be in the migration on dev environment. I guess you could always do a clean and then a new migrate, but this seems to be against the spirit of the flyway design with regard to incremental migrations.

  • The checksums will be different as soon as a new change is added.

Does Flyway still not support such a feature? Does Liquibase, or any other migration tool?

1
database-migrationliquibaseflyway
Liquibase tries to run each changeSet in transaction. Probably you can use updateSql command to generate sql file and post-process it to set up required transaction behaviour and after it apply it using some tool. This pipeline could be implemented for dev/prod to avoid prod-speific issues. - dbf

1 Answers

2
votes

There is no such feature out of the box. It's a great question though and I'd bet it was thought about since Flyway provides the transactional boundaries per migration - hopefully Axel Fontaine will chime in on the technical / design considerations that resulted in this not being a feature.

The FAQ have this and this to say regarding downgrading / failures. The policy boils down to:

Maintain backwards compatibility between the DB and all versions of the code currently deployed in production.... Have a well tested, backup and restore strategy.

In my case, we have being using Flyway for almost 3 years and have abided by the quoted policy. At any given deployment we could have 100 or more migrations running against many databases and happy to say have never had anything untoward happen in production. This all comes down to minimizing the opportunity for failure in your release process.

I used Liquibase on a much smaller project prior to that and don't recall any such feature apart from providing the rollback procedures.