Is there any way to execute repeatable flyway scripts first?

Question

We use flyway since years to maintain our DB scripts, and it does a wonderful job. However there is one single situation where I am not really happy - possibly someone out there has a solution:

In order to reduce the number of scripts required (and also in order to keep overview about "where" our procedures are defined) I'd like to implement our functions/procedures in one script. Every time a procedure changes (or a new one is developed) this script shall be updated - repeatable scripts sound perfect for this purpose, but unfortunately they are not.

The drawback is, that a new procedure cannot be accessed by non-repeatable scripts, as repeatable scripts are executed last, so the procedure does not exist when the non-repeatable script executes.

I hoped I can control this by specifying different locations (e.g. loc_first containing the repeatables I want to be executed first, loc_normal for the standard scripts and the repeatables to be executed last).

Unfortunately the order of locations has no impact on execution order ;-(

What's the proper way to deal with this situation? Right now I need to specify the corresponding procedures in non-repeatable scripts, but that's exactly what I'd like to avoid ....

Answer 1

I found a workaround on my own: I'm using flyway directly with maven (the same would work in case you use the API of course). Each stage of my maven script has its own profile (specifiying URL etc.)

Now I create two profiles for every stage - so I have e.g. dev and devProcs.

The difference between these two maven profiles is, that the "[stage]Procs" profile operates on a different location (where only the repeatable scripts maintaining procedures are kept). Then I need to execute flyway twice - first with [stage]Procs then with [stage].

To me this looks a bit messy, but at least I can maintain my procedures in a repeatable script this way.

Answer 2

According to flyway docs, Repeatable migrations ALWAYS execute after versioned migration.

But, I guess, you can use Flyway callbacks. Looks like, beforeMigrate.sql callback is exactly what you need.