• Redgate Test Data Manager
• Get Started with Clones

Provisioning Clones for Flyway Projects

My previous article, Getting Started with Cloning in Redgate Test Data Manager, covers the basic of creating and maintaining the data images and data containers (clones). Here, we’ll demonstrate how to automate clone delivery, so that developers and testers can then use them directly in branch-based Flyway development projects, and can reset and recreate them at will. We’ll provision an empty copy of the same database on each data container and then use Flyway to connect to each one in turn and migrate the database to the latest development version.

How it Works (overview)

We use the cloning CLI in Test Data Manager (rgclone) to create four data images, all for the same, empty Northwind database but on four different RDBMSs. From each image, we’ll create a data container (clone) as a working database instance. I provide PowerShell routines that will automatically create and teardown all these images and containers, as needed.

Next, we create four Flyway projects, one for each RDBMS. With the connection details and credentials passed back by rgclone, we will then create extra Flyway configuration files, stored securely in the user area, which will be passed to Flyway as parameters when it is executed, to provide it with the necessary credentials.

Finally, we then execute a single PowerShell process that connects each Flyway project to its database, in turn, and runs a single script, in the appropriate dialect, to migrate each database from empty to the required version. The following diagram shows it connecting to and migrating the PostgreSQL data container.

Trying it out

You’ll already need to have installed and set up the Cloning CLI (rgclone), provided the API endpoint for your Clone server, and authenticated to the server. All of this is explained in my previous article, Getting Started with Cloning in Redgate Test Data Manager, along with basic details of creating images and containers.

You can then download the full source code from my Flyway Teamwork project on GitHub. You don’t need to be using my Teamwork framework for this article, as it doesn’t rely on any of the extra functionality that it provides. You can find the migration scripts for the four different version of the Northwind database here:

• Northwind – the SQL Server version
• NorthwindMySQL – the MySQL version
• NorthwindOracle – the Oracle version
• NorthwindPG – the PostgreSQL version

All you need to do then is to execute the Flyway app to execute the migrations.

Setup routine

The following PowerShell routine sets a few variables that keep things neat and simple for our subsequent scripting and automation tasks involving rgclone. It sets up an RGClone directory, within the user profile directory, where we’ll securely store the image definition (YAML) files along with JSON files containing information about the containers we create and their connection details. This setup routine also sets the correct file format and interprets the exit codes from rgclone, making it easier to understand and handle its outputs and errors.

Teardown routine

If you are using this sample project like I did, which is to try out and get used to how cloning works in Test Data Manager, then you’ll want to build the data images and containers you need, migrate them to the required version, and then tear them down again, repeatedly.

Before we get to the “building” part, we’ll start with the teardown routine that destroys all surviving data images and data containers from previous experiments. Please note that this routine, as provided, is a mass-extinction event for all existing data containers and data images, even those unrelated to the current Northwind project. However, it is very easy to add a WHERE clause to the routine to exclude or include just the containers or images you want to remove.

Containers are more ‘volatile’ than images so you can easily execute the first part without the second. However, I wouldn’t want to delete anything without preserving the file used to specify how to create the image (which is the reason for the JSON file created in the previous setup routine).

If all goes well, this will give something like:

Data container '26' deleted!
Data container '27' deleted!
Data container '28' deleted!
Data image '31' deleted!
Data image '32' deleted!
Data image '33' deleted!

Building data images and data containers

Now for the ‘build’ part and the first stage is to create the data images, and after which we can create data containers, from each image. These routines assume you run the teardown process, so no images, or data containers containing “Northwind” in their names, currently exist.

Create the data images

When creating a batch of images, in this case one data image per database engine, I like to create a list containing hashtables, where each hashtable defines the specifications for a different database image. We write the specification for each data image to a data image template (YAML) file, named using a name–engine–project convention, and store them in the RGClone directory. Once you have the YAML files on disk, you can do subsequent mass-creation of images from them.

For the SQL Server image, we will specify the image source as an empty backup, and for the others just create an empty image with a CREATE DATABASE NorthWind ‘prescript’ (see my previous article for details).

We end up with four data images, named according to a name–project–engine convention:

Data image 'Sneezy-northwind-mssql' (35) created!
Data image 'Bashful-northwind-postgresql' (36) created!
Data image 'Grumpy-northwind-mysql' (37) created!
Data image 'Dozy-northwind-oracle' (38) created!

Create the data containers

Now that we have all the data images, the next stage is to create data containers (clones) from each image. We’ll end up with four data containers, each one a working database instance running on a different RDBMS, and each instance containing an empty copy of the Northwind database, ready for Flyway.

We define the branch of the project that the clone will support (in this case, Main) and we also give each container an identifying name. This isn’t strictly necessary, but without it, I got a bit confused as to which was which! We end up with data container named according to the convention ImageName–Branch–ContainerName.

For the sake of simplicity in this demo, I use --lifetime 0 to ensure the data containers never ‘disappear’ until you tear them down. Generally, though, clones are designed to be ephemeral.

And here are our four clones being created:

Data container 'Sneezy-northwind-mssql-Main-Euphrosyne' (29) created!
Data container 'Bashful-northwind-postgresql-Main-Aglaia' (30) created!
Data container 'Grumpy-northwind-mysql-Main-Thalia' (31) created!
Data container 'Dozy-northwind-oracle-Main-Sabrina' (32) created!

Save connection details for each data container in a Flyway config file

Now we have new fresh data containers, but to connect to them, Flyway needs to know the user, port address and password. These change each time we create or reset the containers.

Fortunately, Flyway will take credentials and connection strings from a Flyway configuration (.conf) file, so we’ll grab the details from rgclone and store them in config files in the user area. This will protect these ‘secrets’ from casual access, but in actual use we’d want to encrypt/decrypt the file too (encryption at rest) or obtain credentials direct from rgclone at the point of executing our favorite build/migration tool.

The routines above and below can be changed to allow the encryption of the credentials with GPG or other utilities (I use Yubikey).

Enter Flyway

So, we now have four containerized, empty Northwind databases running on four different RDBMSs (MSSQL, PostgreSQL, MySQL, and Oracle). We can now take Flyway out of its box and use it to apply migrations and bring these databases the latest development version.

For each RDBMS-version of Northwind, we prepare a Flyway project. Here, I’ve kept each Flyway project in a different directory, for simplicity. Each directory has a config file that defines the locations where migrations will be found. In this case, there is only one migration file for each project, appropriate to the RDBMS being used. The placeholders in each config file aren’t required at this point.

The following code automates the process of cleaning and migrating each of the four databases, in turn. It reads the necessary configuration for that database (which we stored in the config files in the user area), constructs the Flyway command-line parameters, and executes the commands.

I’ve kept this demonstration code free of functions just to make it clear what the process is doing. Although this is fine for demonstration work, you’d want to automate this for routine work. Here is a cmdlet, Run-FlywayCommand that does this, taken from the Flyway Teamwork framework.

Here is an example of its use:

Conclusions

Cloning in Redgate Test Data Manager, using the rgclone CLI, is easy to automate. This article is about demonstrating how easy it is to use, and how it might be used, more than it is about the need to create and develop the Northwind database on four different RDBMS.

In this demonstration, the clones have been made to last. This treats them like traditional connections. This was not necessary for this demonstration, because Flyway works well with very temporary, volatile connections. However, the more traditional tools such as SSMS and most other IDEs are poorly set up to cope with databases that change their port, user and password, so that clones that last are handy for the traditional IDE. However, to get the most from working with clones, we need to get over this hurdle, and we’ll tackle that problem in the next article.

Tools in this post

Redgate Test Data Manager

Reliable and secure test data provisioning

Find out more