Flyway Alerting and Notifications
Once a scripted Flyway task is tested and bedded-in, it should rarely cause errors, so when one happens the team need to be notified immediately, especially if it is part of an automated deployment process. This article provides a PowerShell cmdlet that will execute any Flyway command, process and filter the Flyway output and send any errors to the chosen notification system. You can receive the Flyway error alerts on your mobile phone, if required!
If you are doing several scripted operations in Flyway, you’ll want to be alerted to any errors. If you are staring at a screen, you often have a chance of reading an error message as it scrolls up, but a scheduled task, such as one that keeps a group of databases up to date, can’t communicate errors so easily.
Even when running a PowerShell script from the console, there are plenty of routine operations where one tends to get overwhelming screeds of information from Flyway. If errors are located somewhere in a long river of text, they will be missed, especially if the script is running as a scheduled process. I’ve already shown, in Piping, Filtering and Using Flyway Output in PowerShell, how one can filter out the worst of this information overload, leaving just the warnings and errors, and the ‘verbose’ information if you need it.
However, errors should never happen in a scripted process once it is tested and ‘bedded-in’. If an error does happen, then whoever is responsible for maintenance needs to be notified. Otherwise, the world of work can be full of nasty surprises. The obvious way of notifying someone of errors in scripts that are automated, especially if part of a deployment process, will be to use the team’s chosen notification system. We’ll use Synology Chat as an illustrative example.
The Error Catcher: collecting error messages in a script
We need to filter out all the messages that are generally lighting up your screen. Data and messages are passed between PowerShell objects. These messages are passed in a different stream from data within a pipeline. There are several message streams, as follows, and we can redirect these streams to files, other streams or to devices:
- Success stream (
Write-Output
). This stream is used for passing objects through the PowerShell pipeline. The Success stream is connected to thestdout
stream for native applications. Redirected by1>
or>
- Error stream (
Write-Error
) This stream is connected to thestderr
stream for native applications. Under most conditions, these errors can terminate the execution pipeline. Redirected by2>
- Warning stream (
Write-Warning
). Redirected by3>
- Verbose stream (
Write-Verbose
). Redirected by4>
- Debug stream (
Write-Debug
). Redirected by5>
- Information stream (
Write-Information
). Redirected by6>
To suppress a stream, in this case the information stream, you just assign it to $null
…
1 2 3 4 |
&{ Write-Host "Hello" Write-Information "Hello" -InformationAction Continue } 6> $null |
Alternatively, to save it to file, you provide a filename/path. If you want to save it to another stream, you merely do this 3>&1
(saves the warning stream to the success stream). You can combine all streams into one with *>
.
Flyway only uses the Standard input stream, Standard output, and standard error. We can gather up all the Standard error stream messages that come from Flyway by using this method, and send them to our notification system, or to whatever output stream is most appropriate.
If it is a ‘catchable’ error, we can also execute the Flyway command in a Try…Catch
block and extract just the exception message of the error. This will prevent some of those cases where an entire Flyway migration is put into the error message, and it allows the script to send the message even if it is a terminating error. Here is the routine that catches the error, but we haven’t specified the notification yet:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
$ErrorCatcher="$env:TEMP\MyErrorFile$(Get-Random -Minimum 1 -Maximum 900).txt" try{ flyway info "-url=Error" 2>"$ErrorCatcher" } catch { @" $($error[0].Exception.Message) $($error[0].InvocationInfo.PositionMessage) "@ >$ErrorCatcher } # and then we can see if there is an error message in the $Errorcatcher $TheErrorMessage = ''; if (Test-Path $ErrorCatcher -PathType Leaf) # an error message file there { $TheErrorMessage = get-content $ErrorCatcher -Raw; del $ErrorCatcher # delete the temporary file }; if (![string]::IsNullOrEmpty($TheErrorMessage)) # woah! an error message { #execute whatever handler you've passed to the routine. First we create the message &$ErrorNotifier "In $pwd $TheErrorMessage" # At this point we send an error message to whatever notifier is selected throw "In $pwd $TheErrorMessage" #generally you'll want to halt execution there }; <# If it is a terminating SQL error, then Flyway rolls back the migration and terminates Flyway with the error message. This is caught by the try...catch loop which then 'throws' the error to abort the script. If it isn't terminating, the error just goes into the 'Red' error stream. #> |
The Error Notifier: specifying the notification method as a scriptblock
We want the same error handler to work for any type of notification system. We’re doing quite a lot of manipulation of data streams in one routine, so we only want to write it, and maintain it, once.
We’ve chosen to create our error notifier as a scriptblock that determines how the error is notified for any alerting system. It does whatever we specify, whether it be to send the error to a log file, notification system, or pager. If the notification system changes, we just pass to our error handler the scriptblock that is appropriate for that system.
First, we create the notification function that will send webhook notifications to our notification system, Synology Chat:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
function Send-SynologyChatMessage { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string]$webhookUrl, [Parameter(Mandatory = $true)] [string]$message ) # Create a PowerShell object with the message content $messageObject = @{ text = "$message" } # Convert the payload object to JSON $messageJson = $messageObject | ConvertTo-Json $postParams = @{ payload = $messagejson } $Result=Invoke-RestMethod -Uri $webhookUrl -Method POST -ContentType "application/json" -Body $postParams } |
Now we just use it to create our error notifier scriptblock:
1 2 3 4 |
$OurErrorNotifier={ param($TheErrorMessage) $FlywayChannel='https://<whereto>/Link' <# Our Flyway Channel is the URL endpoint for the webhook notifications, set up for synology chat. This will normally contain an authentication key #> Send-SynologyChatMessage $FlywayChannel "In $pwd $TheErrorMessage" } |
Now we’re ready for the final step, which is to pass this cmdlet as a parameter to our Do-AFlywayCommand
cmdlet.
Putting it all together: executing Flyway commands and sending error alerts
We can now create a PowerShell cmdlet that we can use to execute a Flyway project, running all required Flyway commands and operations, and then sensibly handling all the different streams of output. It will sift out details of any errors Flyway encounters and send them to our specified notification system. The choice of system depends on factors like team preferences, existing technology stack, and the specific use case. In many cases, organizations use a combination of tools to cover various aspects of communication and notifications in their database development and operations workflows.
Our cmdlet will use an $ErrorCatcher
, like the one we saw earlier but it will do more work in processing the output of the Flyway command, and then filtering and transforming different types of messages. It will catch and save any errors and then call a custom error handler, specified by a $ErrorNotifier
scriptblock, which will send the notification system, in our case to Synology Chat.
Here is the PowerShell wrapper, made into a Do-AFlywayCommand
cmdlet, for executing the Flyway commands, processing all the output, and sending any error notifications:
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 |
<# .SYNOPSIS Executes a Flyway migration using Flyway CLI, with the parameters you choose and with an optional path to a secrets file. It sends warnings to the warning channel, chatter to the -verbose channel and it sends errors either to the screen or to a function (e.g. webhook or logging) that you specify. .DESCRIPTION This function is used where you need to differentiate the noise (verbose) from the warnings and errors for long migrations. It is also handy if you need to store configurations in secure directories, when you get unexpectedly asked for passwords. It is designed to allow you to save the error messages, add them to a log or send them to a notification system .PARAMETER Parameters An options array of parameters that specify how Flyway should do the migration. .PARAMETER Secrets an optional path to a 'secrets' .conf file. Note that this routine does not allow Flyway to interrogate the user to provide a password. If it does so, it is provided with a dummy password .PARAMETER ErrorNotifier an optional powershell scriptblock that alerts your notification system of an error .EXAMPLE $Result= Do-AFlywayCommand @("-target=1.1.4") $Secrets -Verbose .EXAMPLE $Result= Do-AFlywayCommand info '' -Verbose .EXAMPLE Do-AFlywayCommand @("-url=AnUnusableURL") '' -ErrorNotifier $OurErrorNotifier .Example Do-AFlywayCommand -ErrorNotifier { param($TheErrorMessage) $FlywayChannel='https://OurWebhook/webapi/entry.cgi?api=MyApp' Send-SynologyChatMessage $FlywayChannel "In $pwd $TheErrorMessage" } #> function Do-AFlywayCommand { [CmdletBinding()] param ( [Parameter(Mandatory = $False, ValueFromPipeline = $true, HelpMessage = 'The parameters that specify what you wish Flyway to do ')] [array]$parameters = @(), [Parameter(Mandatory = $false)] [String]$Secrets = '', [Parameter(Mandatory = $false, HelpMessage = 'The scriptblock for hamdling errors ')] [scriptblock]$ErrorNotifier = { } ) $ExtraParameters = { $parameters }.Invoke() # get any extra parameters if (!([string]::IsNullOrEmpty($Secrets))) # if you've specified an extra .conf file { get-content $Secrets | foreach { $_.Split("`n") | where { ($_ -notlike '#*') -and ("$($_)".Trim() -notlike '') } | foreach{ $Extraparameters.Add(($_ -replace '\Aflyway\.', '-')) } } #pass these config lines as parameters $Extraparameters += "-placeholders.ParameterConfigItem=$Secrets"; } #create a temporary file with a random name to catch any errors $ErrorCatcher = "$env:TEMP\MyErrorFile$(Get-Random -Minimum 1 -Maximum 900).txt" try { echo "dummyPassword" |flyway $Extraparameters 2>"$ErrorCatcher" | Where { $_ -notlike '1 row *' } <#the oracle noise #> | foreach{ if ($_ -like '*(SQL State: S0001 - Error Code: 0)*') # SQL Server print statement noise <# we can put interesting chatter in the verbose stream and optionally read it #> { Write-Verbose "$($_ -ireplace 'Warning: DB: (?<BodyOfMessage>.+?)\(SQL State: S0001 - Error Code: [50]0{0,5}\)', '${BodyOfMessage}')" } elseif ($_ -like 'WARNING*') # Some other Flyway warning { write-warning ("$($_ -ireplace 'Warning: (?<BodyOfMessage>.*)', '${BodyOfMessage}')") } elseif ($_ -match '(?m:^)\||(?m:^)\+-') # result of a SQL Statement { write-output $_ } else { write-verbose $_ } } } catch { @" $($error[0].Exception.Message) $($error[0].InvocationInfo.PositionMessage) "@ >$ErrorCatcher # make sure that we catch a terminating error } $TheErrorMessage = ''; if (Test-Path $ErrorCatcher -PathType Leaf) #have we anything in the error catcher file { $TheErrorMessage = get-content $ErrorCatcher -Raw }; if (![string]::IsNullOrEmpty($TheErrorMessage)) #if there really is something { &$ErrorNotifier "In $pwd $TheErrorMessage" #send it to our notification system throw "In $pwd $TheErrorMessage" #generally you'll want to halt execution there }; del $ErrorCatcher } |
Testing the system out
So, let’s try it out on the Pubs Oracle project. In my case, the Pubs databases are hosted in Oracle Cloud. The following script will use our Do-AFlywayCommand
function to run a series of Flyway commands on two separate branches of the project (main and develop). In each case, it will clean the existing branch databases and then rebuild them to the latest version, from the migration files:
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 |
<# set verbose #> $VerbosePreference="Continue" <# the location for our migration files #> $MigrationFiles=' <MyPathTo>' <# Define our error handler #> $MyErrorNotifier = { param($TheErrorMessage) $FlywayChannel='https://<MyDestination>/direct/webapi/entry.cgi?api=SYNO.Chat.External&method=incoming&version=2&token=%<mytoken>' Send-SynologyChatMessage $FlywayChannel "In $pwd $TheErrorMessage"} <# list the two projects we'd like to recompile #> @( @{ 'Name' = 'Develop Oracle Branch'; 'SecretsPath' = "$($env:USERPROFILE)\oracle_PubsOracle_Develop.conf"; #define where your Flyway config secrets should be for your database 'ProjectPath' = "$MigrationFiles\PubsOracle\Branches\Develop" }, @{ 'Name' = 'Main oracle Branch'; 'SecretsPath' = "$($env:USERPROFILE)\oracle_PubsOracle_Main.conf"; 'ProjectPath' = "$MigrationFiles\PubsOracle" } )| Foreach { #Make the project directory your current working directory cd $_.'ProjectPath' write-verbose "Checking the status of $($_.'Name')" <# list out the info #> Do-AFlywayCommand 'info' $_.'SecretsPath' $MyErrorNotifier write-verbose "Cleaning out the old $($_.'Name')" Do-AFlywayCommand 'clean' $_.'SecretsPath' $MyErrorNotifier write-verbose "Now making sure that $($_.'Name') is up to date" # do a complete migration Do-AFlywayCommand @('migrate') $_.'SecretsPath' $MyErrorNotifier } |
There are plenty of ways of trying out the notifications, by forcing an error, and it is satisfying to see the errors on your mobile phone, and in the notifications archive. If you are quietly confident, you can turn your ‘verbose’ preference on or off or do it as a parameter to the Do-AFlywayCommand
cmdlet.
You’ll see that this cmdlet allows all the Flyway commands, along with all the parameters you’d possibly want, either provided in a hashtable, a file or a string. It places the stdout
into more suitable output streams and allows you to specify the notification application to which you want to pass error messages.
You can execute ordinary Flyway commands. We’re passing the credentials in from a configuration file in the secure user area, but you can place password and uid in an environment variable instead, if you prefer.
Here is a simple PowerShell pipeline that executes each command in turn for a single Flyway project:
1 2 3 4 |
$LoginInfo="$($env:USERPROFILE)\Pubs_Main.conf" cd ' <MyPathTo>\Pubs' @('clean','info','migrate')| foreach {Do-AFlywayCommand $_ $LoginInfo $MyErrorNotifier -Verbose} |
As you can see, you could us this for all your Flyway projects merely by preparing a list feeding it into a pipeline that does the work:
1 2 3 4 5 6 7 8 |
@( '<myPathTo>\PubsPG\Variants\Raspberry', '<myPathTo>\\PubsPG\Branches\Develop\Variants\Raspberry' )|foreach{ cd $_; @('clean','info','migrate')| foreach {Do-AFlywayCommand $_ -ErrorNotifier $MyErrorNotifier -Verbose} } |
And we can sit on the sofa at home and admire the error messages on our iPhones:
Conclusions
I don’t generally think in terms of notifications when doing development work, but having tried it out, I’m convinced that it is useful for scheduled repetitive tasks that are ‘bedded down’ to the point that they generally don’t go wrong. For some reason, all scheduled tasks seem to go wrong in the end. It could be a bad update for one of the components, or it could be a punishment by the god of technology for being too complacent, but it is curiously relaxing to have a notification system in place for when it happens, especially if it is articulate to tell you why it has gone wrong.