Phil Factor

25 July 2024

• Flyway
• Automation and workflow
• Team-based Development

Using Bash with Flyway

Flyway takes well to automation via scripting. It conforms strictly to the conventions of the CLI interface, so it is amenable to whatever interpretive scripting language you prefer. As well as using a script to run Flyway, you’re likely to want Flyway to run scripts, such as callback scripts, so that you can run various database tests and other tasks before or after Flyway execute a migration, or some other command. You might also want to run a migration as a script, though this is less likely. All this means that the scripting language you use should work just as well when called by Flyway as when Flyway is called from a script.

You can call Flyway from Bash in any Flyway edition, and you can, in Version 10 onwards, have Flyway run scripted migrations and script callbacks using Bash, Dos Batch, PowerShell or Python.

Installing and using Bash

Bash is likely to be on your machine even if you are using Windows, because Git installs the mingw64 bash shell, as does W64devkit, Winlibs and Cygwin. Before you can use it with Flyway callbacks, you need to test beforehand to make sure that sh.exe is in one of the paths in the configuration by typing ‘sh‘ from the DOS console. If you don’t open a new Bash instance that way, you may need to add the installed mingw64 bash shell to your paths, perhaps copy the executable (sh.exe) to the same directory, and then reboot.

When you are using Git’s mingw64 Bash shell in Windows, you don’t necessarily need to add a shebang line (like #!/bin/bash) at the beginning of your script files. This is because Windows doesn’t rely on shebang lines to determine the interpreter for script files in the same way as Unix-like systems. It uses the PATH environment variable instead. However, a shebang line makes the script clearer and more portable to Unix-like systems in the future. It can also help editors and IDEs recognize the script language and provide syntax highlighting and prompts.

Testing out Bash and Flyway

To test out using Bash with Flyway, the easiest configuration is to create a Flyway project folder and place your Flyway migration scripts in a migrations subfolder:

my_flyway_project/
└── flyway.conf
migrations/
│       ├── V1__create_table.sql
│       └── V2__alter_table.sh
└── ...

In the Flyway project folder, which will be your current working folder, you place all the configuration information that is relevant to the database project, and the way you wish Flyway to operate. However, we never put personal details such as credentials here because this directory is likely to be a network share used by others, and most probably stored in source control.

Instead, we place a flyway.conf file (or flyway.toml file in Flyway V10 onwards) in the user area containing the database credentials that are required for the project. I typically put just a UserID and password in it, but you may need to keep the URL there too if your security standards require it.

This configuration will get you started, but in team-based database development there will be multiple users for each Flyway project, and each user will need to access several development and test copies of the database. Therefore, we need a way to store these details securely, for each user, and then a way to pass these credentials, and any other required information, securely to Flyway to allow it access each database with appropriate credentials.

In the following sections, we’ll explore how to do all this using Bash and Flyway.

Dealing with credentials

In this example, I’m working with ‘classic’ flyway.conf configurations files. My approach is to store all the required credentials for each user, in extra flyway.conf files in the user area. The number of files will build up and it is easy to get them muddled unless you impose a good naming convention on them. I use the convention <Project>_<RDBMS>_<Branch>_<Server>.conf for this. This scheme also means that all users can share a network-based Flyway project folder yet maintain their own credentials.

Our Bash script constructs the path to the required configuration file using the parameters provided. It then parses the contents of the file to read the credentials and passes them to Flyway. I’ll demonstrate a couple of ways to do this, either setting the credentials as environment variables or by piping them directly to the Flyway command using piping.

In Flyway V10 and later, we can instead use flyway.toml configuration files, which introduces simpler techniques for handling all this. For example, we can use the ‘environments’ feature to read credentials and other information for each target database, and property resolvers for pulling secrets into Flyway. I’ll describe these newer techniques in a subsequent article.

Setting credentials as environment variables

You don’t necessarily need to store credentials on disk. An obvious alternative is to just type them in as parameters in the command line or within a script, although this ‘interactive’ approach is now deprecated. A better and more secure way to do it is to save the required credentials as environment variables that have the lifetime of just the current shell and all processes started from current shell. This isn’t too much of a bother in the early stages of a development but the more copies of a database that you have, the more tedious it gets, and besides, even typing credentials is a security risk.

Instead, therefore, we store all the credentials in extra Flyway.conf files in the user area. If you are doing branch-based development, you’ll need a separate ‘secrets’ configuration file per branch. All we then must do is to read the details from the appropriate files into environment variables. Here is a Bash function, set_env_credentials, to do this. You just run it whenever you change branch, fork, or project:

Each project will have its own parameters, so that each user will then be able to have a different password and UserID for each copy. Here, we finish by testing that we connect satisfactorily to the database.

This should set you up. The Flyway documentation provides examples in Bash for most simple operations.

Piping credentials directly to the Flyway command and parsing the JSON output

The following example demonstrates an alternative way of providing the credentials to a Flyway command, by piping the contents of the config file into the flyway info command using the -configFiles=- option. You can use this technique even when the use of environment variables for credentials is frowned upon, or if credentials must be stored in encrypted form to provide ‘encryption at rest’.

The example also demonstrates how we can use Bash to read JSON files where Flyway saves the results of these commands. If, for example, you needed to write a report for each migration, you’d need to know the Flyway schema version number, if only for the filenames, so that you understand which reports pertain to which version of the database. The only way one can do this in a script is by getting the current version of the database from the JSON report files that are produced after each Flyway command, or the JSON file that can be generated from the info command.

Here is the code that creates a GetFlywayVersion function and then executes it. The function reads the required credentials from the user-level flyway.conf file (specified by the values stored in the environment variables), captures the output of flyway info in a JSON file and then parses it to retrieve and return the current schema version. I cheat slightly by using jq, which is a command line utility that parses (and prettifies) the JSON and can return property values. As it obeys the rules of CLI scripting, it is very handy for Bash or DOS scripting.

Maintaining several databases from the one project: an automation example

In this demonstration, we have a single-user installation of Flyway that allows us to maintain several copies of the database, to keep them all up-to-date at the latest version, or at whatever version you specify in the config file. You’d need something like this if several developers need to access their own copies of a branch of the database such as Main or Develop, and you maintain them from a central scheduled process, with access to all the databases.

In this case, we keep the configuration files for each copy of the database in a directory in the user area. We execute Flyway tasks (migrate and info) for each configuration file, by specifying each one as a parameter using the -configfiles configuration parameter.

Although configuration is taken from the config file in the working directory, we are using these extra config files for not just for the credentials and the URL of the database, but also any other details that we would otherwise need to provide as parameters. The config files read in via the -configfiles take precedence over the ones read from the config file in the three default directories of install directory, user area, and current working folder. We are, in effect, using these extra config files as if they were parameters.

This is simpler than the previous technique, but we lose the advantages of piping the text of the configuration file to Flyway. The main two advantages of piping are that we can decrypt an encrypted config file or use dynamic configuration items that we specify at runtime, or extract from elsewhere.

If you’d like to test this out, the typical extra config file in the subdirectory of your home directory would look something like this, but with real values substituted for various “prompts of real values” within angle brackets. Only include the target parameter that is in the following example if you wish to use that version specifically.

The migration files you will need to specify in the flyway.locations are here in the FlywayTeamwork project.

Conclusion

Bash is a useful scripting language for Flyway, because it is so widely available and does everything that DOS Batch scripting will do, and just as easily. It is an alternative to PowerShell, which adopted quite a few of the features of BASH, such as pipelining, aliases, job control, functions and package management. It is ideal for simple automation tasks, and it makes sense to have scripts and callbacks in the same language. My own preference is for PowerShell because it can deal with data as well as text in pipelines. Although I use PowerShell happily on Linux, I must agree that I spend a lot of time in Bash as well. In this article, I’ve shown that, with the help of other CLI tools such as JQ, it is likely to be able to meet your scripting requirements for Flyway whatever platform your workstations use.