• Flyway
• Callbacks and placeholders
• Deployment checks and safeguards

Passing Parameters and Settings to Flyway Scripts

We can use, whether Maven, Gradle, API, DOS or PowerShell. In Flyway, we can use built-in placeholders, and define custom placeholders, in our migration scripts or callbacks, whether they are written in whether Maven, Gradle, DOS or PowerShell. We then provide the actual values at runtime, by passing in the value using a command-line parameter, an environment variable or by specifying the value in a Flyway.conf file.

This ‘placeholder’ information will supplement the default settings and parameters that Flyway already provides as placeholders to its scripts and callbacks. Unlike conventional parameters, the information we provide via placeholders is made available to all scripts and callbacks called within the same Flyway invocation.

Providing Flyway with information via config variables and placeholders

Flyway allows SQL migrations, Java-based migrations, and, in Teams Edition upwards, also DOS batch scripted migrations and PowerShell scripted migrations. All these migrations are ‘shelled’ from Flyway, meaning that they start with an environment that is the same as the one where Flyway was invoked, but will also include any extra information added by Flyway, as well as any placeholder values that we provide as parameters.

Firstly, Flyway adds some useful environment variables to allow a JDBC connection to the same database. These all have a ‘FLYWAY_‘ prefix and include FLYWAY_PASSWORD, FLYWAY_URL and FLYWAY_USER, and well as a variable for the version of Flyway, FLYWAY_EDITION.

Secondly, it also provides a set of default placeholders that have a ‘FP_flyway_‘ prefix, such as FP__flyway_database, FP__flyway_defaultSchema, FP__flyway_filename, FP__flyway_table, FP__flyway_timestamp, FP__flyway_user, and FP__flyway_workingDirectory.

Finally, it provides any user-defined placeholder variables that you have specified, whether by using configuration files, environment variables, or as parameters. When placeholders are used in SQL (or Java) versioned or repeatable migrations or callbacks, then placeholder replacement is done automatically before the script is executed. However, when placeholders are used in script-based callbacks (e.g. DOS or PowerShell), there is no automatic placeholder replacement; we must read the placeholders only from the associated environment variables, which Flyway provides (such as $env:FP__flyway_table__).

All these variables are ‘volatile’ in that they no longer exist as environment variables after Flyway has finished executing. They can all provide information that is useful for your scripts and callbacks that are then called by Flyway.

If you aren’t relying on placeholder replacement, you can of course provide information by writing your own environment variables, before invoking Flyway, and reading the information from them in a callback or script. Even here, however, it is better to use Flyway’s custom placeholders. The obvious advantage of using placeholders is that they also perform string replacement in SQL or Java files, and if you use parameters then your Flyway environment variables don’t leak out into your current scripting environment. It also means that you can provide default values for placeholder variables in the config files, and custom values as parameters. You can use placeholders without any extra programming logic.

There are, of course, restrictions on the length of environment variables. Textbooks will tell you that each variable must be less than 8191 bytes long and shouldn’t really exceed 4K. You can do more, but you are entering uncharted waters.

Using custom placeholders in callbacks

We’ll start by using a simple afterInfo PowerShell callback to test this out, without risk of doing any damage! We’ll provide this script with a parameter, called 'parameter', as a custom placeholder:

Save this one-line PowerShell callback file in any of the script locations that you provide in the Flyway.conf files. Next, we want to provide a value for this placeholder, and then verify that it gets to the code in this callback script. We can specify a default value for our custom placeholder in any of the Flyway.conf file. In this example, we’ll add the following line to our project-level flyway config file:

Finally, we go to the working folder for the Flyway project and run the 'Flyway info' command:

And after the Flyway info command runs (but before it reports the results), Flyway reads the placeholder value from the conf file, substitutes it into the placeholder in the callback and we see the default message output to the console:

WARNING: beware of too much excitement

However, we don’t want to warn about too much excitement! We want to warn about the dangers of running with scissors instead, so we override the default, as follows:

Flyway runs, provides the version info, and finally we get…

WARNING: beware of running with scissors

The value we defined has overwritten the value in the config file and has been read correctly within the callback. In this way, we can pass any values we want as strings so that that are used in the callback, and we can, and should, provide default values, just in case.

This means that we can arrange the code so that a message is only sent if the placeholder value isn’t the default value. In this example, we would know when a message needs to be sent to the console because the default value has changed from ‘too much excitement’. We will illustrate this in a later example.

If you wanted to use a SQL callback instead, you can do something like this:

Just save the one-line SQL Callback, afterInfo__Warn.sql, in your script location, as before, and then whenever you do the Flyway info command you will get the warning:

+-------------------------------+
|                               |
+-------------------------------+
| beware of too much excitement |
+-------------------------------+

Note that, in this case, you get placeholder replacement rather than having to grab the value from the environment variable.

Why would you ever need this?

The most common requirement I’ve come across in Flyway for passing a parameter to a script-based callback is to specify which dataset you want to load into the database, after a successful migration run. Depending on your subsequent test run, you might want a different dataset, or a different size of data.

However, there are plenty of other occasions when you might need to specify one or more parameters to a migration or to a callback. It could be something as simple as documenting the build, perhaps doing the correct build for either the development, test or production environment. Or perhaps you’re tackling the more-complex problem of supporting database variants, where you need to provide variations of the same database to different regions of the world, with varying cultural, financial or regulatory regimes. To get around this, you can create migration files that will compile different variants of the same version of the database. The obvious way of doing it is to use this ‘placeholder’ technique.

Some RDBMSs that support procedural logic will allow you to run different SQL DDL code depending on a placeholder value. It may not seem pretty, but if your RDBMS supports it, the technique ‘plays nicely’ with Flyway, in that a SQL migration with different code for different variants or versions will be executed within a transaction that can be rolled back if the migration fails.

A simple example of executing different SQL, based on a placeholder.

This code, in a callback such as afterInfo, will execute either the first block or the second depending on whether you specify 1 as the value of the MyOption placeholder:

We specify a default value of 1 in the config file:

+--------------------------------------------------------------------------------+
| We have executed the code that you specified 
by setting the placeholder to 1 |
+--------------------------------------------------------------------------------+

If we then change the setting in the config file to a different value (2) we get this:

+---------------------------------------------------------------------------------
| We have executed something else because you 
set the placeholder to something that wasn't 1  |
+---------------------------------------------------------------------------------

Leaving the config file setting at 2, we can override it by specifying a different value, as a parameter:

+--------------------------------------------------------------------------------+
| We have executed the code that you specified 
by setting the placeholder to 1 |
+--------------------------------------------------------------------------------+

Pass SQL as a placeholder

We can, of course, specify the SQL to execute in a callback from the script that invokes Flyway. We just create a SQL placeholder as a config file item:

Now if we invoke Flyway Info, we get:

+-------------+
|             |
+-------------+
| 01 Dec 2023 |
+-------------+

Execute a SQL expression using an environment variable

We are a bit limited in what we can put in a config file or command line parameter. However, we can also specify a placeholder value as an environment variable. There are limits to the size of an environment variable, up to 4K, but that is certainly enough for a SQL expression!

You could use this ‘loophole’ to subvert the whole purpose of the migration approach if you were so minded, but with great power comes great responsibility. You’d use this technique legitimately to run pre-migration checks, such as using a beforeMigrate callback to run an optional check on to make sure the right components or add-ins were installed in the database.

Here is a simple check to determine the space available for the current SQL Server database, just as an example. First, we create a afterInfo__Space.sql callback with nothing more than this in it:

Here is the PowerShell code to create the environment variable as a placeholder:

And, as if by magic, you can execute the SQL you specify before running Flyway:

| File Name   | Physical Name                                                     
    | Total Size in MB | Available Space In MB | file_id | Filegroup Name | is_percent_growth
 | growth | is_default | is_read_only | is_autogrow_all_files |
| PubsDev     | C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\PubsDev.mdf 
    | 72.00            | 64.00                 | 1       | PRIMARY        | 0     
 | 8192   | 1          | 0            | 0                     |
| PubsDev_log | C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\PubsDev_log.
ldf | 136.00           | 133.34                | 2       |                | 0     
 | 8192   |            |              |                       |

I’ve described in another article, Piping, Filtering and Using Flyway Output in PowerShell, how to get this information back in a sensible format, such as JSON, but this involves parsing the ASCII output. We have simple ways of doing that, of course, using the Do-AFlywayCommand cmdlet in the Flyway Teamwork Framework:

growth                : 8192
is_autogrow_all_files : 0
Physical Name         : C:\Program Files\Microsoft SQL 
                        Server\MSSQL14.SQL2017\MSSQL\DATA\PubsDev.mdf
is_percent_growth     : 0
is_default            : 1
Available Space In MB : 64.00
Filegroup Name        : PRIMARY
Total Size in MB      : 72.00
file_id               : 1
File Name             : PubsDev
is_read_only          : 0
growth                : 8192
is_autogrow_all_files : 
Physical Name         : C:\Program Files\Microsoft SQL 
                        Server\MSSQL14.SQL2017\MSSQL\DATA\PubsDev_log.ldf
is_percent_growth     : 0
is_default            : 
Available Space In MB : 133.34
Filegroup Name        : 
Total Size in MB      : 136.00
file_id               : 2
File Name             : PubsDev_log
is_read_only          :

Problems with running a script file based on a parameter

You might think that passing SQL as a placeholder will solve all problems. Well, no. If there is a built-in way of gaining flexibility in a migration run, then use it. If, for example, you wish to prevent an entire migration step from running, or enable it to run, by means of a parameter, there are already built-in ways of doing it. For example, you can prevent a migration being run by setting the script configuration file’s shouldExecute value to false. You can do this according to a value in a placeholder by the method of ‘injecting environments’. This approach is intended for SQL Files. I’ve written about this in Flyway Teams and the Problem of Database Variants.

You can, of course, run SQL that triggers an error if, for example, your server isn’t at a high enough version. I describe how to do it here in Running Flyway Pre-Migration Checks on the Database.

There are times, though, when you have a legitimate use for arranging for different code to be executed. The most obvious example of this is to provide, for performance reasons, slightly different logic in the views, procedures, and functions depending on the version of the RDBMS installed on the server, or the database variant you’re building. This requires you to put conditional logic into the SQL migration code. The logic is best placed in the SQL itself, and each RDBMS requires a different approach. With some RDBMSs, there isn’t a way of doing this.

The placeholder solution is still possible in the case where the RDBMS can’t support much in the way of procedural logic. You’d need logic to detect, for example, the version of the server, and define the environment variable containing the SQL accordingly. A more conventional alternative to this strategy is to execute SQL within a script such as DOS, bash or PowerShell, rather doing it in a SQL migration. You wouldn’t, of course, choose this route if your RDBMS supports a way of doing this in a SQL migration script because Flyway can usually roll back a failed SQL script, again if your RDBMS supports it. Unfortunately, a database such as SQLite can’t do this because it doesn’t support procedural logic. You cannot, for example, get SQLite to execute a different SQL based on the version of SQLite, so you must script it. Any sort of script file such as PowerShell or DOS, whether callback or migration, can take its parameters from a user-defined environment variable, but there is no placeholder replacement. You need to access the environment variables.

Strangely, the biggest problem for a scripted migration or callback in Windows is that Flyway helpfully passes its JDBC connection string and connection details for you to use, but of course you can’t run JDBC on PowerShell as it isn’t a Java application!

Probably the neatest solution would be to provide an ODBC connection string as a placeholder, with the collection details and credentials too. This can’t be done via a .conf file because credentials and connection info must be kept confidential.

Use callbacks and placeholders to report the result of a migration

We can do something slightly more useful with a callback than merely warning people not to run with scissors. We can use an afterMigrate callback to report the result of a migration to a notification system once it is run. I use Synology Chat, but it is easily modified.

To start, ‘we’ll define a couple of general workstation-based placeholders.

As they are for you, and will apply to all your projects, this needs to be in the flyway.conf file in your user area. The message is the ‘default message’ and means that there is a placeholder, but you have no actual message to send. You must change this placeholder value to something else for the message to be sent.

Then you must create an afterMigrate script callback. I’ll use PowerShell:

So, we do the migration…

…and get the message…

This is just a demonstration of the possibilities. For a practical feature you’d want such information as the version number of the database too. For some odd reason, it isn’t currently provided as a default placeholder.

Summary

There are several ways of controlling the execution of callbacks and migration steps. Basically, it involves either user-defined placeholders or environment variables in some way or another. For Flyway Community, you can do a lot with placeholder-replacement in migration scripts, especially if you have an RDBMS with a good batch-based procedural logic such as Transact SQL. You can also use Java to do this. With Flyway Teams, you can do a great deal in a script such as PowerShell. As I’ve explained in the article, there are restrictions on using your own database connection in a script unless it is done in an ‘after…’ callback.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Teams

Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.

Find out more