• Flyway
• Callbacks and placeholders
• Database Testing and Quality

Debugging Flyway Callbacks and Migrations that Use Placeholders

Using Placeholders in SQL scripts

Placeholders, also known as macros or variables, are very useful in database development work for providing values that aren’t known until the SQL is executed. They are supported by several command line interface tools routinely by developers to execute SQL.

In SQL Server and Sybase for example, the iSQL, oSQL and SQLCMD utilities provide macro substitution (a.k.a., SQL Interpolation). A SQLCMD placeholder has a prefix of ‘$(‘ and is terminated by ‘)‘ i.e., $(MyPlaceholder). Similarly, PSQL for PostgreSQL has the -v (variable) assignment and \set statement that does the same thing as the SQLCMD placeholder. The placeholder in this case is has a prefix of a colon and double quotes and is terminated by double quotes, i.e., :”MyPlaceholder”.

Some other CLI query tools, for other database systems, have similar features. However, as far as I know, no tool besides Flyway allows macro substitution while executing SQL across a whole range of databases. I’ve already demonstrated their usefulness in One Flyway Migration Script for Diverse Database Systems, where I show how you can write a single build script that works across a whole range of database systems (SQL Server, PostgreSQL, SQLite and MySQL).

If you are accustomed to using such variables in your SQL scripts, and so already have a whole lot of files that you already use with your CLI query tool, such as SQLCMD,  you’ll want any easy way to convert them for use by Flyway, in SQL migration scripts or SQL Callback scripts. Alternatively, you might simply need to create Flyway migration files with placeholders, from scratch. However, unless a technique such as sending useful error information to a developer log, when you’re developing and testing these scripts in Flyway, you’ll find them hard to debug when things goes wrong. It will be much easier to test them first in PSQL, or in SSMS, which has a SQLCMD mode (reached by using a combination of keys ALT+Q+M) that allows you to do test runs of SQL scripts with variables in them. The complication is that Flyway has a different way of delimiting a variable (placeholder) that needs to be swapped out for the value you provide. Flyway uses ${MyPlaceholder}, and SQLCMD uses $(MyPlaceholder), so you can’t create and test the file in SSMS before using it in a migration.

You have two options to get round this problem. You can either change Flyway’s default delimiters or you can change your code.

Changing Flyway’s default delimiters

If you are working consistently in one database, then the best option might be to change the Flyway delimiters to match the placeholder format used by your CLI tool. You can make this change by using the following configuration items:

flyway.placeholderPrefix	Prefix of every placeholder. (default: ${ )
flyway.placeholderSuffix	Suffix of every placeholder. (default: } )

By changing placeholder prefixes and suffixes, in a config file, command line parameter or environment variable, Flyway should be able to run any of our SQLCMD files that just have variables but no commands (there won’t ever be compatibility for any SQLCMD commands, which can also be embedded into SQL files that are executed by SQLCMD). This would mean that we can use SSMS in SQLCMD Mode to develop and debug any Flyway SQL migration or callback file that has placeholders.

However, there is one more catch. The current version of Flyway uses a non-configurable separator value, a colon (:), to pass built-in placeholder values in a script. Unfortunately, SQLCMD doesn’t allow that character. Admittedly, it is very quick to change that to something compatible like an underscore (_) character, so one can live with the problem until Flyway makes it configurable. However, if you’ve been forced to replace text to change between the colon and the underscore then you might as well consider the alternative approach of transforming files between the two types of placeholders by doing regex replacements.

Changing your code

The other technique that we can use to change every variable in your SQLCMD files into a Flyway variable. We can do this using a couple of regular expressions, either in SSMS search and replace, PowerShell or Grep. This makes it easy to change files from one dialect of placeholder/variable to another. We’ll look at both approaches.

A SQL callback to write version details into the database

As an example, we’ll use a SQL script that puts the essential details of each database version into an extended property of a SQL Server database. We want to be able to develop, test and debug the script in SQLCMD, and then use it in Flyway as a SQL callback, so that it stamps the new version details and project-specific information, into the database after every successful versioned migration. I also make a log entry so that it is clear how and when a migration happened, particularly on a production system. This can be subsequently read by SQL Monitor.

The details in the extended property are a great supplement to the information in the flyway_schema_history table, which you won’t always have permission to access directly.

The callback script with placeholders

Here is the code that writes the database information for each version into an extended property. It’s designed to be tested and debugged in SSMS, operating in SQLCMD mode. You’ll notice the use of various placeholders, using the $(MyPlaceholder) syntax required by SQLCMD, and using underscores in the Flyway default placeholders, flyway_defaultSchema instead of flyway:defaultSchema.

This means that, before I can us this with Flyway, we’ll either need to replace all prefixes, suffixes and occurrences of flyway_, with the defaults used by Flyway, or change the Flyway defaults for the prefixes and suffixes and then do a search and replace of flyway_ for flyway:. We’ll also need to delete, or comment out, that initial variable-assignment block. We’ve chosen to comment them out because we can then do the reverse if necessary.

Once we have our routine, we can debug it at our leisure. Although it looks slick now, it took me a while to get it to work under all circumstances without needing to compare or sort version strings.

If, instead of debugging your SQL migration files in SSMS, using SQLCMD mode, you might need to create the code with placeholders directly in Flyway, but you will still want to know what effect the placeholders had. To do this, you either use dryRunOutput in Flyway Teams to see what substitutions are being done and maybe debug that in SSMS, or else make a guess from the text of the error about what went wrong. This helps a lot but still one pines to be able to just be able to debug and execute it in an IDE such as SSMS. It pays to persevere because placeholders are powerful magic. Sometimes, when you are creating several variants of a database for one version, you can’t easily avoid the use of placeholders, so it is worth doing it properly and not relying on good fortune.

Testing it out

I’m using a flyway-managed copy of the venerable pubs database, called PubsThree, which is at V1.1.2, as you can see from the output of a Flyway info command:

Schema version: 1.1.2
+-----------+---------+--------------------------------------+------+---------------------+---------+----------+
| Category  | Version | Description                          | Type | Installed On        | State   | Undoable |
+-----------+---------+--------------------------------------+------+---------------------+---------+----------+
| Versioned | 1.1.1   | Original Altered For SQL Server 2017 | SQL  | 2021-12-13 16:11:08 | Success | Yes      |
| Versioned | 1.1.2   | Added Tags Etc                       | SQL  | 2021-12-13 16:11:11 | Success | Yes      |
| Versioned | 1.1.3   | UseNVarcharetc                       | SQL  |                     | Pending | Yes      |
| Versioned | 1.1.4   | RenameConstraintsAdd tables          | SQL  |                     | Pending | Yes      |

Simply open a query tab in SSMS, connected to your Flyway-managed database, switch on SQLCMD mode (using the ALT+Q+M keyboard shortcut, and by selecting the SQL CMD mode option from the Query menu), paste in the script and execute it.

The output message should be:

Recording the database's version number - 1.1.2
Completion time: 2021-12-22T14:48:36.4208391+00:0

If you check the properties of the database, you’ll find the new DatabaseInfo extended property:

The strategy of changing the Flyway placeholder suffix and prefix

If you decide to opt for the strategy of redefining the default prefix and suffix used for a Flyway placeholder, you will probably want to do this at project level, in a config file stored in the current working area/current directory.

# Prefix of every placeholder. (default: ${ )
# flyway.placeholderPrefix=
# Suffix of every placeholder. (default: } )
# flyway.placeholderSuffix=
These should be changed to …
# Prefix of every placeholder. (default: ${ )
flyway.placeholderPrefix=$(
# Suffix of every placeholder. (default: } )
flyway.placeholderSuffix=)

Once everything is working, we can remove the initial block (grey background in the listing with all the :SETVAR commands), and run a global replace of flyway_ for flyway:, like this:

It is now ready for use by Flyway.

The strategy of changing the code

The alternative strategy is just to use a couple of regexes, in SSMS or any NET application, to make all the required changes.

To go from SQLCMD to Flyway

SSMS’s Search and replace uses NET regexes if you switch to use regex mode (the highlighted icon in the image below is the toggle). In SSMS, we replace the SQLCMD prefixes and suffixes with Flyway ones (the same regex expressions can be used in PowerShell etc.):

We assume that there are likely to be other prefixes in future. We therefore use a regex to find each placeholder and create named backreferences called ‘prefix’, ‘separator’, and ‘placeholder’. We can then replace them back into the new format. We need to do it in two regexes because NET regexes don’t have replacement text conditionals.

Find…

\$\(((?<prefix>\w{2,80})(?<separator>_|:)|)(?<placeholder>\w{5,80})\)

Replace with…

${${prefix}${separator}${placeholder}}

Then we replace the underscore connectors in every placeholder with colons:

Find…

\$(\(|\{)(?<prefix>\w{2,80})_(?<placeholder>\w{5,80})(\)|\})

Replace with…

${${prefix}:${placeholder}}

You can remove the :SETVAR statements simply, as follows:

Find…

^:SETVAR .+

Replace with…nothing

If you prefer to preserve these :SETVAR lines, then do this…

Find …

^\s*(?<command>:SETVAR .*)

Replace with …

-- ${command}

To go back from Flyway to SQLCMD

Find the commented out :SETVAR commands..

^[ -]*(?<command>:SETVAR .*)

Replace (remove the comments):

${command}

…then…

Find ...

\$(\(|\{)(?<prefix>\w{2,80}):(?<placeholder>\w{5,80})(\)|\})

Replace with

${${prefix}_${placeholder}}

…then…

Find…

\$\{((?<prefix>\w{2,80})(?<separator>_|:)|)(?<placeholder>\w{5,80})\}

Replace with…

$(${prefix}${separator}${placeholder})

Conclusions

Whenever I must deal with anything unfamiliar, I always have that same feeling of dread, as a voice inside my head says, ‘how are we going to test this?‘. If I were younger, I’d answer with a cheery ‘who cares, for what could go wrong (WCGW)‘. I know from experience that a lot can go wrong, and generally does, at the most embarrassing moment, unless of course one is able to test and debug it easily.

With SQL Code in Flyway that uses placeholders, the techniques I’ve illustrated here should give any database developer with more familiarity with SSMS a bit more elbowroom for debugging, before committing the migration or callback to a Flyway migration.

Tools in this post

Redgate Flyway

DevOps for the Database

Find out more