Phil Factor

2 January 2025

• Flyway
• Automation and workflow
• Configuration, Authentication

Using JSON Output to Track and Log Flyway Migration Activity

All the main Flyway commands now report their result via JSON output. This gives developers a standard way to gather critical data like version numbers for tasks such as naming scripts, schema models, or backups. To avoid any confusion, these artifacts must be identified by the database version that was used to produce them. For example, you can use a callback script to extract the schema version from the JSON output, use it to name a build file, and even insert a version comment into the script. The JSON output also contains other data that you can’t get anywhere else, such as the database type and database name.

This is also a great opportunity for developers to improve how they track database migration activity. For example, the JSON output from the migrate command provides almost a complete narrative of what happened during the migration. It includes the database name, initial and target schema versions, execution times, and warnings. It does not include the server, but we can plug that gap easily, by extracting the servername from the $ENV:FLYWAY_URL environment variable.

As a working example of these techniques, we’ll demonstrate how to gather data from the JSON output, supplement it with other configuration data, and use it to provide a simple, human-readable log of Flyway activity. If you’re more ambitious you can send the data to a notification or messaging system that can alert the development team to changes. If a developer sees a change affecting their workstream, they can investigate the full details, including which objects were modified and how, using the Flyway HTML reports.

Using the JSON output

If we run a Flyway command, such as migrate, the JSON output tells us what happened during the migration. If we can also read the environment variables that Flyway provides to a callback or script, we then have all the core information we need for any post-migration task and for reporting. We can, if needed, use Flyway placeholders to add other details to the message, such as the name of the project or who executed the Flyway process.

By clever design, the JSON output is sent to the output stream after the migration completes, but before the afterMigrate callback is executed, so all the details are readily available in the script session invoked by Flyway.

It is slightly frustrating that, in general, the Flyway settings and details are only available to callbacks, because it is sometimes more convenient to run processes separately in the chain. It this therefore very welcome to have JSON output for all Flyway commands. This is particularly the case with the current version number because backups, scripts, and models all need to know at least the current version number.

In previous articles, I’ve demonstrated all sorts of uses for Flyway callbacks, such as the afterMigrate__xxxx callback, for running essential development tasks after a migration completes. They are also handy for generating development activity logs!

Producing a Flyway activity log

I’ve shown in an earlier article how to use PowerShell to parse the JSON output from the info command to produce customizable migration reports: Using Flyway JSON Output in PowerShell for Reporting. Here, I’m going to aim for a simple stream of messages that describe the Flyway activity in a form that is immediately intelligible to any ordinary mortal. It is easier to scan a message if it is clear and has all the information you need to make a judgement, or to be sure of the context of the command, especially if there are any warning or failure messages.

It is easy to miss important information if it isn’t immediately clear, so it must be presented in such a way that it is unambiguous however weary you are when you read it. For example:

A successful 'migrate' operation of SQL Server host MyServer, database 'PubsMain' from empty to V1.1.1 ('Initial Build' ) on 12:18:51 PM Mon 30 Sep 2024 took 5 seconds and had no warnings

How does it work?

We save the command output to JSON using -outputType=json and redirect it to a file so it’s accessible to our callback script. We supplement the information that Flyway provides in the JSON output with the credentials and connection information, stored separately in a Flyway configuration file saved in the secure user area. The callback script uses all this information to construct the messages.

In this article, we write the messages to a simple log file, but it would be easy enough to extend it to send a message to your notification system. I’ve described how to do this in an existing article, Flyway Alerting and Notifications.

The activity log callback

The example callback script below produces a ‘bare-bones’ message like the one shown earlier, summarizing migration details including the initial and target schema versions, execution time, and any warnings. You can customize the message with other project-specific details, as required.

For the callback script to work, you need to specify a filename for the log ($ENV:NarrativeLog) and ensure the JSON output of the migrate command is saved to a file.

This is the text of the afterMigrate callback

If you are testing this callback, you’d be able to see each message by sending it to Write-Host rather than adding it to the end of the message file. If you wish to send it to your messaging service, you’d redirect it as appropriate. I’ve described how you might do it in Flyway Alerting and Notifications.

The demo

To demo this in action, we’ll build the development version of our ‘extended’ Pubs database. It simulates a batch of several migration runs to test the routines out and, with our callback in place, we will generate a log of the migration activity.

The connection and authentication details are extracted from a config file, stored in the user area, using the Add-FlywayEnvironmentConfigItems routine that I’ve described in a separate article: Making Full Use of Environment Variables for Flyway Settings. In summary, we let Flyway determine the correct settings and just update these with the ‘database environment’ connection settings. The script includes basic error handling to capture migration issues. For example, if Flyway encounters an error during migration, it populates the JSON output with an error message and code. You can log these details or use them to trigger alerts (see Flyway Alerting and Notifications).

If we take the database up to version 1.1.15 in three different operations, using the previous code, we get the following text report. In this case, everything worked so we don’t see any errors.

A successful 'migrate' operation of SQL Server host MyServer, database 'PubsMain' from empty to V1.1.1 ('Initial Build' )  on  12:18:51 PM Mon 30 Sep 2024 took 5 seconds and had no warnings
A successful 'migrate' operation of SQL Server host MyServer, database 'PubsMain' from V1.1.1 to V1.1.2 ('Pubs Original Data' )  on  12:19:27 PM Mon 30 Sep 2024 took 10 seconds and had no warnings
A successful 'migrate' operation of SQL Server host MyServer, database 'PubsMain' from V1.1.2 to V1.1.5 ('UseNVarcharetc'  to 'Add New Data')  on  12:20:06 PM Mon 30 Sep 2024 took 40 seconds and had no warnings
A successful 'migrate' operation of SQL Server host MyServer, database 'PubsMain' from V1.1.5 to V1.1.10 ('Add Tags'  to 'AddAddressesPhonesEtc')  on  12:21:43 PM Mon 30 Sep 2024 took 29 seconds and had no warnings
A successful 'migrate' operation of SQL Server host MyServer, database 'PubsMain' from V1.1.10 to V1.1.13 ('AddProcedureWithTest'  to 'Accounting')  on  12:22:58 PM Mon 30 Sep 2024 took 57 seconds and had no warnings
A successful 'migrate' operation of SQL Server host MyServer, database 'PubsMain' from V1.1.13 to V1.1.15 ('AccountingData'  to 'SearchFunctionality')  on  12:24:55 PM Mon 30 Sep 2024 took 1 mins, 27 secs and had no warnings

You will, I’m sure, want to change the wording or provide additional information such as the name of the project. Flyway can provide any number of placeholders for this type of information

Conclusion

The addition of JSON output for Flyway commands opens numerous possibilities for reporting, logging, and alerting. It also enables the automation of other database processes through Flyway callbacks. In this article, we demonstrated how to use the JSON output to generate a human-readable log of activity for each change in the Flyway schema version. Only the migrate and clean commands alter the database version: migrate by applying migrations and clean by resetting the database and removing versioning information.

There’s even more you can do with Flyway’s JSON output. It gives us the option of running a chain of post-migration commands once Flyway has done its work because we now have all the details we need to do so. Several commands are likely to be required after a successful migration run. By incorporating a routine to save the current Flyway settings that I introduced in Making Full Use of Environment Variables for Flyway Settings, we can now add to the connection details, and other configuration settings, the version number of the database, the name, of the database, the server and the database type. With this information, we can tackle even the thorniest of database development tasks such as bulk-loading the data and kicking off the integration test.

We get to use all the post-Flyway callback routines that I’ve covered in previous articles but bypass the problems of working out the settings that Flyway is using when it is reading a complex TOML configuration involving several alternative ‘environments’.