Phil Factor

9 March 2021

• Flyway
• Multidatabase management
• Automation and workflow

Automating Migrations for Multiple Databases using Flyway

Why Automate Flyway?

Although Flyway is easy to use interactively from the console or command-line, it was designed to be easy for developers; it is the natural way of using it. You can use a variety of scripting languages to do this. PowerShell is ideal. With PowerShell and Flyway, it becomes far easier to manage any number of databases to keep them updated at the required version.

Keeping track of database versions

Building or migrating a database reliably requires accuracy. A migration will only work if you are certain of the version of the installed target database. Flyway maintains the current version and the version history in the database of every copy of the database. Flyway does a lot of the checking for potential problems for you. It will ensure that the migration files are done in the right order and make sure that none of the migration steps are ever altered. Flyway will, for example, calculate the checksum of each migration step, and if it has changed it will produce an error:

Migration checksum mismatch for migration version xxxx
-> Applied to database : -845035031
-> Resolved locally    : 956117173. 
Either revert the changes to the migration, or run repair to update the schema history.

Scaling up the number of databases

Reality in database development can be daunting. Imagine that you have four copies of your database in the test cell that need to be at the version of the current release candidate. You have four development machines that need to be at the version of the current build, different from the release. Then we have the application developers who require the database that goes with the current release. There is a developer trying to trace a bug reported in a previous release that has been superseded but might still occur sporadically. He needs a database at that release level.

If you’re a developer reading this, and are suffering an involuntary cringe, you will understand that it isn’t quick or easy to manage database development ‘manually’. Flyway, however, can make light of this, whether you are wedded to a migration, static or hybrid approach to source control. If you have the scripts to ‘version-hop’ between versions of the database, Flyway will oblige. All you need to do is to define the versions that each copy of the database should be at, and Flyway will check to make sure they are at that version, and upgrade to the correct version if necessary. This article will show a script that does this. It uses a centralized system that ensures that every database is at the correct level. We can illustrate this as follows:

Batch process or PowerShell?

The simplest approach, perhaps, is to run a batch that just runs the command all the necessary times. To save you having to type in all the parameters at the long command line, you can set the configuration for Flyway in a global (machine-level) config file, user-level config file or at the level of the current directory. However, when working this way, it is hard to check the overall configuration that was used, which can also make debugging hard.

I’ll show how to use the config-file approach in another article, Batch Processing using Flyway, illustrated with a DOS batch script. In this article, I use PowerShell, which I find more convenient for doing Flyway automation because you can fine-tune each operation or run a series of operations. For instance, you may want to do a ‘Flyway Info’ after every operation, or a series of operations after a clean.

Also, rather than use config files, I ‘splat’ the configuration to the command-line, as parameters. I find that this makes automation less prone because you can be more certain of exactly what configuration items Flyway is using.

The design of the PowerShell automation script

I use an alias to invoke Flyway, and pass parameters to Flyaway by splatting an array of parameters. This makes it neater to add parameters conditionally, and to check the script during a debugging session.

The script I show in this article generates a PowerShell object to control the process, which stores all config details regarding the list of databases to be migrated, the actions to be performed and so on. However, in a production system, you’d want to write this information out to a JSON file, stored in version control, so that you can maintain it using a proper JSON editor.

It is very easy to read the PowerShell object in and write them to a JSON file, and then and use the path to this JSON file to specify the databases, the actions required and the version. This means you’d also have a central way to maintain the global flags, placeholders and config details, as well as those that are specific to each database.

Handling credentials

You never have to store a password in this file. The script will store and retrieve any password for a User ID and server in an encrypted form. It will, if it can, get the credentials from file, and will only ask you once for credentials if it can’t get them from file. It then stores them securely for future use, in your user area. If you change your password, you just delete the relevant file in your user area.

Getting database migration reports

Flyway can be persuaded to produce PowerShell-friendly reports. I’ve written a separate article, Using Flyway Output in PowerShell, to explain in more detail how to process this information to produce reports. I’ve shown in this example how, if you specify JSON output, you can save a JSON report to disk, and to also take the details from the standard output of Flyway and store it in an array. If you don’t specify JSON, the text is sent on to standard output.

Specifying Flyway actions, flags and settings

The script is designed so that you can specify one or more default actions for a group of databases or specify one or more actions for one or more databases. The same goes for flags and settings. Actions are the general migration function that you wish Flyway to perform and flags control what Flyway does.

Actions:

• migrate: Migrates the database
• clean: Drops all objects in the configured schemas
• info: Prints the information about applied, current and pending migrations
• validate: Validates the applied migrations against the ones on the classpath
• undo: [teams only] Undoes the most recently applied versioned migration
• baseline: Baselines an existing database at the baselineVersion
• repair: Repairs the schema history table

Flags:

• -X – Print debug output
• -q – Suppress all output, except for errors and warnings
• -n – Suppress prompting for a user and password
• -v – Print the Flyway version and exit
• -? – Print this usage info and exit
• -community – Run the Flyway Community Edition (default)
• -teams – Run the Flyway Teams Edition

Settings can almost all be specified via config files, environment variables, and parameters. There are too many to list here, but I’ve put them in the body of the script because they are more likely to be used routinely.

Any of them can all be either specified as the default for all the databases in the list, or they can be done for the individual database. Local flags are added to the list of flags for the database, and the same is true of settings. Actions are handled differently. The local list of actions that are specific to the database take precedence over the global action list. The difference that this is an ordered list of actions.

Settings, unlike flags and actions, are key-value pairs so they are represented in the object passed to the script as a hashtable. For local settings, all you need to do is to add them as hashtables. In PowerShell this would be:

In JSON, this would be …

Running multiple database migrations at once: the script

One of the commonest tasks I get Flyway to do is to build a database to a specified version. It isn’t easy to do this purely by a migration because this will only work if you are certain of the version of the installed target database. And, of course, development databases are highly unlikely to be unsullied, at the specified version, because the whole purpose of development databases is to sully them.

Once you’ve made changes, they are no longer at a specific version, so they can’t be migrated. This is also the case if you want to regress the database to an earlier version. I therefore find myself doing the Clean > migrate > Info sequence of actions on a database. The GlobalActions configuration setting determines the actions that will run on a database, by default, but you can override this at the database level simply by listing the required actions in order, for each database, like this:

So here is the script. I’ve set it, as a demonstration, to work on the project PubsAndFly, in the folder PubsFlywaySecondMigration. It is set up to run on three different servers and will migrate any copies of the Pubs database you specify to the version you specify.

The only mandatory parameters that you need to pass to Flyway command-line are the database connection string, for which you’ll need to fill in the server, database, User ID and port, and the path to your project folder containing the migration files (and in this case, also ensure there is a subfolder within it called Reports).

Conclusions

Why automate a task like this? I suspect that it isn’t just my intolerance of boredom that comes from doing an unstimulating and repetitive task. Once a task such as creating copies of a database becomes easy to perform, it can open up possibilities for testing, and allow developers more freedom to experiment. It can alert you to problems far earlier and allow you to include all those precautionary tasks, such as generating build scripts that you know are useful but you somehow never get around to if you perform every task ‘by hand’.

Flyway is built from the ground-up with the philosophy of automating database development tasks, and I hope that I’ve provided you with enough in this article to get you started with PowerShell scripts for Flyway.