• Flyway
• Database Versioning
• Deployment checks and safeguards

Find the Version of a Flyway-managed Database

first published:13 September 2022

If you are scripting a process in Flyway, you can get most of the information you need from environment variables. You get the connection details, credentials and your custom placeholders, for example. However, you can’t get the most essential fact, the current schema version of the database, from an environment variable. This is rather frustrating.

After all, if you are using callback script to back up the current version of database before migrating it, or to generate a build script after a migration run for the new version, you need to know what version to put in the filename of the backup file or build script. If you need to see what’s been changed by a migration, you will probably need to know not only that version but the previous one too. Alternatively, you might have a routine that it’s only appropriate to use after a particular version, or on a specific range of versions. It is also useful to be able to associate entries in an event log or bug report with the database version.

For all these reasons, it is very handy to know the schema version in a callback or migration script, and occasionally useful to be able to get that value in SQL too. So how do we get it?

Asking Flyway for the database schema version

When using Flyway, the built-in the definitive source of the current version is the current schema version deduced from the Flyway schema history table, which also records the history of the changes, who did them, how long they took, and the type of change.

Before we start, I’ll say that the best solution, in my view, would be for Flyway to make available an ephemeral schemaVersion default placeholder, which we could use with the ‘after’ callbacks such as afterMigrate, afterUndo, afterInfo, afterRepair and afterVersioned. You’d just add the placeholder to your SQL code and Flyway would perform the substitution before running the code. In a PowerShell or DOS script, you’d read the environment variable.

Unfortunately, they don’t provide this. However, until then, here’s your options to get the current schema version from Flyway.

Read the ASCII output of Flyway info

If you run flyway info, you’ll see the current schema version hidden in plain view just above the migration history table:

In fact, Flyway provides two returned values in its output, one of which is the schema version, and the other is the connection string. If all we need is the current version, we can get the latest schema version from Flyway and use that value to stamp the database with its version number, using a database comment or extended property. Yeah, good idea, especially if you can do this in a Flyway post-migration callback.

To get just the current version, you can just read the value with a regex match on the output (hoping that the format Flyway uses for its ASCII feedback doesn’t ever change): In this code, I’m working in the project directory and I’m reading a flyway CONF file with my credentials in it and I’ve saved the path to this file in $Credentials:

It works, but if you’ve got a few migration files, it gets slow because it calculates the checksum of all the migration files before running the info command

Also, if you try this trick in an AfterEach… callback or any other callback that gets called within the transaction used for the migration, then the system locks up entirely due to mutual blocking until you kill one of the sessions, and thereby the connection.

This means that, for any process that is making reference to the current version, and that version is changing (imagine the time soaked up just validating your migration files over and over again).

Read the JSON output

Instead of trying to read the ASCII output, you can run flyway info -outputType=json, to persuade it to write the information out, grab the JSON output, and read the schemaVersion value from this JSON document:

As well as the schema version, you get some other useful information such as the list of schemas, the list of migrations and the name of the database

The problem here is that you still need to run Flyway Info every time you need an up-to-date version. There is now a solution is that the latest Flyway versions now keep a similar file to this, the report.json, up to date every time it performs a migration. The bad news is that it also writes a huge report.HTML with nothing useful in it for our purposes

Use the Flyway JSON report

The Flyway CLI now supports a -reportEnabled parameter, which when set to true will mean Flyway produces a JSON report when the version changes. This is easy to read via a procedural script such as Bash or PowerShell, though inaccessible to a SQL script.

Flyway saves the report.json in the current working directory, on every change, not just on running the info command. The downside is the added clutter of a report.html. To get around that, I put a flyway.reportEnabled=false in my user profile (It is now false by default) and add a -reportEnabled=true parameter whenever I need to get hold of the version.

Assuming this value is updated by all the Flyway actions, we now know the version just by reading this JSON file. The only problem with this is that it tells you just the current version, not the previous one, and there are times, such as when detecting changes, when you need this information.

Use the Flyway Teamwork framework

To work around all the limitations, I decided to provide a generic PowerShell routine, $GetCurrentVersion, which was usable across all supported databases (see Cross-RDBMS Version Checks in Flyway). It’s available as part of the Flyway Teamwork framework for PowerShell. It can calculate the version that was the result of an undo or repeatable migration, within PowerShell, because it can compare version numbers.

Getting the schema version in SQL

If none of the previous options suit, then you’ll need a reliable way to retrieve the schema version using SQL. You can get the current version in a SQL script by querying the flyway schema table directly.

Again, though, there are a few complications

Getting the name and location of the schema history table

The first complication that you have when using SQL is that the name and schema of this table isn’t fixed. To do this query, you’ll need to know the name and schema of the Flyway Schema History table. Unfortunately, this isn’t set in stone. When you first create a Flyway project, you can either accept the default name and location, or you can change either setting in the project-level configuration (if you want to change the table name or schema in an existing project then you need to be careful, because it isn’t entirely straightforward).

Since you are executing your SQL in Flyway (e.g. in a SQL callback or a script migration), you can, and should, use Flyway placeholders for the name location of the table, and these then get filled in automatically by Flyway before running the SQL script. You can now easily place, in your database, a routine such as a procedure that gets the version from whatever name and schema location you’ve specified for the table previously known as flyway_schema_history.

These placeholders are provided by:

• ${flyway:defaultSchema} – the default schema for Flyway
• ${flyway:table} – name of the Flyway schema history table

So, in a Flyway SQL Script, these placeholders will allow you to do a query that will give you the current version of the database

Migrations aren’t always upwards

If you only ever use versioned (V) migration scripts, and never Undo or Repeatable migrations, and you only want the current version, then getting the value from SQL couldn’t be simpler:

However, this isn’t accurate under all circumstances. In many Flyway projects, migrations aren’t always upwards. Repeatable migrations have no version and leave the current version as NULL. In Flyway Teams projects, you can also use undo (U) migrations to reverse to a previous version. These U migrations have the same version number as their V file counterpart, so the schema version returned by the previous queries won’t be the version that was the result of the undo, but the version that was undone.

The SQL logic to obtain the current and previous version in those circumstances isn’t straightforward. You might assume that you need to compare or order version numbers, which is not a datatype that is supported in SQL. Since there is a reliable incrementing key, the installed_rank, it is possible to avoid that, but the results aren’t entirely pretty.

If you are using SQL, then you can get a direct substitution of the actual version if you can provide this as a placeholder. Before Flyway executes a SQL migration, it sees whether it can swap out any of the placeholders, either the built-in ones or the custom ones, with their current values. You would have to maintain this value with a callback routine that checked the version after a migration.

Reliably reporting the schema version using SQL

To deal with the problem of undo migrations, where the result is to revert to the previous version, we need to be rather cleverer. The easiest approach is to use a procedure or, preferably a function because MySQL/MariaDB doesn’t support batches with variables and conditional blocks/control structures. It also allows us to compile a routine that records the location of the schema history table, so we can use it independently from Flyway. We can’t use this approach with SQLite but can provide something that works when executed by Flyway, and this will work with all major relational databases.

While we’re about it, we’ll collect the previous version as well as the current one. There is no way of knowing this version in an automated process otherwise because versions just need to be ordered, but without any fixed increment. It is also handy to know how we got to this version, so we ought to return that. We’ll aim to provide the following:

• ‘Current Version’ – this must always match the value that Flyway info reports for schemaVersion.
• ‘Current Type’ – the type of migration that produced the current version (SQL, Java etc.)
• ‘Previous Version’ – the version that the preceding migration in the chain produced.
• ‘Previous Type’ – the type of migration that got to the previous version
• ‘Last Action Type’ – the type of action that produced the current version

This extra information can be useful. I use it to help to determine what was changed by the last migration run. Here is an example. I took the Pubs database up to version 1.1.12 (in SQL Server, in this example). The function reported this:

Then, I did an undo run all the way back to version 1.1.7. This time, the function reported this…

This tells us that we slid right back down from version 1.1.12 to 1.1.7 via an UNDO_SQL migration run.

Basically, you need to read the Flyway schema table to work out which was the final migration. If, upon investigation, it wasn’t an undo or repeatable migration, it is easy. You just read the version number and calculate the previous one. Otherwise, it is not easy.

• Repeatable – if the last migration was a repeatable, the current version will be NULL because there was no version number specified in the entry for the repeatable file. In that case, we look back until we find the row with the last versioned migration and hold onto that.
• Undo – if the last migration was an undo, the version number it provides is the version that Flyway undid, not the current version. To find out what the previous version was, we must find what version was the result of the last successful forward migration. This isn’t easy because an undo chain could have happened.

All the following code examples, for each of the RDBMSs, use the raw table name, rather than the Flyway version. Every mention of dbo.flyway_schema_history must be replaced with ${flyway:defaultSchema}.${flyway:table} if you are going to run it from Flyway. I’ve left these with the default name and schema just so you can try it out in your favorite IDE.

SQL Server

In SQL Server, we will need to force the version number into a hierarchy datatype. This will allow us to compare and sort by version number. Database projects often have a separate utilities schema, but in this example, I’ve put the function in the dbo schema of the database, as there is no common standard for its location.

So, there we have it. What about MySQL/MariaDB?

MySQL/MariaDB

We’ll leave out the comment block as it is rather repetitive. Here, the only datatype that seems to be useful for sorting versions is the internet address datatype (INET_ATON). There is no table-valued function in MySQL, so we use a procedure instead.

PostgreSQL

The PostgreSQL version converts the version into an array and does a comparison on those lines. In principle, it is like the MySQL/MariaDB version except that it works as a table-valued function.

We can call this by:

SQLite

You’d have thought that, because SQLite has no way of comparing or ordering version numbers, has no IF…THEN…ELSE logic, no variables or procedures, then it would be impossible. OK, let’s make a small compromise and return just the version number. If we allow ourselves to make several queries into the history table, then it is reasonably easy.

You might look at this and think ‘why don’t we just do this for all the RDBMSs?’ The hitch is that it must be executed by Flyway if you aren’t using the standard name of the table. Also, it is quite long and slightly opaque, and if you were to provide all the information of the other versions, it would be even longer, and it is already hitting the flyway_schema_history table up to six times. Yes, it is a worthwhile compromise, but it is still a compromise.

Roundup

If you are using a script, the easiest way to get the current version is to use flyway info, but this isn’t always possible when you are using a callback script, and it can be painfully slow. Flyway now keeps this information current in the report.json file for the project, so it can easily be read from there.

If you just want to find the current version of a flyway database from SQL within a Flyway SQL script, or you don’t want a function, then the code for SQLite will work with minor modifications for all the RDBMS I’ve covered (SQL Server uses TOP rather than LIMIT. PostgreSQL insists on success being true rather than 1, because Flyway defines the Success column for PostgreSQL as being Boolean rather than bit).

You’ll also need to replace flyway_schema_history with ${flyway:defaultSchema}.${flyway:table}. Remember, though, that the current name of the flyway table is available to a script or callback run by Flyway, it is not guaranteed to work outside Flyway, unless you can find a way of knowing for certain the name and schema of the flyway history table. This is easily obtained by any callback script that just writes the information to a file. This information, along with much else, is presented to you by my Flyway Teamwork framework.

Hopefully, I’ve given you a few solutions that should work for you to find out the version number of your flyway database via SQL, Dos Batch, Bash or PowerShell.

Tools in this post

Redgate Flyway

DevOps for the Database

Find out more