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.
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 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:
1 2 3 4 5 6 7 |
SELECT VERSION, TYPE FROM flyway_schema_history WHERE installed_rank = (SELECT MAX (installed_rank) FROM flyway_schema_history WHERE VERSION IS NOT NULL AND success = 1); |
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):
1 2 3 4 5 |
SELECT TOP 1 VERSION, TYPE FROM flyway_schema_history WHERE VERSION IS NOT NULL AND success = 1 ORDER BY installed_rank DESC; |
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 forschemaVersion
. - ‘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:
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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
IF (Object_Id ('dbo.FlywayVersion') IS NULL) EXEC ('CREATE FUNCTION dbo.FlywayVersion() RETURNS @returntable TABLE (dummy char(1)) AS BEGIN RETURN END'); GO /* what we used to do before CREATE OR ALTER */ /** Summary: > This returns a one-row table that tells us what the current flyway version is, and what the previous one was. Author: Philip Hypotenuse Factor Date: Thursday, 8 September 2022 Examples: - Select * from dbo.FlywayVersion() - SELECT Current_Version, Current_Type, Previous_Version, Previous_Type, Last_Action_Type FROM dbo.FlywayVersion() Returns: > nothing **/ ALTER FUNCTION dbo.FlywayVersion () RETURNS @WhatHappened TABLE (Current_Version NVARCHAR(50) NOT NULL, Current_Type NVARCHAR(20) NOT NULL, Previous_Version NVARCHAR(50) NULL, Previous_Type NVARCHAR(20) NULL, Last_Action_Type NVARCHAR(20) NOT NULL) AS /*define our variables */ BEGIN DECLARE @Latest_Installed_Rank INT, @CurrentVersion NVARCHAR(50), @PreviousVersion NVARCHAR(50), @CurrentType NVARCHAR(20), @PreviousType NVARCHAR(20), @LastActionType NVARCHAR(20); /* we find out the last action taken, the version number and type*/ SELECT @Latest_Installed_Rank = installed_rank, @CurrentVersion = version, @CurrentType = type, @LastActionType = type FROM dbo.flyway_schema_history WHERE installed_rank = (SELECT Max (installed_rank) FROM dbo.flyway_schema_history WHERE version IS NOT NULL); -- check for result of REPEATABLE /* Now we assume that it was not an undo so we find out the version number achieved by the last forward migration */ SELECT @PreviousVersion = version, @PreviousType = type FROM dbo.flyway_schema_history WHERE installed_rank = (SELECT Max (installed_rank) FROM dbo.flyway_schema_history WHERE installed_rank < @Latest_Installed_Rank AND type NOT LIKE 'UNDO%'); /*oh. Complication it was an undo */ IF (@CurrentType LIKE 'UNDO%') BEGIN /* in this case the previous version was that of the last forward migration */ SELECT @PreviousVersion = version FROM dbo.flyway_schema_history WHERE installed_rank = (SELECT Max (installed_rank) FROM dbo.flyway_schema_history WHERE type NOT LIKE 'UNDO%'); /* in this case the current version is the one before the one in the entry that turned out to be an UNDO*/ SELECT TOP 1 @CurrentVersion = version, @CurrentType = type FROM dbo.flyway_schema_history WHERE Cast('/' + version + '/' AS HIERARCHYID) < Cast('/' + @CurrentVersion + '/' AS HIERARCHYID) ORDER BY installed_rank DESC; END; INSERT INTO @WhatHappened (Current_Version, Current_Type, Previous_Version, Previous_Type, Last_Action_Type) VALUES (@CurrentVersion, -- Current_Version - nvarchar(50) @CurrentType, -- Current_Type - nvarchar(20) @PreviousVersion, -- Previous_Version - nvarchar(50) @PreviousType, -- Previous_Type - nvarchar(20) @LastActionType -- Last_Action_Type - nvarchar(20) ); RETURN; END; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
DELIMITER // create or replace procedure FlywayVersion() BEGIN DECLARE vLatest_Installed_Rank INT; DECLARE vCurrentVersion VARCHAR(50); DECLARE vPreviousVersion VARCHAR(50); DECLARE vCurrentType VARCHAR(20); DECLARE vPreviousType VARCHAR(20); DECLARE vLastActionType VARCHAR(20); /* we find out the last action taken, the version number and type */ SELECT installed_rank, `version`,`type`,`type` INTO @vLatest_Installed_Rank, @vCurrentVersion, @vCurrentType, @vLastActionType FROM dbo.flyway_schema_history WHERE installed_rank = (SELECT MAX(installed_rank) FROM dbo.flyway_schema_history WHERE `version` IS NOT NULL); -- check for result of REPEATABLE ); /* Now we assume that it was not an undo so we find out the version number achieved by the last forward migration */ SELECT `version`, `type` INTO @vPreviousVersion, @vPreviousType FROM dbo.flyway_schema_history WHERE installed_rank = (SELECT MAX(installed_rank) FROM dbo.flyway_schema_history WHERE installed_rank < @vLatest_Installed_Rank AND `type` NOT LIKE 'UNDO%'); /*oh. Complication it was an undo */ IF @vCurrentType LIKE 'UNDO%' THEN /* in this case the previous version was that of the last forward migration */ SELECT `version` INTO @vPreviousVersion FROM dbo.flyway_schema_history WHERE installed_rank = (SELECT Max (installed_rank) FROM dbo.flyway_schema_history WHERE `type` NOT LIKE 'UNDO%'); /* in this case the current version is the one before the one in the entry that turned out to be an UNDO*/ SELECT `version`, `type` INTO @vCurrentVersion, @vCurrentType FROM dbo.flyway_schema_history WHERE INET_ATON(substring_index(CONCAT(`version`,'.0.0.0'),'.',4)) < INET_ATON(substring_index(CONCAT( @vCurrentVersion,'.0.0.0'),'.',4)) ORDER BY installed_rank DESC LIMIT 1; END if; SELECT @vCurrentVersion AS `CurrentVersion`, @vCurrentType AS `CurrentType`, @vPreviousVersion AS `PreviousVersion`, @vPreviousType AS `PreviousType`, @vLastActionType AS `LastActionType`; END; // DELIMITER ; CALL flywayVersion(); |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
create or replace function dbo.FlywayVersion() returns table ( Current_Version VARCHAR(50), Current_Type VARCHAR(20), Previous_Version VARCHAR(50), Previous_Type VARCHAR(20), Last_Action_Type VARCHAR(20) ) language plpgsql AS ' declare vLatest_Installed_Rank INT; vCurrentVersion VARCHAR(50); vCurrentType VARCHAR(20); vPreviousVersion VARCHAR(50); vPreviousType VARCHAR(20); vLastActionType VARCHAR(20); begin SELECT installed_rank, version,type,type INTO vLatest_Installed_Rank, vCurrentVersion, vCurrentType, vLastActionType FROM dbo.flyway_schema_history WHERE installed_rank = (SELECT MAX(installed_rank) FROM dbo.flyway_schema_history WHERE version IS NOT NULL); -- check for result of REPEATABLE ); /* Now we assume that it was not an undo so we find out the version number achieved by the last forward migration */ SELECT version, type INTO vPreviousVersion, vPreviousType FROM dbo.flyway_schema_history WHERE installed_rank = (SELECT Max (installed_rank) FROM dbo.flyway_schema_history WHERE installed_rank < vLatest_Installed_Rank AND type NOT LIKE ''UNDO%''); IF vCurrentType LIKE ''UNDO%'' THEN /* in this case the previous version was that of the last forward migration */ SELECT version INTO vPreviousVersion FROM dbo.flyway_schema_history WHERE installed_rank = (SELECT Max (installed_rank) FROM dbo.flyway_schema_history WHERE type NOT LIKE ''UNDO%''); /* in this case the current version is the one before the one in the entry that turned out to be an UNDO */ SELECT version, type INTO vCurrentVersion, vCurrentType FROM dbo.flyway_schema_history WHERE string_to_array(version, ''.'')::INT[] < string_to_array(vCurrentVersion, ''.'')::INT[] ORDER BY installed_rank DESC LIMIT 1; END if; return query -- SELECT version FROM dbo.flyway_schema_history where version is not null ORDER BY string_to_array(version, ''.'')::INT[] DESC LIMIT 1; Select vCurrentVersion ,vCurrentType , vPreviousVersion, vPreviousType, vLastActionType; end;'; |
We can call this by
1 |
SELECT * FROM dbo.FlywayVersion(); |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SELECT CASE WHEN ( -- check whether we are dealing with an UNDO Select type FROM flyway_schema_history WHERE installed_rank = (SELECT Max(installed_rank) FROM flyway_schema_history WHERE success = 1) ) LIKE 'UNDO%' /* IF so then we need to get the next version before the UNDO version */ THEN -- if it is an UNDO (select Version FROM flyway_schema_history where version is not NULL -- probably a pesky repeatable and success = true AND TYPE NOT LIKE 'UNDO%' AND installed_Rank<( /*the latest migration that is lower then the version quoted in the UNDO operation which is actually the version you are undoing */ select min(installed_rank) FROM flyway_schema_history where version = ( Select version FROM flyway_schema_history WHERE installed_rank = (SELECT Max(installed_rank) FROM flyway_schema_history WHERE success = 1 ) ) and success = 1 ) ORDER BY installed_rank desc LIMIT 1 ) ELSE -- then it is simple (Select version FROM flyway_schema_history WHERE installed_rank = (SELECT Max(installed_rank) FROM flyway_schema_history WHERE success = 1 ) ) END AS TheVersion |
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.