Phil Factor

9 May 2024

• Flyway
• Database migrations

Flyway’s Baseline Migrations Explained Simply

The “Flyway Commands Explained Simply” series of articles provides simple, visual explanations of what each Flyway command does, how it works, how and how not to use it:

What is a baseline migration?

A baseline migration file is a single script that will build a particular version of the database. It will migrate an empty database, or one that Flyway has ‘cleaned’, to the version specified in the file name, creating all the database objects in the correct dependency order. For example, a script with the prefix B250__ is the baseline script for migrating a database from clean to version 250. If you’ve already applied Flyway migrations to a database, then baseline migrations will be ignored.

Baseline migration scripts are supported in all editions of Flyway, though only the Enterprise Edition will auto-generate them. We briefly discuss alternative strategies to using a baseline migration at the end of the article.

When do we use baseline migrations?

There are two main use cases for a baseline migration file:

• Consolidation of historical migrations into a single script, for rapid provisioning of new database copies
• Recreate the current version of a pre-existing production database in a single script, so it can then be developed via Flyway Desktop

Consolidation of historical migrations

The primary use case for a baseline migration (B) file is to consolidate a long chain of historical migration files into a script that creates the same database in a single leap. For example, running a baseline migration file with the prefix B2.5.0 will produce, from an empty database, the same database as running the versioned migration files V1.0.0 through to V2.5.0, inclusive.

In this way, baseline migrations provide a simpler and faster way to create new development or test copies of a database, at the baseline version or later, without causing any disruption to the original migration chain, or to work on existing databases. Flyway will only use baseline migration files on new or cleaned copies of a database.

Why simpler? Often a long chain of migration files records long-forgotten twists and turns, blind alleys and failed ideas that no longer exist in the current version of the database. A production system will, hopefully, never have to be recreated from them. In development, however, we can use a baseline migration to replace this “thicket” of migrations with a single, simpler script that will create the same version, starting from an empty database.

Why faster? Flyway no longer needs to re-verify checksums for every file and then execute the whole series of migrations (such as from V1 to V250).

Capturing versioned ‘build script’ for production schema

A related use case for a baseline migration is to capture a single ‘build script’ for a production database that will reproduce the current production version on an empty database. This is effectively just a ‘special case’ of consolidation.

For example, let’s say you have a production database that was previously deployed by a tool other than Flyway, such as SQL Compare. As the production database would have been modified by a change script on every deployment, you might have a hundred SQL Compare synchronization scripts that got the metadata of the production database to its current state. When you create a baseline migration file from the production database it will, in effect, consolidate all the metadata changes in the historical files into a single script that builds the same version of the metadata, and has a version number. This use case is especially relevant for Flyway Desktop users, where this special baseline migration script marks the starting point for developing and deploying subsequent Flyway migrations.

Baseline migrations aren’t directly related to the Baseline command, but in the case where a production database is not currently managed by Flyway then we’d need to run the Baseline command on the production database, giving it the same version as that assigned to the baseline migration file.

Producing a baseline migration file

To create a baseline migration script in Flyway, you typically generate or write a SQL script that represents the state of your database schema where you’d prefer the migration run to start. It is essentially just a build script, but without the database or schema creation code. Most RDBMSs will allow you to generate one from an existing database. Then you remove:

• All database-level DDL – such as CREATE DATABASE statements
• All schema creation statements – schema creation is managed by Flyway
• Any DDL for objects in a schema that isn’t managed by Flyway – you supply the list of managed schemas to the flyway.schemas configuration.

Flyway Enterprise will auto-generate the script in the correct format for the RDBMS you’re using. Name the script according to Flyway’s naming conventions, with a B prefix, and place it in one of the migration script locations within your project.

Advantages of a baseline migration file over a normal ‘build’ script

We can add Baseline migration (B) files to a Flyway project that already contains migration files without causing any disruption to the development of existing databases, since they are ignored by Flyway when migrating existing databases.

This allows the team to preserve the original migration history, alongside baseline migrations. This means that, at least temporarily, different migration routes to the same version coexist, for new and existing databases. New copies will be created using the latest baseline migration, but any earlier existing versions will still use the original migration chain.

For example, if we have a series of migrations V1-V250 plus a B250 baseline migration, and we instruct Flyway to migrate a new or cleaned database to V250, it will use the single B250 file. If we use the same project to migrate a database that is already at V10, then Flyway will run the whole sequence of versioned migrations V11-V250.

At the point where no copies of the database still reference any original migration files with version numbers below the current baseline version, you can safely remove (archive) those files.

Conversely, what if instead of a baseline migration we created a single, consolidated versioned migration, V250? We’d immediately need to archive the original V1-V250 migration files and either clean every existing copy of the database and rebuild it using the V250 file, or alternatively use the Repair command (see later for more detail).

How baseline migrations work

This section briefly summarizes some of the “rules” of using baseline migrations as they apply to new databases and to existing databases.

New or cleaned databases

When we use a Flyway project containing one or more baseline migration (B) files to migrate a new or cleaned database, Flyway will only use the B file with the highest version number plus any subsequent versioned migrations.

In the following example project we have V1-V4 versioned migrations and one B3 baseline migration. Flyway migrates a new database to the latest version by running the B3 and V4 scripts. Only these two files are registered in the Flyway schema history table. Flyway ignores any files with a version number equal to or less than the baseline migration version.

Because Flyway ignores any files with a version number equal to or less than the most recent baseline migration, it means we can no longer migrate an empty database to any of the earlier versions. For example, if we were to issue the command flyway migrate 'target=2' on an empty database, Flyway would instead migrate to the latest version, as above. If we need to build a new database at V2 we’d have to temporarily ‘disable’ the baseline migration file (e.g. by adding DoNotUse to the start of the filename)

Similarly, if a project has B10 and B20 baseline migrations and we ask Flyway to migrate a new database to V11, it will not comply. It will only migrate to version 20 and higher.

Existing databases

When migrating existing databases, Flyway will ignore any baseline migrations files you subsequently add to the project.

In the following example project we initially had V1-V4 versioned migrations, and we have an existing database at V2. Subsequently, we added a B3 baseline migration. If we migrate the existing V2 database to the latest version, Flyway does this by running the V3 and V4 scripts. Only the versioned migration files are registered in the Flyway schema history table. Flyway ignores all subsequent baseline files.

This also means that if we have an existing database that used the B3 baseline migration and we subsequently add a B10 file, Flyway will ignore it and instead use V4-V10 migrations.

What to do if you can’t use a baseline migration file

Instead of a baseline migration file, you might decide to create a new super-migration file to replace all or parts of the original chain of migration files. A versioned migration file is simpler to understand than a baseline migration file but can cause messy difficulties to existing copies of the database. When you introduce it, you will need to ‘retire’ the original migration files from the Flyway Schema History table.

A simple, but drastic, approach is simply to remove the original files from the Flyway locations and then use the Flyway Clean command on all current development and test copies of the database. Of course, you can’t do this on your production database or any other database such as Staging where you need to preserve the production data.

The Flyway Repair command is usually a better approach. First, remove the block of migrations that you wish to retire, and replace the contents of the final migration with a new script that makes all the changes previously done the whole sequence of archived migration files. Make sure that you don’t alter the name of the file, even the description. Then, run the Repair command on all the current copies of the database. This will mark all but the final migration as being ‘deleted’ and replace the checksum for the final migration. This last migration in the sequence now has changed contents that migrates from the version below the lowest of the deleted files.

Conclusions

Small databases,  typically managed by Flyway Community projects, are less likely to suffer from migration-bloat. However, when managing bigger and more complex databases, the number of migrations increases rapidly. The baseline migration file allows you to keep them if you want, but allows a faster way to reproduce development copies , because Flyway doesn’t have to validate hundred of files on many of the Flyway commands, or repeatedly execute them every time you wish to copy a database from source.

Using baseline migrations is by far the simplest way of decluttering a project. It doesn’t affect existing databases using the same migration files. You create a baseline migration file that builds a database at the lowest version that is still useful in development. Once all active builds from the migration locations are using the baseline migration file rather than the original migration files, you can archive the old migration files.

If instead, you prefer to use a consolidated versioned migration (V) script, then you will need to use the Repair technique instead.