Product articles Flyway Database migrations
Flyway Teams and the Problem of…

Flyway Teams and the Problem of Database Variants

The 'ShouldExecute' script configuration option in Flyway Teams simplifies 'conditional execution' of SQL migration files. This makes it easier to support multiple application versions from the same Flyway project, to deal with different cultural or legislative requirements. It also helps developers handle environmental differences between development, test and staging, such as the need to support multiple versions or releases of the RDBMS.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Life as a database developer can be made more complicated by the need to support different ‘variants’ of the same database, meaning you need to maintain several copies of a database at a particular version, each one with some additional or unique functionality.

One frequent example happens when the development copy of a database has just a subset of the functionality of the production database, or when the test database has special ‘hooks’ to make tests easier to automate. Sometimes, we just need to support the identical database on different releases of the RDBMS. In SQL Server, for example, the introduction of JSON and the string_agg() function, in SQL Server 2017, suddenly made certain operations a lot easier. If you can’t compel your production team to upgrade the RDBMS, then you need to produce variants of database codebase, each variant designed to run on different version of the RDBMS.

More generally, the classic example of the requirements for variants is a database that, in production, must support several different legislative areas. It could be for an international organisation that is trading in many different countries and must deal with local taxes as well as local cultural differences. I once had to develop such an application, an international database-driven payroll system. My approach was to develop a single application and then to set a ‘culture and legislative area’ for each connection. The generic code is shared between all variants, to build the version, and then the ‘special code’ for each version is amended, when necessary, as a post-migration/build step.

If you’ve ever done something similar, you’ll know the complexity of creating and maintaining variants; even for a pan-European database, it wasn’t easy. However, this approach made for a single data store, and simplified maintenance and enhancements. It is certainly a lot easier than having a host of entirely separate databases that must be kept updated with bugfixes and general improvements.

File-level configuration in Flyway

The secret to controlling individual migration files is in the file-level script configuration file, stored in the same folder as its namesake migration script.

A script config file must have the same name as the related script file but with an extra .conf appended. For example, a script config file for the V1.3.12__MyImprovement.sql migration script would be called V1.3.12__MyImprovement.sql.conf (not V1.3.12__MyImprovement.conf).

Currently, these script config files are used mainly for specifying the encoding of the associated script file (encoding=<setting>), to specify that the associated migration script should not be run in a transaction (executeInTransaction=false), and finally to specify whether the script should be executed or ignored (shouldExecute=<true/false>). It is this last script configuration option that is used for variants and what in Flyway-speak is called ‘Injecting Environments’.

The ShouldExecute script config option accepts an expression that resolves to true or false, according to the value of a placeholder. If it resolves to true, the associated file is executed and will appear in the Flyway schema history table. If ‘false’ the file is avoided, and Flyway will not update the schema history table. For example, a script config file to change to a variant’s code, dealing only with language, could contain:

where variant is the name of our placeholder, whose value is retrieved by the ${} expression, and which is checked against the list of values. The associate migration file would then be applied only to our Portuguese, Brazilian, Angolan and Mozambiquan variants.

Similarly, if we need to support more than one production environment, we could set up a placeholder called ServerVersion and test it against a list of suitable versions. This would allow us to use code for each variant that either exploits the advantages of the later version of the RDBMS or uses a slower or more awkward workaround for the older version.

Workarounds for users of Flyway Community

You can switch out or switch in sections of SQL Code via placeholders, but the snag with omitting whole files is that they will appear in the history whether any migration code is executed or not. It also requires the logic of testing the placeholder and defining the block of code to be executed to be done in the dialect of SQL that you are using. Good luck with doing that in SQLite for example! The use of ShouldExecute is a cleaner solution for the Flyway Teams user because it requires no change to the code in the migration.

Trying it out: supporting different releases of SQL Server

In my previous articles, using Flyway Community, I’ve already written a sample migration for the SQL Server version of the Pubs publications database that tackles the problem of ‘legacy’ versions of SQL Server. However, it does it in an unconventional way that upsets older versions of Flyway. The script tests to see if the String_Agg() function is supported. It does this by executing a sample statement with that function in it and catches the error. If no error, it alters an existing view so that it provide lists of titles published by each publisher, using the String_Agg() function. If it gets an error, it alters the view so that it generates the lists using XML instead.

The script looks like this (it is the V1.1.9 migration script in the Scripts folder of the PubsFlywaySecondMigration project):

We can simplify this code, with Flyway Teams, and I’ve included files in the PubsFlywayTeamsMigration project to illustrate how. You’ll see the two files called V1.1.9__AddconditionalVersion.sql and V1.1.9__AddconditionalVersion.sql.conf. They are dealing with the same view, TitlesAndEditionsByPublisher, that I used in the previous example. By default, the view uses the XML method of generating lists, and I then superimpose the String_Agg method only if supported by the installed version of SQL Server, using a migration that is controlled by a script config file.

An earlier migration file (V1.1.8) creates the ‘generic’ version of the view, which generates lists using XML. This will run on any version of SQL Server currently in circulation. The V1.1.9__AddconditionalVersion.sql migration file than alters this view to take advantage of the String_Agg() function:

However, our corresponding V1.1.9__AddconditionalVersion.sql.conf file ensures that the above V1.1.9 migration only ever runs if the server supports the String_Agg facility:

i.e., only if our canDoStringAgg placeholder evaluates to true. Before we run the Flyway migration, we check support for String_Agg (as well as for JSON) by interrogating the server to detect the SQL Server version and set the canDoStringAgg (and canDoJSON) placeholders to true or false according to whether the server has the capabilities that we need.

As you will see in the script to run the Flyway migration, shortly, I’ve made this pre-migration interrogation a bit fancier than necessary to demonstrate a generic way of using SQLCMD with PowerShell. This avoids having to set up an ODBC connection.

As we’re using Flyway by splatting the parameters, we’ll add the placeholders we need to the array of parameters. You might think that the code would be more elegant if we were to use a scripted ‘BeforeMigrate‘ callback to get this information about the version of the server, but there is no obvious way of passing this information on so it can be used. In fact, there is no way by which a script can return information back to the Flyway instance that ran it. It is no inconvenience to do it in script before executing Flyway, but the downside is that if someone just runs Flyway manually for this migration, without the initial querying of the server and setting the placeholder, then the build will just default to the old technology.

To test this out, we can run the complete migration on two different servers, one with SQL Server 2016 and the other with SQL Server 2017. Once done, we can confirm that the only difference between the two builds is the use of the String_Agg function in the TitlesAndEditionsByPublisher view.

Here is the script that I used (UndoBuild.ps1, in the project directory). I ran it twice, once for each server, on databases that were initially blank:

When you run the Flyway info command on the SQL Server 2016 database, you’ll see that the V1.1.9 migration is listed as “ignored”, because of the lack of support for the String_Agg function that it requires:

Chart Description automatically generated with medium confidence

Of course, this is just a test to get a feel for the technology, but it is likely that where you have a whole series of changes that are dependent on server version, you can place several in the one migration.

Conclusions

Flyway Teams can provide a solution for some long-standing problems. The ShouldExecute config script option is an example of this because it just makes complex builds easier. You’ll have your own favorite pain-points depending on the sort of database you’re building, but the most obvious ones for me are for releases that must support database variants to provide for cultural, technical and legislative differences. It also helps with the problems of provisioning databases for development, test, and release in the light of all the different requirements.

Traditionally, it is difficult to accommodate variants into the build process of a database. In SQL Server, it is possible to use macros, the equivalent of Flyway’s placeholders, but it is very difficult to get the same flexibility into the build. The idea of building sufficient intelligence into a database build and migration process to allow builds for different versions of the RDBMS is rather a novelty for me, and Flyway Teams develops this idea into the realm of enterprise-scale database development where the one database version may have to accommodate to several different production environments.

Tools in this post

Flyway

Version control for your database. Robust schema evolution across all your environments and technologies.

Find out more

Flyway

Version control for your database. Robust schema evolution across all your environments and technologies.

Find out more