• Flyway
• Database Deployments
• Automation and workflow

Using Flyway Teams with SQL Change Automation PowerShell Cmdlets

As a database gets larger and more complex and as more people get involved, the practice of using migration scripts as a ‘primary source of truth’ for version control begins to present more difficulties. I’ve explained why in other articles, so I’ll just say here that source control can only compare text rather than meaning, and an infinite number of migration script collections can represent exactly the same database. Also, it becomes difficult to examine, in source control, the changes on a subset of database objects, such as one or more tables to determine who did what to which, and when.

At this stage in the lifecycle of a database, it becomes necessary to track changes using a standard way of representing database objects as scripts. This ‘object source’ is generated automatically for every version and must use a consistent way of doing so. SQL Change Automation’s PowerShell cmdlets will give you this, by automating SQL Compare under the covers. It does much more, of course, but the source directory is a useful prize because SQL Change Automation and SQL Compare can use this as a source or target in a database comparison. You no longer need to have live databases to represent a version.

To show how to create an object-level source directory for each version created in Flyway, I’ll be demonstrating a way of using SQL Change Automation (SCA) in a callback script in Flyway Teams. This allows you a degree of precision about when to invoke SCA: after every successful migration run (a ‘run’ being a chain of one or more migration files applied as a migration by Flyway in version order).

If you are restricted to Flyway Community, you can still create these source directories after calling Flyway, as I’ve demonstrated in my article Creating Database Build Artifacts when Running Flyway Migrations. This approach can also be modified to use SCA because that part of the code is very similar.

The low-hanging fruit

SCA is easily persuaded to create a ‘release object’. This is probably best understood as a package of useful deployment materials. We can export this package to a folder as a set of files or a zipped package. We can also extract the goodies directly from the object. As well as an object-level source directory for the source database, it contains the metadata of the target database, as an object-level script, so you can check the target for version creep (a.k.a. database drift), an Update script that will perform the ‘synchronization’, any warnings about potential data loss, and a report on any issues with your code. It also has a description of the comparison between the source and target database in HTML and JSON. The SCA release object is a useful prize because, as well as providing all this, it can be subsequently used in any deployment pipeline that is based on SCA.

We tend to save the release artifact separately from the migration scripts because this information is, by its nature, version specific. I usually store it in a separate project repository with a directory for each version.

The plum in the release artifact, for our requirements, is the source code directory of the individual objects in the source database, because in our case the source database will be the new version of the database, created by the Flyway migration that triggered our callback.

Testing and debugging Flyway callbacks in PowerShell

Flyway applies some sort of management to the execution of a PowerShell callback script and its standard inputs and outputs. All parameters must be passed as placeholder values that appear as environment variables. There are a set of basic values passed as default placeholders and the others must be passed to Flyway as placeholders, either in environment variables or one of the Flyway.conf files. See Scripting with Flyway Teams and PowerShell for more details.

The easiest way to check out the values passed as environment variables is to dump these, using a callback. I use a callback with the one line…

Save this as an afterVersioned__ callback script. We only need it while testing of course, so we delete it after it has given us the list of environment variables that are passed to a callback.

With this, we can create a ‘harness’, to thoroughly test a callback as a conventional PowerShell script in the IDE (ISE or VS). All I need to do is recreate all the environment variables and it becomes easy to execute it directly. These will be, for example:

Just add this block of code to the start of the callback to provide it with suitable values, and then execute it in the ISE, VS or your favorite PowerShell debugger. Remember to remove it before you once again use the PowerShell script as a callback!

All this may seem like extra trouble, but unfortunately it isn’t always easy to debug a callback by running it as a callback. If everything works fine first time, it all seems simple, but not if you run into a bug. For a start, all your Printf debugging strings are cached until the callback finishes so, if it doesn’t finish, you’ve lost them. Why might it not finish? Ah! PowerShell will stop and ask for the value of a parameter to a cmdlet if you don’t provide it. If you mistype or somehow corrupt the tick character that signifies the continuation of a line, PowerShell thinks you haven’t supplied a parameter and so asks for it. Then it waits until you return the value in the input stream. This is all concealed by Flyway, so you never see that prompt for the value of the parameter. Flyway just appears to lock up solid. The only way out is to terminate the session. It may be that one can unlock the script by typing something at the keyboard, I don’t know but suspect not.

If, however, you develop the callback in a harness that supplies all the correct environment variables, then all is sweetness and light, because the inevitable errors are obvious and well described by PowerShell.

Creating SCA release artifacts during Flyway migrations

In our example we perform a Flyway migration run, for example from V1.1.0 to V1.1.3. When the migration run completes successfully, Flyway automatically runs our afterVersioned_SCA.ps1 callback script. This script uses two SCA Cmdlets, New-DatabaseReleaseArtifact and Export-DatabaseReleaseArtifact to produce build artifacts for our new version (V1.1.3).

The New-DatabaseReleaseArtifact cmdlet compares the newly migrated database (source), to an empty folder (target) to produce the SCA Release object. I’ve described this object exhaustively in the article Simple SQL Change Automation Scripting: The Release Object and so there is no need to repeat this here.

Basically, the release object is a package that can be used to successfully deploy an update to a database, even if it is remote from the network, and even if you are only vaguely sure of the version of the target database.

We use this release object, picking out all the reports and scripts that we want. Your needs may be different, of course, which is why it is worth checking it out. Because I’m comparing the newly migrated database, to a blank target (a newly created source directory), the Update script SCA produces is a build script for the new version. I also extract from the release object detail- level and summary-level overviews of all detected code issues, as simple text files.

I then use Export-DatabaseReleaseArtifact to save the release artifact to a directory, extracting from it the Change reports, code analysis file, warnings and, critically, the object-level source for the new version.

The callback script

Before we get too far, we pull in the SCA cmdlets the PowerShell library I use for all the common routines, DatabaseBuildAndMigrateTasks.ps1. I use this to run task such as getting the current version of the Flyway-managed database.

There are four main activities:

1. First, we get hold of the information passed to the callback and put it into a standard hashtable that we use to get the password for the user ID.
2. We use the hashtable to execute a couple of standard routines to get version and password.
3. Now that have everything we need, we can call the two SCA Cmdlets to get the Release Artifact and we extract all the information contained in it into a directory, as described previously
4. We take all the information that we want from this and put it, with all our data that is relevant, in the subdirectory named after the version of our database that Flyway has built for us.

Here is the script, saved as afterVersioned_SCA.ps1 in the Scripts folder of my GitHub repo:

Trying it Out

To try it out, simply run a migration on one of your flyway-controlled databases. As usual, I used the Pubs database, and ran the migration using the UNDOBuild.ps1 script in the project directory.

Just fill out your $details like this:

…And then run the migrate:

The result should be a newly migrated database, at V1.1.10 in this example, and at the $CurrentPathToWorkingFiles location (C:\Users\Phil\Documents\GitHub\SCA\1.1.10, in my case), you should find all the release artifacts we saved, including the object-level source for V1.1.10:

And the build script for it:

And finally, the various useful build reports:

If we click on Changes.html, we can then drill into the script, the changes and the object-level scripts and work in an interactive User interface to check out what has happened as a result of the migration.

Because we store this information with every version automatically as we work on a database, we are never short of information about what is happening. Of most importance, we have the object-level script directory for source control and comparing versions of the database.

Conclusions

With SCA, there always seems to be several ways of achieving a task. This is certainly the case when all we really need is the standard object-level directory, suitably populated for the purposes of source control. However, the release artifact seems the best place to get this and to get all those reports as well.

Of course, having got the taste for this, it occurs to me that if, instead of comparing the built database with a blank database to get a build script, I compare this version to the previous version. We then get a report that tells us about the differences between versions. After all, SCA and SQL Compare can use an object-level directory as easily as a live database for comparing. We can even get UNDO scripts out of it. Sigh. I hear the distant dinner-gong, so I fear that this would be a topic for another article!

Tools in this post

Flyway Enterprise

Standardize Database DevOps across the organization

Find out more

Redgate Flyway

DevOps for the Database

Find out more

SQL Change Automation

Automate your database changes with CI and automated deployment

Find out more