Phil Factor

20 December 2021

• Flyway
• Testing migration scripts
• Database Testing and Quality

Testing a Flyway Database Migration

There are several reasons why you might want to maintain the same version of a database in more than one development database, probably in different server environments. You might need to run a preliminary test to make sure that a new version, created by a Flyway migration, works as expected, before committing the new migration file to your branch. You may need to test the same database running on different versions of the database system. You may need to test several variants of the database, running a series of tests in a post-migration script.

For these and other reasons you need to be prepared to do a migration run more than once. It must be easy to specify any number of databases, on any number of servers, for a Flyway migration, and so migrate them all, in turn, to the same version. We can do this most easily by executing Flyway from PowerShell. In this example we’ll generate JSON parameter files, storing all the required details for each database that we wish to migrate. We read all these parameter files, connecting to each database in turn, and on each one run the same series of Flyway actions, such as a clean followed by a migrate. If any errors or warnings occur, at any stage, we capture the output in a JSON doc, and report the problems. Any errors will abort the process unless we opt instead to try the next version of the database. Otherwise, we get a report verifying that each of the database we specified is now at the new version.

In this example, I’m using a SQL Server database, but it will do any Flyway database if you modify the way that the URL variable is created.

Creating the JSON parameter files

This routine stores all the details for the databases in a parameter directory. Unless you need to make changes, you need only do this once.

First, it defines the common parameters that will apply to the whole project. Then it contains an array of objects, each one of which represents a database. Each database could be on a different server, or they could all be on the same server, with different database names. If the databases are on different servers, there may be different User IDs executing the migrations.

Each object is used to create an array of Flyway parameters and a few variables that are needed to process the Flyway Actions (e.g., Migrate). You can, for example, specify the order in which they are executed. You can also specify whether the process should be aborted if an error occurs with the Flyway commands you execute. At different times, you may want to react in differently, if there was an error, and you can specify this accordingly. Each of the resulting objects is then saved as a JSON file. No passwords are saved in these files, just usernames.

Once we’ve saved all this information in a JSON document, one for each copy of the database we are migrating, we then store them in a directory. If this directory is on the network, then a team can then use this information. As these files contain JSON documents, it is easy to change them in situ.

The following sample is set up to run a ‘test’ migration on a test server, maybe a local laptop or a handy test server somewhere in the network. It only proceeds to the main migration if the test went well. When you run this for real, you’ll want to specify the database-specific integration tests that need to pass before the migration is applied. These can most easily be applied as a post-migration SQL Callback.

Using the parameter files in a Flyway migration

When we want to do a set of migrations, we execute a second PowerShell routine. In the directory of parameter files, I’ve set up just two files, Test and Dev. This second routine just processes each file in order, picks up passwords, if required, from an encrypted file in your user directory, and performs the Flyway operations using the data. It captures the output from Flyway, as a JSON document, and responds to any errors. You’d probably also want to use the output in a report. I show how to do this in Using Flyway Output in PowerShell.

Here is a typical error from a run, where Flyway could not run the initial clean command due to a login failure:

WARNING:  at the CLEAN command of Test
         DB_CONNECTION Unable to obtain connection from database (jdbc:sqlserver://<MyOtherServer>:1433;databaseName=
<MySecondDatabase>) for user '<User_ID>': Cannot open database "<MySecondDatabase>" requested by the login. The login failed. 
ClientConnectionId:5dd63054-2b41-49fe-9c86-38e8f6f6c997
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
--------------------------------
SQL State  : S0001
Error Code : 4060
Message    : Cannot open database "<MySecondDatabase>" requested by the login. The login failed. ClientConnectionId:5d
d63054-2b41-49fe-9c86-38e8f6f6c997

Because I’ve specified that an error should abort the whole process it hasn’t gone on to attempt the migration. So, let’s get everything right and see what happens. OK, I’ll admit that I’m just running a tiny database migration, because I want to get it on the page!

category  version description                          type executionTime
--------  ------- -----------                          ---- -------------
Versioned 1.1.1   Original Altered For SQL Server 2017 SQL           1395
Versioned 1.1.2   Added Tags Etc                       SQL            663
Sum               Max               Minimum           Ave              
---               ---               -------           ---              
00 mins, 02 secs. 00 mins, 01 secs. 00 mins, 01 secs. 00 mins, 01 secs.
Name                 Value               
----                 -----               
database             <MySecondDatabase>           
flywayVersion        8.0.0               
initialSchemaVersion                     
migrationsExecuted   2                   
operation            migrate             
schemaName           dbo, Classic, people
targetSchemaVersion  1.1.2               
category  version description                          type executionTime
--------  ------- -----------                          ---- -------------
Versioned 1.1.1   Original Altered For SQL Server 2017 SQL            718
Versioned 1.1.2   Added Tags Etc                       SQL            547
Sum               Max               Minimum           Ave        
---               ---               -------           ---        
00 mins, 01 secs. 00 mins, 01 secs. 00 mins, 01 secs. 00 mins, 01 secs.
Name                 Value               
----                 -----               
database             <MyFirstDatabase>            
flywayVersion        8.0.0               
initialSchemaVersion                     
migrationsExecuted   2                   
operation            migrate             
schemaName           dbo, Classic, people
targetSchemaVersion  1.1.2

So, here is the script, in this case running a clean followed by a migrate, but you can alter what happens before the migrate, as you require. Most obviously, you would probably just run the new migration file that you’ve just created without running the clean. The code would be much less daunting without the extra work to produce a nice report, but I wanted to show the benefits or running Flyway from PowerShell.

Conclusions

Here we have the bones of a system that will scale easily but can be used to just test a migration. In a way it is a shame to have to execute Flyway from PowerShell, but it adds a lot of flexibility to a Flyway migration and allows you to do a lot more. This is intended to be a simple introduction to the idea of specifying several databases for a Flyway migration. Although I find it saves me time, it can get its real power by running integration tests so that it not only tests that the migration works, but also that it does what it is supposed to do. We’ll have to leave that for another article!