• Flyway
• Database Build tasks
• Automation and workflow

Scripting with Flyway Teams and PowerShell

In this article, I’ll explain how script callbacks work, how to pass information to them using environment variables, and how to get information from them. I’ll demo all this with a PowerShell callback script designed to run your set of required development tasks, each time Flyway executes a versioned migration script successfully. To try it out, existing users of Flyway Community will need to upgrade to Flyway Teams (you can get a free trial license from the Flyway website).

Why use Flyway Teams rather than Flyway Community?

Flyway Teams is a tool that you grow into as your development team gets larger, the requirements get more elaborate, and the development work requires more scrutiny. Things that you can get away with as a start-up or when developing a small database can become problems, or a drain on your time, as you scale up because it is inevitable that, the more important a database becomes, the more ‘controls’ that there are on it.

Perhaps I ought to give a few examples of the ways that more demands are made on your database development methods as databases get larger, more complicated, and of more importance to the organization.

• More scrutiny – A large organization must be confident that its databases meet security guidelines and conforms to the legislative framework for their industry so the design will need more team scrutiny.
• Cross-team collaboration – the organization must also be sure that your work can be maintained in the future to meet changes, so you may need to deal with corporate coding standards, code reviews, and documentation. Your development may need to conform with existing source control strategies that aren’t geared to a database migration approach.
• More people – as a database scales up, more people will be working on it at once. You will need an easily managed review process. You may then need to deal safely with branches and merges of the database being worked on by colleagues.
• More rigorous testing and reviews – with increasing demands for testing, and with the test scenarios increasing, you need a robust way of dealing with any problems, and making reviews easier when a new version is to be deployed into production

All of this means that your development pipeline will need to incorporate many extra processes, alongside the basics of running database migrations. This might include producing backup scripts, object-level build scripts, performing a ‘diff’ see which database objects were affected by a migration, performing code analysis, creating progress reports, or generating documentation. I’ll provide several examples in this series of articles, available on GitHub for you to try out.

The way you deal with all these complications is likely to take its inspiration from a DevOps approach: by scripting, automation, cooperation, and close liaison between different teams to ensure that complications can never mature into problems.

Using script callbacks for development pipeline tasks

Leaving to one side the possibility of a Java-based or Maven-based integration with Flyway, the most sophisticated scripting system used for automating development work is PowerShell. You can use PowerShell, or DOS Batch scripting, with Flyway Community, to run these extra processes, such as generating a build script or performing a ‘diff’, but only if Flyway is called from within the PowerShell script. You need to ensure that your script runs each of the necessary Flyway commands, followed every time by the required task. This approach won’t allow you to use Flyway interactively because Flyway will always need to be part of the scripted process. It relies on everyone involved understanding the system.

By using Flyway Teams, you can adopt a more subtle approach, use script callbacks, in our case PowerShell scripts, to add functionality to Flyway ‘events’, such as a migrate or an undo command starting or completing successfully, or a series of migration files (a migration run) completing, or an error occurring. We simply create the required task in a callback file, which is just a special type of PowerShell script file, name it according to the event on which we need the task to run, and place it in the Scripts location. Every time the event occurs, Flyway will automatically run the associated callbacks.

When working with script callbacks, we rather ‘invert’ the model of working with Flyway. In all previous Flyway Community articles, we’ve driven Flyway, through PowerShell, and provided script-blocks and functions for all the tasks we needed to carry out as part of a migration. With Flyway Teams, we simply provide the tasks in callback files and Flyway runs the scripts at the appropriate points in the migration process. This gives you much more flexibility and power during the migration lifecycle.

Specifying when a callback script is to be executed

To specify when a callback file must be executed, simply give the filename a prefix according to the Flyway ‘event’ that must trigger it, such as before each individual migration file runs (beforeEachMigrate) or after a whole migration process completes successfully (afterMigrate), or perhaps only on an error condition occurring for any of the Flyway commands. You can also include a description of the task performed so, for example, for a PostgreSQL database, you could have a beforeMigrate__vacuum.ps1 callback. The supported file types for script callbacks are the same as those for script migrations.

Passing information to callback and migration scripts

You will occasionally need to pass extra information to a script, either a SQL migration script or a script callback. You cannot pass values as parameters directly to Flyway scripts because they are run by Flyway, not directly by you. Scripts in Flyway rely on placeholders provided by Flyway to get all the essential values they need.

In SQL Migration and SQL callback scripts, these placeholders behave like a standard macro that holds the place in the code where the values get substituted. When it executes the script, Flyway automatically passes in the values of any default placeholders. When using Flyway at the command line we can pass in custom placeholders and their values through environment variables (FLYWAY_PLACEHOLDERS_MYPLACEHOLDER = value), or parameters, or configuration key/value pairs (flyway.placeholders.myplaceholder = value). SQL migrations, undo migrations, and callback scripts all receive all the placeholders whether they are relevant or used.

PowerShell and DOS Scripts must read the values of placeholders from environment variables. Unlike with SQL (or Java) callback scripts, there is no ‘placeholder replacement’ that provides automatic value substitutions. You fetch each value and assign it dynamically. Flyway provides environment variables that hold a copy of the values associated with the JDBC connection (FLYWAY_URL) and the Flyway user (FLYWAY_USER). As with SQL or Java scripts, the default placeholders are passed too. This means that you can use any ‘EACH’ callback (called on each event, such as a successful or failed migration) from either a MIGRATE or UNDO command that requires the name of the current migration file.

We must use environment variables to read the values of the default placeholders that hold the  name and schema location of the flyway_schema_history table , which we’ll need if they differ from the default (for example, there is a default placeholder called ${flyway:table} and the corresponding environment variable is called, somewhat enigmatically, $env:FP__flyway_table__).

Some information that the callback script will need, like the schemas we use in the database, or the name of the project and a description, won’t be passed in unless we supply the values with custom placeholders, when we run the Flyway migration command  (see the section “Running the callback script” later). Alongside the information available from the placeholders, you can, in the script, find out the current script folder (a project can have several) because the script is always executed with the current script folder as the current working directory.

However, a value that is currently missing from all callback scripts is the current version of the database. If you need this, you’ll have to get it from the flyway_schema_history table, where Flyway writes the current version number, after each successful migration. I’ve provided a PowerShell script block task ($GetCurrentVersion) to get this value. There is no way of using a placeholder to get the current version number because this value is very likely to change during a migration or undo run.

Getting information from scripts

It might seem odd to worry about the output from a script, especially if you neither make an occasional mistake nor need to debug a script. Most scripting systems have a concept of separating different output streams which makes verbose output, logging and error handling easier. However, the only output stream from Flyway is the default one. There is no warning or error stream. An exception will be handled appropriately. Most generally, a script will write its output to a file or a database. You can, of course, create files to pass values between processes.

Working with environment variables

The best way to pass information to any script that is executed as a shell process, is to use environment variables. These variables can be permanent, such as the path to your installation of Flyway, or temporary, erased when the shell is closed.

Every time a shell session or PowerShell process spawns, information is gathered from a variety of different files and settings on the system. This is made available to the shell process and its child processes. Because Flyway creates a new DOS or PowerShell process to run a script migration file, it can add any placeholders or Flyway-specific values that are required. The environment variables allow any shell process to get or set settings and, in turn, pass these on to its child processes.

In one way, these environment variables are very valuable. It makes it easy to debug scripts. You can execute a script in the ISE if you initially define all the environment variables that are usually provided by Flyway. In DOS, you can dump the current settings to a file with a one-liner:

This makes it easy to set up the same environment for debugging. You can also, at run-time, test whether a required value exists, so you can use environment variables to override default values.

The range of callback events

The first decision you need to make is what callback to use. This can seem rather daunting, and I’ve made bad choices in the past, so I created a crib sheet for my own use. Here it is just in case it proves useful to anyone else. All ‘Each’ callbacks are executed as part of the original transaction for the migration. This is no problem for SQL callbacks because they are executed in the same connection as the migration itself. However, it can cause problems for any script (e.g. powershell) callbacks, which have to open a separate connection to the database (while the original transaction is still active). Odd things can happen due to locking and blocking if you do SQL accesses into the database within an “Each” script callback. It’s much safer to use after callbacks, such as afterVersioned, that are only executed after the original transaction commits.

Name		When	Each?	Error?	Execution	Type	Teams?
beforeMigrate		Before	✔	✔	Before Migrate runs	Migration run
beforeRepeatables		Before	✔	✘	Before all repeatable migrations during Migrate	A repeatable migration run
beforeEachMigrate		Before	✘	✔	Before every single migration during Migrate	Each migrate
beforeEachMigrateStatement		Before	✘	✔	Before every single statement of a migration during Migrate	Migration statement	Teams Only
afterEachMigrateStatement		After	✘	✔	After every single successful statement of a migration during Migrate	Migration statement	Teams Only
afterEachMigrateStatementError		After	✘	✘	After every single failed statement of a migration during Migrate	Migration statement	Teams Only
afterEachMigrate		After	✘	✔	After every single successful migration during Migrate	Each migrate
afterEachMigrateError		After	✘	✘	After every single failed migration during Migrate	Each migrate
afterMigrate		After	✔	✔	After successful Migrate runs	Migration run
afterVersioned		After	✔	✔	After all versioned migrations during Migrate	Versioned
afterMigrateError		After	✔	✘	After failed Migrate runs	Migration run
beforeUndo		Before	✔	✔	Before Undo runs	Undo run	Teams Only
beforeEachUndo		Before	✘	✔	Before every single migration during Undo	Each undo	Teams Only
beforeEachUndoStatement		Before	✘	✔	Before every single statement of a migration during Undo	Each Undo statement	Teams Only
afterEachUndoStatement		After	✘	✔	After every single successful statement of a migration during Undo	Each Undo statement	Teams Only
afterEachUndoStatementError		After	✘	✘	After every single failed statement of a migration during Undo	Each Undo statement	Teams Only
afterEachUndo		After	✘	✔	After every single successful migration during Undo	Each undo	Teams Only
afterEachUndoError		After	✘	✘	After every single failed migration during Undo	Each undo	Teams Only
afterUndo		After	✔	✔	After successful Undo runs	Undo run	Teams Only
afterUndoError		After	✔	✘	After failed Undo runs	Undo run	Teams Only
beforeClean		Before	✔	✔	Before Clean runs	Clean action
afterClean		After	✔	✔	After successful Clean runs	Clean action
afterCleanError		After	✔	✘	After failed Clean runs	Clean action
beforeInfo		Before	✔	✔	Before Info action	Info action
afterInfo		After	✔	✔	After successful Info action	Info action
afterInfoError		After	✔	✘	After failed Info action	Info action
beforeValidate		Before	✔	✔	Before Validate runs	Validate run
afterValidate		After	✔	✔	After successful Validate runs	Validate run
afterValidateError		After	✔	✘	After failed Validate runs	Validate run
beforeBaseline		Before	✔	✔	Before Baseline runs	Baseline run
afterBaseline		After	✔	✔	After successful Baseline runs	Baseline run
afterBaselineError		After	✔	✘	After failed Baseline runs	Baseline run
beforeRepair		Before	✔	✔	Before Repair runs	Repair action
afterRepair		After	✔	✔	After successful Repair runs	Repair action
afterRepairError		After	✔	✘	After failed Repair runs	Repair action
createSchema		After	✔	✔	Before automatically creating non-existent schemas	Creating a schema

Writing a PowerShell callback script

The demonstration callback script is called afterVersioned__Build.ps1 and so Flyway will execute it after every versioned migration script that runs successfully during a Migrate. You’ll find it in the Scripts folder of the GitHub Project. It can, in fact, be used for any type of PowerShell script that is called by Flyway, including a callback, undo or migration.

Although it’s just one callback script, I present it here in two parts. The first part gathers the data we need and places it in a convenient form, and then the second part uses the information that we’ve gathered to execute a range of tasks from our library of functions. These are contained in the DatabaseBuildAndMigrateTasks.ps1 file on GitHub, for the SQL Server version. See Running SQL Code Analysis during Flyway Migrations for more details of each task.

Gathering the data

This section of the callback script simply assembles the commonly needed parameters. It uses the FLYWAY_URL and FLYWAY_USER environment variable, plus some others to pass in the name and schema of the flyway_schema_history table,  from the corresponding default placeholders, and other details like the name of the project and a description that we’ll need to pass in form custom placeholders.

You might puzzle about the RDBMS value, which I provide in case you, like me, host more than one RDBMS in the same server, such as sqlserver and mysql or postgresql. This is pulled for free from the FLYWAY_URL environment variable using a simple regex.

Doing stuff

Now that we have the data we need in the right format, by gathering up all our variables into a hashtable, we now continue the script by specifying our tasks, in their order of execution.

Most of the work one would want to do with a database involves credentials, so this is generally the priority. This value isn’t in the FLYWAY_URL environment variable, thank goodness, so we do it our own way using the $FetchAnyRequiredPasswords task, storing credentials encrypted and in the user area. We also generally need to know the current database version, so we almost always need a task for that ($GetCurrentVersion). You get the idea: we need an initial chain of tasks to get almost anything done.

With all that done, the callback script can now get to work running each of our required post-migration chores, such as generating a build script for the new version, running code analysis checks and so on. Just comment out all the tasks you don’t need, and the ones for which you don’t have the tool (the build script and script folder tasks need SQL Compare and the code analysis tasks uses the Code Analysis for SQL Server command line component).

This code needs a bit of explanation. Each task is provided as a script block. Since each task needs the same parameters, I pass in the same hash table, by reference. Any result is written back to the hash table. The obvious advantage of passing a reference to an array is that we can add parameters without having to alter each script block, and because script blocks test to see if parameters that they need are there, and protest if they aren’t, one can safely remove unused parameters. A big advantage of using script blocks is that they can be easily stacked up as a chain of processes, and executed conditionally, if you need that. As well as being run in a series, they can be used individually. If one of them needs maintenance, it is very easy to pull it apart and run it interactively.

The tasks are designed to run with Flyway Community or Teams editions. You can use them to maintain backups or scripts for each database version and make a Flyway a full participant in any source control system in use, even to the point of providing an object-level source to allow the evolution of individual tables to be tracked. For example, the $CreateBuildScriptIfNecessary task generates the build scripts that are essential, I believe, for drift detection as well as for successful branching and merging. You might also need a similar task for providing a means of rolling back, when migrating databases such as MySQL that do not roll back DDL changes in failed transactions (see Dealing with Failed SQL Migrations in MariaDB or MySQL).

Some of these utilities are there primarily to show how to run external SQL to get JSON-Based data. I have examples of SQL Code executed from a variable containing a query, and from a file.

Running the callback script

All that remains is to run the callback as part of a flyway migration. To set everything up, we’ll use the UNDOBuild.ps1 script in the PubsAndFlyway\PubsFlywayTeamsMigration directory. After you’ve executed this script, you can then execute whatever Flyway commands you wish:

As a test-run, I usually clean the test database, and run through all the migrations.

Conclusions

Using Flyway must be kept simple, with the complexity being hidden away. When one is trying to concentrate on a difficult database process, distractions can be destructive. For that reason, many database developers are keen scripters: not because they enjoy it, but because it allows them to deliver better designed databases that are more reliable, and do it more quickly

Callback scripts in Flyway therefore have great potential for automating chores, allowing the team to focus on the essential design work.

Tools in this post

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

Redgate Flyway

DevOps for the Database

Find out more