Phil Factor

18 November 2022

• Flyway
• Callbacks and placeholders
• Database Deployments

Executing Optional Code in Flyway Callbacks

With Flyway, you probably have a standard set of tasks, such as logging or taking a backup, that you generally want to run before or after every migration, but you need the option to switch them on or off. There are other tasks that should only be run just occasionally such as the loading of data. There may also be certain code that should only run for a hosted server rather than a network server.

Similarly, we may need to control when certain migration code executes. For example, if we need to maintain different versions of code for different versions of the same RDBMS, then we’ll want a way to ensure that code doesn’t get executed on a target instance that doesn’t support it

We’ll need this conditional execution of code to be very easy to configure, by which I mean without changing a migration file. We need to control it just using an environment variable, or the value of a config item or a Flyway parameter. Anything that involves changing a migration script isn’t possible. Whichever way we choose, it must work for SQL code or PowerShell/Dos/Bash scripting.

The technique I describe in this article is simplicity itself. We just create a placeholder, to act as an on/off switch for its associated block of code, and then dynamically set its value to a Boolean expression.

A word of warning though: were you silly enough to try, you could use the technique to defeat the discipline of Flyway versioning, by executing different DDL code within a migration file, such as CREATE or ALTER statements, depending on the option. It is worth making certain that this doesn’t happen. Migrations must make the same changes to a database, whichever options are switched in.

Using custom Flyway placeholders and blocks of code

We’ll use custom Flyway custom placeholders and blocks of code to demonstrate this. Most coding allows for the conditional execution of a block. All we need to do is to ‘parachute in’, via a placeholder, an expression that resolves to true or false. Some languages make it easy because they have Boolean constants such as true and false (JSON), or $true and $false (PowerShell). In SQL, we generally must use an expression such as 1=0 or 1=1, though PG-SQL has true and false constants. Then, we just put the part of the code we want to switch on or off within the block associated with the IF expression, using a placeholder.

SQL has:

DOS has:

PowerShell has:

Bash has

Flyway uses placeholders or ‘macros’ for this sort of work. I’ve demonstrated some uses for placeholders in the past. In one case, I showed how they could be used to ‘gloss over’ the differences in SQL Syntax between Relational database systems. In another article, I showed how one can use a placeholder to test a migration without committing it. In this article, I’ll show how use them to switch in, or out, any arbitrary block of code.

Selectively running Flyway callback tasks

We’ll demonstrate this as simply as possible, using a SQL callback script to provide the task “switches”. A scripting language such as PowerShell or Java will be even more versatile. A single switch statement could be used to control several different actions.

In this example, we’re using a beforeMigrate__tasks SQL callback. Save it to your Migrations folder in the Flyway project. It includes a built-in placeholder, which has the flyway: prefix, and three user-defined placeholders that don’t. I’ve left out the actual code for the backup, logging and test code because it would be a bit of a distraction

We must now add a default definition of these placeholders in the flyway.conf file that is in your current working directory. Flyway will throw an error when it tries to run the file if we don’t do this.

The first “=” is accepted as the assignment operator and every subsequent character is assumed to be part of the string being assigned to the placeholder.

We now simply change to the current working directory and run flyway migrate on the target database, and see this:

Executing SQL callback: beforeMigrate - Test
WARNING: DB: Forget about a backup of PubsWithoutData because the maybeBackup placeholder is set to 1=0  (SQL State: S0001 - Error Code: 0)
WARNING: DB: No logging of this migration run of PubsWithoutData because the MaybeLogging placeholder is set to 1=0  (SQL State: S0001 - Error C
ode: 0)
WARNING: DB: We will forget about testing PubsWithoutData because the MaybeTest placeholder is set to 1=0  (SQL State: S0001 - Error Code: 0)

Unfortunately, in Flyway, SQL print statements are assigned a ‘warning’ level for some reason lost in the mists of time.

What if we want to run a backup? Simple. We activate the task at the command line:

flyway migrate -target="1.1.8"  "-placeholders.MaybeBackup=1=1" 
Executing SQL callback: beforeMigrate__Tasks
WARNING: DB: We are doing a Backup of PubsWithoutData because the maybeBackup placeholder is set to 1=1  (SQL State: S0001 - Error Code: 0)
Executing SQL callback: beforeMigrate - SQLBackup
+---------------------------------------------------------------------------------------------+
| SQL Backup v10.1.18.2060                                 
+---------------------------------------------------------------------------------------------+
| Backing up PubsWithoutData (full database) to:                                              |
|   <PathToBackpLocation>\FULL_(local)_PubsWithoutData _20220916_122305_1.1.7.sqb             |            
|                                                                                             |
| Database size       : 144.000 MB                                                            |
| Compressed data size: 1.053 MB                                                              |
| Compression rate    : 99.27%                                                                |
|                                                                                             |
| Processed 1120 pages for database 'PubsWithoutData', file 'PubsWithoutData' on file 1.      |
| Processed 6 pages for database 'PubsWithoutData', file 'PubsWithoutData_log' on file 1.     |
| BACKUP DATABASE successfully processed 1126 pages in 5.999 seconds (1.466 MB/sec).          |
| SQL Backup process ended.                                                                   |
|                                                                                             |
|                                                                                             |
+---------------------------------------------------------------------------------------------+
+--------------+------------------------------------------------------------------------------+
| name         | value                                                                        |
+--------------+------------------------------------------------------------------------------+
| exitcode     | 0                                                                            |
| sqlerrorcode | 0                                                                            |
| filename001  | <PathToBackpLocation>\FULL_(local)_PubsWithoutData_20220916_122305_1.1.7.sqb |
+--------------+------------------------------------------------------------------------------+
 
WARNING: DB: No logging of this migration run of PubsWithoutData because the MaybeLogging placeholder is set to 1=0  (SQL State: S0001 - Error C
ode: 0)
WARNING: DB: We will forget about testing PubsWithoutData because the MaybeTest placeholder is set to 1=0  (SQL State: S0001 - Error Code: 0)

Basically, we’ve controlled the execution of the SQL code of the callback. We could control the execution of the code in a SQL migration in precisely the same way, but this could easily compromise the value of strict versioning, as there would be no record in the history table that the migration did not actually run.

There is better way of optionally executing a migration file provided in Flyway Teams, using script config files, which ensures that the Flyway schema history table will keep a record of what migration was applied.

Conditional execution using the Script configuration file

We can prevent the execution of an entire migration file by using a script configuration file, and the shouldExecute placeholder. It is not a direct substitute for the technique I’ve described above, because it only works for migrations, not callbacks. It also requires Flyway ‘Teams’ edition; it is not provided in Flyway Community. If you’re using Flyway Community, you can use the system I’ve already described, but the disadvantage is that there will be no record in the Flyway schema history table that certain code was ignored.

In this sample Pubs migration that I provide, I show how to use Flyway Teams’ file-level configuration to optionally execute SQL migrations on a per-script basis. To do this, you need a script configuration file in the same folder as the migration file. Additionally, the script configuration file name must match the name of the migration file, but with the .conf suffix used. That’s all you need to do. In my example, there is a migration file called V1.1.9__AddconditionalVersion.sql and an associated config file called V1.1.9__AddconditionalVersion.conf. I’ve explained how this works in Flyway Teams and the Problem of Database Variants.

Besides choosing whether to execute or ignore a migration file, the Flyway docs describe a few other ways that we can use a script configuration file the control the execution of the associated migration file, such as to determine whether to execute the migration in a transaction. RDBMSs like PostgreSQL and SQL Server can’t execute a few DLL statements within a transaction, so you can opt to run a file outside a transaction, as long as you are prepared to pick up the bits and

Conclusion

Placeholders have the potential of giving you a great deal of control over the way that a callback is executed. It also gives you the same control over the way that a migration can be executed. The most obvious reason for wanting that control is to allow you to run optional service routines that don’t affect the resulting version of the database, such as backup, integration test or data-loading. You’ll want these in some development contexts but not in others. You may need it to create different versions of your code for different levels of your database server.

The worry with any technique like this, or repeatables, or even callbacks, is they all can reduce your confidence that a database is exactly at the version held in the Flyway Schema History table. It is this strict ‘versioning’ that allows rapid team-based database deployment, because every team member knows that the version tested, reviewed and signed off, is the release-candidate. With great power comes the risk of a mighty wrath from rest of the team if you get it wrong.

Flyway works best if you work by the rules. It’s not always possible, which is why Flyway provides ways of getting around various difficulties, but to use these circumventions means effectively communicating and discussing their use.