Tonie Huizer

18 July 2022

• Flyway
• Desktop Development
• Database Build tasks

Re-baselining a Database using Flyway Desktop

What is a Flyway ‘baseline migration’?

Phil Factor already covers this topic in some detail, see Getting In A State Using Flyway but, briefly, a baseline migration is a single script that will ‘build’ a particular version of the database, from scratch, creating all the objects in that database in the correct dependency order. For example, a script with the prefix B001 is the baseline script for building version 001 of the database.

Running and tracking migration scripts in Flyway Desktop

In Flyway Desktop, this special baseline migration script is the starting point for developing subsequent migrations. We capture the “initial baseline”, the B001 script, from the current production version of the database, to which we’ll eventually be deploying changes.

If a developer runs this B script on an empty development copy of the database, the schema of their development database will now match that of the production database. Whenever developers alter their development copy, they use Flyway Desktop to save the schema changes to a “schema-model” and then generate a new Flyway versioned migration (‘V’) script that does the alteration.

Each ‘V’ script describes the changes required to move a database from its current version to the version identified in the script prefix, while preserving all existing data. We end up with a ‘B001’ script followed by a series of ‘V’ scripts (V002, V003…etc.) that Flyway will run, in version order, to migrate a target database to the latest version, or to any required version.

The following screenshot show the contents of the Migrations folder in a Flyway Desktop project:

In the default schema of every database that Flyway manages, it creates a special table called the flyway_schema_history table, where it records what files were used to build the current version, who created which version, and when.

You can retrieve details from this table by running the Flyway info command. In Flyway Desktop, on the Migrations tab it automatically shows you output from the info command, for the connected database. Alternatively, you can simply query the flyway_schema_history table directly:

If you’re relying on a query to return the current version (as part of an automated process) then you also need to check the success column:

What if I need to “re-baseline” a database?

What if, over time, we’ve accumulated hundreds of migration scripts, with many database objects being created, altered, and dropped across many migrations. Every time Flyway needs to create a fresh copy of the latest version (V205, say) of the database, from scratch, it must first verify checksums for every script and then execute the whole series of migration scripts from B001 to V205.

Flyway will need to create the latest version from scratch if we request it (i.e., we connect to an empty target database and issue flyway migrate. Occasionally, it will also need to do it ‘internally’, when running and verifying migrations in the shadow database.

This might start to slow things down. The obvious solution is to generate a second baseline migration script, B205, that will create the latest version in a single leap!

To do this, we need to create a baseline migration file with the same version number as the latest migration. In the following example we have migrations up to and including V021, so we need to create a baseline migration file that captures all the changes in script B001 – V021 in a single script, B021:

Now if we connect this project to an empty database or issue the clean command on an existing database, then then run migrate, Flyway will simply run the B021 script to create the latest version.

Persuading Flyway Desktop to generate a second baseline migration file

The challenge here is that that Flyway Desktop only supports the creation of an initial baseline when there are no migrations in the project. Currently there is no way in the UI to create a new baseline script, manually.

Well, there is a way, but it involves a small ‘hack’, to trick Flyway Desktop into thinking that there are not yet any migrations in the project. To do so, we rename the migrations folder in the project to migrations_.

Now if we refresh the Generate Migrations tab, it will show us a banner suggesting that we have not created a baseline. Let’s hit the Create baseline button to do so:

The baseline creation process is the initial baseline process as described in the Redgate documentation. The only difference this time is the version number, which should match the latest migration version, 021:

Flyway Desktop will save this B021 file to a new migrations folder. We copy all the other migration files from the migrations_ folder to new migrations folder and then delate migrations_. It’s vital that the whole chain of existing migrations (B001-V021) is preserved for any existing databases, unless you’re going to clean and rebuild all those databases from scratch using the new baseline (this could be tricky for the production database!)

Now when we go over to the Version control tab, and you’ll see that the only change we need to commit is the new B021 baseline:

Creating new databases after “re-baselining”

If we switch the Migrations tab and connect our Flyway Desktop project to an empty target database, we’ll see that only the B021 file will be applied:

We can verify this by running Flyway migrate and then querying the dbo.flyway_schema_history table:

Of course, what this indicates is that you can only have one “active” baseline migration, so the earliest version of the database that we can now recreate from scratch is 021. Let’s say we supported multiple customers, and one customer was stuck on an older version (V014, say). We’d no longer be able to recreate that version from scratch, using our Flyway Desktop project. Only create a second baseline if you’re certain you’ll have no need to recreate an earlier version from scratch.

Working with existing databases after re-baselining

The second baseline file will have no impact at all on existing databases that were created using B001-V021. If we connect our project to the production database, or to any existing database copy that was built using B001 onwards, we can see that the original migration chain is still being used and the B021 doesn’t even appear in the schema history table; Flyway completely ignores it. B021 will only be used for creating any new copies from scratch.

Conclusion

I’ve described a simple ‘hack’ to persuade Flyway Desktop to generate as second baseline migration file for an existing project, in cases where we need speed up the creation of a new or shadow database.

Of course, as always, there are a couple of alternative solutions, one of which is simply to create a build script in SSMS, or the equivalent script tool in your RDBMS. The script must exclude any schema creation and the flyway_schema_history table (both of which must be managed by Flyway). You can then save it as a baseline file, with the correct version, in the migrations folder.

Alternatively, of you’re using SQL Server or Oracle, you can use SQL Compare to generate a flyway-compatible build script for the latest version, as described in Creating Flyway Migration Files using Redgate Schema Comparison Tools.