How to manage development of changes in Flyway?

Question

Let's say we're developing a feature and we have need for a new table. Awesome, we make a Flyway entry and we're off and running! Then, a few days later after some testing we realize wait, this column really should have been a blah, or we need a field we didn't anticipate to cache some value for performance, or for whatever reason our ivory tower version of the data model didn't match reality.

The simple solution would be to make another Flyway entry. But since this never even left development that seems like a poor decision. It really should be in the original build. But to do that I have to reset my database, which is a big waste of time for something so simple.

How should I be handling this on a day to day basis to protect my sanity and my time?

Answer 1

There's some documentation over at Flyway discussing the usage of Spawn to supplement your development and testing environments by creating temporary databases and handling the management of those for you. One part of this page explicitly documents "undoing mistakes" and "restoring to previous states".

This sounds exactly like what you'd like to be able to do.

With this, after you've realised that a migration script should've been different, you can run spawnctl reset data-container <your_container> to take the database back to the original state. Then you'll be able to run flyway migrate once again with your fixed migration script as if the original erroneous migration never got deployed.

This should be considerably easier than resetting your database state manually, and has the benefit of taking you back to exactly where you started. You can make arbitrary save points as you go along developing too, so that you can reset back to various known-good states.

Answer 2

Just as a supplement to Julia's answer, the interesting phrase in the question is 'But since this never even left development that seems like a poor decision'. That's a good point: your scenario is that the changes you made failed testing, so shouldn't be in the main migration 'narrative'. If you can adopt a Branch/Merge strategy that adds the migration to the 'develop' branch only when unit and integration testing is complete on your branch, then you can avoid this.

I go into this in a lot more detail here Branching and Merging in Database Development using Flyway. Flyway can manage this pretty easily as long as you can create an initial migration file for the branch that builds the branch version of the database to an exact version (the version at the point of branching). The merge process can be tricky if there are concurrent developments on the same part of the database or on dependent database objects, but that is generally true with database development..

Answer 3

The simple solution has one big thing going for it - it will track exactly what your dev database went through to get to its current state, and replay that exactly in other environments; so you won't have mistakes due to not rolling up changes correctly.

Another solution is writing complementary undo scripts for Flyway (note: paid feature) so that you can navigate up and down the version history until you're happy with it.

Alternatively, this will work without database resets: in DEV:

V1_0_1.sql: CREATE TABLE blah ...

V1_0_2.sql: ALTER TABLE blah ...

Then when you're happy, change the scripts to match reality:

V1_0_1.sql: CREATE TABLE blah (new version) ...

V1_0_2.sql: <commented out>

This can be run in production, and in dev run flyway repair to re-align the file checksums. Or, in a similar vein, you can maintain two sets of scripts, one for dev and one for production which contains only the changes you want to promote. Neither of these will guard against dev and prod drifting apart unintentionally. As mentioned in the comments, the fact that resetting the dev database is a pain is in itself a smell and deserves revisiting.