Phil Factor

25 October 2021

• Flyway
• Tracking development changes
• Callbacks and placeholders

A Flyway Teams Callback Script for Auditing SQL Migrations

When a new version of a database is successfully created for the first time, it is probably the right moment to kick off some other processes that are outside Flyway’s remit. There are several things that might need to happen, depending on the type of database and its importance.

For example, do you need to generate a build script for the new version? Probably. Do you need to load a full set of data? Maybe. What about checking to see that the version that you’ve built can run automated tests successfully? What about a backup? Should you check the code for possible issues that could cause technical debt? Does anyone in the team need an alert that there is a new version? Should you send a ‘Slack’ message? Do you need to generate reports, logs, documentation, or a summary of what’s changed?

Sure, all this can all be done in Flyway Community if you remember to kick off the process, or if you embed Flyway within a script. I’ve written several articles previously to demonstrate how you can script a process that uses Flyway both to create the new version and then run “script block” tasks to do these other things. It is, however, a bit restricting for the team when it gets more complicated because Flyway was designed to be run and be used interactively.

With Flyway Teams, it is Flyway automatically kicks off the callback script for these other processes, which makes the work a lot simpler. It also means that you can then use Flyway interactively in a console, and let Flyway worry about setting all these processes going at the correct time and in the correct order. These callback scripts reside with the SQL or Java scripts in the Scripts folder that is used by Flyway. The type of callback you are going to populate with a script is denoted by the filename.

Writing and debugging Flyway scripts

As I described in my previous article, Scripting with Flyway Teams and PowerShell, Flyway passes the parameters to the script as environment variables. One of the challenges in writing PowerShell callback scripts is that Flyway Teams passes in certain useful information to the callback, automatically, but not everything you need.

Currently Flyway passes in the name of the user (FLYWAY_USER), the URL used by JDBC to access the database (FLYWAY_URL), and the default placeholders. These don’t include the current schemas, the current version of the database, the last file that it has processed, or whether it is doing a ‘dry run’. You can, of course supply some of this information, such as the current schemas, as custom placeholders or as ordinary parameters, but some bits of information, such as the version of the database at the point a callback is executed, are known only to Flyway. You are also probably going to need passwords, which could never be tolerated in an environment variable.

The second challenge is in debugging. A script that is going to be executed by Flyway may display its output stream on the console, but then again it might not. If Flyway is set to direct its output to JSON, then this output doesn’t go to the screen. I’ve never found out where it goes. For this reason, I always use a log since this could be of long-term use.

It is easy to debug any PowerShell script in the ISE or Visual Studio, but in that case, you can execute just selected portions of code or see the value in variables. When the execution context is different and you can’t do conventional debugging, it makes the process more complex.

In the following PowerShell callback script, for auditing migrations, I’ll also show how to get around all these problems, by sending useful error information to a developer log (usage.log), so that you can debug the code.

Auditing and reporting for Flyway migrations

We’ve been asked, let’s imagine, for a way of reporting and auditing on the migrations that are run on a database. The management might want reports based on the version number, server, database, a description of the version, when it was first created, what migration file was used, who did it, when, how long it took, what Flyway version was used. It sounds as if we need to collect all the relevant information into a database and use this as a basis for reports. I’ll show how to do this using a PowerShell callback and SQLite.

Until one stops to think about it, it is possible to underestimate what’s involved. Flyway can tell us quite a bit about what is going on with individual migrations via the info command, but we want to collect and store information about all the actions that can affect all the databases for a whole project, not just for a particular database. Therefore, we need to collect this information from all the databases at the time the Flyway commands are executed.

The flyway_schema_history table is only relevant for one database, and any one project is likely to involve several of them, which might be ephemeral. You may, for example, use Flyway for making copies of the database. You might move the development database or use branching. You may have several copies of the database already out there that need to be kept up to date; these may be on laptops or servers, each of which could have been created by different people at different times. There will be the test cell. Not only that, but you might even deploy the same database to different RDBMSs. For example, I generally use MySQL or MariaDB for websites because they are so often provided free as part of a LAMP stack, but will develop in an RDBMS such as SQL Server, which has the best Dev tools. Knowing the name of the server isn’t enough unless you have a different server for each database system you use (I don’t!).

We need to keep tabs on who did what and when. If we record it all in an audit database, we can create reports on what’s been done for co-workers or managers. At some point you are at peril of getting distracted by the complexity or bored by the repetitive detail. This is the point at which you really need to start using Flyway Teams, and PowerShell callbacks, to create a better way of doing it.

Getting audit information for a migration

The PowerShell script will create a log entry that tells you when a new version was created, who did it and so on.

Database       : MyDatabase
Server         : MyServer
RDBMS          : sqlserver
Schema names   : dbo, person, sales, shipping, HR
Flyway Version : 7.15.0
category       : Versioned
description    : BugFixViewParameters
executionTime  : 35
filepath       : <myFilepathTo>\Scripts\V1.1.9__BugFixViewParameters.sql
installedBy    : Phil Factor
installedOn    : 2021-04-13 10:47:48.113
installedOnUTC : 2021-04-13T09:47:48.113Z
state          : Success
type           : SQL
undoable       : No
version        : 1.1.9

We can pack this type of record away in a log, database or report very easily, if it is a PowerShell custom object. In our case, we put it in an SQLite database that the script will create in the PubsFlywayTeamsMigration GitHub project folder.

We’ll use Flyway info command to give us a lot of this information. We’ll call Flyway from the callback script, which is, itself, called from Flyway. This works, as long as you don’t do this routine as an ‘EACH’ callback. The rest of the information we need, such as the server name, scheme names, type, Flyway version, who ran the script, execution time and script location, we get from various environment variables, both default and custom.

The great advantage of using Flyway with an info command to determine the current version of a database is that it provides a device-independent way of getting this information that accounts for any changes in the place where the flyway_schema_history is located and named. The disadvantage is that it is slow. One also must be careful to provide all the parameters that it needs such as the schemas, otherwise it can provide incorrect information.

The PowerShell callback

Having created and debugged the routine for collecting all the audit information, we can turn it into what Flyway terms a “script callback”. It will write a log for every new version created by Flyway, as described above. It saves the audit log records to a SQLite database file (FlywayHistory.db), in a table called versions. The idea is that you just add it as a callback to any project where you need a complete audit of migrations. It will work with any RDBMS.

We want to generate an “audit record” for every successful versioned migration run, rather than every version within the run, since it is likely that every migration file will have been, at one time, the end of a successful migration run. We’ll therefore, after careful examination of my “Flyway callback crib sheet“, create it as an afterVersioned callback.

Here is the callback script. You’ll find the file (afterVersioned_Report.ps1) in the Scripts folder of my PubFlywayTeamsMigration project on GitHub. Note that I’m accessing SQLite via the command line. I think that the SQLite ODBC interface is rather more robust as an interface with SQLite, but the command line interface is blazingly fast, if you can tame it. I’ve designed it so that it doesn’t write the same records twice, so you’ll only see one entry for each version. As mentioned earlier, the script also demonstrates a way for developers to get an internal “usage log”, for debugging purposes.

Trying it out

Just place the afterVersioned_Report.ps1 in the Scripts folder of your Flyway project (remove the DontDo prefix if you downloaded the copy from my GitHub project). You’ll need to set the correct paths to Flyway and SQLite.

Then, just execute a Flyway migration on one of your databases, or across a range of different RDBMSs. For example, you can use the UndoBuild.ps1 script from the project folder. All you need to do is fill in the $details of your server, database and project. The script does everything else for you, including populating the custom placeholders that provide “missing” information to the callback.

It is slow to run, but all the slowness is caused by Flyway doing its lengthy initialization.

Each successful versioned migration of a database should generate a new audit record in the versions table of our SQLite database. Here is the result of running a single migration script file, on several copies of the same PolyGlotPubs database, each installed on a different RDBMS.

Once you have collected a whole lot of information in your audit database, you’ll want to use it, import it into a spreadsheet, or into another database. To read the information from the database in PowerShell you may want to use…

…to get JSON output from this SQLite database that can then be read easily into a script or converted into a PowerShell object that can be placed into a spreadsheet via ODBC. Your SQL will vary according to the information you want!

Conclusions

Callback scripts have been around for quite a while in Flyway, but with Flyway Teams it’s now possible to get user-defined placeholders into a callback script, as environment variables. This makes callbacks a more practical as a way of adding value and simplifying the day-to-day use of Flyway.