• Flyway
• Configuration, Authentication
• Automation and workflow

Flyway Without the Typing

Ideally, you’d want Flyway to work with as little typing as possible, especially if you’re working interactively. You may, for example, need several different actions to resolve one of the more complex operations such as a merge. To make it easier to do this sort of interactive work, Flyway is designed to be usable by invoking it simply from the command line, despite needing to rely on quite a lot of information such as database connection strings. To allow this, Flyway will read information from config files and environment variables, leaving you just to specify the action, which it can’t second-guess. OK, maybe you’re happy with scripting or writing Java applications but this article describes configuration techniques for the people who need to run their dev processes and aren’t into scripting, or who just want to type out the simple commands.

Flyway and its Config Files

Unless instructed otherwise, Flyway determines its working settings and configuration by reading config files that are stored in three places. It searches each location for a file containing any settings, in the following order:

1. Within flyway.conf in the conf subdirectory of the directory where Flyway was installed (installDir/conf/flyway.conf).
2. User’s home directory (userhome/flyway.conf).
3. Current directory (workingDir/flyway.conf).

If you put Flyway into Verbose mode (-X) then you will see when this happens. In my case:

DEBUG: Loading config file: C:\ProgramData\chocolatey\lib\flyway.commandline\tools\flyway-7.8.1\conf\flyway.conf
DEBUG: Loading config file: C:\Users\phil\flyway.conf
DEBUG: Loading config file: C:\Users\phil\Documents\Databases\MyCurrentDB\flyway.conf

If the same key has different values in the different files, it will overwrite the prior value, meaning that the later config file in the chain takes priority. Until you add flyway.conf files to the second or third locations they are just checks to see if any file is there. If a config file exists, it is read to fetch any settings.

The config file in Flyway’s installation path, (flyway.commandline\tools\flyway-x.y.z\conf\flyway.conf) has all the possible settings, commented out along with explanations. It can be used as a template for the other files. A lot of these settings constitute connection information. There is also information on current projects whether cloud-based or on the local filesystem. Finally, there are many settings that determine the way that Flyway does its tasks. All config files are allowed to have the same settings: they are different merely in their location. As well as parameters that are unique to it, the command line also allows parameters that will override these configuration settings. In the command-line, we can even pass to Flyway a list of config files to read to get more settings. We can do the same thing by using settings from environment variables.

This may seem overly complex, but the reason for starting with three different files for settings is that users can put settings for different purposes in the different locations. All global settings that are needed to be permanent are usually done in flyway.conf in the install directory. Settings that need to be kept secure, such as UserIDs and passwords must be in the user Home directory or subdirectories. All project-based information must be kept in the current working directory and the user must change directory to pick up this information when doing a migration.

This design allows Flyway to be invoked with the minimum of keystrokes. Ideally, unless you are scripting, you just want to specify the action (migrate, info, clean etc.). You just make the right directory your working directory and tap in Flyway Migrate and the migration will burst into life.

This starts to get more elaborate if you are managing more than one project, or you are managing several instances of a database. You can only store one set of connection details in your user area. A config file in the working directory cannot point back to the location of a config file in the user area for specific connection details. The -configfiles parameter of Flyway isn’t matched by a similar entry in the config files. One can understand the consequences of getting that wrong unless you check for a circular definition! Normally, each project needs just one database connection, so it could be argued that you can place the project in the user area and just add the database connection details to the config file. This will be read in from the working directory when Flyway is invoked. The problem comes with source control. The project configuration details can’t be saved because they’ll probably have UserIDs and passwords in them.

Running Flyway from config files

We’ll set up some config files to demonstrate some of this. We’re going to use the flyway.conf file in the current working directory for all the project settings. To make things more complicated for us, we’ll test the limits by using my PubsAgnostic GitHub project where we build the classic Pubs project for four different databases systems based on four different servers with different credentials, and a lot of different placeholder values. See my previous article, One Flyway Migration Script for Diverse Database Systems, for details of how to run Flyway migrations across multiple database systems.

We want to make it possible to do interactive work from a terminal, maybe getting information, fixing a failed migration that couldn’t rollback, cleaning a database that you’re rebuilding after a change to an early migration.

So, the rules are this:

• The user just needs to type in Flyway, with the action and any special parameter they need.
• No connection information in the project file directories.
• You are allowed just one command to type in to set things up to work on a particular database.
• No scripting allowed.

We’ll set up four config files, each in its own directory in the current user area. It will have UserIDs and passwords in them as well as the placeholder values and other project details, so we’ll need to rely on the operating system security for protection. If you are a dyed-in-the-wool command-line freak, you’d probably want to type in these Flyway config files by hand, create the directories and so on. I’ll explain how to do this later in the article.

However, I use the Create-FlyWayParametersets cmdlet (PolyglotFlyway.ps1) that I wrote for the above article, because I keep all my working parameters as JSON files, and I can make a change that is, where necessary, rolled out to all the current config files. This cmdlet accepts an array of placeholders ($FlywayArray), one placeholder for each database system. From this, it creates parameter sets that it can then use to connect to each system in turn. We put each in its own directory so that, merely by making it the current working directory, you will adopt the flyway.conf file in that directory. All you need to so in order to run it is to fix the $FlywayArray with your parameters as appropriate and alter the parameters below for your system (the code for everything is in the linked file):

You’ll see that it has created the directories for you and placed in them the appropriate config file.

Now we can try one out. Firstly, we’ll use PowerShell. All we do is move to the relevant directory and execute flyway.

PS C:\Users\phil> cd "$($env:UserProfile)\Documents\Databases\PubsMariaDB"
>> flyway info
Flyway Community Edition 7.8.1 by Redgate
Database: jdbc:mariadb://MyServer:3307/pubsPolyglot (MariaDB 10.6)
Schema version: 1.1.2
+-----------+---------+--------------------+------+---------------------+---------+
| Category  | Version | Description        | Type | Installed On        | State   |
+-----------+---------+--------------------+------+---------------------+---------+
| Versioned | 1.1.1   | Initial Build      | SQL  | 2021-08-24 17:18:50 | Success |
| Versioned | 1.1.2   | Pubs Original Data | SQL  | 2021-08-24 17:18:51 | Success |
+-----------+---------+--------------------+------+---------------------+---------+

Now we’ll do it with DOS with these commands

%Homedrive%
cd %USERPROFILE%\documents/databases/PubsMariaDB
flyway info

Paste that into the DOS command line

C:\Users\Phil>%Homedrive%
C:\Users\Phil>cd %USERPROFILE%\documents/databases/PubsMariaDB
C:\Users\Phil\Documents\Databases\Pubsmariadb>flyway info
Flyway Community Edition 7.8.1 by Redgate
Database: jdbc:mariadb://MyServer:3307/pubsPolyglot (MariaDB 10.6)
Schema version: 1.1.2
+-----------+---------+--------------------+------+---------------------+---------+
| Category  | Version | Description        | Type | Installed On        | State   |
+-----------+---------+--------------------+------+---------------------+---------+
| Versioned | 1.1.1   | Initial Build      | SQL  | 2021-08-24 17:18:50 | Success |
| Versioned | 1.1.2   | Pubs Original Data | SQL  | 2021-08-24 17:18:51 | Success |
+-----------+---------+--------------------+------+---------------------+---------+
C:\Users\Phil\Documents\Databases\Pubsmariadb>

OK. That seems almost too easy so let’s now do four different projects involving four different types of database system, accessing different servers and different databases. We’ll delete the existing versions of the databases and rebuild them.

Ad-hoc migrations

Following are some examples of the sort of session you might be doing for ad-hoc work. In PowerShell, the following set of commands will tell us what version the databases are at, clean them and rebuild them. It does this for our four databases, all on different servers and with different relational databases (I’ve deleted the screenfuls of information that came back at each stage…):

And here is the DOS version. Again, I’ve removed the screenfuls of information that came back after each line…

%Homedrive%
cd %USERPROFILE%\documents/databases/PubsMariaDB
flyway info 
Flyway clean
Flyway migrate
cd %USERPROFILE%/documents/databases/PubsPostgreSQL
flyway info
Flyway clean
Flyway migrate
cd %USERPROFILE%/documents/databases/PubsSQLite
flyway info
Flyway clean
Flyway migrate
cd %USERPROFILE%/documents/databases/PubsSQLServer
flyway info
Flyway clean
Flyway migrate

Batches

If you are doing batches, you’d want to do something like this in PowerShell

In this example, I’m just getting information, but you get my point. You can do all sorts of actions on a group of databases, together or independently.

In DOS, it is even easier, though the syntax might take more studying.

%Homedrive%
for %%x in (
        PubsMariaDB
    PubsPostgreSQL
    PubsSQLite
    PubsSqlServer
       ) do (
    cd %USERPROFILE%/documents/databases/%%x
    flyway info
       )

In both cases, you can open the PowerShell command line session or the DOS command line session and tap in whatever complex Flyway commands you need. As long as you have the correct current working directory, all should be well.

Setting things up manually

To achieve all this, I just had to prepare the four config files, each in its own subdirectory of the user Home directory. We start off creating our four subdirectories in our user home area and copying a config file into each one.

I chose to put them in the Documents\Databases\ subdirectory, but you’ll have your own preferences. Here is the SQL Server config file.

For this project, which is exceptional, I also needed a whole bunch of placeholders to make substitutions or macros into the SQL script to make them executable for this particular RDBMS. I did this in order to make sure that placeholders were picked up properly.

Developing strategies of using Config files

Those config files look like hard work, but it is a one-off process. Once they’re done, they are reused repeatedly. All ephemeral (one-off or state) information is in the command-line or stored within the target database. I’ve provided a PowerShell cmdlet to make it easier to generate.

If you have placeholders that applies across all your database projects, such as your identity, or other information that applies to all your work (your company, source-control information etc), then the user area is probably the best place for it. I like to store passwords encrypted so I make temporary config files and store them in the user area only when I need them for a task. It takes just a moment to generate them

Conclusions

Flyway’s development has always been driven by requests from its users. This has meant that, even if you go ‘off-piste’ in your database development work, you’re likely to find a feature that supports what you are doing. Is there a ‘royal road’ to using Flyway? No, definitely not, because it is designed to fit in with the way that you and your team prefers to work, and will accommodate a wide range of database development practices from the small-scale to the Enterprise.

There are plenty of different ways of doing migrations. They can be interactive or script-based. You can use Flyway as part of a workflow process or application build system. In this article, we’ve explained some of the interactive ways that you can use Flyway before the point where scripting has eventually to kick in.

In this article, I’ve shown that are many ways of telling Flyway what configuration you want. You can use environment variables or parameters to specify a configuration file, you can run Flyway within a working directory that contains a flyway.conf file with the information, or you can even place all the information in environment variables or parameters, a method that is easy to script.

Which method do I use? It varies, depending on the size of the project, the scripting techniques available, and how I would eventually automate the process. There are plenty techniques to choose from.

Tools in this post

Flyway

DevOps for the Database

Find out more