Product articles SQL Compare Database Build tasks
Creating Flyway Migration Files using…

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.

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.

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.

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.

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:

Schema comparison between a database and object-level source scripts.

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:

Setting up a SQL Compare filter

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:

editing filter rules

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.

Setting SQL compare options for Flyway-compatibility

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.

using SQL Compare to generate a flyway-compatible migration file

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.

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\)

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.

Tools in this post

Flyway Enterprise

Standardize Database DevOps across the organization

Find out more

Redgate Flyway

DevOps for the Database

Find out more

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more