Phil Factor

9 February 2021

• Flyway
• Callbacks and placeholders
• Database Deployments

Customizing Database Deployments using Flyway Callbacks and Placeholders

Any way of automating a database development process must meet a wide range of requirements and practices. It is no good for a tool to require a development team to change the way it works, but it is fine for a tool to allow it to do so. Flyway gains its versatility from the ease with which the process of building a database can be customized. By incorporating use of SQL callback files, and placeholders, into your database automation scripts, you can add some magic to the process. This article gives a practical illustration of how that can happen.

For a general introduction to Flyway for the developer, see Managing database changes using Flyway: an Overview and for a simple set of demonstrations of how it might work, see Getting Started with Flyway and SQL Server. For all current and forthcoming Flyway articles, see Flyway Content Map.

What are SQL callbacks and placeholders?

SQL callbacks are SQL Files that are outside a specific database migration step but are triggered in Flyway by most events in the process, such as before or after a migrate command runs, or at the end of the entire process, or if an error occurs anywhere within it. You can specify that the SQL file is called before or after almost any type of event (start, error or completion) and for every action (Baseline, Clean, Info, Migrate, Repair, Undo and Validate).

Callbacks are often used before a migration (‘beforeMigrate‘ callbacks) to do checks or initialization work, and after a migration (‘afterMigrate‘ callbacks) to perform tasks such as setting access controls or loading data or recording in the log that the migration has taken place. You can set callbacks for every file or statement in a repeatable, migration or undo file, in response to an event such as an error or successful completion. There are very few events that don’t allow a callback.

Flyway uses Placeholders to allow us to specify values that can be used by any executable file. They perform the same role as ‘macros’ in traditional tools, and the scripting variables in SQLCMD. Before Flyway executes a SQL File, it looks for placeholders and will swap them out for the run-time values before the code is executed.

There are a few built-in placeholders that you can use to pass important information to any callback or script, such as the date, the database name, location of the flyway database and the username. You can also create your own custom placeholders, just by specifying them in a config file, environment variable or command line parameter. This allows you to provide parameters for the callbacks.

The way you use placeholders will vary according to the way your development team works. You can use them to prevent or allow sections of code being executed, or more likely to have a particular variant of code executed. Some databases, for example, have different schemas, table-suffixes, or even different column names, for different variants of a version. You can also use them within scripts, such as callbacks, to allow them to send database messages or alerts to the correct destination.

In this article, I’ll build on the example that we developed for the previous articles in this series, building a sort of super-Pubs database (publications, not public houses, sadly). I’ll show you a couple of examples of using callbacks and placeholders in combination.

Attaching a version stamp directly to a database

To detect the version of a database that has been built by Flyway, you must use Flyway itself, or interrogate the flyway_schema_History table. This is by design because Flyway is designed to work with many different database systems. However, SQL Server has extended properties that can be attached directly to databases. Many DBAs prefer to use these because they contain much more information, can be read more easily, and require fewer access permissions. So. why not have both?

Although the MS_Description key for the database is generally used to tell us about the version, and is what Redgate SQL Doc uses, the format isn’t standardized across tools. To write to the extended properties we generally call a SQL Routine that either creates or updates the property when the version changes. Flyway allows us to provide the routine, just once, as a callback, after a migration run has completed. Flyway will read the start of the filename to work out when we want it to be called. All we need to do is to start the filename with ‘afterMigrate‘ and Flyway will execute the SQL file after it has completed a migration run.

The following ‘afterMigrate‘ callback file will add or update an extended property (Database_info) on the target database recording the final version of the database.

When the file is executed, the script will be able to determine from SQL Server metadata functions the name of the database and the user, so it just needs the name of the version. Unfortunately, there is no placeholder for this. Flyway writes the final version to the flyway_schema_History table before this ‘afterMigrate‘ callback gets executed, so we can pick up the correct version from there. We just need to know th table’s location in the target database, and we can find the user’s default schema using another built-in placeholder (flyway:defaultSchema).

Here are the contents of the file:

You’ll see the placeholders. By default, they start with a dollar sign and curly brace (${) prefix and end with a curly brace (}) suffix, but you can change that by specifying what you want to use instead, in the config file. You can specify the prefix 'flyway.placeholderPrefix=' and the suffix using 'flyway.placeholderSuffix='. SQL Server die-hards will want to stick to the sqlcmd convention of using a ‘$(‘ prefix and ‘)‘ suffix so that their files can be used by both sqlcmd and Flyway, and they can test out Flyway callbacks using SQLCMD, or SSMS in SQLCMD mode.

You reference the default placeholders using ${flyway:placeholder}, whereas custom placeholders just use ${placeholder}. These placeholders correspond with your Config file settings, but with the colon (:) instead of a dot (.).

The above callback file has two custom placeholders, ${projectName} and ${projectDescription} which I can either specify in the command-line, (-placeholders. myplaceholder =”Value”) an environment variable (FLYWAY_PLACEHOLDERS_MYPLACEHOLDER=value), or in one of the Config files (flyway.placeholders.myplaceholder=value).

You can even switch placeholders on and off (default: true) using the 'flyway.placeholderReplacement=' setting. If you set it to false, the placeholder values won’t be swapped in.

Save the SQL Callback file as afterMigrate__Add_Version_EP.sql and place it in in the Scripts subdirectory of the project directory PubsAndFlyway\PubsFlywaySecondMigration\Scripts. I’ve left an example in the GitHub project. Be careful to make it a UTF-8 file and remember too that Flyway uses case-sensitive filenames for compatibility with the Linux environment.

Now we just run a migration using Flyway, which I’ve described how to do in the previous articles. This time, we’ll take Pubs from zero up to version 1.1.3 and then see if the extended property has been set as we expect.

When we execute this, we get the normal burst of messages, and hopefully at the end we get this encouraging news…

And if we use SSMS we see:

Writing to the SQL Server Log

In this example, we’ll use a callback to log a Flyway migration in a SQL Server database so that it can be checked by a DBA or picked up by a monitoring system, such as SQL Monitor. The statement that logs the build must be executed after a deployment. It can’t be put in any particular migration file because it must be executed just once, at the end of the migration process, which could involve several migration files. We also can’t put it in every file because it is quite usual to have a migration that includes more than a hundred files, which would be bad news for the log file. We therefore use a callback.

We add this to the previous SQL callback file (afterMigrate__Add_Version_EP.sql) and run the migration again.

If you then check the current SQL Server error log, you’ll see our deployment message:

Conclusions

Placeholders and callbacks don’t necessarily have to be used together. Callbacks provide a powerful way to prevent you having to duplicate code, and to manage aspects of the build, such as the insertion of data, server and database-level configuration and access control, that aren’t normally considered part of the development schema. Placeholders provide a powerful way of reporting, and of producing variants of a version as when a database must have special procedures for each legislative area.

This is typical of Flyway. It has evolved to meet the diverse needs of a large community of developers, who are using many different tools, sometimes in surprising ways. Flyway aims to fit in with the flavor of database being used, and the development build tools that are being used to release software. It illustrates the real ‘DevOps’ approach of allowing tools to interact and work together by being versatile, accommodating and above all, configurable!