Phil Factor

22 October 2021

• Flyway
• Team-based Development
• Database Build tasks

Getting In A State Using Flyway Baseline Migrations

What is a baseline migration script?

A baseline migration script, in Flyway Teams, is a single script representing the ‘state’ of a particular version of the database. It provides a single script alternative to running the entire set of historical migrations, when creating a new copy of the database, and you can add them to existing Flyway Teams projects without disrupting any of your database environments, since they are not used by any existing databases.

How are baseline migrations used?

Baseline migrations scripts (previously called State scripts) are intended for use as a way of provisioning extra copies of the database that can then all be kept up to date from a single set of Scripts locations. You can just create a database ‘environment’, consisting of several databases that share the same script ‘locations’. When you need to create a new copy of the database, at a specific version, you use the latest static script, and then update it with any subsequent migration scripts. The new database ‘joins the club’. The baseline migration scripts don’t get used by the original environment of databases, just by the additional databases that you need to provide.

To create test databases, or to create branch databases, without the use of baseline migration scripts, you normally need to execute the whole sequence of migration scripts every time you need a fresh copy of a particular version. In cases where there could be hundreds of these migration scripts over the lifetime of a database project, it much simpler and quicker to create a single, cumulative script that represents the state of your database after all those migrations have been applied. Flyway selects the Baseline Migration script nearest to the version of the database that you specify and then executes every subsequent migration file.

Creating a baseline migration script

A baseline migration script is possibly better imagined as a migration script that goes from zero to the version. It mustn’t include the flyway_schema_history table; it mustn’t use transactions or engage in anything that is non-transactional; it shouldn’t clear or initialize the schemas that you’ve specified, because this is done by Flyway.

Baseline migration scripts are prefixed with B, followed by the version of your database they represent, and then a description. For example: B1.6.12__BranchForVogonProject.sql represents the state of your database after applying all versioned migrations up to and including V1.6.12. Baseline migration scripts do not replace versioned migrations, so it is quite usual for a baseline migration script and a versioned migration to share the same version number.

These Baseline Migration scripts used to be called State scripts and had the S prefix. If you’re already using State scripts you can, instead of having to rename them, specify the ‘S’ prefix in the the baselineMigrationPrefix parameter and carry on as before.

When creating a baseline migration script, you will need to tackle both the metadata and the data. The resulting database must be identical to the database that is created normally, purely with migration scripts. If you needbui abu different dataset for testing, then this must be done subsequently.

Executing baseline migration scripts

If you deploy a project containing “B” scripts on a database where some Flyway migrations have already been applied, the “B” scripts will be ignored. In fact, they are only visible in a database’s flyway_schema_history table when they’ve been used to create the database. This means you can safely add these scripts to existing script locations without disrupting ongoing development.

When deploying a new copy of a database, where there is no existing schema table, Flyway will first find the baseline migration script with the latest version and execute it to bring the database up to that version. Then, it will apply any later migrations, repeatable scripts, and callbacks. Any migrations with a version older than the baseline migration script’s version are not applied and don’t appear in the schema history table of the newly built database.

Trying it out: a “build-and-fill” baseline migration script

I’ve provided a GitHub project that contains everything you need to try out baseline migration scripts, without the spadework.

We want to create a database for work on a new branch. First, we use SSMS to create a build script from an existing database that is at the correct version (V1.1.11 in our project). We check the resulting build script to make sure that there are no references to any Flyway schema tables, and that it doesn’t specify the name of the destination database anywhere. We test it out in SSMS to ensure that it builds the metadata to the same version as the migration. We then use SQL Data Compare to create the insert statements, and add them to the script.

This baseline migration script must start with a B prefix followed by the version number (1.1.11). In the sample GitHub project, it’s called B1.1.11__BuildSchemaAndData.sql, temporarily disabled by adding DontDo to the start. Simply remove the DontDo as shown in the following screen:

Before you run Flyway, wipe the destination clean (I just delete and recreate the database usually), and you’re ready to create the branch using Flyway. The script I used to achieve this is here, but nothing special is needed in the parameters to achieve the execution of the static script, as long as you have Flyway Teams.

Schema version: 1.1.11
+-----------+---------+------------------------------+--------------+---------------------+----------+----------+
| Category  | Version | Description                  | Type         | Installed On        | State    | Undoable |
+-----------+---------+------------------------------+--------------+---------------------+----------+----------+
|           |         | << Flyway Schema Creation >> | SCHEMA       | 2021-10-15 16:47:12 | Success  |          |
| Versioned | 1.1.11  | BuildSchemaAndData           | SQL_BASELINE | 2021-10-15 16:47:36 | Baseline | No       |
+-----------+---------+------------------------------+--------------+---------------------+----------+----------+

Hmm. Is the data the same as in the Develop Branch? We can run a check with SQL Data Compare.

Strategies for using Baseline Migrations with Flyway developments

Flyway Teams has some powerful capabilities that make full use of the configuration. The use of a baseline migration script is a good example of this. The new database is within the same project and uses the same script locations as the existing databases. However, the flyway schema history in the database created with the baseline migration is different and must be so if it is to be updated, because it applies only to that database.

Baseline migration scripts during development

The baseline migration comes into its own for provisioning extra copies of the database for testing, where you might otherwise need to copy the database via backup/restore. However, it also helps when you need to create branches with their own script directories, either for team working or for experimenting with features.

Without a baseline migration script, you would need to execute the whole migration chain each time you need to create a new branch database, and when it comes time to merge you might be faced with a formidable problem (see Branching and Merging in Database Development using Flyway). The baseline migration script potentially makes branching and merging much simpler. It allows us to mark the branch point in the develop branch and use the same baseline migration script to mark the start of the branch location. As long as there are no version-sequence collisions or problems with object references, such as changes in column names, the branch can be subsequently merged by copying the migrations to the parent (develop) branch.

For the simpler database that requires just one developer, all you just need one set of script locations, and the baseline migration script is designed to expand from this point without pain. Even as a single developer, you are likely to need create experimental branches to try out ideas or to develop functionality that won’t be required immediately. As you add to the team of developers, there will be extra script locations created, each corresponding to a temporary branch.

Baseline migration scripts for all deployments?

What if you decide that you wanted to entirely replace a vast train of migration files, with all its missteps and best-forgotten diversions, with a neat and quick Baseline Migration?

You can’t just delete all those historical migration files, because they are still needed by any existing databases that weren’t created from the baseline migration script. If you do, you’ll find that the next time you want to do a migration, you get will get an error (Validate failed: Migrations have failed validation) until you put the files back. A Baseline action doesn’t help because this applies only to databases without a Flyway schema history table.

The only way to do it is to rebuild all the existing databases using the Baseline Migration script (once you are sure that no existing database would ever need to suffer an Undo run to a version earlier than that of the baseline migration).

This rebuild is likely to include both the production and the develop branch, as well as all the existing development databases. The rebuild of the production database with a baseline migration file, whilst reliably preserving its data, is going to be an even bigger challenge, especially if you can’t take it offline. Redgate Deploy is designed around this sort of problem. None of this is likely to be worth the gain. For as long as there is a database that requires one of the historic files for any version upgrade, and this likely to be at least the production database, they must all stay.

Where Flyway project information is held

The idea that two different copies of the same database will have very different schema history tables for the same project may seem odd. This is because flyway_schema_history table represent just one level of information about a database project, and a Flyway project uses information at several different levels.

Some information is based on the workstation (the installation Flyway.conf) and applies to all users for all databases. However, for any development work, we also need information at user level (such as UserID and credentials), project level (e.g., migration files), database ‘environment’ level (e.g., the actual databases that need to be maintained), and at the server level (e.g., the port that the database is listening on).

For example, the name and location of the flyway_schema_history table, or the prefix to be used for the baseline migration script (B by default) are project-level configuration details. It would cause all sorts of problems if they weren’t used. This information can’t be on the workstation because it can’t be shared, but instead it must be in a shared network resource.

Some information is at the database-level. Some, but not all, is in the schema history table. Some information may be at the server-level. Here is a typical layout, but it can get more complex (you may, for example, be using different database systems within the one project).

Conclusions

Baseline migration scripts represent powerful magic, but it is best to become familiar with the way that they fit into the Flyway methodology before basing your development methodology on them. Where they score is that they do not disrupt existing database environments because they are only visible to new ‘clean’ databases. This will give an interesting by-product in that the database environments will have different origin narratives in their schema history tables. This opens many possibilities for teamwork and should make branching and merging within development a great deal easier.