Discovering What’s Changed by Flyway Migrations
A set of PowerShell cmdlets that will 'diff' two versions of a database and provide a high-level overview of the major database changes made by successive Flyway migrations. You can 'diff' a SQL Server database to the same one on PostgreSQL and find out which objects are the same and which are different.
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.
|
1 2 3 4 5 6 7 8 9 10 |
$connSServer = new-object system.data.odbc.odbcconnection $connSServer.connectionstring = "DSN=MySQlServerDSN;" $connpsql = new-object system.data.odbc.odbcconnection $connpsql.connectionstring = "DSN=MyPostgreSQLDSN;" Diff-Objects ` (Get-ODBCSourceMetadata $connSServer -WantsComparableObject $true) ` (Get-ODBCSourceMetadata $connpsql -WantsComparableObject $true) Diff-Objects ` (Get-ODBCSourceMetadata $connSServer -WantsComparableObject $true) ` (Get-ODBCSourceMetadata $connpsql -WantsComparableObject $true) |
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.
|
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 82 |
#tell PowerShell where Flyway is. You need to change that to the correct path for your installation Set-Alias Flyway 'C:\ProgramData\chocolatey\lib\flyway.commandline\tools\flyway-7.8.1\flyway.cmd' -Scope local #specify the DSN, and create the ODVC connection $ProjectFolder = 'S:\work\Github\PubsAndFlyway\PubsPostgreSQL' $ErrorActionPreference = "Stop" $DSNName = 'PostgreSQL' $ConnPSQL = new-object system.data.odbc.odbcconnection $ConnPSQL.ConnectionString = "DSN=$DSNName"; $ConnPSQL.Open() #now use the information stored in the DSN to create the JDBC connection string for Flyway. $parameters = (Get-OdbcDsn -name $DSNName).KeyValuePair | where { $_.Key -in @('Database', 'Servername', 'Username', 'Port', 'Password') } | foreach{ @{ $_.Key = "$($_.Value.Trim())".Trim() } } @('Database', 'Servername', 'Port') | foreach{ if ($parameters.$_ -ieq $NULL) { Write-Warning "the DSN didn't have the $_ " } } if ($parameters.Username -ne $null -and $parameters.Password -eq $null) { Write-Warning "the DSN didn't have the password " } $versionPath = "$($env:USERPROFILE)\Documents\PolyGlotPubs\$DSNName\" #we create a path to where we store the model and reports for each version #This is separate from the project source as it is ephemeral $TheArgs = @("-locations=filesystem:$ProjectFolder\Scripts", <# the migration folder #> "-schemas=dbo,people") $TheArgs += if ([string]::IsNullOrEmpty($parameters.Username)) { @("-url=jdbc:postgresql://$($parameters.Servername):$( $parameters.Port)/$($parameters.Database);integratedSecurity=true") } else { @("-url=jdbc:postgresql://$($parameters.Servername):$($parameters.Port)/$($parameters.Database)", "-user=$($parameters.Username)", "-password=$($parameters.Password)") } #if this works we're probably good to go Flyway @TheArgs info #now we clean the database and do a new run of the migrations Flyway @TheArgs clean # Drops all objects in the configured schemas for the Pubs database <# now we iterate through the versions we've actually achieved, and after doing each migration successfully, we can then store a model of the metadata as a JSON file to disk in the $versionpath we've just defined #> @('1.1.1', '1.1.2', '1.1.3', '1.1.4', '1.1.5', '1.1.6') | foreach{ $Version = $_; #first we check that the report directory exists for this version. if (-not (Test-Path -PathType Container "$:versionPath$Version")) { # does the path to the directory exist? # not there, so we create the directory $null = New-Item -ItemType Directory -Force "$:versionPath$Version"; } #Fine, so we do the migration Flyway @TheArgs migrate -target="$Version" # Migrates the database to a particular version if ($? -or $LASTEXITCODE -eq 1) { # we get a report of the metadata and save it as a json file that we can inspect #at our leisure and use as the basis of comparisons. Get-ODBCSourceMetadata $connpsql -WantsComparableObject $true | ConvertTo-JSON > "$:versionPath\$Version\Model.json" } else { Throw "migration at level $version failed" } } #Now we can gnerate a report for every migration but the first $PreviousVersion = $null; # @('1.1.1', '1.1.2', '1.1.3', '1.1.4', '1.1.5', '1.1.6') | foreach{ $Version = $_; if ($PreviousVersion -ne $null) { #see what is changed by comparing the models before and after the migration $Comparison = Diff-Objects -Parent 'Pubs' <#get the previous model from file#>` ([IO.File]::ReadAllText("$:versionPath$PreviousVersion\Model.json") | ConvertFrom-JSON)<#and get the current model from file#> ` ([IO.File]::ReadAllText("$:versionPath$Version\Model.json") | ConvertFrom-JSON) | where { $_.match -ne '==' } $Comparison | Format-Table > "$:versionPath$Version\Changes.report" $Comparison | ConvertTo-JSON > "$:versionPath$Version\Changes.json" #we can do all sorts of more intutive reports from the output } $PreviousVersion = $Version; } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
@('1.1.2', '1.1.3', '1.1.4', '1.1.5', '1.1.6') | foreach{ $Version = $_; $changes = ([IO.File]::ReadAllText("$:versionPath$Version\Changes.json") ) | ConvertFrom-JSON $changes | foreach{ $current = $_; switch ($current.Match) { '->' { "$(if ($current.Target -ne 'PSCustomObject') { "Added '$($current.Target)' to" } else { "Added" }) '$($current.Ref) with version $version'" } '<>' { "Changed '$($current.Source)' to '$($current.Target )' at '$($current.Ref) with version $version'" } '<-' { "deleted '$($current.Source)' at '$($current.Ref ) with version $version'" } default { "No metadata change in version $version'" } } } } |
…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:
|
1 2 |
display-object ([IO.File]::ReadAllText("$($versionPath)1.1.3\Model.json") | ConvertFrom-JSON) ` -Avoid @('public.flyway_schema_history','dbo.flyway_schema_history') |
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.
Tools in this post
Flyway Teams
Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.
You may also like
-
Article
Dealing with Mutually Dependent Databases in Flyway
How to create and manage 'stub' objects, in Flyway migrations, in order to overcome build failures caused by circular, or mutual, dependencies between objects in different databases. The technique should be applicable to any RDBMS.
-
Article
Automatically Tracking and Deploying Static Data in Flyway Enterprise
Static data is often required for the basic functioning of a database and any dependent applications. Therefore, it's vital that we can track this static data to understand how, when, and why it changed, and that we include any static data changes in our database deployments. Flyway Enterprise will now do both tasks automatically.
-
Article
Getting Started with Flyway Migrations on PostgreSQL
Use PowerShell to run some Flyway migration scripts that will build, fill and modify a PostgreSQL database.
-
Article
Searching Flyway Migration Files using Grep and Regex
This article demonstrates a cross-RDBMS way of searching through a set of SQL migration files, in the right order, to get a narrative summary of what changes were made, or will be made, to one or more of the tables or routines within each migration file. Getting these summary reports, even from a set of SQL migrations, isn't difficult, but having a few examples makes it a lot quicker to get started.
-
Article
Getting Data In and Out of SQL Server Flyway Builds
This article provides SQL "bulk copy" routines to extract data from and load it into a SQL Server database, rapidly, and demonstrates a way to automatically bulk insert the test data in a Flyway build, using a SQL callback.
-
Article
Managing Database Code Quality in a Flyway Development
In this article, I'll discuss the most important quality metrics for a database development, and then practical ways to ensure that a Flyway-managed database is designed and implemented to a high enough standard that it is reliable to use.
