Product articles Flyway Database migrations
Finding the Version of a Flyway-managed…

Finding the Version of a Flyway-managed Database Using SQL

Maintaining a version of a database opens a lot of possibilities, especially if an automated process can easily grab the current version, at runtime, using just SQL. You might, for example, have a routine that is only appropriate after a particular version. It is also very handy to be able to associate entries in an event log or bug report with the database version. No more desultory quests, when dealing with support issues, or when bug fixing, to find which database version was running when the bug happened.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

What’s my database version?

When using Flyway, the built-in mechanism is to get the current version 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. If this was dead easy to do, then this would be a very short article but…

…It ain’t always that easy

The first complication you have is that the name and schema of this table isn’t fixed. 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. This is fine if you are executing your SQL in Flyway because you can, and should, use Flyway placeholders for the name location of the table, and these then get filled in automatically by Flyway. 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.

However, if you if you want to change the table name or schema in an existing project then you need to be careful, because it isn’t straightforward. Flyway doesn’t handle it well because it can’t directly detect a change in any of its settings.

The second complication is that migrations aren’t always upwards. You can, with undo migrations, devolve to a previous version, meaning that if your SQL simply reports the version number for the record in flyway_schema_history with the highest installed_rank and with success=1, then if the last migration was a successful undo, you’ll grab the wrong version. The version recorded in the row of the table won’t be the version that was the result of the undo, but the version that was undone. You need to work out name of the previous version.

In fact, Flyway Teams also gives you several different types of migration, such as a ‘repeatable’, which leaves the current version as NULL. Useful? No. The SQL to get the logic correct to obtain the current and previous version in those circumstances isn’t that straightforward – at some point, you’ll need to compare or order version numbers, which is not a datatype that is supported in SQL.

Ask Flyway?

Right, you think, this is getting tiresome, so why not instead just 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 SQL callback.

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

the current schema version of a database in flyway

The problem here is that Flyway has no placeholder value for this schema version. You have to run flyway info -outputType=json, grab the JSON output, and read the schemaVersion value from this JSON document to then stamp the database with the version number.

Fine, 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 telling you the info. 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.

What would make this all very easy would be the availability of an ephemeral ‘schemaVersion’, for use with placeholders in the ‘after’ callbacks such as afterMigrate, afterUndo, afterInfo, afterRepair and afterVersioned. However, the development team at Redgate haven’t got around to adding this to Flyway Teams yet, so it isn’t.

To work around this limitation in my Flyway Teamwork framework for PowerShell, I had to provide a generic routine, $GetCurrentVersion, which was usable across all supported databases (see Cross-RDBMS Version Checks in Flyway). It can calculate the version that was the result of an undo or repeatable migration, within PowerShell, because it can compare version numbers. Fine if you are using my framework, otherwise not. No, in that case, you need the SQL.

Getting around all the complications in SQL

We’ll choose to create a SQL function or procedure to do this. This can effectively solve the problem of working out where the history table is if you create or update the procedure within a Flyway migration. It will allow you to pick up the config items you need, both schema and table name, as placeholder values.

How difficult can it be to get some values from a table in SQL? Essentially, if we know we never have anything like an Undo or Repeatable migration, and we only want the current version, then it couldn’t be simpler:

You can just make the last line to ‘true’ rather than ‘1’ for PostgreSQL. Or, slightly more variable across RDBMS but with only one access of the table (use LIMIT instead of TOP with other RDBMSs):

However, 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

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:

Reporting the flyway version in SQL

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

Reporting the flyway database version after an undo

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

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, uses 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. Other than that, it is fairly straightforward. Database projects usually have a separate utilities schema, but in thing 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 similar to 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 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, 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.

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.

Tools in this post

Flyway

DevOps for the Database

Find out more