• Flyway
• Deployment checks and safeguards
• Callbacks and placeholders

Running Flyway Pre-Migration Checks on the Database

To ensure a reliable outcome of many SQL development tasks, we often need to check that the right ‘pre-conditions’ exist for the task to succeed. Before you run a Flyway clean operation, for example, you’ll definitely want to run a preliminary check that you’re attached to the right database! If your database code relies on features of a particular version of your database engine, then you ought to make sure that the database server that is provided for you is at that version or higher. If your code relies on certain RDBMS extensions being installed then, guess what, you need to check that everything is in place!

With Flyway it’s easy to do a preliminary check that issues a warning, or throws an error, if the necessary conditions aren’t met. With an error, you can stop a migration dead in its tracks, and it is best to do this before the migration runs, by running the preliminary check in, for example, an afterInfo or beforeMigrate callback. If you do the check and stop the process before executing the migration script, or any other type of script such as an undo, baseline or repeatable, then there is nothing to repair in the database. You can just laugh nervously, sort out the problem, and carry on. You’ve saved time and effort.

Creative use of Flyway callbacks for doing database checks

Let’s say you’ve created a new database view that relies on a built-in function that’s only available in the latest RDBMS version. Before running the migration that creates this view, you’ll want to check the RDBMS version and either simply abort the migration with an error, or provide an alternative implementation of the view, supported by lower versions.

If you add the code to perform the check to a suitably named callback script file, Flyway will run it before or after a whole range of ‘events’. The most obvious event is the error event, but you can run callbacks before or after the execution of a statement, or any sort of migration file. Having saved the callback in any of the script locations that you provide in the flyway.conf files, Flyway will call it at the right point in the process and provide it with the necessary information about your project, via environment variables or placeholders.

With Flyway Community, callbacks will be SQL scripts, but with Flyway Teams you can use a wider range of scripts such as Bash, DOS Scripts, or PowerShell. These scripts enable you to do all sorts of chores, such as creating schema-level or object-level build scripts, documenting the changes in a migration run, or importing data.

Placeholders for SQL or Java scripts

Any SQL or Java code that is executed can receive its project information automatically, from placeholders. We define placeholders in our migration scripts or callbacks, and provide the actual values at runtime, using a parameter, environment variable, or Flyway.conf file. Flyway will substitute the placeholder ‘keys’ with the provided values, dynamically, during the execution of the SQL. These could be either the default placeholders or the custom placeholders that you define for a particular project or command.

These Flyway placeholders are defined using the format ${placeholder} within your SQL migration scripts. For example, you can define a placeholder for a required server version using a ${serverversion} placeholder in your SQL script. This would allow you to, for example, check that the database server is set up to the requirements of your database project. You could change the requirements merely by changing the config value.

SQL files have the advantage that they are executed using the connection so this allows us an easy start. They also work with Flyway Community.

Placeholders for script callbacks

Whereas for SQL or Java callbacks the values of the placeholders are substituted for the actual placeholders, in scripting languages such as DOS Batch or PowerShell, you must read them from the environment variables. Some types of scripted callbacks will run in a new connection and can sometimes cause you blocking problems.

Configuration variables

Some of the commonly required configuration parameters are automatically made available to all non-SQL scripts and callbacks through the following environment variables (the values are available for the duration of the OS session in which your callback is executed):

FLYWAY_EDITION	Community, Teams or enterprise
FLYWAY_PASSWORD	YourWellChosenPassword
FLYWAY_URL	jdbc:postgresql://MyPGServer:MyPort/pubs
FLYWAY_USER	PGuser

Default placeholders

The following configuration values are always provided by Flyway in the following default placeholders. Flyway will read the values from the environment variable (e.g., $env:FP__flyway_table__) and supply it to the corresponding default placeholder, used in script callback:

FP__flyway_database	pubs
FP__flyway_filename	afterInfo__check.ps1
FP__flyway_table	flyway_schema_history
FP__flyway_timestamp	2023-06-09 12:26:26
FP__flyway_user	postgresUser
FP__flyway_workingDirectory	<path To>\PubsPG\Branches\Develop

Custom placeholders

Flyway will provide any user-defined placeholder variables that you have specified, whether in configuration files or as parameters. In my case, I’ll generally use the following custom placeholders:

FP__projectDescription	A very simple example with pubs sample database
FP__projectName	PubsPg
FP__Variant	default
FP__Profile	PGPubsConn
FP__serverversion	14

Running the pre-migration checks

Let’s take a look at a couple of examples. Your database application may rely on specific features or functionality provided by the database server. For example, it might rely on features that are available only in the most recent versions of the RDBMS. Therefore, we need to pass back the server version so that we can check that the required features are available and supported. If the server version is below the minimum requirement, we can provide appropriate feedback or take alternative actions.

Similarly, your application might rely on require a particular extension to be installed, so we’d need to pass back some indication of whether it’s actually available on the target server, so we can run the check.

I’ll show how to trigger the required check so that it runs automatically after the Flyway info command executes, using an afterInfo callback. We could, alternatively, run the checks in a beforeMigrate callback.

Check the Postgres server version

If we want to see the server version in PostgreSQL, we can simply call the version() function. If we return SQL values, they are generally returned by Flyway in decorative ASCII tables that aren’t particularly easy for a script to read. A JSON value can be parsed more easily in scripts, so we can run this SQL:

Now, if we place this code in an afterInfo__ServerVersionCheck SQL callback…

Flyway info

…we will get the output via STDOUT:

Executing SQL callback: afterInfo - ServerVersioncheck
+------------------------------------------------------------------------------------+
| version                                                                            |
+------------------------------------------------------------------------------------+
| [{"DatabaseVersion":"PostgreSQL 14.4, compiled by Visual C++ build 1914, 64-bit"}] |
+------------------------------------------------------------------------------------+

I’ve described in another article Piping, Filtering and Using Flyway Output in PowerShell how to read this sort of information into a value within a script. If you opt to put the information that you usually get from Flyway info into a JSON document, using Flyway info -outputType=json, then sadly, you will get nothing returned by this query since all the STDOUT is suppressed by the JSON output option.

Raise an error if the server is not at required version

We probably wouldn’t want to merely get a message. It would be better to raise an error. Here is a PostgreSQL callback (simply place all the following code into the afterInfo callback file):

This time, if you execute Flyway info, you will get:

Flyway : ERROR: Error while executing afterInfo callback: Migration afterInfo__ServerVersioncheck.sql failed
At line:1 char:1
+ Flyway info
+ ~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (ERROR: Error wh...heck.sql failed:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
--------------------------------------------------
SQL State  : P0001
Error Code : 0
Message    : ERROR: Sorry, but PostgreSQL version 15.0 or higher is required.
  Where: PL/pgSQL function check_postgresql_version() line 12 at RAISE
Location   : .\Migrations\afterInfo__ServerVersioncheck.sql 
(S:\work\Github\FlywayTeamwork\PubsPG\Branches\Develop\.\Migrations\afterInfo__ServerVersioncheck.sql)
Line       : 17
Statement  : Select check_postgresql_version()
Caused by: Migration afterInfo__ServerVersioncheck.sql failed
--------------------------------------------------

Use a placeholder to specify the required version

We can do even better. The problem with our simple code is that it is hardwired to ’15’. If we add a placeholder definition, then we can use any version number in the placeholder so that we can change our criterion. With any placeholder, you have the advantage of being able to provide default values. You could give it a default value in a project configuration file and overwrite it with a different specific value as a parameter, which would be used instead.

So, if we have a server at version at version 14 (ours is at 14.004), we can test it out. If we run…

flyway '-placeholders.serverversion=13' info

…we get the version number…

+--------------------------+
| check_postgresql_version |
+--------------------------+
| 140004                   |
+--------------------------+

…along with all the usual info. However, if we specify that the PostgreSQL server has to be at version 15, we would get an error:

SQL State  : P0001
Error Code : 0
Message    : ERROR: PostgreSQL version 150000 or higher is required.
  Where: PL/pgSQL function check_postgresql_version(integer) line 24 at RAISE
Location   : .\Migrations\afterInfo__ServerVersioncheck.sql 
(S:\work\Github\FlywayTeamwork\PubsPG\Branches\Develop\.\Migrations\afterInfo__ServerVersioncheck.sql)
Line       : 32
Statement  : Select dbo.check_postgresql_version(150000)
Caused by: Migration afterInfo__ServerVersioncheck.sql failed

So, we’ve shown that you can easily use this sort of callback to stop a migration, if the server settings (version in this case) aren’t sufficient.

Checking for extensions, optional features and modules

We can use the same method to check for a variety of server capabilities. Each project is likely to have its own special conditions and perhaps require access to optional features or extensions. If your database code uses one of these ‘extras’ that aren’t in the standard version, you’d need to check if it is installed.

In SQL Server, you can check if optional features like FileStream and Reporting Services are installed by querying the corresponding system views and tables. For example, is a database is FileStream-enabled then in the sys.database_files system view it will have a filegroup with the type column set to 2 (FGType = 2):

Similarly, running the following query will display the corresponding error based on whether the ReportServer database exists (there are other ways of checking):

In PostgreSQL, there are a wide range of community extensions to the database, such as PostGIS, pgAdmin, pgBouncer, Citus, TimescaleDB, and pgRouting. The pg_extension system catalog table will tell you which versions of what extensions have been installed on a PostgreSQL database

This will tell you what extension(s) are installed. If the query returns no rows, it means the extensions are not found on the server. You can expand on this to check whether a list of these that you specify are all installed.

If you wish to trigger an error if any of these aren’t in the list, then you just need to check the result to see if the result includes the word ‘absent’ in the status column.

If you only have one PostgreSQL project, then you can use this directly in the callback, merely by changing my list of examples for the list of extensions that are required.

If you have several PostgreSQL databases or projects, each of which needs its own list, then You now just need to specify the list as a Flyway Placeholder, and you now have a callback that will throw an error and prevent the migration if not all the extensions are there.

Each RDBMS has its own optional extensions, modules or services. It can save some head scratching if you run a check on what your project requires before you do a migration. Flyway callbacks such as afterInfo or beforeMigrate are good places to run the check.

You can, of course run a script as a beforeInfo or afterInfo, to do the installation work for the database server, rather than just trigger an error, but you’d need Flyway Teams to do that, and it is unlikely to be a frequent task that would warrant the time spent on automating it.

Conclusions

When you are trying to script as much as possible of your development, Flyway is best suited to take on the role of coordinator. We can use callbacks in Flyway to automate routine tests and checks that were previously just too arduous to do manually.

Nobody I know of actually does a manual check of the capabilities of the server before doing a build, for example. Once an automated check in in place, however, it just makes database development a bit less like a high-wire act. I’ve illustrated some very simple checks just by means of illustration but there is a lot of useful potential in using Flyway callbacks along with placeholders once you’ve gained familiarity with them.

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