Phil Factor

11 January 2022

• Flyway
• Tracking development changes
• Team-based Development

Reporting on the Progress of a Flyway Database Development Project

It is tempting to get a bit vague when reporting on the progress of a database development project. In fact, though, I’m an enthusiast for copious reporting. It reveals, quickly, what is and isn’t going on for the team. If you make all the documentation available on an internal Wiki, then it also makes it more difficult for anyone in the deployment chain to use the hackneyed defense ‘You never told me that you were doing x‘ (where x can stand for almost every database change). As a bonus, I’ve always found that the quickest way to subdue senior managers is to drown them in a tsunami of reporting. It is the project leader’s equivalent of the Vulcan Nerve Grip.

When I say that I am a reporting enthusiast, I should add the rider ‘only if it can be automated‘. When required to write conventional documentation I am, like all developers, gripped with an extraordinary and inexplicable torpor, as if drifting off into semi-consciousness. It is only the idea that I can automate the task that gives me the necessary mental stamina to do it.

How to plot the progress of a Flyway project

How can Flyway help the busy database developer keep track of development progress? Well, it has the Flyway Schema History table. For each database managed by Flyway, this table keeps a record of what happened and when. If you maintain a strict one-to-one correspondence between the folder(s) of migration files and the branch database (e.g., Production, Develop, Bugfix, and so on) then you have, in the Flyway Schema History table, a complete history of changes and who made them.

We can extract this data and present it in a Gantt Chart, for example:

You will notice that I’ve plotted the time it took to develop and apply each migration. It is the elapsed time between the previous and current migration (i.e., the “end” time for V1.1.1 migration is the “start” time for the v1.1.2 migration, and so on).

I’ve also shown who installed each version. The name that appears as a resource (‘Phil Factor’ in my case) is taken from the installedBy parameter, not the database user_ID. Unfortunately, this project was done just by me so, we can’t do much with the ‘resources’ information. If you want to keep a check on resources, generally, you’ll need to be sure that each user has this value set appropriately, in the flyway.conf file within their user directory.

I produced this Gantt chart by generating it in SQL (I’ll show the code shortly). It extracts the data from the Flyway History Table and then transforms it into PlantUML (PUML) code. The resulting source (.puml) for the above Gantt chart is as follows:

It is best to review the Gantt Diagram page of the PlantUML site for the explanation of this script. They are adding useful extensions regularly.

Using this script as the source, we can then draw the chart using plantumlc.exe, or by using a PUML editor such as ATOM. There are online resources to help with the latter, but I just use plantumlc.exe, especially as this allows me to decide the format, such as PDF.

Generating the Gantt Chart

There are two main approaches to getting to this information and turning it into a PUML script like this: you can use the RDBMS-specific SQL to query the table, or you can use the slower RDBMS-independent approach of using the Flyway info command to get the information (Flyway does additional checks) and presenting it in JSON form for processing.

Using SQL to generate the PUML

We’ll start with the RDBMS-specific way. This query is for SQL Server.

This will produce the PUML script I showed earlier. You should use ‘Results to text’ (Ctrl T) in SSMS if you wish to generate the PUML directly from the query. For the sake of this article, you can then use the PlantUML online demo server to render it into the Gantt chart. Simply paste in the code and hit “Submit“.

Using PowerShell to generate the PUML

The alternative RDBMS-independent approach is to execute a Flyway Info command that specifies JSON output. We can then use a script to create the PUML. In the interests of avoiding repetition, I’ll leave it to you as how you get those Flyway args into a hashtable but I’ve already covered this in several articles. Note that this code actually calls Flyway so using it in certain callbacks will cause blocking, but it is safe to do after the end of a ‘run’ (e.g. ‘afterVersioned’), because the transaction for the migration will have ended.

Conclusions

A Gantt chart showing the migration run, and who did what, is a good place to start with reporting. I like to add a report on what changed at every version, but I’ve already shown how to do that in Reporting on Changes Made by Flyway Migration Scripts. In another article, I’ll show how to embed this code in a PowerShell script and in a Callback for Flyway Teams.

PlantUML is useful because it is well-maintained by enthusiasts and does ER Diagramming, documenting JSON, and a whole range of other useful tasks as well as Gantt charts. I haven’t mentioned graphs. These are easily generated too and are very handy for the more complex database performance tuning work because it makes it much easier to detect performance changes that affect more than one metric. PlantUML doesn’t support this but there are many graphing packages that do.