Phil Factor

30 May 2022

• Flyway
• Database Documentation
• Database Testing and Quality

Taming Database Documentation with Flyway and MySQL

The database documentation, in the sense of comments attached to database objects and attributes, isn’t really part of the metadata of a database. However effective the comment is in shedding light on the reason for a table or column, it doesn’t affect the working of a database in any way. For this reason, adding documentation to a database object shouldn’t change the version of a database. It should never be part of a database comparison. This implies that we should not add database object documentation directly to build scripts or the migration scripts. It must be done separately; but how?

If you have a separate script that applies the documentation, you can use it as a Flyway ‘callback script’ and Flyway will apply it automatically at the end of a migration run. You merely name the script according to the Flyway specification and it applies it to the database.

Maintaining documentation for a SQL Server database

I’ve already provided an example of this way of maintaining JSON-based documentation for a SQL Server database. It uses a SQL-based callback called afterMigrate__ApplyTableDescriptions.sql that applies a documentation to a database, and a task script block called $ExecuteTableDocumentationReport, within DatabaseBuildAndMigrateTasks.ps1, to generate a JSON document of the current documentation of tables and columns, with blanks where more documentation is needed.

In the original SQL Server version, I chose JSON because I found it easy to update the information in the JSON document, and SQL Server can easily read from a JSON document, and use the information to update comments, and to accommodate new tables as and when they are added to the project. Flyway doesn’t get upset if you change a callback. As I mainly use SQL Server, I’m a lot more conscious of the usefulness of comments. SQL Prompt can display them in tooltips. You can apply named comments to almost any database object or attribute, and these can be almost 8000 characters in length. I store structured documentation in YAML in them as well as simple strings.

Challenges of documenting other databases

I wanted to extend support for database documentation, within my Flyway Teamwork framework, to include other databases. Unfortunately, support for comments in other RDBMS is patchy because there is no SQL standard for a comment. The best one can hope for is to document tables and columns. MySQL and MariaDB can’t even add comments to views or the columns of views. SQLite knows nothing of comments but stores the original source of the tables and columns, including the SQL code comment blocks (/* */) and comments (–). This is fine up to a point, but extraction of these comments is a rather delicate operation involving Regex.

The most obvious way of showing the developers what tables and columns still need comments is to produce a report on every successful migration run listing all the tables and columns with blank strings where there are no comments. Then the developers can add the missing comments using an updated version of the afterMigrate__ApplyTableDescriptions.sql script, in time for the next migration. It is all very civilized.

The only cloud on the horizon for this JSON-based system is that relatively few RDBMSs can read JSON documents into a relational form, so if you need a standard format for documentation across RDBMSs, you’ll be disappointed.

If you are using Flyway community, then your callbacks must be in Java or SQL, not PowerShell. In Flyway Teams or Redgate Deploy, we can use PowerShell in callbacks so we can use this to deal with the JSON data and make the calls to the database to update the documentation far more simply.

A Flyway callback to add documentation to a MySQL or MariaDB database

Some head-scratching was needed before attempting the first cut of a MySQL/MariaDB version of the documentation callback. I decided that the simplest way of representing the documentation was via a multiline VALUES statement. This means editing the SQL when a table is added, or you wish to alter the comment. It isn’t that much more of a hassle but isn’t nearly as convenient as JSON.

The objective is to add a comment only when strictly necessary. This means that you need to see what comments are there already, and whether they are different and therefore have changed. This is more of a worry with the way that MySQL attaches or alters a comment to a column. The only way to do it is to present the entire column definition for the column for which you are altering the comments. If you get that process wrong, you can change the column definition and thereby trigger a table rebuild or worse. It is a scary process but there is no way around that.

So here is the afterMigrate__ApplyTableDescriptions.sql script for a MySQL or MariaDB database. It applies comments to both the table and its columns.

Reviewing the documentation

If you want to try this out, I’ve provided an example PubsMySQL project as part of my Flyway Teamwork framework. The above afterMigrate__ApplyTableDescriptions.sql callback script is included in the Migrations folder for this project so all you need to do is run a migration and the callback will automatically add the documentation specified by the script to the database objects.

We can then execute the $ExecuteTableDocumentationReport as a post-migration task and it should generate a documentation report (TableDocumentationReport.json) for each final version of any migration run and save it in the Versions/…/Reports folder of the project.

The resulting documentation should look like this:

Generating a build script with comments

If, having made your project your current working directory and executed ‘preliminary’, as shown in the previous script, you can also then execute this PowerShell to get a ‘build script’ for the current version, containing the DDL of all your tables complete with lots of informative comments:

Summary

We’ve illustrated a solution to the problem of applying comments easily to a database version in Flyway without changing its version. Comments should never represent a change to a database. They are there for ‘decoration’ because they don’t affect the workings of the database. A comment doesn’t change data or a process. Sure, they make a database easier to understand and maintain if they are done properly.

If you use structured comments, they can be very informative, and are useful in prompting the busy developer to provide all the necessary information, such as, in the case of a function, what it does, how to use it, and why, what all the parameters do and what the output is, who created it and when.

All that remains is to produce a version of this callback for all the relational database systems supported by Flyway!