Phil Factor

6 June 2023

• Flyway
• Variants, Localization
• Team-based Development

Maintaining Variants of a Database using Flyway Locations

What are database variants?

Sometimes a development team must support different ‘variants’ of the same database at a particular version. All variants must share the general improvements and changes in the general part of the database, but then each variant may have some additional or unique functionality.

If, for example, you are providing an inventory database to several customers, each industrial sector may have its own requirements. You may, alternatively, need to provide variants of a database for different versions of database server, so that you can exploit the latest features.

Whatever the requirement, the changes that you make to the core database will need to be carried through to each variant being used. Each release version will need to be created or migrated in every variant.

Whatever system you adopt to develop the database, it is easy to get the system for creating and maintaining variants wrong. You might think of a migration approach as being strictly linear, but you can adapt it to create the core version and then provide a migration from this core version for each variant. If you have, say, five different types of customer application using installations of your database, you install the database to the latest release version, and then apply the variant migration files. These variant files are usually created within the Develop branch and then, after testing, transferred to Main at the time the release is prepared. This sounds like extra work, but if the database is well-designed, there will be very few changes to the variants, and so they will often be blank files that are merely used to ‘stamp’ the database and inform Flyway which variant it is dealing with.

Even if you are using a build-based development system, the provisioning of a specific variant is almost inevitably a post-build migration, however it is performed. In other words, you build the core version, and then apply a migration that turns it into the appropriate variant so that, for example, your accounting system is transformed into its Lithuanian variant.

From the database perspective, there is little difference between a build, and a migration from scratch. The advantage in Flyway’s approach is that whatever way you prepare the release of database variants, you’ve rehearsed their release(s) into production from staging.

Managing Variants in Flyway

We know, from reading questions on forums, that it is easy to create an impressive system to manage variants which is far too complex. Simplicity is important. With Flyway, you can use several techniques to create and test each variant, and there are several features that can help with the development process involving variants.

In some cases, it’s possible to support several variants of each version of a database application from the same Flyway project, by using the ShouldExecute script configuration option in Flyway Teams. I’ve already demonstrated how to use this in my article Flyway Teams and the Problem of Database Variants, to create variants of a function, depending on the version of SQL Server installed.

More generally, though, I prefer to use a shared, hierarchical directory structure, where each variant is in its own sub-directory, as it’s the approach where there is the least opportunity for error. If you need them, any development branch, even main, can have variants as well as branches in this approach (see What is the Flyway Teamwork Framework? for an illustration of this project structure, used within my Flyway Teamwork PowerShell framework).

An obvious way to create the variant via a SQL file would be to use an AfterMigrate callback script, probably with placeholders. For example, to create a variant of a particular procedure for current release (main branch), you’d run the standard migration chain for main and then, once that completes successfully, create the variant using a callback, with a placeholder to switch in the functionality you want. My article Try Before You Commit in Flyway creates a Rollback variant using a callback with a placeholder. In that example, we just alter the outcome of the migration, rather than alter the metadata, but the approach would be similar. It’s easily done this way, but it compromises versioning. An easier and safer method is to use the Flyway locations config parameter to ‘switch in’ the files for the variant you want.

Variants used merely to make database copies

We’ll start with the simplest possible example of a variant. All we want to do is create several copies of the same database, at the same version, with the only ‘variation’ being that each one needs to be on a different server. For example, we might need several copies of the development branch for the testing of applications and downstream data feeds such as reporting or analysis.

I’ll start by doing it the simplest way, but it involves copying project files around, so I’ll then demo a better approach that uses relative paths in the Flyway locations.

Copying project files to the variant sub-directory

Let’s say we need to create a “Test Cell” variant of the current release (main branch). In the very simplest approach to managing variants, we just create a “Test Cell” sub-directory, and essentially make a direct copy of the database represented by the current working directory (the main branch), complete with its Migrations folder and Flyway.conf file.

In the Test Cell directory, you then just change the connection information in the copied Flyway.conf file to point the URL at the Test Cell database.

In fact, in this example, you can see that the project level Flyway.conf file is very simple and has no connection details for the main branch database. It indicates only the location of the migrations, in the Migrations folder of the current working directory, and which schemas Flyway needs to manage. This is because I chose the more secure approach of putting all the connection information to the server and database in an additional Flyway config file within the user profile area, safe from prying eyes. I show how to do this in more detail in Getting Started with Flyway Migrations on Oracle. Basically, you provide a parameter to Flyway in an environment variable rather than specify it to Flyway by using a command-line parameter. This is useful when using Flyway interactively at the command line, running several commands in a session (see Pipelining Configuration Information Securely to Flyway for alternative approaches that either pipe the contents of the config information to flyway via STDIN or use PowerShell ‘splatting’).

Before we run Flyway, we just need to inform it where to find the additional conf file that has the connection details for the test cell variant. If you use the command-line in Windows, you can run RUNME.BAT to use this system, and this must always be configured even if you don’t use it directly:

S:\work\Github\FlywayTeamwork\PubsOracle\Variants\Test Cell Copy>Runme.bat
S:\work\Github\FlywayTeamwork\PubsOracle\Variants\Test Cell Copy>set FLYWAY_CONFIG_FILES=C:\Users\phil\oracle_PubsOracle_Copy.conf

Having done that, then away you can go with a flyway info followed by a flyway migrate:

S:\work\Github\FlywayTeamwork\PubsOracle\Variants\Test Cell Copy>flyway info
<Removed loads of text> 
Schema version: << Empty Schema >>
+-----------+---------+--------------------+------+--------------+---------+----------+
| Category  | Version | Description        | Type | Installed On | State   | Undoable |
+-----------+---------+--------------------+------+--------------+---------+----------+
| Versioned | 1.1.1   | Initial Build      | SQL  |              | Pending | No       |
| Versioned | 1.1.2   | Pubs Original Data | SQL  |              | Pending | No       |
+-----------+---------+--------------------+------+--------------+---------+----------+
S:\work\Github\FlywayTeamwork\PubsOracle\Variants\Test Cell Copy>flyway migrate
<Removed loads of text> 
Creating schema "PEOPLE" ...
Creating schema "ACCOUNTING" ...
Creating Schema History table "DBO"."flyway_schema_history" ...
Current version of schema "DBO": null
Migrating schema "DBO" to version "1.1.1 - Initial Build"
no rows updated
Migrating schema "DBO" to version "1.1.2 - Pubs Original Data"
Successfully applied 2 migrations to schema "DBO", now at version v1.1.2 (execution time 00:10.398s)
S:\work\Github\FlywayTeamwork\PubsOracle\Variants\Test Cell Copy>

This will update the copy, and work fine, but to do it, you will have copied migration files, which carries the risk of an uncontrolled change in one of the migration files. Still, it is a start.

If you eventually want to do all the automation and PowerShell callbacks, then you’re better off using my Flyway Teamwork framework:

Using relative paths in the locations parameter

Now we can improve on this. To fix the awkwardness of copying files, we merely change our list of locations in our Test Cell’s Flyway.conf to access the migrations that are specific to the parent branch, via a relative path. In this case, it is ‘..\..\migrations‘

The local Migrations folder can be empty, in this example, because we don’t reference it. Flyway will only pick up and use the two migration files located in the Migrations folder of the main branch. If we add migrations to the local Migrations folder and reference it, these migrations will be used as well, but only the variant will use them.

This will work just as long as the Flyway configuration at the parent branch isn’t using any location other than that Migrations folder. To make things simple, we’ve assumed that there are no other locations specified in the list that the parent branch is using. We should, of course, be using the entire location list of the parent branch, converting relative references as appropriate. If we forget that the parent branch has references in its configuration file to other locations, we’ll be missing those parts of the migration run.

To avoid this, I restrict use of relative links to variants and strictly adhere to the rule that all migration files are in one place only: the branch in which they were created. At the point of merge, from branch to parent, I simply prepare a single migration file the contains the cumulative changes made in the branch and add it to the parent with the correct version number.

In Flyway Teams, you can use a special file called a ‘Baseline Migration’ that creates a new database at the required version, such as at the version where the branch was created. Every variant will just use this consolidated migration and the parent’s subsequent migrations to build a copy of the database. If you are just using ‘absolute’ paths in your list of locations, you can, of course, simply create a working directory anywhere you like, using the list of locations of the parent branch.

Now, if you need to update several copies of the database, you can just run the following code to ensure that they are all up to date. Each variant must be in a subdirectory of the ‘Variants’ folder. With each variant’s sub-directory there must be a config file containing the correct settings for the variant, including the database and the connection details.

Unfortunately, this re-introduces the security issue of having connection details stored outside the relative security of the user area. To avoid this, you can instead pipe the credentials directly into Flyway, using either the STDIN or PowerShell splatting techniques, as described in Pipelining Configuration Information Securely to Flyway.

Updating Variants that have database modifications

Although it is useful to create an exact copy of a database to a precise version, and I end up doing this often, the time comes to add the code for your variant. The obvious means of doing this is to use, at each version, a file that makes any necessary adjustments to the variant. You’ll use it to execute the migration code to add any special features to the database for the variant, or to improve the code in the variant.

To do this, we need the locations list to include two relative or absolute references, each with the filesystem: prefix. For our example, one of the two list items must reference the local ‘migrations’ subfolder, and the other one the parent’s folder of migrations.

Let’s suppose you created a “French” variant from v 1.1.1 of the Develop branch of our PubsOracle project, to present costs in Euros rather than dollars. We must make sure that there is no pre-existing file in the local migrations folder to run this next test (remove those copies of the two migrations from our first experiment, for example).

In the local Migrations folder of the French variant, you might have a file called V1.1.1.1__French_Euros_not_Dollars.sql. For the French variant. Flyway picks up all the branches’ migration files from the locations and sorts them into version order. We need to make sure that there is always a file in the local directory with the name ‘French’ as the first word of the description, so that we can determine the variant purely by looking via the Flyway info command. Normally you’d put the variant ‘tag’ in the fourth part of the version number. Unfortunately, we cannot put the name of the variant in the version number because Flyway only supports version numbers, not semantic versions.

Even if the variant isn’t changed at a particular version, a blank file will be needed, because of the usefulness of having Flyway record the version of the variant that is embedded in the filename of your new migration.

Whether it contains migration code or not, this file will then be used by Flyway as the final migration file to be executed, and so the database will be stamped with the ‘semantic’ version that represents the variant of the parent version:

+-----------+---------+-------------------------------+------+--------------+---------+----------+
| Category  | Version | Description                   | Type | Installed On | State   | Undoable |
+-----------+---------+-------------------------------+------+--------------+---------+----------+
| Versioned | 1.1.1   | Initial Build                 | SQL  |              | Pending | No       |
| Versioned | 1.1.1.1 | French Euros Not Dollars      | SQL  |              | Pending | No       |
| Versioned | 1.1.2   | Pubs Original Data            | SQL  |              | Pending | No       |
| Versioned | 1.1.2.1 | French Data                   | SQL  |              | Pending | No       |
| Versioned | 1.1.3   | UseNVarcharetc                | SQL  |              | Pending | No       |
| Versioned | 1.1.3.1 | French Pubs No Change         | SQL  |              | Pending | No       |
| Versioned | 1.1.4   | Rename Constraints Add Tables | SQL  |              | Pending | No       |
| Versioned | 1.1.4.1 | French Pubs No Change         | SQL  |              | Pending | No       |
| Versioned | 1.1.5   | Add New Data                  | SQL  |              | Pending | No       |
| Versioned | 1.1.5.1 | French publication types etc  | SQL  |              | Pending | No       |
| Versioned | 1.1.6   | Add Tags Etc                  | SQL  |              | Pending | No       |
| Versioned | 1.1.6.1 | French books                  | SQL  |              | Pending | No       |
+-----------+---------+-------------------------------+------+--------------+---------+----------+

So, we’ll try it out:

Flyway migrate
<a lot of stuff deleted>
Creating schema "DBO" ...
Creating schema "PEOPLE" ...
Creating schema "ACCOUNTING" ...
Creating Schema History table "DBO"."flyway_schema_history" ...
Current version of schema "DBO": null
Migrating schema "DBO" to version "1.1.1 - Initial Build"
no rows updated
Migrating schema "DBO" to version "1.1.1.1 - French Euros Not Dollars"
Migrating schema "DBO" to version "1.1.2 - Pubs Original Data"
Migrating schema "DBO" to version "1.1.2.1 - French Data"
Migrating schema "DBO" to version "1.1.3 - UseNVarcharetc"
Migrating schema "DBO" to version "1.1.3.1 - French Pubs No Change"
Migrating schema "DBO" to version "1.1.4 - Rename Constraints Add Tables"
Migrating schema "DBO" to version "1.1.4.1 - French Pubs No Change"
Migrating schema "DBO" to version "1.1.5 - Add New Data"
8 rows updated
Migrating schema "DBO" to version "1.1.5.1 - French publication types etc"
Migrating schema "DBO" to version "1.1.6 - Add Tags Etc"
1000 rows updated
1000 rows updated
1000 rows updated
21 rows updated
1499 rows updated
86 rows updated
35 rows updated
1000 rows updated
Migrating schema "DBO" to version "1.1.6.1 - French books"
Successfully applied 12 migrations to schema "DBO", now at version v1.1.6.1 (execution time 04:06.412s)

Conclusions

Variants are used primarily where a database team must maintain and deliver a database system to several sites, where each site has requirements that require small modifications to the database. Flyway can deal very easily with this requirement and leave no doubt as to whether a database is a variant and, if so, which one. However, having provided a solution to that, it also solves other problems such as having to provision several servers/databases with the same version of the database. I suspect that there are also other ways that this sort of flexibility would be useful.

There are ways of making the system even more flexible. For example, one can imagine a problem where a database needs to be migrated retrospectively to become a variant. If, for example, you have a variant that is designed to run on an old or deprecated version of a relational database server, you’d want to transform it into a variant that is designed to run on a more up-to-date version, while preserving the site’s data. It’s an interesting problem that will have to wait for another article!

For other examples of handling ‘localization’ and other issues using variants, see the Variants and localization section.