• Flyway
• Database Versioning
• Variants, Localization

Maintaining a Utilities Schema in a Flyway Project

By putting utilities in a separate schema, it keeps them away from the application, and we can then use a separate Flyway project to manage just the diagnostic objects in this ‘util’ schema, and to install and maintain the latest utilities on as many databases as you like. You might want to use a single Flyway project to manage the utilities for every installation of every database on a given RDBMS. It can be done!

What are utilities?

Utility functions, views or procedures aren’t part of a database application but would be concerned with reporting on, or maintaining, a database. They merely extend the support provided by the RDBMS. Typically, they would include ways of checking growth, investigate caching and index usage, reporting on users and their activities, monitoring waits, observing and fixing sessions, checking for possible intrusions, and so on.

Generally, you want to keep these diagnostic objects separate from the database code, which explains why monitoring and diagnostic tools tend to avoid creating them, and instead use batches to get the information they need. However, this can get very unwieldy for anyone having to query the database interactively to see what is going on, or to resolve deadlocks. Many diagnostic processes rely on having a function, and perhaps an associated table, to do the work.

It is generally considered more useful to have all the ‘utility’ functions and procedures that are associated with a project in a separate schema. Of course, before considering a separate utility schema, you must ensure that the code in your utilities doesn’t reference the objects within the database directly. Without care, you can end up with utilities that are entirely dependent on the version of the database, usually because they assume a particular composition of tables or routines. Conversely, if they are designed sensibly, without direct references to database objects, you can even use the same utilities schema for different databases.

Managing a utilities schema using Flyway…

These utilities aren’t such an intrinsic part of the database that they should be versioned with the database. For a start, you are unlikely to develop your handy diagnostic routines at the same time, or in the same sprint, as your databases. They are usually stored in a separate schema and are maintained and deployed with a different set of migrations from those used to maintain and deploy the database. After all, if they are useful for this database, they’ll be of use for other databases too.

You would normally only want to deploy the latest version of any utility. If, for example, you improve a utility that checks the database for blocking, you’d want the latest version of the utility to work with all current versions of the database, retrospectively, especially if it amounted to fixing a bug.

So, the question is: how would you maintain such a utility schema as part of a Flyway project so that we can run the migrations needed to the latest version of the utilities to all versions of the database, but without affecting the version of the database? For this task, Flyway offers two approaches: use a separate Flyway project, based on the ‘utilities’ schema, or use repeatable migrations.

…Using a separate Flyway development project

The easiest way of creating and maintaining these utility functions is using Flyway migration versioned scripts. However, if we manage these versioned scripts from the same Flyway project that we use to manage the database objects, then updating the utilities would amount to a DDL change and so would update the version of the database. This would create a false alarm if, for example, you added or updated a utility after a release candidate was created.

Fortunately, we can have more than one Flyway project on the same database, just as long as they occupy different schemas, and assuming that the RDBMS that you are using supports schemas. All the major RDBMSs provide ways of partitioning a database into schemas.

Flyway understands the ‘scope’ of a project, for a given database, from the list of schemas that you provide in the Flyway configuration file (Flyway.conf). You can find out a lot more about Flyway configuration in A Programmer’s Guide to Flyway Configuration but, briefly, you provide a comma-separated, case-sensitive list of schemas that it must manage. Unless you specify otherwise, Flyway assumes that the first schema in the list is the default schema, and that is where it will store its schema history table. Flyway will manage the full lifecycle of all the schemas that you specify, meaning that it will create these schemas in a database, if they don’t exist. When you run the Flyway Clean command, it will drop any schema that it created, in the order of this list of schemas.

A database within an RDBMS can easily host more than one set of schemas, and therefore more than one Flyway development. Therefore, a ‘utilities’ schema can be managed by a separate Flyway development, independently of the database objects, with a different set of migrations. This isn’t enforced, though, just allowed.

A word of caution: despite causing eye-watering confusion, it would be possible for the two Flyway development projects to interfere with each other’s code by referencing each other’s schemas in DDL code within a migration, without Flyway knowing.

..Using Repeatable migrations and callbacks

The alternative to migrating the Utilities schema from a separate Flyway project, is to migrate it from the same Flyway project that manages the database, using a Repeatable migration script.

Repeatable migrations are un-versioned migrations. They have a description and a checksum, but no version. Instead of being run just once, they are applied every time their checksum changes. They are always applied last, at the end of a migration run. If there are more than one, they are applied in the order of their description. When managing objects in a Utilities schema, the repeatable (R) migration script would consist of the SQL DDL ‘built or alter/replace’ code to build every object in the schema. The only advantage of using a Repeatable migration to run the build script, rather a SQL afterMigrate callback, is that there will be a record of their use in the Flyway schema history table

Like the build code in the afterMigrate callback, the code in the Repeatable should be idempotent, meaning that it should produce the same result whatever the current state of the Utilities schema. In the case of the callback, the build code will still be executed, even if it isn’t necessary. Nothing suffers wear in consequence, of course, but it adds to the time for a migration.

In neither case will the Utilities schema be cleaned or created independently You could, where necessary, clean it by adding it to the list of schemas in the project, but that would clean the database too! The Utilities schema cannot easily be updated outside of a migration of the database.

Deploying a utilities schema from a separate Flyway project

Every method has its conveniences, but I prefer to use the system of having a separate utils schema for the database, managed by a separate Flyway project, so that’s the option I’ll demonstrate here.

I need to maintain fifteen database copies, located across three different servers, which is enough for the demo. We want to deploy the latest utilities to every copy of the database, whatever its version, and we want to update all the databases in one go. Actually, maintaining this number of different databases would be extremely tedious to do any other way!

In the following, simplified depiction we run the Utilities Flyway project on two databases: PubsTest, which is at V1.1.1 and has no util schema, and PubsDev, which is at V1.1.3 and has an existing util schema at V1. Both databases end up with a util schema at the latest version (V2) without affecting the overall version of the database.

In this solution, which uses my Flyway Teamwork PowerShell framework, we access all these databases from the same working directory, so we can put the project-level flyway.conf file in this directory and specify the locations for the code that is executed just once. We will be using a consistent name for this utilities schema: util.

If you want to try this out, I’ve provided a sample utilities GitHub project. The migrations folder contains some sample utility functions, split across a couple of migrations. It includes a TheFlywayVersion function for Finding the Version of a Flyway-managed Database Using SQL, and some of Glen Berry’s wonderful collection of SQL Server Diagnostic Queries.

The list of databases

In a typical Dev environment, you are likely to want to add or update a utilities schema to a number of databases at once. We start by making a list of all these databases, with their connection information. We can then use this list for doing the migration, whenever we add or change a utility.

As luck would have it, this util schema is for SQL Server. This means that I just need to provide the URL, user and password. The url config item is a connection string that contains the server and database.

Since this list will have my login and password details (if you’re using Windows credentials, you should be able to leave these blank), I need to store it in the user area and encrypt it so only I can access it. If you have a more complicated set up, then more information would need to go in this array but the aim here is to keep things simple.

When you run the following code, it converts the PowerShell array, containing the credentials, to JSON, encrypts it and saves it in a FlywayConf directory, within the secure user area, with the name utilsEncrypted.json.

Migrating utilities to the latest version for every database

Now, with this list of databases, we can run whatever Flyway tasks we need. Here, we do a Flyway info to check what version the util schema is at, for each database:

In my example, two of the databases don’t yet have the util schema and one does:

Schema version: 1.1.1
+-----------+---------+------------------------------+--------+---------+----------+
| Category  | Version | Description                  | Type   | State   | Undoable |
+-----------+---------+------------------------------+--------+---------+----------+
|           |         | << Flyway Schema Creation >> | SCHEMA | Success |          |
| Versioned | 1.1.1   | Basic Utilities              | SQL    | Success | No       |
+-----------+---------+------------------------------+--------+---------+----------+
Schema version: << Empty Schema >>
+-----------+---------+-----------------+------+--------------+---------+----------+
| Category  | Version | Description     | Type | Installed On | State   | Undoable |
+-----------+---------+-----------------+------+--------------+---------+----------+
| Versioned | 1.1.1   | Basic Utilities | SQL  |              | Pending | No       |
+-----------+---------+-----------------+------+--------------+---------+----------+
Schema version: << Empty Schema >>
+-----------+---------+-----------------+------+--------------+---------+----------+
| Category  | Version | Description     | Type | Installed On | State   | Undoable |
+-----------+---------+-----------------+------+--------------+---------+----------+
| Versioned | 1.1.1   | Basic Utilities | SQL  |              | Pending | No       |
+-----------+---------+-----------------+------+--------------+---------+----------+

If we simply swap the info command for a migrate command, in the above code, all three database get the latest version of the util schema:

Creating schema [util] ...
Creating Schema History table [PubsDev].[util].[flyway_schema_history] ...
Current version of schema [util]: null
Migrating schema [util] to version "1.1.1 - Basic Utilities"
Successfully applied 1 migration to schema [util], now at version v1.1.1 (execution time 00:01.609s)
 
Creating schema [util] ...
Creating Schema History table [pubsone].[util].[flyway_schema_history] ...
Current version of schema [util]: null
Migrating schema [util] to version "1.1.1 - Basic Utilities"
Successfully applied 1 migration to schema [util], now at version v1.1.1 (execution time 00:01.528s)
Creating schema [util] ...
Creating Schema History table [pubstwo].[util].[flyway_schema_history] ...
Current version of schema [util]: null
Migrating schema [util] to version "1.1.1 - Basic Utilities"
Successfully applied 1 migration to schema [util], now at version v1.1.1 (execution time 00:01.582s)

By adding a migration file to the location specified in the Flyway.conf file, the util schema can be changed to add, delete or modify the utilities.

Running utilities inside or outside Flyway

Once they are installed, you may want Flyway to run one or two of these utility routines to check the database, after a migration. However, more often they are likely to be run from outside Flyway, such as by a monitoring application.

Within Flyway, successful execution of certain utilities may rely on use of Flyway placeholders to provide runtime values for certain parameters. For example, the utilities include a SQL function, TheFlywayVersion that returns the database’s Flyway schema version (knowing this version number is essential when doing diagnostic reports). As another example, your utilities might include one to check the effectiveness of indexes. It uses a view utility to get all the tables for index analysis, with a WHERE clause to exclude the Flyway schema history table.

However, the name and location of the flyway history table isn’t necessarily fixed, even for a project so Flyway relies on placeholders to provide the schema and name of the flyway schema history table. When calling the function or view from outside of Flyway, from your monitoring application, or perhaps from SSMS or another IDE, the calling routine wouldn’t have access to these placeholders. You might think it would be simple to just pass the name and location of the Flyway Schema history table to the function, but it isn’t entirely straightforward, and liable to be unsafe to do so. You are likely to just get a SQL error if you try to do it. Procedure, yes possibly, function no.

To cater for either eventuality (executing the utilities from inside or outside Flyway), the calling routine can simply include a check of how the code is being run. In this example, the calling statement passes the relevant information from the Flyway schema history table as a JSON document (a SQL Server-specific solution). This is only practical if the amount of data is small but, in our case, the flyway_schema_history table isn’t that large, and we only need a very few columns from it. Flyway can use the placeholders to locate the table, but if we’re not running within Flyway, it extracts the JSON data directly from the table instead.

The code is here:

The code tests to see if the built-in placeholders had been substituted. This will only happen if you are executing under Flyway. If running under Flyway, only the right-hand side of the expression will be recognized as a parameter. As it is a default parameter, Flyway substitutes the value for the key. Otherwise, the two expressions will evaluate to the same string.

Of course, this relies on us knowing where the schema history tables is located, and its name, for that project. This can’t be avoided unless you store the version number of a schema elsewhere, such as in an extended property (for SQL Server).

Here is the more general form of ‘Flyway detection’ to show how the calling routine would detect whether it is running in Flyway:

Conclusion

We have created a util schema in several databases, and managed migrations to the schema, so we can keep them updated. Flyway will manage just the schemas in the list you provide, though it will create, alter or delete objects in other schemas within the same database.

Flyway works most simply if you can establish the required configuration for a single project in a flyway.conf file in the current working directory. However, if you are working with PowerShell or Java, it is simple to get Flyway to sequentially process a whole list of schemas in databases, possibly on different servers. A list like this will have UserIDs and passwords in them, so I’ve shown how to keep the list secure in an encrypted JSON document in a Windows environment, so that only the current user can access it.

I’ve also demonstrated how utilities would be able to report the Flyway version number even when running the code directly from a monitoring program, and demonstrated a way of checking in SQL Code whether the code is running within Flyway.

Tools in this post

Flyway

DevOps for the Database

Find out more