Using Flyway Teams with SQL Change Automation PowerShell Cmdlets
Use Flyway to run your database migrations, each time automatically creating a SQL Change Automation release object to provide object-level scripts and a build script for the new version, along with change reports and code analysis reports.
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…
1 |
gci env:* | sort-object name |
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:
1 2 3 4 5 6 7 8 |
$Env:FLYWAY_URL='jdbc:sqlserver://MyServer:1433;DatabaseName=Pubs' $Env:FLYWAY_USER = 'Phil_Factor' $Env:FP__flyway_database__ = 'Pubs' $Env:FP__flyway_defaultSchema__ = 'dbo' $Env:FP__flyway_table__ = 'flyway_schema_history' $Env:FP__flyway_user__ = 'Phil_Factor' $Env:FP__projectName__ ='ExtendedPubs' $Env:FP__schemas__ ='dbo,classic,people' |
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:
- 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.
- We use the hashtable to execute a couple of standard routines to get version and password.
- 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
- 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
# run the library script, assuming it is in the project directory containing the script directory "First load" . "..\DatabaseBuildAndMigrateTasks.ps1" "loaded tasks" <#To set off any task, all you need is a PowerShell script that is created in such a way that it can be executed by Flyway when it finishes a migration run. Although you can choose any of the significant points in any Flyway action, there are only one or two of these callback points that are useful to us. This can be a problem if you have several chores that need to be done in the same callback or you have a stack of scripts all on the same callback, each having to gather up and process parameters, or pass parameters such as the current version from one to another. The most useful data passed to this script by Flyway is the URL that you used to call Flyway. This is likely to tell you the server, port, database and the type of database (RDBMS). We can use the URL. If we just want to make JDBC calls. We can't and don't. Instead we extract the connection details and use these. This is just to pull in the SCA cmdlets required to execute new-DatabaseReleaseArtifact #> write-verbose "Importing SCA" Import-Module SqlChangeAutomation $FlywayURLRegex = 'jdbc:(?<RDBMS>[\w]{1,20})://(?<server>[\w\-\.]{1,40})(?<port>:[\d]{1,4}|)(;.+databaseName=|/)(?<database>[\w]{1,20})' #the FLYWAY_URL contains the current database, port and server so it is worth grabbing $ConnectionInfo = $env:FLYWAY_URL #get the environment variable if ($ConnectionInfo -eq $null) #OMG... it isn't there for some reason { Write-error 'missing value for flyway url' } <# a reference to this Hashtable is passed to each process (it is a scriptBlock) so as to make debugging easy. We'll be a bit cagey about adding key-value pairs as it can trigger the generation of a copy which can cause bewilderment and problems- values don't get passed back. Don't fill anything in here!!! The script does that for you#> $DatabaseDetails = @{ 'RDBMS' = ''; # necessary for systems with several RDBMS on the same server 'server' = ''; #the name of your server 'database' = ''; #the name of the database 'version' = ''; #the version 'ProjectFolder' = ''; #where all the migration files are 'project' = ''; #the name of your project 'projectDescription' = ''; #a brief description of the project 'flywayTable' = ''; #The name and schema of the flyway Table 'uid' = ''; #optional if you are using windows authewntication 'pwd' = ''; #only if you use a uid. Leave blank. we fill it in for you 'locations' = @{ }; # for reporting file locations used 'problems' = @{ }; # for reporting any big problems 'warnings' = @{ } # for reporting any issues } # for reporting any warnings write-verbose "Getting values from JDBC connection info" if ($ConnectionInfo -imatch $FlywayURLRegex) { $DatabaseDetails.RDBMS = $matches['RDBMS']; $DatabaseDetails.server = $matches['server']; $DatabaseDetails.port = $matches['port']; $DatabaseDetails.database = $matches['database'] } else { write-error "failed to obtain the value of the RDBMS, server, Port or database from the FLYWAY_URL" } $DatabaseDetails.uid = $env:FLYWAY_USER; $DatabaseDetails.Project = $env:FP__projectName__; $DatabaseDetails.ProjectDescription = $env:FP__projectDescription__; $DatabaseDetails.ProjectFolder = split-path $PWD.Path -Parent; if ($env:FP__flyway_defaultSchema__ -ne $null -and $env:FP__flyway_table__ -ne $null) { $DatabaseDetails.flywayTable = "$($env:FP__flyway_defaultSchema__).$($env:FP__flyway_table__)" } else { $DatabaseDetails.flywayTable = 'dbo.flyway_schema_history' }; <# Now we need to get the credentials, and the current version of the flyway database#> $PostMigrationInvocations = @( $FetchAnyRequiredPasswords, #checks the hash table to see if there is a username without a password. #if so, it fetches the password from store or asks you for the password if it is a new connection $GetCurrentVersion) #checks the database and gets the current version number #it does this by reading the Flyway schema history table. write-verbose "Finding version number" Process-FlywayTasks $DatabaseDetails $PostMigrationInvocations # the version and password is now in the $DatabaseDetails array4 write-verbose "preparing SCA call" $EscapedProject = ($DatabaseDetails.project.Split([IO.Path]::GetInvalidFileNameChars()) -join '_') -ireplace '\.', '-' $CurrentPathToWorkingFiles = "$($env:USERPROFILE)\$ReportLocation$($EscapedProject)\$($DatabaseDetails.Version)"; $SourceConnectionString = "Server=$($matches['server']);Database=$($matches['database']);User Id=$($env:FLYWAY_USER);Password=$($DatabaseDetails.pwd);Persist Security Info=False" write-verbose "Creating directories if necessary" <# If the scripts directory isn't there ... #> if (-not (Test-Path "$CurrentPathToWorkingFiles\scripts" -PathType Container)) {<# ..then create the scripts directory #> $null = New-Item -ItemType Directory -Path "$CurrentPathToWorkingFiles\scripts" -Force } <# If the source directory isn't there ... #> if (-not (Test-Path "$CurrentPathToWorkingFiles\source" -PathType Container)) {<# ..then create the source directory #> $null = New-Item -ItemType Directory -Path "$CurrentPathToWorkingFiles\source" -Force } if (-not (Test-Path "$CurrentPathToWorkingFiles\source\*" -PathType Leaf)) { #only do this once ... # create a release artefact and fill the source directory write-verbose "creating the release artefact" $iReleaseArtifact = new-DatabaseReleaseArtifact -Source $SourceConnectionString -Target "$CurrentPathToWorkingFiles\source" # it will fill the blank source directory #export a build script write-verbose "Creating build script" $iReleaseArtifact.UpdateSQL> "$CurrentPathToWorkingFiles\Scripts\V$($DatabaseDetails.Version)__Build.SQL" #export a detailed report of code issues write-verbose "Creating detailed code report" if (-not (Test-Path "$CurrentPathToWorkingFiles\Reports" -PathType Container)) {<# ..then create the scripts directory #> $null = New-Item -ItemType Directory -Path "$CurrentPathToWorkingFiles\Reports" -Force } $iReleaseArtifact.CodeAnalysisResult.Issues>"$CurrentPathToWorkingFiles\Reports\DetailCodeIssues.txt" #export a summary report of code issues write-verbose "Creating summary code report" $iReleaseArtifact.CodeAnalysisResult.issues | sort-Object @{ expression = { $_.CodeAnalysisSelection.LineStart } } | select CodeAnalysisSelection, IssueCodeName, ShortDescription >"$CurrentPathToWorkingFiles\Reports\SummaryCodeIssues.txt" #export a release artifact that can be used to compare release objects write-verbose "Saving release artefact" $iReleaseArtifact | Export-DatabaseReleaseArtifact -path "$CurrentPathToWorkingFiles\Artifact" #add to reports what is in the version, including objects and code issues Copy-Item -Path "$CurrentPathToWorkingFiles\Artifact\Reports\*" -Destination "$CurrentPathToWorkingFiles\reports" -force Copy-Item -Path "$CurrentPathToWorkingFiles\Artifact\States\Source\*" -Destination "$CurrentPathToWorkingFiles\source" -recurse -force Remove-Item -Path "$CurrentPathToWorkingFiles\Artifact" -Force -Recurse } |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$Details = @{ 'server' = 'MyServer'; #The Server name 'database' = 'MyDatabase' #The Database 'Port' = '1433' 'uid' = 'MyUID'; #The User ID. you only need this if there is no domain authentication or secrets store #> 'pwd' = ''; # The password. This gets filled in if you request it 'version' = ''; # TheCurrent Version. This gets filled in if you request it 'schemas' = 'dbo,classic,people'; 'project' = 'SCA'; #Just the simple name of the project 'ProjectDescription' = 'A Simple description of the project' 'ProjectFolder' = '<mypathto>\PubsAndFlyway\PubsFlywayTeamsMigration'; # parent of the scripts directory 'Warnings' = @{ }; # Just leave this be. Filled in for your information 'Problems' = @{ }; # Just leave this be. Filled in for your information 'Locations' = @{ }; # Just leave this be. Filled in for your information } |
…And then run the migrate:
1 2 3 |
Flyway @FlywayUndoArgs info Flyway @FlywayUndoArgs clean Flyway @FlywayUndoArgs migrate '-target=1.1.10' |
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!