Phil Factor

18 January 2024

• Flyway
• Callbacks and placeholders
• Database Build tasks

Running DOS Scripts as Callbacks with Flyway

Flyway Teams can run DOS batch scripts as well as PowerShell scripts. It can be a strange culture-shock to find oneself suddenly writing shell scripts for DOS again, after many years with PowerShell. It is terse, but strange, and curiously easy, and there are plenty of operations people who are very skilled in the use of DOS scripting to perform any necessary database tasks, but who would recoil in fear when required to deal with PowerShell.

The most interesting challenge is to obtain enough information from Flyway to do worthwhile processes. For example, let’s say we want to capture a backup or create a DACPAC each time Flyway creates a new version of a database. Our process will need to extract from Flyway information such as the type of RDBMS, name of the database and, crucially, the new schema version of the database, which Flyway just produced. It will then capture the backup or DACPAC and name the file appropriately.

I’ll provide an example Flyway callback script that captures the Flyway information needed to run almost any command-line process that you might wish to perform in a callback. In this example, I create a DACPAC for SQL Server, but just to illustrate an approach that works with CLI tools. You could be doing backups, generating build scripts, checking code, doing source control operations or writing out to logs or journals. It’s a minor change in each case, and I’ve already done the hard work in the example.

Getting sufficient information to perform a useful task

Flyway Teams executes these DOS scripts either as callbacks or migrations. Effectively, that means that the Flyway users cannot pass parameters to them directly, but merely make placeholder variables accessible to them. Flyway passes some basic values to callbacks as environment variables:

FLYWAY_EDITION=enterprise
FLYWAY_URL=jdbc:postgresql://MyServer:5432/pubs
FLYWAY_USER=PhilFactor

These default Flyway environment variables are there to allow you to make a simple connection to database, via JDBC. They’ll pass to your scripts the information needed to make a connection, even the more esoteric values required to connect to some cloud-based databases.

In addition, Flyway provides some default placeholders. These are useful for passing static information, such as where you want to put your reports. Placeholder replacement for callbacks works just as it does for SQL migrations. Flyway provides built-in placeholders (FP_Flyway_*) to retrieve the defaultSchema (location of Flyway table), User, Database, Timestamp, Filename (of the current script), workingDirectory, and table (name of Flyway table).

However, neither will help you to write any scripts that require dynamic parameters, such as the current version of the database, or the name of the last script executed successfully. If you need to pass information about ‘state’, such as the database version, to any callback processes, you need to actively get it from Flyway, into the callback. For example, you’ll often need to supply the current version (schemaVersion) of the database you’re busy developing, but this isn’t information you could provide via a placeholder because Flyway might have already changed the version of the database before it runs the callback.

Flyway info can produce a JSON document that provides the version of the database, and it’s possible to write that to a file. Here is a batch file that, when run, puts the result in a file called CurrentInfo.json:

We now want to get that value. A DOS batch file has no built-in way of reading or writing JSON data but fortunately the command-line jq.exe is the usual way to do it. It is a very simple installation process. Once it is installed, you can then fetch any value, as you need it. At its simplest, you can just do this from the DOS prompt…

C>jq ".schemaVersion"  currentinfo.json -r
1.1.16
C>

The only other useful value in the JSON file might be the name of the database, but you can get that more easily from the default placeholder, because it is a static variable. So, here is what you need to do in any subsequent DOS file whenever you need to know the current schemaVersion. You just read it from the JSON file into a standard DOS variable:

You would, of course, need to run Flyway info on every migration run or clean operation that you do, but Flyway Teams allows an afterVersion and afterClean callback that can do this automatically for you. Without Flyway Teams, you wouldn’t be able to run batches anyway, so that’s no real extra restriction.

A Flyway callback to create a DACPAC

Now that we are confident that we can get all the information we want, including the schema version, we can take stock and think about practical uses for DOS callbacks during Flyway migrations. Database backup is an obvious routine chore especially now that we can label the backup with the version number, or perhaps we can now run the creation of a build script or database model. Each of these tasks would require you to label the resulting files with the correct version number.

If you have just Flyway Community, you could run this as a separate batch file with the database SchemaVersion as a parameter. If using Flyway Teams or Enterprise, you can get the version automatically before or after a migration run. In general, I guess you’d usually want to run a task immediately after a migration run.

What would this look like? Let’s take a practical example using SQL Server and write a callback script that creates a DACPAC. This would be called after each migration run. To run this, you would need two batch files. The first, which I’ve called afterVersioned__An_Update_of_CurrentInfo.bat, merely updates the schema version in the CurrentInfo.json file in the current working directory.

Then the second, that I’ve called afterVersioned__Create_a_DACPAC.bat assembles all the information and writes the DACPAC file. It is easy to substitute your own command-line utility to do the task you need. I just chose DacPac because

You’ll probably notice that you need to update the version number before you write the DACPAC out, otherwise the file won’t give the right version number. To do this, make sure that the description of the first, in my case ‘An_Update_of_CurrentInfo‘, sorts higher than the description of the second, ‘Create_a_DACPAC’.

We now install the two callback scripts and run some migrations, and we have DACPACs for every successful migration run.

Summary

Writing DOS scripts for callbacks might seem an odd diversion for a developer who is familiar with PowerShell, but there are a huge number of DOS scripts out there being used that have been developed and refined over the years. I’ve written many myself, and once they’re bedded down and the team that uses them have confidence in them, then they are reliable, easily-maintained, compatible with legacy systems, and easily integrated with existing command-line tools.