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.
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:
|
1 |
shouldExecute=${variant}==Portugal Brazil Angola Mozambique |
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
DECLARE @TheErrorNumber INT SELECT @TheErrorNumber=0 BEGIN TRY EXECUTE sp_executesql @stmt = N'SELECT String_Agg(text,'','') FROM (VALUES (''testing''),(''testing''),(''one''),(''Two''),(''three''))f(Text)' END TRY BEGIN CATCH SELECT @TheErrorNumber=Error_Number() END CATCH; /*On Transact SQL language the Msg 195 Level 15 - 'Is not a recognized built-in function name' means that the function name is misspelled, not supported or does not exist. */ IF @TheErrorNumber = 0 BEGIN EXECUTE sp_executesql @stmt = N' ALTER VIEW [dbo].[TitlesAndEditionsByPublisher] AS /* A view to provide the number of each type of publication produced Select * from [dbo].[TitlesAndEditionsByPublisher] by each publisher*/ SELECT publishers.pub_name AS publisher, title, String_Agg ( Publication_type + '' ($'' + Convert(VARCHAR(20), price) + '')'', '', '' ) AS ListOfEditions FROM dbo.publishers INNER JOIN dbo.publications ON publications.pub_id = publishers.pub_id INNER JOIN editions ON editions.publication_id = publications.Publication_id INNER JOIN dbo.prices ON prices.Edition_id = editions.Edition_id WHERE prices.PriceEndDate IS NULL GROUP BY publishers.pub_name, title;' END ELSE EXECUTE sp_executesql @stmt = N' ALTER VIEW [dbo].[TitlesAndEditionsByPublisher] AS /* A view to provide the number of each type of publication produced Select * from [dbo].[TitlesAndEditionsByPublisher] by each publisher*/ SELECT publishers.pub_name AS publisher, title, Stuff ((SELECT '', ''+Publication_type + '' ($'' + Convert(VARCHAR(20), price) + '')'' FROM editions INNER JOIN dbo.prices ON prices.Edition_id = editions.Edition_id WHERE prices.PriceEndDate IS NULL and editions.publication_id = publications.Publication_id FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(max)''),1,2,'''') AS ListOfEditions FROM dbo.publishers INNER JOIN dbo.publications ON publications.pub_id = publishers.pub_id ' |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
ALTER VIEW [dbo].[TitlesAndEditionsByPublisher] AS /* A view to provide the number of each type of publication produced Select * from [dbo].[TitlesAndEditionsByPublisher] by each publisher*/ SELECT publishers.pub_name AS publisher, title, String_Agg ( Publication_type + ' ($' + Convert(VARCHAR(20), price) + ')', ', ' ) AS ListOfEditions FROM dbo.publishers INNER JOIN dbo.publications ON publications.pub_id = publishers.pub_id INNER JOIN editions ON editions.publication_id = publications.Publication_id INNER JOIN dbo.prices ON prices.Edition_id = editions.Edition_id WHERE prices.PriceEndDate IS NULL GROUP BY publishers.pub_name, title; go |
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:
|
1 |
shouldExecute=${canDoStringAgg}==true |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
<# tell PowerShell where Flyway is. You need to change that to the correct path for your installation. You only need to do this if your flyway installation has not provided a path command #> Set-Alias Flyway 'C:\ProgramData\chocolatey\lib\flyway.commandline\tools\flyway-7.15.0\flyway.cmd' -Scope local #specify the DSN, and create the ODBC connection # and here are our project details. The project folder $ProjectFolder = '<MyPathTo>\PubsAndFlyway\PubsFlywayTeamsMigration' $Server = '<MyUserName>' $Database = '<MyDatabaseName>'; $ProjectName = 'Publications'; $ProjectDescription = 'A sample project to demonstrate Flyway Teams, using the old Pubs database' <# you only need this username and password if there is no domain authentication #> $username = '<MyUserName>' $port = '<MyPortNumber>' # add a bit of error-checking. Is the project directory there if (-not (Test-Path "$ProjectFolder")) { Write-Error "Sorry, but I couldn't find a project directory at the $ProjectFolder location" } # ...and is the script directory there? if (-not (Test-Path "$ProjectFolder\Scripts")) { Write-Error "Sorry, but I couldn't find a scripts directory at the $ProjectFolder\Scripts location" } # now we get the password if necessary if ($username -ne '') #then it is using SQL Server Credentials { # we see if we've got these stored already $SqlEncryptedPasswordFile = "$env:USERPROFILE\$($username)-$Server-sqlserver.xml" # test to see if we know about the password in a secure string stored in the user area if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf) { #has already got this set for this login so fetch it $SqlCredentials = Import-CliXml $SqlEncryptedPasswordFile } else #then we have to ask the user for it (once only) { # hasn't got this set for this login $SqlCredentials = get-credential -Credential $UserName # Save in the user area $SqlCredentials | Export-CliXml -Path $SqlEncryptedPasswordFile <# Export-Clixml only exports encrypted credentials on Windows. otherwise it just offers some obfuscation but does not provide encryption. #> } $FlyWayArgs = @("-url=jdbc:sqlserver://$($Server):$port;databaseName=$Database", "-locations=filesystem:$ProjectFolder\Scripts", <# the migration folder #> "-user=$($SqlCredentials.UserName)", "-password=$($SqlCredentials.GetNetworkCredential().password)") $SQLCmdArgs = @{ 'Server' = $Server; 'Database' = $Database; 'User' = $SqlCredentials.UserName; 'Password' = $SqlCredentials.GetNetworkCredential().password } } else { $FlyWayArgs = @("-url=jdbc:sqlserver://$($Server):$port;databaseName=$Database;integratedSecurity=true". "-locations=filesystem:$ProjectFolder\Scripts")<# the migration folder #> $SQLCmdArgs = @{ 'Server' = $Server; 'Database' = $Database } } $FlyWayArgs += <# the project variables that we reference with placeholders #> @( "-schemas=dbo,Classic,people", "-placeholders.schemas=dbo,Classic,people", #This isn't passed to callbacks otherwise "-placeholders.projectDescription=$ProjectDescription", "-placeholders.projectName=$ProjectName") <# if your install of the SQL Server utilities has added the environment PATH variable then you don't need this #> $SQLCmdAlias = "$($env:ProgramFiles)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\sqlcmd.exe" Set-Alias SQLCmd $SQLCmdAlias <# we'll use a script block to access SQLCMD #> $GetdataFromSQLCMD = {<# a way of accessing SQL Server to get JSON results withhout having to open a connection. We are going to access the server via flyway and so only want one connection going #> Param ($Theargs, $query) $TempFile = "$($env:Temp)\Temp.json" $FullQuery = "Set Nocount On $query" if ($TheArgs.User -ne $null) { sqlcmd -S $TheArgs.server -d $TheArgs.database -Q "`"$FullQuery`"" -U $TheArgs.User -P $TheArgs.password -o `"$TempFile`" -u -y0 } else { sqlcmd -S $TheArgs.server -d $TheArgs.database -Q "`"$FullQuery`"" -o `"$TempFile`" -u -y0 } If (Test-Path -Path $TempFile) { $response = [IO.File]::ReadAllText($TempFile); #Remove-Item $TempFile if ($response -like 'Msg*') { "[{`"Error`":`"$($TheArgs.database) says $Response'`"}]" } elseif ($response -like 'SqlCmd*') { "[{`"Error`":`"SQLCMD says $Response'`"}]" } elseif ($response -like 'NULL*') { '' } else { $response } } } <# now we use the scriptblock to determine the version number and name from SQL Server #> $ServerVersion = $GetdataFromSQLCMD.Invoke($SQLCmdArgs, " Select Cast(ParseName ( Cast(ServerProperty ('productversion') AS VARCHAR(20)), 4) AS INT)") [int]$VersionNumber = $ServerVersion[0] if ($VersionNumber -ne $null) { $FlyWayArgs += @("-placeholders.canDoJSON=$(if ($VersionNumber -ge 13) { 'true' } else { 'false' })", "-placeholders.canDoStringAgg=$(if ($VersionNumber -ge 14) { 'true' } else { 'false' })") } cd "$ProjectFolder\scripts" #if this works we're probably good to go Flyway @FlyWayArgs info Flyway @FlywayArgs clean #only if doing a re-run Flyway @FlyWayArgs migrate |
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:
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 Teams
Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.
You may also like
-
Article
What is the Flyway Teamwork Framework?
The FlywayTeamwork PowerShell framework is designed to help get you started quickly with scripting Flyway migrations for a range of database systems. It introduces a PowerShell task library to help with the scripting of repetitive chores and to generate some of the 'build artifacts' that are often required during team development work. This article explains the basics of the framework's design and provides a demo how to use Flyway to migrate a PostgreSQL database, while generating a high-level narrative of the changes made between versions.
-
Article
Using Flyway Teams with SQL Change Automation PowerShell Cmdlets
Use Flyway to run your database migrations, each time automatically creating a SQL Change Automation release object to provide object-level scripts and a build script for the new version, along with change reports and code analysis reports.
-
Article
Code Visibility: Browsing through Flyway Migration Files
If you can convert a SQL file to HTML, then you can inspect your Flyway migration files in a browser. This is especially useful if your SQL is color-coded with the same conventions as it was in your IDE. It is even better still if your browser can allow you to scan through many files, moving from file to file with a single click. This article demonstrates how to do this with a few PowerShell scripts.
-
Article
Building an AWS DevOps Pipeline for Databases
The first time you try to automate a database deployment using any of the available flow control tools, all the moving parts makes the task look insanely difficult and this is not any different within the AWS Developer Tools. However, after you get over that initial shock, the processes are not nearly as difficult as
-
Article
Using Flyway as a Multi-Database Migration System
Flyway can scale easily to enterprise-scale database systems, even if they involve a mix of relational database systems, are cloud-based, containerized or involve complex authentication. This article demonstrates how we can use Flyway Teams to do a single-batch, multi-database migration, comprising SQL Server, Oracle Cloud, PostgreSQL, MySQL and SQLite databases.
-
Article
A Database DevOps Workflow Using Flyway Enterprise and Redgate Test Data Manager
Tony Davis describes a typical database development cycle and deployment pipeline supported by Flyway Enterprise and Redgate Test Data Manager. It allows branch-based database development, using disposable databases (clones) and version control tools, promotes continuous integration and testing of changes and automates the build and deployment processes so that they are repeatable, fast and reliable.
