Phil Factor

7 January 2022

• Flyway
• Configuration, Authentication
• Automation and workflow

A Programmer’s Guide to Flyway Configuration

As well as reading any relevant environment variables and parameter values, Flyway can collect all the details it needs to run any database migration from a configuration files. These configuration parameters provide it with, for example, the location of the migrations files, connection details for the target database, and the schemas that Flyway needs to manage and update. They also determine the way that Flyway does its tasks, such as how it executes a migration run, and how it responds to and reports errors.

My general approach to working with Flyway is to use PowerShell scripting, supplying the values for the required configuration settings directly from PowerShell parameters. When using callbacks, I read the runtime values of environment variables passed to the script by Flyway. However, there are times when it’s best to specify some of the required settings in one or more of Flyway’s configuration files. For example, if you wish to use Flyway interactively, with minimal typing, or if you need to maintain several different versions of the same database, to support each of the various branches of development. For me, the use of Flyway’s configuration files is limited only by my extreme reluctance to ever place credentials unencrypted in a file or environment variable.

Using Flyway’s multi-level configuration system

Flyway works most comfortably with a series of flyway.conf files, each of which is a text file with series of lines providing key/value pairs for each parameter.

By default, Flyway reads three configuration files in succession. Each file is read and any keys that aren’t commented-out are parsed, and their values assigned. These files are in three locations: Flyway’s installation directory, in the user directory, and in the current working directory, which is normally the project that you’re working on.

The reason that there are three different files is that you might need to set the way that the tool behaves on the workstation, set the information about the user, and set the information about the project that you are working on.

If you need further Flyway configuration files to provide new information or to overwrite the values you’ve provided for Flyway, you can provide these in a list as a value to the –configFiles parameter when you execute Flyway.

By making the development branch you are working on the current working directory and having the appropriate information in the three levels of flyway.conf file, including one in the branch directory, you can then move easily between branches without having to make any other changes. If you are doing team-based development with Flyway, this may have to be supplemented with your specific credentials for the development database you are working on.

Navigating the Flyway configuration parameters

The range of configuration settings, almost all of which have corresponding parameters, is large, and can be a bit bewildering at first. My aim with the remainder of this article is therefore to provide a “programmer’s guide” to the various configuration settings, making it easier to understand the different categories of config parameters and the role of each of them within that category.

I’ll cover the the configuration options in the following categories:

• Connection Details
• Schemas and flyway_schema_history
• Configuring migration files
  • file location
  • file naming
• Controlling Flyway behavior
• Executing Migrations
  • Placeholders
  • Running migrations
  • Callbacks

Establishing the connection information

Flyway uses JDBC drivers to connect to databases. These are loaded dynamically according to the database connection strings that you specify. Certain JDBC drivers are packaged with Flyway and are well tested with it on every release. However, you can use alternative drivers for your RDBMS by installing them in Flyway’s installation folder, and we can specify these via flyway.conf settings.

A database connection will involve a number of settings such as ANSI-compatibility, and error-handling which are either added to the URL or set in SQL (see flyway.initSql). The user credentials need to be in the user area to provide reasonable security against access by other users, whereas the database details need to be in the flyway.conf within the project. If you have a single login to all the databases used by all projects, then it is all easy. With Flyway Teams you can use Secrets Management; otherwise the credentials will need to be provided security either by parameters or environment variables (the $FetchAnyRequiredPasswords task in my PowerShell library encrypts passwords and stores them in the user area), or by referencing a flyway.conf file for that specific server/database in the user area.

Specifying the database connection

Schemas, default schemas and the flyway_schema_history table

Flyway always puts the flyway_schema_history table in the default schema. On start-up, Flyway sees whether you have specified what schema should be the default schema, which you do using the flyway.defaultSchema config setting. If you haven’t specified a default schema, Flyway creates its schema history table in the first schema in the list you provide in flyway.schemas. If you haven’t specified these, Flyway uses the default schema for the database connection, if the database system supports this. Schema names are case-sensitive. I’ve explained in detail how Flyway uses the schema history table in Exploring the Flyway Schema History Table.

If Flyway is asked to clean a database of all its contents, then Flyway creates and fills a fresh flyway_schema_history table. If Flyway creates a schema, it will then drop the schema when performing the ‘clean’ action.

Specifying the migration files

Migration files can be in several different ‘locations’, which are just places, such as directories, where you can store files. You might need to do this if some of your migration files are cloud-based, or you may have a branching strategy that requires this. You might need to keep callback scripts or Java/PowerShell scripts separately. Flyway will work out the correct sequence wherever they are stored.

Specifying the locations

Specifying the naming conventions

Migrations have the following file name structure: prefixVERSIONseparatorDESCRIPTIONsuffix. You can, if necessary, change the prefix, separator and suffix for the migration files you use for a project. You may be moving gradually from an older build/migration system and so need compatibility with it.

Controlling Flyway’s behavior

This concerns the way that Flyway logs events, the way that it handles exceptions or resolve migrations. It also deals with compatibility issues such as text encoding and the way that long SQL migrations are handled.

Executing migrations

There are several configuration items that determine the way that migration files are executed. These determine the format of placeholders, the way that Flyway arranges and executes the migration run, and the way that Java-based callbacks are done.

Placeholders

Placeholders greatly extend the usefulness of Flyway. They work like macros, where you embed locations in text, into which you substitute values at runtime. Flyway provides some default placeholders, referenced in your SQL scripts using ${flyway:Placeholder}. These provide information such as the date when Flyway ran a script, the database name, username, and the location of the flyway schema history table.

In the Flyway.conf file, you can also define your own placeholders and configure the value for these placeholders using, for example, flyway.placeholders.superuser=Philip J Factor. You can then reference these user-defined placeholders in a SQL migration script or callback, using ${superuser}.

Similarly, your placeholder values will also be passed to script migrations (i.e. those written in a scripting language such as PowerShell or bash) and script callbacks, but in this case the values just get passed as environment variables and you have to use your own scripting methods to insert these variables into the code.

Migrations

You can specify a specific version to which the database is migrated and ask Flyway to stop at the end of the chain if that version isn’t yet represented by a migration file. You can also run preliminary checks on a migration chain before you execute the chain or cherry-pick the files that get executed.

Callbacks

There are three types of callbacks, the java extensions that hook into the Flyway Lifecycle, the SQL callbacks, and in Flyway Teams the script callbacks

SQL callback files that match the correct name pattern are loaded from the file locations that you specify.

Conclusions

In looking through all the possible parameters for configuration files, it becomes obvious that it is very much a design decision as to where to put configuration information for Flyway. Generally, the ephemeral values, such as the nature of a migration, are best supplied as parameter values rather than being placed in a flyway.conf file. The project-level or branch-level values need to be kept with the project location so that they are read in as part of the work. The installation parameters such as the license key, custom database drivers, or the Java callback extensions should be in the installation folder, and identity information should be in the user area’s configuration file. Credentials and other ‘secrets’ need to be managed carefully, either using Flyway Teams, or by hand-crafted ways of encrypting and restricting access to passwords and accessing them when necessary.

If you can get all this right, then Flyway is a great deal easier to use for the larger database projects.