Phil Factor

13 August 2021

• Flyway
• Tracking development changes
• Team-based Development

Discovering What’s Changed by Flyway Migrations

Normally, when using Flyway with SQL Server, I use SQL Compare to generate build scripts and to provide a ‘narrative’ of what changes are made with each migration. I value the ‘overview’ report of what metadata has changed. I can also use it to generates deployment scripts and synchronize the target schema with that of the source.

However, what if we can’t use SQL Compare, such as if we want to compare two versions of the same database, implemented on different database systems? In this article I’ll provide a set of PowerShell routines that will provide a high-level ‘narrative’ of any database changes that are recorded in the ODBC interface for the database.

It will only detect changes in the metadata of the tables, views, procedures and functions because these are recorded in the ODBC interface, and it can’t record changes in constraints. We also lose synchronization if we can’t use SQL Compare. However, although it isn’t perfect, it is sufficient to help application developers to gain an understanding of ‘what changed’ as a result of a Flyway migration.

Comparing two versions of a database, one on SQL Server, and one on PostgreSQL

As well as comparing two versions of a database on the same platform, it can compare them on different platforms, which would come in handy when you’re using Flyway to develop a single database on several database platforms such as SQL Server and PostgreSQL. By way of an example, I’ll compare a PostgreSQL version of pubs with the SQL Server version. In the Scripts folder of the PubsPostgreSQL project folder, on GitHub, you’ll find the scripts to create and populate the pubs database on PostegreSQL. I explained how to run them with Flyway in my previous article, Getting Started with Flyway Migrations on PostgreSQL.

To perform our “diff”, we need two ODBC connections: one to the Postgres Server and pubs database ($connpsql), and the other to SQL Server and the pubs database there ($connSServer). Of course, you’ll need to ensure 64-bit ODBC drivers are installed for both SQL Server and PostgreSQL (you can use Application Stack Builder to install it, for the latter), and then set up DSNs for each one.

In my GitHub Powershell Utility Cmdlets, you’ll find the Get-ODBCSourceMetadata cmdlet, which extract the source metadata from each database, and Diff-Objects, which runs the comparison. When you compare the two databases, one on SQL Server, and the other on PostgreSQL, you can see that there are plenty of differences, but they are mostly cosmetic or the way that the ODBC drivers were implemented, but it is interesting to be able to check, and I wrote this purely because I needed it and I’ve never come across a tool that does this comparison! You’ll see in the SQL Server version (the source) there is an extra procedure I’ve put in for making UML database diagrams. It is, of course, missing in the other!

To run the diff, just load the two cmdlets in PowerShell (just execute them both, if you are using the PowerShell ISE), and then the diff is produced simply by executing the following PowerShell. Notice that you don’t need the User ID (uid) or password (pwd), if you are using integrated security or if you’ve set up the password within the DSN.

You can, of course filter on the comparison to get just the ones that are the same, or the differences. You can also filter for ones only in the source or in the target. You can opt to filter out awkward tables such as Flyway’s flyway_schema_history table.

The ‘match’ column is there so you can filter the report to tell you what is different between the source and target (<>), what is the same (==) only in the source (<-) and only in the target (->) or which can’t be compared.

Getting a narrative of changes made by a Flyway migration on PostgreSQL

Getting a narrative of changes in each version of a database is always useful in a database migration, but getting this narrative is trickier for a migration than for a build script. Basically, after every successful migration we just save the object ‘model’ of the database as a JSON file. We can then ingest this into a PowerShell script to compare any two object models to tell us what has changed. This can be made relatively automatic via a simple addition to Flyway. In this case, however, there are so few migration steps that we can do it by hand.

We can use any of the PowerShell scripts from my previous article Getting Started with Flyway Migrations on PostgreSQL, with any necessary ($MyArgs or $Credentials) modifier to provide parameters to Flyway. The following script is in the PubsPostgreSQL project folder, on GitHub, here). Just change the Flyway path, $ProjectFolder, $DSNName and $versionpath as you require. You’ll also need to make sure the correct port is defined in your DSN.

You’ll see, in the final block comment, the report of the changes made in version 3. It is just telling you where the changes are. Where it simply says Object[], it means that there is a list of objects there, indexes in our case. In the fifth line, it could well be that it was just the simple addition of an index.

Naturally, if you want a narrative that makes more sense to a manager, you add this to the end of the previous code, and more besides, to sharpen it up.

…which produces (in our case) this report:

This is just to get you started with ideas! The source for the Diff-Objects Cmdlet and Get-ODBCSourecMetadata Cmdlet are on my GitHub repository ‘Powershell Utility Cmdlets’.

Inspecting the current metadata at any version

To see what is in the metadata after each migration, you just do something like this:

You’ll see I’ve specified version 1.1.3 in the $VersionPath directory path we previously defined. We are just reading in the JSON file and converting it to a PowerShell object before displaying it. The source for the Display-object cmdlet is here.

Summary

Flyway is best seen as a JDBC database migration utility that can be used with a wide range of databases that have suitable JDBC drivers.

The ODBC/JDBC interface is far more than a ‘connection’. It obliges the publisher of the JDBC/ODBC driver to provide the metadata of the database that is visible to the user who makes the connection. This allows all manner of application/database interfaces to be created in a device-independent way. In our case, we just want to know what metadata changes have been made by a migration, or set of migrations, and what tables, views, functions and procedures exist, and in what state, in any version. It is also very useful as a means of providing a narrative of changes from version to version and is an effective supplement to any documentation. Because the script that I’ve shown allows you to compare any two versions, it can yield an overall summary of the transformations made by a whole set of migrations, as a summary.