• Flyway
• Callbacks and placeholders
• Database Build tasks

How to Write and Debug a PowerShell Callback for Flyway Migrations

Flyway supports the use of PowerShell callback scripts. They are little more than an ordinary PowerShell script that is executed by Flyway but there are a few differences which I’ll explain. I’ll suggest that you use whatever IDE you prefer for creating PowerShell scripts and do as much development and testing as possible, before creating the callback. I’ll also be emphasizing that the style of programming should be as defensive as if you were writing embedded code.

I’ve covered Flyway SQL Callbacks and DOS callback scripts elsewhere.

Setting up the Flyway environment

When you’re writing a callback, you will want to work as closely as possible to normal. With PowerShell, this will probably mean working interactively within an IDE, checking all the values, and testing every part of the script, as comprehensively as possible, as you go.

The first thing you are likely to need is a test database migration project. It’s best to go for an established project that is geared for the relational database system (RDBMS) that you’re using. In SQL Server, a database like AdventureWorks is ideal, when adapted as a Flyway migration project, because it has deliberately inserted a few “gotchas” that will trip you up, such as a function in a CHECK constraint, a CLR datatype, a user-defined type, or some wacky XML. For general use, I like the old Pubs database because you need a more tolerant and conventional database in the early stages. My Pubs project for Flyway is on GitHub for various flavors of RDBMS, and you’re welcome to use it.

Once you have the project set up and working to your satisfaction, then you can start adding callbacks. The most common ones are those that run useful tasks after a migration run completes successfully. You need to run tests, maybe add your DCL code and bring the test data up to the right version. Possibly you’ll want to do backups.

Flyway passes values to callbacks when it executes them, via environment variables. Here are the default environment variables that are passed to your callback:

You can pass parameters to callbacks from the Flyway command line, but in a callback, they will appear as user placeholders. Flyway uses placeholders to provide various useful bits of static information to your SQL or PowerShell callback. For SQL callbacks, you just use the placeholders to indicate where in your code you want these values substituted and placeholder replacement is automatic, just like in SQLCMD. In PowerShell, DOS or Bash, there is no substitution: You must read the values from the associated environment variables:

The list will vary depending on the nature of the connection and the version of Flyway. They save quite a bit of work, but there are some essential values missing. The most obvious one is the schema-version number of the database. There are two ways I know of to get around that: one is to call Flyway within the script to get a JSON file that provides that information, and the other is to query the actual Flyway Schema History table. To do this, the necessary name and whereabouts (schema) of this table are provided in FP__flyway_defaultSchema__ and FP__flyway_table__. The values you are given are sufficient to make a JDBC connection. Unfortunately, you cannot make a JDBC connection in PowerShell. Elsewhere I show you how to get around that problem, by determining the server and, if you’re working with several RDBMSs, the RDBMS. You can then use ODBC.

You can even use custom user placeholders along with script config variables to perform conditional execution of the SQL in a callback, such as to optionally run a backup task, or even to switch in or our an entire migration script. See Executing Optional Code in Flyway Callbacks.

How to test your callback code in an IDE

I hope that I’ve said enough for you to appreciate that it would be a good idea to begin your PowerShell development project by imitating the callback environment. You really should avoid running your code in a callback until you are completely confident that it works.

With a PowerShell script, it is actually easier to set up a development environment than it is for SQL code (I cover techniques for doing this in Debugging Flyway Callbacks and Migrations that Use Placeholders). Your code will start with a block that defines the values you may want for running the code in an IDE for testing. You probably won’t need them, but they are great if you do!

Once you are ready to test the completed callback, you’ll remove this block if you are an optimist or comment it out if you’re not, so you can return to an IDE environment to work out what went wrong. The same script will work as a callback once you clear out the block of code where you’ve defined the values.

It makes sense to use functions and libraries in your code (as with Flyway Teamwork framework), and these will need to be loaded at the start of your script because Flyway will execute your callback in a fresh version of PowerShell. The functions that you write should get their data from parameters so that you can use them outside or inside a callback. This means that you’ll probably want to create functions to do the work, with parameters to hold the values you need to get from Flyway environment variables. You would then pass these Flyway values as parameters to the functions you’ve created, rather than to get them directly as environment variables, so that you can then re-purpose the functions more generally. However, once a routine is wrapped up as a function, you’ve lost a lot of your means of debugging and testing unless you unwrap it, so you’ll want to create the function from your block of code as late in the job as possible.

When you are ready to test the finished callback, you can usually run it from PowerShell, setting the environment variables first:

Or you can do it from DOS:

Errors and messages

Things will occasionally go wrong in a callback so you will need to implement thorough checks and error handling in your callback script, for the various categories of error. This includes logging, alerting, and the means of handling exceptions gracefully. This helps in identifying and resolving issues during migration. The PowerShell callback will send to Flyway its various streams (information, verbose, warning and error). However, Flyway only understands the error and information stream, so passes everything that isn’t an error back to STDOUT, in plain text.

Flyway tends to be verbose, so it is easy to miss minor or even important errors. I wrote an article, Piping, Filtering and Using Flyway Output in PowerShell that shows how one can filter out verbosity to allow you to avoid this. It is also possible in PowerShell to send the different types of messages to separate log files.

When you need more information

You will often need more information than can be provided by the environment variables. An obvious example is the name of a DSN that can be used to make ODBC SQL calls into the database being migrated, or to extract the database metadata. See Using ODBC in PowerShell Scripts and Callbacks for an example.

More generally, you’ll need more mundane details such as a path for saving reports, scripts or backups. The most obvious way of passing information like this to a callback is to pass it as a custom placeholder. In a SQL Script, this will simply substitute the value wherever the placeholder’s name is placed between delimiters. With a PowerShell script, this appears as an environment variable and must be fetched in the same way as the built-in placeholders. This isn’t much of a problem because PowerShell makes it easy.

As I advised earlier, make sure that all the placeholders you need are there at the start of the callback, because it is so easy to get that wrong. Here, I check to make sure that the user and password that was used by Flyway to make the JDBC connection is there, and that the name of the ODBC DSN was passed as a custom variable.

Choosing the correct event in the Flyway lifecycle

Any busy database developer needs to automate as much as possible of the routine tasks in database development. To do this effectively, you need to hook into different phases of the migration process. There are several events in a Flyway process where you might want to add a callback script. Most commonly, you’ll want to do it after a migration run takes place, which may include several version steps. You might, for example, want to write extra information to the database whenever a successful migration run happens.

However, you might also need to do it at different points in the development of a database. Before you do a ‘Flyway Clean’ for example, you might need to check whether you have a backup of what you’re about to delete. If you are doing a migration using a relational database that doesn’t allow rollback of DDL SQL Code within a transaction, you’ll need a script to make sure that there is a backup of the current version before you try out a migration, to make the mopping-up process easier (See Dealing with Failed SQL Migrations in MariaDB or MySQL).

To hook into a particular Flyway event, and run a callback task before or after it happens, you just name the callback with the right prefix, such as afterInfo__Testbackups.ps1, afterVersioned__TestResults.ps1, afterEachMigrate__ApplyTableDescriptions.sql, or afterMigrate__Add_Version_Info.sql. Save the script to one of your migrations folders and Flyway will then invoke it at the point in the process indicated by the filename.

You can use these various types of callbacks to hook into whatever part of the Flyway lifecycle you need. Events are always being added to the product so you will need to check the information in the documentation.

Making scripts idempotent

A Flyway callback script generally needs to be idempotent, in the sense that running the script repeatedly won’t have unintended side effects. If you already have a backup for the database before attempting a migration, then there isn’t a need for another, and it might even cause a file error. If a callback creates a database object, it should check whether it is already there.

This is very different to the typical SQL migration version file that is always executed from a known state (the previous version). For example, I use a callback to insert the current documentation of each object and attribute into a database after a migration, using just one callback. It checks whether an object exists and will remove existing documentation first if you’ve changed it. Callbacks are executed outside the version sequence; you can’t specify a different callback for various versions. They must work for all.

Defensive programming

It pays to write callback code defensively, meaning that nothing is taken for granted, and that you abort the process the moment you detect something wrong. This means checking environment variables and user placeholders, checking for the existence and integrity of any files you intend to read and ensuring that the process worked. Obviously, the errors should be so specific that even a teammate who is surreptitiously catching a late breakfast while the process works will know that the callback failed and what to do about it.

The fact that a callback has worked fine up to now isn’t a reason to relax. I’ve been caught out with callbacks when a Flyway configuration file has disappeared or has been altered. Such things are beyond the control of the callback. Obviously, it pays to document the code sufficiently that a team-mate in a hurry doesn’t have to puzzle out your ingenious but opaque code before fixing a bug. Things happen and if you miss the fact that the callback process didn’t end well, it could be painful.

Conclusion

Flyway doesn’t do anything particularly magical, but it enables magic to be done. The magic of a callback is that you can automatically do processes that support a database development at the right point in the process. You might think ‘what processes?’.

Do you need to do a backup before you apply a migration script? Do you need to run integration tests on every version change? Would it be sensible to run a pre-migration check or perhaps a publish an audit report after every successful migration? Do you need to load data? Do you need to provide a build script for every version change? Should you report on progress? Do you need to document the changes made to reach a particular version? What about a single documentation for all your tables, columns, views and functions that gets embedded in the code as remarks or extended properties? And that is just the start.

The list of supporting tasks is long but once you have it all automated, not only is development faster and more precise, but you can sit smugly sipping coffee while it all happens, watching the scrolling screen.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Teams

Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.

Find out more