• Flyway
• Automation and workflow
• Tracking development changes

Using Flyway JSON Output in PowerShell for Reporting

When you run a Flyway command line task, such as a database migration, you get screens full of information that seem of little use to mortals. Some of it shows the configuration used to make the connection, which isn’t particularly interesting, and the table format is for glancing at rather than parsing into a table or using in a spreadsheet. This may be frustrating when you need to know whether the task was successful, and if not find out why not. Which database was it? At what version was the database left? What warnings or information messages were there, what is the contents of the history?

Flyway logging, by default, sends all debug, info and warning output to stdout, but all errors are sent to stderr. You can write this to a file by adding -outputFile=MyPathTo/output.txt to the argument to also write output to the specified file. This is only applicable to the command line.

What makes this useful in a scripting language is that Flyway is easily persuaded to output all this information as a JSON document. You can do this in v7 onwards using the outputType=json to the argument list to print JSON instead of human-readable output (previously, the command line option was -json from v6.1.0). This cannot be specified from the configuration.

Here is a sample:

If you select JSON output, arrays of errors are included in the JSON document instead of being sent to stderr.

Arrays of warnings are also included. Details of migrations are included as JSON arrays of objects.

For PowerShell, this suddenly makes life a lot simpler. We can consume the output either as JSON or convert it to a PowerShell object.

Getting a Flyway migration report from the JSON output

As an example, we’ll use the sample project PubsAndFlyway, using the SecondMigration example. I’ve already done a complete migration on a database called PubsThree. Here’s our parameters in an array called $FlyWayArgs which we then splat against Flyway to provide us with a basic report constructed from the JSON output:

We get the following report:

category  version description                 type installedOn             state  
--------  ------- -----------                 ---- -----------             -----  
Versioned 1.1.1   Initial Build               SQL  2021-02-12 16:42:01.633 Success
Versioned 1.1.2   Pubs Original Data          SQL  2021-02-12 16:42:02.707 Success
Versioned 1.1.3   UseNVarcharetc              SQL  2021-02-12 16:42:05.323 Success
Versioned 1.1.4   RenameConstraintsAdd tables SQL  2021-02-15 15:39:52.32  Success
Versioned 1.1.5   Add New Data                SQL  2021-02-15 15:46:08.123 Success
Versioned 1.1.6   Add Tags                    SQL  2021-02-15 15:46:12.65  Success

Name            Value    
----            -----    
allSchemasEmpty False    
database        PubsThree
flywayVersion   7.3.1    
operation       info     
schemaName               
schemaVersion   1.1.6

The most interesting value to get is the current version of the database, which is accessible via …

… if you don’t have an error and you use flyway info. It isn’t available from flyway migrate. As well as getting a report, you might just want a nicely-presented list of the migrations and their details. To get this use …

Getting database migration runtimes

As you can see, this gives you most of the information you could need. You can get more from the schema history table, though, such as the minimum and maximum time a migration took and the total time.

Count    : 6
Average  : 63190.8333333333
Sum      : 379145
Maximum  : 366717
Minimum  : 755
Property : executionTime

This can be made more meaningful with formatting:

Sum               Max               Minimum           Ave              
---               ---               -------           ---              
06 mins, 19 secs. 06 mins, 07 secs. 00 mins, 01 secs. 01 mins, 03 secs.

Limiting reports to certain dates or versions

You can limit the range of reporting of the Flyway history table that is returned by the info action, if it begins to get rather large, by using some additional parameters. Dates must be specified in the date format, dd/MM/yyyy HH:mm

• infoSinceDate: only the successful migrations applied after this date, (but all unapplied migrations).
  Example: -infoSinceDate="07/02/2021 14:00"
• infoUntilDate: show only migrations applied before this date
  Example: -infoSinceDate="07/02/2021 14:00"
• infoSinceVersion: Limits info to show only migrations greater than or equal to this version, and any repeatable migrations.
  Example: -infoSinceVersion="1.1.3"
• infoUntilVersion: only migrations less than or equal to this version, and any repeatable migrations.
  Example: -infoUntilVersion="1.1.6"
• infoOfState: Limits info to show only migrations of a particular state that is specified (pending, applied, success, undone, outdated, future, missing, deleted, ignored or failed.)
  Example: -infoOfState: ="success"

Ad-hoc reports using report files

If you specify both options, to produce output as json and to save it to a file (-outputFile = MyPathTo/ReportFile) then you get both the StdOut and the file (if you leave out the -outputType=json switch, you get text which will cause an error with the PowerShell cmdlet that converts from JSON to a PowerShell object).

This is probably the most useful way to configure it because you can go back and do ad-hoc reports if you have saved the JSON files.

The PowerShell Reporting Script

Here is a PowerShell script that you can use to experiment with ways of formatting the output. You’ll find that different actions produce differences in the reports. I’ve simply outputted the details as objects, but you will see that is very easy to get values such as the version of Flyway, the schema version, or the time taken from them:

…which gives, on my machine:

Tools in this post

Redgate Flyway

DevOps for the Database

Find out more