Phil Factor

30 July 2024

• Flyway
• Configuration, Authentication

Using TOML Configuration Files with Flyway

Flyway configuration

The Flyway configuration system aims to provide a structured, readable, and flexible way to manage your database migration settings. It supports all aspects of Flyway configuration, including:

• Connection and authentication details – what database to connect to, how it should connect and with what credentials.
• Migration file locations – where the various migrations files and callback files are stored
• Schema settings – which schemas Flyway should manage, how, and which is the default
• Flyway behavior – e.g., how Flyway logs events, handles exceptions, resolves migrations.
• Executing migrations – validation and clean options, placeholder specification and so on

Flyway allows you to maintain several configuration files that are read in sequence from workstation level, then user level, through to project level. If the same key has different values in the different files, Flyway will overwrite the prior value, meaning that the later config file in the chain takes priority.

These configuration settings can then be over-ridden, or added to, by environment variables, parameters or even by the configuration settings within lists of extra configuration files. This gives it a great deal of flexibility.

Why transition Flyway configuration to TOML files?

Until recently, the CONF configuration format has been sufficient to specify Flyway configuration settings. However, as Flyway expands some of the new features require configuration data that is difficult to represent in this file format.

TOML is one of the most popular initiatives to update the longstanding INI and CONF formats of configuration files to provide a standard format that everyone can then use, but which is a better fit for contemporary use. In Flyway versions 10 and later, TOML is now the default format for Flyway configuration.

Readability and structure in TOML versus CONF

TOML files aim to provide a clear structure that is easy to read, write and understand, which is a major requirement for configuration. A CONF file can be quickly converted to TOML format because the traditional CONF format shares many similarities with TOML. However, there are important differences in how each format structures and interprets data.

CONF files

A traditional flyway.conf configuration file might have entries like this, using the dotted notation to define various configuration items, including user placeholders:

flyway.mixed = true
flyway.outOfOrder = true
flyway.locations = filesystem:migrations, filesystem:callbacks
flyway.validateMigrationNaming = true
flyway.defaultSchema = dbo
flyway.oracle.walletLocation=/User/db/my_wallet
flyway.placeholders placeholderA = A
flyway.placeholders placeholderB = B

Most configuration items in this traditional format have just one dot, meaning that the configuration item is directly associated with flyway. There are exceptions, though. Custom placeholders, for example, have their own subcategory. There are also dedicated subcategories for functionality that’s specific to databases, such as Oracle.

In CONF files (as with INI files), we don’t need to identify the exact data types of each configuration item. The parsing of comma-delimited lists, such as flyway.locations, is done by the application, as is the dot notation of the keys. It determines, for example, whether a value with commas is meant to be a single string or a list of items.

TOML files

Whereas in CONF, we rely on the application parsing the file to know how to correctly interpret the data types or recognize a continuation character and so on, in TOML, it’s all part of the standard, and natively supported. TOML behaves a bit more like a language in that it recognizes and enforces the data types.

The advantage is that this allows nested structures to be explicitly defined and grouped together under tables, meaning that the same configuration information can be represented in TOML as follows:

[flyway]
mixed = true
outOfOrder = true
locations = ["filesystem:migrations","filesystem:callbacks"]
validateMigrationNaming = true
defaultSchema = "dbo"
oracle.walletLocation = "/User/db/my_wallet"

[flyway.placeholders]
placeholderA = "A"
placeholderB = "B"

Notice that instead of the dotted notation, the information is organized in tables (a.k.a. “sections”, in DOS and in INI files), each defined by a header in square brackets and each containing a collection of key/value pairs.

We have a [flyway] table containing settings directly related to Flyway’s operation and configuration. The dotted notation is still recognized in TOML so the Oracle wallet location can simply be listed as an oracle subcategory within the flyway table. Similarly, TOML would also allow you to define the placeholders like this, i.e.:

[flyway]
placeholders.placeholderA = "A"
placeholders placeholderB = B

However, for the placeholders, it’s generally clearer and simpler to define them in a separate nested table called placeholders, still within the flyway namespace:

[flyway.placeholders]
placeholderA = "A"
placeholderB = "B"

This nested table contains all the key-value pairs for a set of user placeholders, which are used to substitute values in the migration scripts or configurations.

Advantages of TOML

TOML supports all the current aspects of Flyway CLI configuration, as described above, and provides a simpler, more structured and readable way to manage and organize these settings, especially in complex projects. The TOML standard allows lists, inline tables and nested tables and arrays that mean it can accommodate more advanced configurations and represent more complex or hierarchical configuration data. It also supports more versatile string handling.

Flyway doesn’t yet use all the features of TOML, but it already uses it to support features such as environments and provisioners, used to select connection details for different database environments, and to provide data for code analysis.

Tables, inline tables and arrays

TOML’s ability to structure data within tables and inline tables, and within nested tables and arrays, can help simplify the organization of complex configurations. As we’ve seen, these tables follow the INI file convention of having their ‘section’ title in square brackets followed by a list of the key/value pairs.

The JSON equivalent would look something like this:

{
    "flyway":  {
                   "mixed":  "true",
                   "defaultSchema":  "dbo",
                   "placeholders":  {
                                        "placeholderB":  "B",
                                        "placeholderA":  "A"
                                    },
                   "locations":  [
                                     "filesystem:migrations"
                                 ],
                   "validateMigrationNaming":  "true",
                   "outOfOrder":  "true"
               }
}

For short tables, TOML also lets you “cheat” and instead use a single-line inline table. This will list several key/value pairs, inside curly braces with a comma between elements, like this:

flyway.placeholders = { placeholderA = "True", placeholderB = "False" }

Here, flyway.placeholders is a map key, an identifier used to access a specific value which is an inline table, comprising the set of key-value pairs. There is no need for semicolons or any other character to end a line, but there should be spaces around every key pair and the equals (=) sign. The values within these inline tables can also be inline tables or arrays.

Arrays are defined by the map key followed by values in a comma-delimited list within square brackets.

locations = ["filesystem:migrations","filesystem:callbacks"]

Arrays of tables can be expressed by using the double angle-bracket delimiters for the key value of the array. Subsequent sets key/values pairs are stored as an object held as an array element. Arrays can have both arrays and tables as values.

String handling

TOML has support for delimited strings that are defined by the key followed by the value in double quotes. Literal multiline strings (unescaped) are delimited by triple quotes, or we can use three double-quotes (escaped characters allowed, like \n for a newline):

MyPlaceholder  = '''
SELECT schema_name 
FROM INFORMATION_SCHEMA.SCHEMATA 
WHERE SCHEMA_OWNER = 'dbo'
    FOR JSON PATH '''

TOML, like the CONF format, also supports string folding, with backslashes, to allow long lines to be visible within a narrow terminal window:

str3 = """\
       The quick brown \
       fox jumps over \
       the lazy dog.\
       """

When the last non-whitespace character on a line is an unescaped backslash, it will be trimmed along with all other whitespace, even newlines, up to the next non-whitespace character or the closing delimiter.

Environments and provisioners

In Flyway, when configured using TOML, we can use tables to define ‘environments’ that allow you to store sets of alternative configuration settings for different database environments (e.g. development, testing, and production) and then switch between them easily using the flyway.environment setting. We can define multiple environments within a single file, of which only the default one, or the one we specify as a parameter, is used.

Before this environments feature was added, we switched between databases by specifying a -configfiles parameter at the command-line that provided an extra config file that had the connection details, credentials, the custom placeholders and so on. See, for example, Pipelining Configuration Information Securely to Flyway. In TOML we specify each environment as a table (or section) in the environments namespace, like this:

[environments.philsPlayground]
url = "jdbc:oracle:thin:@//Philf:1521/dev"
user = "developmentUsername"
password = "developmentPassword"
schemas: ["FW-PROJECT"]
displayName = "Development database"

[environments.development]
url = "jdbc:oracle:thin:@//Dev01:1521/dev"
user = "developmentUsername"
password = "developmentPassword"
schemas: ["FW-PROJECT"]
displayName = "Development database"
 
[environments.test]
url = "jdbc:oracle:thin:@//Test01:1521/test"
user = "shadowUsername"
password = "shadowPassword"
schemas: ["FW-PROJECT_SHADOW"]
displayName = "Shadow database"
provisioner = "clean"

[environments.Deployment]
url = "jdbc:oracle:thin:@//Hoster:1521/DMZ"
user = "shadowUsername"
password = "shadowPassword"
schemas: ["FW-PROJECT_SHADOW"]
displayName = "Shadow database"
provisioner = "clean"

The sections for environments currently allow only a restricted subset of Flyway configuration variables that mainly govern connections to database, such as Connect Retries, Connect Retries Interval, Driver, Init SQL, JDBC Properties, Password, Provisioner, Schemas, URL and User. As I write this, for example, it isn’t possible to have custom placeholders that are specific to an environment. However, I suspect that this feature will develop quickly to make it more broadly useful.

The most obvious use for environments is to allow developers to switch easily between test variants of the database, for example, or between different Flyway versions of a database. Once you have a series of these environments set up, you switch between them simply by a command line parameter:

flyway info -environment=Test

In the Enterprise edition of Flyway, the environments feature allows for Provisioners. These allow Flyway to provision a database which does not exist yet before connecting to it, or potentially to re-provision an existing database, restoring the database to a known state. They are also designed to set up specialized data sources such as containers, clones or cloud services. This will be particularly useful where Flyway is being used at the command line rather than via scripting.

The credentials and any other parameter specific to an environment that needs to be secure can be kept in a separate TOML file and read in from an encrypted TOML file using the -configfiles= trick.

gpg -d -q flyway.toml.gpg | flyway info -configFiles=-

Switching existing Flyway projects from CONF to TOML files

When Flyway starts, it will check to see if you’ve specified TOML configuration files in a -configfiles parameter or environment variable. If so, it will just look for TOML files in the standard directories. Otherwise, it will search for both types of configuration files in the install directory, followed by the user home directory, then the execution directory and finally the working directory.

If Flyway finds any TOML file in this search, it will use TOML and ignore all CONF files. If it finds none, it will assume that you are using the CONF format. Having determined what type of file to read, it then reads just the files of that format.

Flyway cannot use both Legacy CONF format and TOML format together so that, if you mix the two formats (CONF and TOML) in the list of files within the -configFiles configuration parameter, Flyway will generate an error.

Conclusions

TOML is rather over-engineered for simple configuration data, in my view, but by its broad range of use, it has become so widely-used that it is likely to be familiar to many users. It provides a good standard way of storing configuration data, but it also tries to bridge the gap between the specification of configuration data and the ability to represent any data, as a sort of competitor for YAML or XML.

This is a laudable aim, but it loses the important simplicity of the old INI or CONF standards. This makes work more difficult for configuration managers, but it also affects scripters or third-party apps that need to determine some values from the way that Flyway is configured. Whereas it is easy to read a configuration file in a script, it needs specialized drivers to do the same with a TOML file, and reference an environment variable ${flyway:environment} to establish the current database environment selected.

All that said, TOML is going to allow Flyway a much more versatile way to specify and then switch between database environments, such as when developing database variants, because it will allow more complex configurations. The extra features of TOML are obviously very welcome and will allow much better support for the more complex tools and services. By providing a popular de-facto standard, other organizations and groups of developers are encouraged to implement ways of reading and writing to the format.