• Flyway
• Multidatabase management
• Configuration, Authentication

Using Flyway as a Multi-Database Migration System

Flyway works naturally as a deployment system. It is CLI-based, has a versatile configuration system, and knows how to bring any instance of a database reliably to the version you want. These features mean that it is fundamentally easy to adapt to the requirements of even complex migrations. These will often include several databases, maybe in different places, possibly from different vendors, and often with non-standard authentication requirements.

Although Flyway evolved initially for developing and deploying small databases such as those used in microservices or web-based applications, the commercial editions, Flyway Teams and Flyway Enterprise add the features required to support team-based database developments and enterprise-scale deployments, respectively. In this article, I’ll illustrate how we can migrate several databases, some with special requirements, in one batch, using Flyway Teams and PowerShell.

Working with Flyway’s Configuration system

Flyway works out how to perform its task by reading its configuration and it provides an innovative configuration architecture. The settings for Flyway needn’t just be provided as command-line parameters or environment variables but can be also be contained in a hierarchical range of configuration files, which I’ve explained in A Programmer’s Guide to Flyway Configuration. These configuration settings contain information such as the way that you wish Flyway to work, the names it uses, the locations it accesses, the credentials, the details of database access, the project details and so on.

There are three default configuration files that are read every time Flyway is started. The first is in the workstation’s install folder; the second in the home folder of the current authenticated user; the third is in the project folder accessed from the current working folder, which can be a shared network folder. The obvious place to store install information such as the license number is in the install directory. User identity, credentials and database access details are best kept in the workstation’s user area, and all the project information should be on the network in the working project folder and subject to source control. If even this isn’t sufficient, then you can provide a further list of configuration files as environment variables or provide the settings as command-line parameters. I’ve demonstrated a lot of this in a previous article, Pipelining Configuration Information Securely to Flyway.

Flyway’s configuration model is viable even in more complex developments. It allows you to store settings that involve credentials or connections in a secure place. When users need role-based credentials, they can be stored in a file with a name that indicates which is the appropriate set of credentials. If credentials must be encrypted or stored in a ‘vault’, it is easily managed. If a cloud database needs a more complex credential, then that can be accomplished via placeholder values set within the relevant config file.

We’ll show some of this in a practical way in this article, by demonstrating how easy it is to have a single Flyway process run a series of tasks on several databases. We’ll end up by showing, as a demonstration of some of the techniques, a system that was designed for testing every release of the Flyway Teamwork Framework.

Handling complex database migrations

To make our demonstration more thorough, we’ll use a variety of database systems, including Oracle Cloud. Although Oracle is “just another relational database” as far as Flyway is concerned, it is a useful example of a configuration that needs some thought and planning. Like other cloud services, the security and credentials for cloud connections must necessarily be much more stringent and can go way beyond the simple paradigm of connection string (RDBMS, Server, Database), User and Password. In the case of Oracle Cloud, Flyway must deal with services and wallets, but other cloud databases will present similar complications.

As database work becomes more complex, it is easier to manage migrations if you separate out the connection information from the project information. When you’re running tests in parallel to speed up deployment, you need several identical databases, at the right version. One project, but with several different connections. This boils down to being able to run Flyway within a pipeline of different connections, so it can, for example, update each copy of the database as defined by its connection information.

Where are the complications? The system needs to make it easy for teams to opt to use individual credentials (such as wallets in Oracle mTLS, or with a secrets manager like Vault or Dapr) for each user, so that each user can then be individually authenticated using their unique digital certificate and private key. This should allow better tracking and auditability, as each user’s actions can be traced back to their specific credentials. This suggests that the easiest approach is to have one or more individual configuration files for each user. A DBA, or even a developer, could easily access the system under different user IDs with different privileges to perform different types of tasks, as a precaution.

For a more in-depth look at this, I give a quick ‘How to get started‘ guide to using Oracle with Flyway in Getting Started with Flyway Migrations on Oracle, along with a sample database build using the old Pubs database.

Demonstrating a multi-database migration

To meet these more complicated requirements, I’ve provided a Get-Conf cmdlet that can turn any config file into Flyway parameters at the point of execution, so that they are never saved within the main script. We can then extend this technique by splatting any config items that need to be gathered from a zipped file, taken from a vault or decrypted. Here they are taken from the current user area:

As far as Flyway is concerned, these configuration items are being presented as parameters. This means that they take priority over any default settings. With a technique like this, we can specify everything from one or more files, so a deployment method can be as complex as you like, even if, for some reason, you dislike using a current working directory as a basis for a project.

Now let’s build a list of databases that need to be maintained. In my case, it is all the Pubs databases in my FlywayTeamwork-Pubs GitHub project.

Now we can quickly do a status check of all the databases, while we have a cup of coffee:

With a minor modification, we can make sure that every database is up to date:

Supplying configuration and authentication for multiple databases

You’ll see that with all these examples we are getting the basic project information from the project directory and getting the credentials, where necessary from the user area. If we provide a path as a value for the SecretsPath key, the configuration items in the file are fetched and passed to Flyway as parameters, otherwise just the information in the project config files are used.

We couldn’t have just relied on getting the connection and credential information from the flyway.conf file in the user area because each database will need their own and there can only be one flyway.conf file there. We could, of course, have just changed the environment variable that can be used to specify an extra list of config files to be used.

Although this technique of specifying the extra files is a good one to use when you are doing deployments within a DOS or BASH session, it cannot be used when you need to decrypt a file or take it from a vault. Also, this technique will set the value for the whole subsequent PowerShell session rather than for that single invocation of PowerShell, so you must unset this session variable when you no longer need it. This can be the cause of confusion, especially as the config values that Flyway then reads take precedence over what you believe you’ve supplied to Flyway as a parameter.

A potential problem of using parameters for a complicated database that does scripted callbacks is that they would probably need access to some of the detailed connection information passed as a ‘secret’. This isn’t passed to the callback by Flyway as an environment variable. Fortunately, the get-conf cmdlet sets a placeholder with the secretspath as a value, and this path gets passed to any callback so that it can access the file to fetch the secrets.

Filtering out verbosity

We can improve on even this. As soon as you’ve got Flyway doing a run of migrations, you’ll notice that the whole process is too verbose. All the information needs to be logged but you need to see warnings, and the verbosity can be distracting. To get around this, I use a way of executing Flyway commands that allows you to specify a secrets parameter, which takes a path to a valid Flyway.conf file with a ‘secrets’ configuration. This Do-AFlywayCommand cmdlet, which is included with the framework, filters out the warnings and errors during a migration so that they don’t get lost from among the verbose messages from Flyway. I’ve previously used it as part of my Flyway Alerting and Notifications system.

Redoing a project from scratch

When I’m routinely testing the Flyway Teamwork framework, I need to do much more than I’ve demonstrated in the previous examples. I must run all the scripts using all Flyway editions for every supported RDBMS and check the results for errors.

I use a test script that will entirely redo a project consisting of several databases and sub-projects. It reads each project from an array with all the necessary details. What is more to the point, it will, for each version, run all the extra scripts such as creating a database model, creating the script folders and creating the change narrative that tells you what each migration did. It can then save this information for source control. I’m assuming use of Flyway Teams, in which case you can run have Flyway run all these extra tasks automatically, using script callbacks. If the edition of Flyway is Community, it will still run the tasks, albeit very slowly, after each migration.

We can use the list of sample databases, like the one above, but we’ll need a few more parameters. When we are testing the Teamwork framework, we clear out all the existing models, scripts and source files that relate to each version and then we need to execute each migration separately to ensure that all the callbacks and utility scriptblock tasks work. There are all sorts of glitches that can stop them happening, and sometimes they just seem to stop working from sheer entropy. We therefore need to indicate whether we are checking whether to use callbacks or, if working with testing Flyway Community, or whether we execute the scriptblock tasks manually in a script and if so, which ones.

The actual Redo-EveryFlywayMigrationSingly cmdlet is a bit too long to publish here, but it is available here in the GitHub repository for Flyway Teamwork. Its effect, if used against a bank of databases of several different varieties of RDBMS, is to spend several hours testing the system to the limits!

Here is just a section of the list of jobs, just to show how to set up the array. To do this, you’ll need to have run the preliminary.ps1 script first. You will also need to set your $Github variable with the path to your Flyway Teamwork directory

Conclusions

With code like this, we can keep several databases of any type, and in different locations or cloud services, up-to-date at once, from one process, and deal with any sort of security systems, secrets and vaults that we need to. Using this same mechanism, we can run tests on them if we want or monitor them. It could be that I’m the only person who would ever actually need Redo-EveryFlywayMigrationSingly but the principles it uses can be adapted for a variety of tasks as well as deployment, for checking, searching or maintaining a gaggle of databases.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Teams

Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.

Find out more