Is Flyway Java migration the right tool for this use case?

Question

We have a database which stores, among others, identifiers from an external system. Now the identifiers changed (the system changed the scheme), and the database needs to be updated. It can be done - I have a mapping so I can generate just enough SQL to make it work, and in the end this will need to be done like this.

The question is - is this use case for a Flyway Java migration? I tend to think that it's not the case, but I can't really say why, it's a gut feeling. But, the external system's schema is not versioned, at least not by us, so I feel it doesn't fit into out Flyway migrations at all; I think it should be executed just once, outside of Flyway.

Can anybody with more experience maybe help, explain why or why not?

Answer 1

It's mostly opinion based, but it seems to me as it's just the same as to use a steam-hammer to crack nuts. Flyway is a very useful tool for periodical migrations and for cases, then there is a number of databases, you have to recreate from the scratch or update'em regularly, rather then for a single use.

What is the reason to include some relatively large framework in your project, spend some time to make it work, and use it only once? Furthermore, Flyway need some extra table to exist in your DB to store it's inner info about the the current version and applied migrations. Don't think, that is the thing you really want to have in your case.

As for me, I think that if you have to do this task just once and can do it without Flyway, then just do it this way.

Answer 2

I think one question we should be asking ourselves when we determine whether or not to write a Flyway script for our data migrations is "Is this necessary when creating this db from scratch?"

Flyway uses a versioning system so in your case, would it make sense to flip the values from the old version to the new version when standing up a new environment? What about multiple modifications? Does it make sense to store the old values and apply them sequentially if you are creating a new environment?

If you answer "NO", then flyway is probably not the way to go. Flyway is better utilized for schema changes where the structure of the database is changed and data is converted into the new structure. If you're just changing configuration values, I believe flyway is probably not your best bet, simply because it is not necessary to store all the changes to these configuration values.