• Flyway
• Automation and workflow
• Database Deployments

Flyway Alerting and Notifications

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 the stdout stream for native applications. Redirected by 1> or >
• Error stream (Write-Error) This stream is connected to the stderr stream for native applications. Under most conditions, these errors can terminate the execution pipeline. Redirected by 2>
• Warning stream (Write-Warning). Redirected by 3>
• Verbose stream (Write-Verbose). Redirected by 4>
• Debug stream (Write-Debug). Redirected by 5>
• Information stream (Write-Information). Redirected by 6>

To suppress a stream, in this case the information stream, you just assign it to $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:

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:

Now we just use it to create our error notifier scriptblock:

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:

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:

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:

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:

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.

Tools in this post

Flyway

DevOps for the Database

Find out more