I’m using/working on some old SAAS projects written in Java. There is a lot of technical depth, as is to be expected with older projects that many people have worked on over many years. However, one pain point I’m not used to keeps coming up over and over: (broken) repeatable migrations.

Migrations are a handy way to get a database to a known state, and many blog posts and tutorials cover the why and how. But Flyway also supports “repeatable” migrations: migrations that are not based on a version, but that run whenever the migration changes.

To me, this concept just breaks the whole idea of DB migrations. As soon as they are used, it has the potential to break any subsequent migrations, because they can no longer assume the state of the DB to be correct. Even if they do not break things, the database of different environments can differ in subtle ways depending on when some of the repeatable migrations were ran.

The Flyway tutorial says

They are typically used for

• (Re-)creating views/procedures/functions/packages/…
• Bulk reference data reinserts

How is this a good idea? Views/procedures/functions/packages/… are all typically very much depended on the database schema. Why would you decouple them from the versioning system used for the DB schema? The same goes for data insertions, “bulk” or not.

If e.g. a table is changed in a (versioned) migration but a stored procedure to use that table is managed by a repeatable migration, Flyway will happily change the table, but the stored procedure will be broken until it is updated in the repeatable migration.

IMHO, repeatable migrations should come with a massive disclaimer to think twice, drink some coffee or nervous system stimulant of choice, and think again, before you use them. There might be a use for them, but any I can think off are better achieved in a different way.

Is there any actual sane use for repeatable DB migrations? I have the feeling my predecessors used them far too often, for all the wrong purposes.