Flyway Migrate [SQL] - Handling object dependencies

user_3877393 0
Viewed 304

I have an existing database that I am putting into source control and going to be using Flyway to handle migrations.

Rightly or wrongly, some of the Views, Functions and Stored Procedures have dependencies on each other.

My original file structure follows this format;

R__SP__StoredProcOne
R__SP__StoredProcTwo
R__UDF__UserDefinedFunctionOne
R__UDF__UserDefinedFunctionTwo
R__V__ViewOne
R__V__ViewTwo

An example of my issue is that R__UDF__UserDefinedFunctionOne has a dependency on R__UDF__UserDefinedFunctionTwo. But due to their names, the R__UDF__UserDefinedFunctionOne function migrates first, causing Flyway to throw an error saying it can't be found.

I have mitigated this issue by introducing additional numbering, to force the migration order.

But I feel like there should be a better way of doing this or a setting in Flyway that will allow it to migrate regardless of the file ordering.

I have tried the -mixed=true and -group=true parameters but to no avail.

flyway sql-server sql
2 Answers

The repeatable migrations should always run last. This means, outside of dependencies within those repeatable migrations, any other existing dependencies should already be in place.

As per the documentation, the repeatable migrations are indeed run in the order of their description, name, so, yeah, if you have dependencies on the repeatable migrations, using a naming standard to ensure they run in a particular order is currently the solution.

user_7938960

I was thinking about this issue recently. I would like to write more than I will at the moment and I may come back to edit at a later time. I'm still researching flyway, can you order by folder? My thoughts assume that you can. (And it's totally possible you can't looking now.)

Functions usually do not have dependencies on other objects. They can be put in their own folder that will run before the rest of the other scripts.

Stored procedures sometimes depend on functions, so if they are run after the folder full of functions, that's fine.

Now, stored procedures that call other stored procedures: Let's say there 2 procs that both depend on another proc. And that one proc they both depend on does not depend on anything else. (or maybe it depends on a function.) We can put such procs in another folder under the functions folder. and those procs are always run before all those other procs that depend on them.

But let's say you have:

        A
      /   \
    B       C
  /   \       \
D       E       F

Where this is a proc dependency graph. Regarding the left half, we can put A in the aforementioned folder. But how do we make sure B is always run before D and E? I'd say you can do 1 of 3 things:

  1. Make a second folder for things that depend on a proc and that other things depend on. Then you don't want to let the dependency tree get more than 3 levels high.
  2. Put B in the same folder as A and use a naming convention to ensure ordering. With this, we've mitigated using numbers but we are trying to avoid this.
  3. Keep dependencies at 1 level deep.

Sometimes there is some trigger happiness in extracting and modularization. And maybe this starts to rear its head when using tools like this.

Like above, Why does B have to be its own proc? maybe it should have been an extension of A. And C definitely shouldn't be a thing (YAGNI). And this just echos what you already mentioned:

Rightly or wrongly, some of the Views, Functions and Stored Procedures have dependencies on each other.

user_85462580
Related