Creating Flyway Migration Files using Redgate Schema Comparison Tools
How to use Redgate's schema comparison engines to generate object-level scripts for every database version that Flyway creates, and then use them to create ad-hoc, Flyway-compatible migration files.
There are plenty of reasons why you’d want to create an “ad-hoc” migration file that will work with Flyway. If you’ve used the FlywayTeamwork framework that I wrote to demonstrate Flyway’s use, especially when integrated with other tools, you’d have noticed that I like to save a directory of object-level scripts at the end of every migration run. This can be generated by the appropriate Redgate Comparison tool for the RDBMS you’re using.
As well as being handy for source control, this source directory can be used by a Redgate comparison tool either as a database source or database target. This can then provide you with the detail of changes between any two versions and even generate a migration file to go from target to source. For example, I already have tasks in the framework that use SQL Compare CLI to automatically generate an undo file for the freshly completed migration, to check that the database, in a repeated migration, is the same as it was the last time you built the database. There is now also a task for doing a forward migration from the current version to the actual current state of the database.
Although we can automate Redgate’s Compare tools easily to provide the milestones between each version, quite a lot of work of generating migration scripts will be ad-hoc, and for this you’d need to use the SQL Compare user-interface.
Automating a system using Flyway and PowerShell
If you have SQL Compare and my FlywayTeamwork PowerShell framework, you can generate both a build script and a directory of object-level scripts, one for each database version produced by Flyway. Once you have this, SQL Compare can use the versioned object-level directory in a schema comparison.
Command line licensing
Any automations that require deploying the command line to machines which are not your own, such as build servers, require Redgate Deploy licenses. For full details, see the Changes to distribution of command line page of the documentation.
Let’s say you want to capture all the development work you’ve achieved on a database since you last did a successful migration run with Flyway. We simply set the object-level directory for the last successful migration as ‘target’ and the current development as ‘source’, and SQL Compare will, with a bit of tweaking, generate the migration script we need. We can use a similar, but mirror-image, operation to create first-cut UNDO scripts for the reverse action.
The framework has several sample tasks that use SQL Compare. So far, they are limited to SQL Server, but I hope to expand the coverage. Each task is a scriptblock, and you just stack up the ones you want to use in a list. Here is an example, perhaps quite extreme, of what you can do. In this case, I’m using Flyway Community, executed within a PowerShell script, and executing the tasks after each migration run. With Flyway Teams, you don’t even need to use PowerShell directly because I’ve produced an afterVersioned script callback that will automatically run the required tasks for every successful migration.
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 |
<# Below are the tasks you want to execute. Some, are essential before you execute others. In order to execute tasks, you just load them up in the order you want. It is somewhat like loading a revolver. #> $PostMigrationTasks = @( $GetCurrentVersion, #checks the database and gets the current version number #it does this by reading the Flyway schema history table. $CreateBuildScriptIfNecessary, #writes out a build script if there isn't one for this version. This #uses SQL Compare $CreateScriptFoldersIfNecessary, #writes out a source folder with an object level script if absent. #this uses SQL Compare $ExecuteTableSmellReport, #checks for table-smells #This is an example of generating a SQL-based report $ExecuteTableDocumentationReport, #publishes table documentation as a JSON file that allows you to #fill in missing documentation. $CheckCodeInDatabase, #does a code analysis of the code in the live database in its current version #This uses SQL Codeguard to do this $CheckCodeInMigrationFiles, #does a code analysis of the code in the migration script #This uses SQL Codeguard to do this $IsDatabaseIdenticalToSource, # uses SQL Compare to check that a version of a database is correct #this makes sure that the target is at the version you think it is. $SaveDatabaseModelIfNecessary #writes out the database model #This writes out a model of the version for purposes of comparison, narrative and checking. $CreateUndoScriptIfNecessary # uses SQL Compare #Creates a first-cut UNDo script. This is an idempotentic script that undoes to the previous version $GeneratePUMLforGanttChart # This script creates a PUML file for a Gantt chart at the current version of the #database. This can be read into any editor that takes PlantUML files to give a Gantt #chart ) Process-FlywayTasks $DBDetails $PostMigrationTasks |
The PowerShell scriptblock tasks that use SQL Compare
All the scriptblocks are contained in a utility file called DatabaseBuildAndMigrateTasks.ps1, which you can find in the resource folder in the root of the project. These are the ones that use SQL Compare:
$CreateScriptFoldersIfNecessary
This task creates, if necessary, an object-level script directory. It first checks to see if the Source folder already exists for this version of the database and, if not, it will create one and fill it with subdirectories for each type of object. A tables folder will, for example, have a file for every table each containing a build script to create that object. When this exists, it allows SQL Compare to do comparisons and check that a version has not drifted. It saves the Source folder as a subfolder for the supplied version, so it needs $GetCurrentVersion
to have been run beforehand in the chain of tasks.
$CreateBuildScriptIfNecessary
This produces a build script from the database, using SQL Compare. It saves the build script in the Scripts folder, as a subfolder for the supplied version, so it needs $GetCurrentVersion
to have been run beforehand in the chain of tasks.
$IsDatabaseIdenticalToSource
This uses SQL Compare to check that a version of a database is correct and hasn’t been changed. To do this, the $CreateScriptFoldersIfNecessary
task must have been run first for this version, so it is more relevant for the task of provisioning identical databases for tasks such as testing. It compares the database to the associated source folder, for that version, and returns, in the hash table, the comparison equal to true if it was the same, or false if there has been drift, with a list of objects that have changed. If the comparison returns $null
, then it means there has been an error. To access the right source folder for this database version, it needs $GetCurrentVersion
to have been run beforehand in the chain of tasks.
$CreateUndoScriptIfNecessary
This creates a first-cut UNDO script for the metadata (not the data) which can be adjusted and modified quickly to produce an UNDO Script. It does this by using SQL Compare to generate an idempotent script comparing the database with the contents of the previous version.
$CreatePossibleMigrationScript
This creates a forward migration that scripts out all the changes made to the database since the last successful migration.
Generating the ‘build artifacts’ for each version
Here is an example of using these tasks in Flyway Community, with SQL Server. To try this out, you can use the Pubs directory in the FlywayTeamwork-Pubs project. For example, I created a “NoData” variant of the Develop branch, having Flyway create each version in turn and then run post-migration script blocks tasks to generate the build script an object-level source for each version. You’ll also need to create the project-level Flyway.conf, as I described in my previous article.
If you’re using Flyway Teams it’s even easier since the Migrations folder of this project includes an afterVersioned__Build.ps1 script callback that runs these and several other useful tasks automatically, so you can omit the lines that specify and execute the $PostMigrationTasks
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
$ProjectFolder = '<path to>\FlywayTeamwork\Pubs\Branches\Develop\Variants\NoData' #your project folder cd $ProjectFolder . '.\preliminary.ps1' # we now set the password. This can be done as an environment variable. but that isn't quite as secure #/ $pword="-password=$($dbDetails.pwd)" Write-Output @" Processing the $($dbDetails.variant) variant of $($dbDetails.branch) branch of the $($dbDetails.project) project using $($dbDetails.database) database on $($dbDetails.server) server with user $($dbDetails.installedBy)" "@ $PostMigrationTasks = @( $GetCurrentVersion #checks the database and gets the current version number #it does this by reading the Flyway schema history table. $CreateBuildScriptIfNecessary #writes out a build script if there isn't one for this version. $SaveDatabaseModelIfNecessary #writes out the database model # ) @('1.1.1', '1.1.2', '1.1.3','1.1.4', '1.1.5', '1.1.6') | foreach{ Flyway $pword migrate "-target=$($_)" # Migrates the database to a particular version if (!($?)) {break} Process-FlywayTasks $DBDetails $PostMigrationTasks } |
You can see that a lot can be done automatically, but not everything. You always need the Compare user-interface for one-off operations such as generating scripts for bug-fixing, doing a merge, or disentangling a merge conflict. We can do these one-off ad-hoc operations whenever we want without much effort just as long as we had the foresight to create a model of the database after the last successful migration.
However, if you want SQL Compare to produce a Flyway-runnable script, each time, there are a few options you need to set.
Generating Flyway migration scripts manually, using SQL Compare
Imagine that your development database started at V1.1.6, but you’ve now made some ad-hoc development changes to it, directly from within SSMS, and now need to capture these changes into a Flyway script. If you have saved a ‘metadata model’ for V1.1.6, and have a schema comparison tool like SQL Compare, the task is relatively straightforward.
We simply compare current development database (source) with the object-level script directory for V1.1.6 (target) and SQL Compare will generate a ‘synch’ script:
However, there is more work to do. Redgate’s schema comparison tools will, unless you ask otherwise, produce a synchronization script that is designed to work with any simple build system or query tool. It has its own error handling and rollback mechanism. It creates a transaction. It makes certain that it stops on the first error, ‘fatal’ or not. Flyway, by contrast, works on a simple script without any of those features, and does all the rollback niceties itself, using different means. In a Flyway migration, we need that simple migration script, so we need to ask SQL Compare nicely to give us it, by setting the appropriate options.
SQL Compare Filters
Firstly, Flyway likes to have full control over the creation of schemas so that a Clean operation actually produces an empty database. This means that we mustn’t have DDL that affects schemas in your migration files, so we need to set up a filter. To add a filter into the Compare project, you need to do a comparison and then click on the Action menu to show the Filter Setup pane:
Secondly, you must also filter out Flyway’s schema history table; SQL Compare must never include any code in a migration that makes any change to this table (this is only relevant to build scripts as its design is not likely to change).
It will normally be called flyway_schema_history and located in the default schema, but there is an option in Flyway to change both so you’ll need to check what the setting is for your project and create a filter to exclude it. To do this, click the Custom filter rules… link on the Filter pane and filter out the table as follows:
If we are automating a SQL Compare task, using Flyway, we have to save our filter as a file with the file extension .scpf. To do this, click ‘Save’ on the Filter pane and then, in the Save Filter dialog box, type in the name for the filter. Save the .scpf file to the ‘Resources’ directory of a Flyway project, and it will then be used by SQL Compare.
When you save an edited filter, you can either save it with the same name to overwrite it or change the name to create a new filter. If you change the name of the flyway schema history table, you’ll need to do it for every development withing the project and update the .scpf file.
SQL Compare Options
There are also some of the options you need to select that directly affect the creation of the migration file, top ensure it is Flyway-friendly. You’ll need to check the Don’t add error handling statements to deployment scripts and Don’t use transactions in deployment scripts boxes.
In additions to these, there are also some “ignore” options that you may need or want to set, in addition to the Redgate defaults, such as Ignore collations, Ignore system named constraint and index names and possibly also Ignore extended properties and Ignore authorization on schema objects.
There are others I prefer for general use, such as Ignore Quoted Identifiers And Ansi Null Settings, and Ignore No Check And With No Check.
Saving the migration script
With the filters defined and the options set, you can finally create the migration file. Run the comparison, select all the objects listed with differences, hit deploy, and save the script.
Unless you’ve had warnings, you’ll have a file that can be used a Flyway migration file. To test it out, simply create a fresh copy of pubs at V1.1.6, run the migration script and then compare this copy to your development database – they should be identical.
One “Flyway gotcha” to watch out for is that, by default, it will read only UTF8 or Windows 1252 files. Flyway can’t read files with other encodings, and it will throw the “unable to calculate checksum” error. Most text editors will allow you to convert the text encoding of a file, and in SSMS you can use “save with encoding” option to do this.
Generating the Flyway migration script automatically
This is all OK, but really, you will want to have this automated. It is not just the time it takes but the tedium, and like most developers, I hate having to do things repetitively, both in database development and the washing up.
As I’ve mentioned in the introduction to this article, The framework now has a $CreatePossibleMigrationScript
task; you can find it in the DatabaseBuildAndMigrateTasks.ps1 file in Resources folder at the root level for the project.
It automatically does a check for differences between the current version of the database, and the object-level source for that version, if it exists. If so, it will produce a Flyway-compatible migration script and a report of the differences. The task sets the required SQL Compare options for creating a Flyway-compatible migration script, as described previously, and sets other sensible options.
1 |
"/options:NoErrorHandling,IgnoreExtendedProperties,IgnoreQuotedIdentifiersAndAnsiNullSettings,NoTransactions,DoNotOutputCommentHeader,ThrowOnFileParseFailed,ForceColumnOrder,IgnoreNoCheckAndWithNoCheck,IgnoreSquareBrackets,IgnoreWhiteSpace,ObjectExistenceChecks,IgnoreSystemNamedConstraintNames,IgnoreTSQLT,NoDeploymentLogging" |
If you run the following, it will capture any untracked changes on your database, by comparing it to the object model reference for the recorded Flyway version of the database. it saves the script in Scripts subfolder for the version (e.g., in \Versions\1.1.6\Scripts\)
1 2 3 4 5 6 7 8 9 10 11 12 |
cd S:\work\Github\FlywayTeamwork\Pubs\branches\develop\Variants\NoData . '.\preliminary.ps1' Write-Output @" Processing the $($dbDetails.variant) variant of $($dbDetails.branch) branch of the $($dbDetails.project) project using $($dbDetails.database) database on $($dbDetails.server) server with user $($dbDetails.installedBy)" "@ $PostMigrationTasks = @( $GetCurrentVersion, #checks the database and gets the current version number #it does this by reading the Flyway schema history table. $CreatePossibleMigrationScript #writes out a migration script if there is drift detected # ) Process-FlywayTasks $DBDetails $PostMigrationTasks |
Conclusions
SQL compare is ideal to use with Flyway if you are using a relational database system that is covered by one of the Compare tools, such as Oracle, MySQL or SQL Server. Although it is great for all the routine jobs that come up with development work, it also allows developers to work informally in a feature branch of a development database, using whatever methods suit the team, and retrospectively develop a first-cut migration that becomes the definitive artefact for the work. Although much of the work done by SQL Compare can be automated, the GUI version of the tool is still an ‘essential’ for ad-hoc work.
To work well with Flyway, SQL Compare must generate SQL Scripts that obey the rules of a migration. This isn’t a big deal, since years of use has made SQL Compare very configurable.