• Flyway
• Database Deployments
• Automation and workflow

Piping, Filtering and Using Flyway Output in PowerShell

When you are using Flyway, there are many advantages to driving Flyway from within a script, to provide an automated system. However, if you use Flyway ‘raw’ at the command-line, in a Shell script or in PowerShell, you’ll probably get an overwhelming output, some of which is just noise (e.g., ‘1 row updated’, repeated a thousand times). It will be painfully easy to miss information we do care about, such as the warning messages that Oracle sends to tell you that a procedure didn’t compile, or output from migrations or callbacks that describe what it did.

Flyway outputs text a line at a time to the standard output stream (STDOUT). I’ll show how to use PowerShell to direct different types of output to the correct stream and filter out the most meaningful parts, with the ability to pass on useful details as an array of data objects that another process in the pipeline can use. I’ll illustrate how to:

1. Filter Flyway’s verbosity so that you don’t miss an important warning – by directing the output you want to the correct stream and the rest to the “verbose” stream.
2. Get the results of SQL queries in callbacks or migrations into a useful format – one that allows you to process them or save them in JSON, XML, CSV or YAML. This means that, for example, a SQL callback that documents a database can now return a result that tells the application or developer exactly what it did.

I use PowerShell as the scripting language with Flyway, though the principles I demonstrate apply to any other scripting languages that support ‘piping’.

Although Flyway Teams will allow some limited modification of the error handling and reporting, the Flyway Community version assumes that the script that is using Flyway will deal with output, warnings, information and results. This article will be useful to users of either version of Flyway.

Filtering Flyway’s output into streams

The script that runs Flyway, or any CLI tool, needs where possible to intercept messages, and pass them on via the correct output stream. This way, depending on the type of message we receive from the CLI tool, we can opt to abort a process, or send the message to a log, or display it or even just throw it away.

Unfortunately, there are only two console outputs in DOS, stdout (1) and stderr (2), whereas one ideally needs more for a smooth interface. With PowerShell we have more options. We can write to several different logical streams, the of which most useful are:

• Output stream (Write-output) – STDOUT is the default output stream for Flyway’s output, which can be processed line-by-line as it arrives from Flyway.
• Error stream (Write-Error) – Flyway will handle errors as such and dispatch them via the STDERR stream of the Command line tool. These arrive in the error stream and are placed in PowerShell’s $Error automatic global variable.
• Warning stream (Write-Warning) – a warning from Flyway will, if put in this stream be handled by PowerShell as a warning and displayed in scary orange.
• Verbose stream (Write-Verbose) – these are only displayed if you want to see everything that is happening, by setting the verbosity level for the cmdlet or script. The verbose stream is the normal output destination for all those preliminary messages that Flyway puts on-screen, unless you wish otherwise.

There is also a Debug stream that asks you whether you want to proceed on every line, an Information stream that is bizarre and mystical, and a Progress stream that is rather special purpose of telling you how far you are in a long process.

Using these various streams, we can make life easier, by keeping information out of sight, but we can opt to save messages, where we wish to diagnose faults or anomalies. We can easily divert warnings and verbose information and send SQL messages, such as the SQL Server PRINT statements, to the appropriate stream.

We can assume that the rest is background chatter and put it in the verbose stream, except for the output of Flyway info (the schema history table), and the results of queries in a migration or callback, which you might need to save as JSON. Lastly, there are values sent from Flyway such as the version of the database at the end of a migration, and the JDBC connection string being used, both potentially useful for post-migration tasks.

Flyway isn’t a full participant of a pipeline because it cannot take its input from a pipeline, but it passes each line of text to the Standard output stream. All you need to do is to put Flyway at the start of a pipeline. Every line that is output by Flyway is then passed through the pipeline so you can filter out the things you don’t want and emphasize the things you do want.

For example, when Flyway (and most RDBMSs) outputs a warning, each line will start with the string ‘WARNING:‘, which we can send to a warning stream. This can be useful, not only to alert you to a failed creation of a stored procedure in Oracle, but also for logging. Similarly, we can divert SQL Server PRINT statements, incorrectly categorized as warnings by Flyway, into the verbose stream so we can turn them off, if necessary, just by changing the verbosity setting (we could, in Teams, opt to prevent the ‘WARNING:‘ prefix and error codes using error overrides):

You can put a list of whatever parameters you wish in a string array to pass to the function. The cmdlet assumes that you’ve properly installed Flyway with a Path declared in the path environment variable.

Note: I’ve since expanded on this idea to produce the Do-AFlywayCommand cmdlet, described in Flyway Alerting and Notifications, which you can use to execute any Flyway project. It will run the required Flyway commands and operations, process all the different streams of output and send any errors to your specified notification system.

Reporting on the outcome of callbacks or migrations

Having done this, you’ve probably thought, “Hey, that’s neat, but wouldn’t it be nice to be able to pick up the ASCII table that Flyway sometimes issues and save it for reporting or pass it along the pipeline?“.

It would indeed. When you issue a Flyway info, you get a table output containing details of the applied and pending migrations. It is useful for ‘eyeballing’, and you can access the values in the results programmatically very easily, by opting for JSON output in Flyway using -outputType=json. I’ve explained how to use PowerShell to do some useful reporting from this JSON output in an earlier article: Using Flyway JSON Output in PowerShell for Reporting.

Similarly, you might have noticed that if you put a SQL Query into your migration script in Flyway, the results are returned in the STDOUT output stream, in the form of an ASCII table. Here is an example in the standard Pubs Migration files I use as an example script. The ASCII table is embedded in spurious warning messages caused by PRINT statements:

WARNING: DB: Now at the inserts to discounts .... (SQL State: S0001 - Error Code: 0)
WARNING: DB: Now at the inserts to jobs .... (SQL State: S0001 - Error Code: 0)
WARNING: DB: Checking identity information: current identity value 'NULL'. (SQL State:
 S0003 - Error Code: 7989)
WARNING: DB: DBCC execution completed. If DBCC printed error messages, contact your sy
stem administrator. (SQL State: S0001 - Error Code: 2528)
WARNING: DB: Now at the inserts to employee .... (SQL State: S0001 - Error Code: 50000
)
Migrating schema [dbo] to version "1.1.3 - UseNVarcharetc"
WARNING: DB: Dropping foreign keys from [dbo].[sales] (SQL State: S0001 - Error Code: 
0)
+-------------------------------+-----------+--------------+-----------+--------------
----+------+------------------------+-------------------------+-----------------------
--+---------------+--------------+---------------------+
| name                          | object_id | principal_id | schema_id | parent_object
_id | type | type_desc              | create_date             | modify_date           
  | is_ms_shipped | is_published | is_schema_published |
+-------------------------------+-----------+--------------+-----------+--------------
----+------+------------------------+-------------------------+-----------------------
--+---------------+--------------+---------------------+
| FK__sales__stor_id__1273C1CD  | 466152756 |              | 1         | 50151274     
    | F    | FOREIGN_KEY_CONSTRAINT | 2023-02-27 08:37:27.423 | 2023-02-27 08:37:27.42
3 | 0             | 0            | 0                   |
| FK__sales__title_id__1367E606 | 482152813 |              | 1         | 50151274     
    | F    | FOREIGN_KEY_CONSTRAINT | 2023-02-27 08:37:27.43  | 2023-02-27 08:37:27.43
  | 0             | 0            | 0                   |
+-------------------------------+-----------+--------------+-----------+--------------
----+------+------------------------+-------------------------+-----------------------
--+---------------+--------------+---------------------+
WARNING: DB: Dropping constraints from [dbo].[sales] (SQL State: S0001 - Error Code: 0
)
WARNING: DB: Dropping constraints from [dbo].[authors] (SQL State: S0001 - Error Code:
 0)

Unfortunately, when you use -outputType=json with a migrate or undo, these extra results don’t currently get returned as JSON but are still returned as an ASCII table in the standard STDOUT stream.

Now, getting results from a process in a SQL Callback, or even sometimes from a migration, into JSON would be mighty useful. So, what we need is a way to parse an ASCII table into the equivalent PowerShell object, which we can then convert to JSON, or to whichever type of output we require. If there is more than one table, then it will return an array of PowerShell objects.

We can add this functionality to the pipeline by means of a pipeline-aware function called ConvertFrom-AsciiTable. I’ve added this to the resources directory in the Flyway Teamwork project. Its input can come from a variety of sources, such as a file, a command output, or user input. In our case, it’s input will be whatever is sent to Flyway’s default output stream, after running a migration plus some callbacks.

To try this out, I used an existing SQL callback called afterMigrate__ApplyTableDescriptions.sql that updates the table documentation after a Flyway migration. I describe how it works in my previous article, Transferring Database Documentation into SQL DDL Scripts. The entire callback is a bit too long to show in this article but I’ve set up a Feedback variant of the development branch of the Pubs project on GitHub as a playground for trying this out, and you can find the full script there.

Whenever this callbLack adds or updates table documentation it now saves details of what it did to a table variable called @auditTable. At the end of the callback, a query will report what it had to do, which is sent to the standard output stream where we can grab it:

So now we can try the two PowerShell Cmdlets together. If there is only one table that flyway returns it is as simple as this…

If you want to see the verbose messages, you simply add the -verbose parameter to the Do_A-Migration cmdlet.

Naturally, you can use any of the ConvertTo- cmdlets, so you can convert the $result object to CSV, XML, YAML, JSON or whatever.

It will read as many ASCII tables as go down the pipeline. We’ll simulate this by passing two ASCII results, as sent from the Do_A_Migration cmdlet.

Notice that you have to specify the array element to get the result when there is more than one, so that, instead of using $Resultset, we now use $Resultset[0] to get to the first element and $Resultset[1] to get to the second. This allows you to have as much feedback from you migrations as you want.

So now, you can get and save information from any callbacks you have. This means that you can run tests in a migration or callback and pass the results back for saving or processing. Suddenly, you have a new way of getting information from Flyway.

Conclusion

Although I’ve been using a PowerShell framework that aims to make it easy to run PowerShell-scripted callbacks, Flyway can also be used as part of a more completely scripted migration. In such cases, it really helps to provide a cleaner and neater interface where you can opt out of the scrolling lines of text that Flyway write to the standard output, and that make it so easy to miss important warnings. With PowerShell’s ability to deal with saving of output streams separately, they can always be saved to a file just in case.

To get to a simple distraction-free way of managing Flyway, it is helpful to get as much information as possible about what is going on in the migration, and the ideal way is to use the excellent feature of Flyway, being able to pass back ASCII tables of SQL Queries or SQL Callbacks that are made as part of a migration.

Tools in this post

Flyway

DevOps for the Database

Find out more