Phil Factor

22 November 2023

• Flyway
• Multidatabase management
• Automation and workflow

Doing Flyway Migrations for Many Different Databases and Projects

Some Flyway database projects can get complicated and messy when there are several databases, branches and variants. I’ve described previously a couple of ways of scripting builds and migrations for Flyway projects. Batch Processing using Flyway uses DOS batch files to run a range of migrations tasks, each defined as a configuration file, across multiple databases. Automating Migrations for Multiple Databases using Flyway shows how to use PowerShell automation to build multiple builds of a single project.

In this article I’ll explain a PowerShell approach for cutting through the chores and simplifying the processes. It is designed to allow you to run a Flyway task on multiple projects, migrating any number of databases, hosted on any RDBMS. It’s designed to simplify the management of a range of tasks, from executing a single command on a single database, to running a full clean-build-test routine to verify every branch of a project, to migrating every database to the latest version, across multiple different projects.

It uses PowerShell, and a list of objects representing each Flyway project that allow you to store all the UserIDs and connection details, for each server and database, safely in the user area of your workstation. It is also designed for running regular tests, in that it does the filtering of output so you can see warnings and errors more clearly and send alerts to a notification system.

The Inspiration

One of my regular chores is to do complete rebuilds for all my sample databases in the Flyway Teamwork project to make sure that everything is still working, after any project or framework changes, or even after upgrading to a new version of Flyway. There are about twenty databases on five different servers. Even if I’ve made few changes, it is always worth doing and surprising errors sometimes happen, even when cause and effect is somewhat mysterious.

There are two different tests. The regular chore, which causes the least trouble and is more relevant to general work, is to make a connection, clean the database entirely and do a complete migration run, in a single ‘migrate’ command. However, if you only ever did this first test, you’d miss checking any scripts, routines or integration tests other than those that are applied right at the end of an entire migration run. The other test, which takes longer, migrates one step at a time, performing each migration file in every project to make sure that all the ‘after’ callbacks still work after every migration.

As I hate writing anything twice, I developed a couple of PowerShell cmdlets that do the entire test run and let me know by means of a notification if there are any errors. I can then use them within a scheduled task, and I even get pinged on my mobile phone if there is an error!

Principles

These are the principles by which I developed the Do-AFlywayCommand cmdlet, and its migration-by-migration partner, Redo-EveryFlywayMigrationSingly:

• It must allow the simplest command, such as getting info on a single database.
• It must extend to any number of commands, for any number of databases, with a range of connections and credentials.
• It must integrate with any notification or reporting system
• It should be designed to protect as much information, such as connection details or credentials as the user requires.
• It should allow the logical separation of project configuration, connection information and personal details.

Rebuild all databases in one or more Flyway projects and report errors

The Do-AFlywayCommand cmdlet places all the chatter from Flyway, and the SQL code, into the verbose stream, which you can switch in with the -verbose switch. Warnings and errors are sent to the appropriate streams. If you define a notification handler, it will send errors to it.

I show the full code for the cmdlet in my previous article, Flyway Alerting and Notifications, so rather than give a blow-by-blow account of how it is used, the following script runs demonstrates it by example, from running an info command on the current project, to doing a full clean and build on all the branch or variant databases of a project, or on databases across different projects:

With my simple test runs, I do an info, clean and migrate command. This means that I create a pipeline that runs each of the commands in turn. In this case, we’ll clean the databases for two branches (main and develop) of the PubsMySQL Flyway project, and then rebuild them both. Each of the databases is on a different server:

The Do-AFlywayCommand cmdlet runs the ‘Flyway migrate’ command without qualification so it will execute every migration in a single run. It will test AfterEach… callbacks after each migration in the run, but After… callbacks will only run at the end of a successful migration run. If it hits an error, it rolls back to the last successful migration.

Another similar Cmdlet

There is a companion Cmdlet called Redo-EveryFlywayMigrationSingly that executes each migration in turn. If, for example, you are at version 1.1.3, it will run a command to take you to V1.1.4. If successful, it runs all the After… callbacks. It will then run a migration to V1.1.5, and so on. If there is an error, it will roll back to the previous version.

This, of course, will take a lot longer, especially if you are doing a lot of Flyway callbacks, but you will get reports and data for every version. Here is an example. Again, I haven’t shown you the entire list of databases.

This uses Do-AFlywayCommand so that you get can notifications for free with this. Although Do-AFlywayCommand doesn’t need any other aspect of the Flyway Teamwork framework, it works happily with it.

The Redo-EveryFlywayMigrationSingly can be used to run a whole lot of scripts after each migration if you don’t have Flyway Teams. You need to install preliminary in the project folder, and you should be good to go.

Summary

Please don’t think that I like complexity. I automate repetitive work to waste less time on routine tasks. I show you these cmdlets to demonstrate that, however complex the database system is that you are developing, Flyway can be used to increase the accuracy of the processes and reduces the risk of human error. If you have good notification systems, you can schedule regular tasks, such as overnight migration runs, with the confidence that you’ll be alerted if anything goes wrong.