Batch Processing using Flyway

How to create a batch file that executes any number of database migration tasks across a range of servers and databases, using Flyway.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Batch files are still in common use for automating database deployments because it is very easy to run lots of tasks concurrently, across several databases. Although PowerShell now dominates for database deployment tasks on Windows, many of Flyway’s features are intended as a way of making batch file automation easier.

The DOS batch files to run Flyway can be as simple as you like. Some can be little more than a means to preserve for repetitive use the list of commands required for a successful run of a task. They also provide a simple way of integrating with other build automation tools and software development tools.

In this article, I’ll show you how to create a batch file that automates database migrations for any number of databases, across several servers, upgrading every specified database to the requested version, from a single set of migration scripts. It is all very easy and very configurable. It is done by storing all the tasks as config files and executing them each in turn as parameters to Flyway. I show how to do something similar with PowerShell automation in Automating Migrations for Multiple Databases using Flyway.

Why do batch processing with Flyway?

In the past, before PowerShell became popular, Flyway command line has, when run in Windows, mainly been executed at the command prompt or within DOS batch scripts. These scripts don’t have the same level of risk with network security that you have with PowerShell. Some shops will even discourage the use of PowerShell, but I’ve never seen or heard of DOS batches being banned.

I’m currently working on a Flyway project in my programmer’s editor, creating a migration script, and it offers a good example of why you might choose to use a DOS batch script. They’re a breeze to integrate. I can run a ‘clean’, ‘info’, ‘migrate’, or ‘repair’ instantly, save a script to Git, as well as being able to execute the SQL Script, just by clicking a menu item. There are plenty of examples of such so scripts on the Internet. Like many programmers’ editors, the one I use allows you to extend its facilities. Although SSMS is my natural habitat, old developer habits occasionally kick in, and I grasp for a good programmer’s editor to help with trickier database development activities.

Understanding Flyway configuration files

To understand how this works, it pays to appreciate the subtleties of the Flyway configuration files. If you use them sensibly, it saves you a lot of typing, and effort. When Flyway starts up, it reads several config files in order, as follows:

  1. <installDir>/conf/flyway.conf file. You are likely to store here just the generic machine-level configuration items only, such as the license key or the text encoding system you use.
  2. <userhome>/flyway.conf file, which would contain all the user-level information and any general information that should be kept private to your login, such as network credentials.
  3. <WorkingDir>/flyway.conf file. This is in the Flyway project directory and will have all the project-level configuration items that are shared.

These flyway.conf files are intended for your general work with Flyway and your project in general. However, you can still get Flyway to use other configurations by using the configuration parameter -configFiles="myFirstconf,mySecondconf…". These files can have any filename, but I think they ought to have a .conf filetype. What additional config files would you want to specify at the command line? Here, you’d want to specify the location of project-specific files that have connection details and any other details specific to a particular server or database. These need to be in a subfolder within your user area so they are protected. As you can supply a list of paths to config files, you can, if you want, save yourself time and effort by having a config file for each server, and a config file for each database as well and having one for project settings. You can then provide them in a list to the -configFiles configuration parameter. Flyway is very accommodating.

If you store these files in the user area, you can rely on Windows security to offer protection for files that may contain credentials. DOS Batch processing doesn’t have an easy way of doing encryption. If Flyway reads credentials direct from a config file in the user area, they never appear in the script, which is good for us and very tiresome for the hacker. It isn’t ideal for team-working though, especially when you need to make changes to the way databases are migrated, but then it isn’t hard to develop the system by setting up secure network shares.

Although Flyway Community doesn’t allow passwords to be stored in a vault, Flyway Teams is more geared to the corporate user as it allows access to HashiCorp Vault.

Migrating multiple databases using a batch file and Flyway

We’ll take advantage of all this by keeping a config file for every database that we want to manage. These are stored together in a config folder within the user area. Each copy of a database, Pubs in this example, will be managed from one project, one Scripts folder, and one config folder. This is easily expanded if required, as described previously.

A task for our DOS script consists of one or more databases for a single project that ought to be deployed at the same time. I’ll show later how we can define a subset that you want for a task, or don’t want.

To demonstrate, we’ll use the same long-suffering sample project PubsAndFlyway that we’ve used in previous articles and you can find the source scripts here: https://github.com/Phil-Factor/PubsAndFlyway/tree/main/PubsFlywaySecondMigration/Scripts.

We’ll need a folder of databases in the Home directory, each one represented by a config file like this:

Flyway configuration files for each database

A typical config file will have contents like this.

The placeholders are the ones I’m using to add an extended property to the database to make it easy to check the current version. You only need the URL and locations to get started. Here is one with credentials.

You just need one for every database that you want to manage. If you initially use an empty database for each, then you should be fine for this demo.

Just to show how useful this sort of scripting can be I’ll start showing how to do migrate, where required, all the databases in the project. It is for doing this sort of task that a script like this becomes essential. Then we’ll show how easy it is to use batch files to work with single databases, because there is always some hands-on micro-administration to be done in development work.

Multi-database automated migrations

Here is the code of the batch script. It upgrades every database that has a config file in the config folder you’ve created. You will need to add the path to where you’ve installed Flyway, and the name of the folder with all your config files.

If you want to use just a subset of the files, you’ll need to alter the following clause so that the regex used by Findstr filters out only the files you want:

For example, if you only want to do PubsOne and PubsTwo you might do:

Or, if you only want databases on a particular server then you could use:

If you want to exclude filenames, use the /v switch. Alternatively, you might want to get complicated with regexes.

The script uses the phrase for %%a in (migrate info) to specify that you first do a migrate action, followed by the info action. I’ve specified JSON output because I find it easier to read, but you might not like that, in which case delete -outputType=json (this option is for v7 onwards). You can, of course, add any parameters that would apply to every database in the project. Even neater, if you have a lot of project-based placeholders, have a special config file for them so you have less to put into the database config files.

The solution looks like this. In this example, we are managing database migrations for 6 copies of the Pubs Database from a single project, and a single set of migrations scripts:

Automating migrations for many databases using a batch file and flyway

Working on single database migrations

If you need to do some custom work on a particular database, it is easier to work on it singly. Here is a batch job to do just that. Here you just specify the action you want, and any extra parameters and you should be good to go.

I’ve just specified the database by specifying the config file and then executed the info action. When I execute it, it gives me a text table

Schema version: 1.1.6
+-----------+---------+-----------------------------+------+---------------------+--------------+
| Category  | Version | Description                 | Type | Installed On        | State        |
+-----------+---------+-----------------------------+------+---------------------+--------------+
| Versioned | 1.1.1   | Initial Build               | SQL  | 2021-02-24 18:03:34 | Success      |
| Versioned | 1.1.2   | Pubs Original Data          | SQL  | 2021-02-24 18:03:36 | Success      |
| Versioned | 1.1.3   | UseNVarcharetc              | SQL  | 2021-02-24 18:03:40 | Success      |
| Versioned | 1.1.4   | RenameConstraintsAdd tables | SQL  | 2021-02-24 18:03:45 | Success      |
| Versioned | 1.1.5   | Add New Data                | SQL  | 2021-02-24 18:04:12 | Success      |
| Versioned | 1.1.6   | Add Tags                    | SQL  | 2021-02-25 09:15:30 | Success      |
| Versioned | 1.1.7   | Add Indexes                 | SQL  |                     | Above Target |
+-----------+---------+-----------------------------+------+---------------------+--------------+

Hmm. I’d like to update to V 1.1.7 please, and then get the new info:

And we get the info once again

+-----------+---------+-----------------------------+------+---------------------+---------+
| Category  | Version | Description                 | Type | Installed On        | State   |
+-----------+---------+-----------------------------+------+---------------------+---------+
| Versioned | 1.1.1   | Initial Build               | SQL  | 2021-02-24 18:03:34 | Success |
| Versioned | 1.1.2   | Pubs Original Data          | SQL  | 2021-02-24 18:03:36 | Success |
| Versioned | 1.1.3   | UseNVarcharetc              | SQL  | 2021-02-24 18:03:40 | Success |
| Versioned | 1.1.4   | RenameConstraintsAdd tables | SQL  | 2021-02-24 18:03:45 | Success |
| Versioned | 1.1.5   | Add New Data                | SQL  | 2021-02-24 18:04:12 | Success |
| Versioned | 1.1.6   | Add Tags                    | SQL  | 2021-02-25 09:15:30 | Success |
| Versioned | 1.1.7   | Add Indexes                 | SQL  | 2021-02-25 18:05:59 | Success |
+-----------+---------+-----------------------------+------+---------------------+---------+

Conclusions

I’ve spent many decades trying to escape from creating and running batch files, and I’m wedded to PowerShell automation. However, with a command-line tool such as Flyway, it makes a lot of sense to be able to control it via batch files where required. Flyway is designed with this in mind, and so its design has evolved to make it all very easy.

Batch files are very ingenious and terse, but they’re not the easiest way of doing automation. Surprisingly, though, they still have their uses.

 

Tools in this post

Flyway

DevOps for the Database

Find out more