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

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 2

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.