Phil Factor

24 November 2021

• Flyway
• Undo migrations
• Rollback strategies

Automating Flyway Undos

It is the dream of every database developer and administrator to be able to move a database between versions at will, while preserving the current data in the tables. The migrations-based approach to database development proves that it is possible if you are moving to a higher, later, version. Flyway also allows you to create backward, or “undo”, migration scripts that revert to the previous version. If you chain them together into an undo run, then you can regress the design back to any version you like. Once you have the means to do this in a single Flyway command, it can become a fascinating activity, especially when you’re doing debugging work.

It is worth the effort, and the task is made a lot easier when you have a tool, like SQL Compare, that compares the two versions and generates the SQL to allow the backward migration to happen.

Writing Undo scripts

The joy and excitement of creating Undo files manually soon fades, but it is possible and I’ve described the process in Secrets of Flyway Teams Undo. I much prefer to autogenerate a “first cut” undo script, using a database comparison tool, and then review it, tweak it, test it and then use it. It is never possible for a machine to entirely deduce the evolution of a database design, especially if a lot of renaming is going on, but it sure helps.

Of course, the task of migrating a database, either forwards or backwards, isn’t just about the metadata. It is also about preservation of data. A lot of the uncertainty in doing a radical migration or undo is about where the data goes. If a forward migration has deleted data, then no automated script for the database’s metadata could ever restore that data. This must be done by a separate data comparison.

In addition, there is the separate problem of deciding how you preserve any data that has been introduced into the database since it was migrated to that version. This is unlikely to concern a change in development but will be important when upgrading a production database that subsequently must be rolled back while preserving any additional data changes when the revision was ‘live’. I’ve discussed the various approaches to reversing out of a failed migration of a production database, in Database Updates: Roll Back or Fix Forward?

Generating a first-cut Undo script for a Flyway migration

For SQL Server and Oracle, Redgate provides tools that can do a great deal towards providing the ‘first cut’ of an undo migration. In this article, we’ll use Flyway Teams and SQL Compare with SQL Server, but you can use the equivalent tools with an Oracle database as well.

This undo script will need to be tested and will sometimes need to be altered, because a few things such as a table-split, DML operations, or system-generated constraints will confuse it. An auto-generated ‘synchronization’ script cannot always divine what the developer did, especially if tables and columns are renamed. Deletes and creations are easy but renames aren’t. It is always possible to change one database into another, even if it means deleting one and replacing it entirely with the other, but the data isn’t going to be preserved. SQL Compare will warn you of any issues that need a change in the script.

Ideally, you’d want to have an undo script for every version, because if there is a complete chain, we can do undo runs down any number of versions. To get an undo script for every version, we need to generate for every version a ‘source’ directory containing scripts that define the metadata of that version, such as object-level build scripts. I’ve shown how create these scripts, and other useful build artifacts, for every version, either using the relevant tasks in my PowerShell functions library or by using SQL Change Automation.

If you’re using Flyway Teams, we can run these post-migration tasks using an “afterVersioned” callback script. Once the migration completes, the callback script using the SQL Compare engine to generate all the artefacts and reports associated with the new version. I save these separately from the migration project folder. I have a Scripts directory with a build script for the version, which could be adapted to become a Baseline migration. I keep a Source directory with an object-level source for comparing old versions of the database and for adding to source control. There is also a Reports directory for change reports, static and dynamic code checks on tables to highlight any issues, and so on.

As far as this system is concerned, then, the generation of automated Flyway Undos is just another task that we can add to our PowerShell task library. All it needs to do is determine the version of the database, fetch the details saved for the previous version (V1.1.2, in the above example) and then, if required and assuming it’s there, use the object-level Source directory for V1.1.2 as the target in the SQL Compare command-line, with the newly migrated V1.1.3 database as the source, to generate a script that will move the database from the later version to the earlier version.

We then add the “Undo script” task to the afterVersioned callback, along with the others. We store the auto-generated undo script in the project output Scripts directory, alongside the build script for the version. This allows us to test it, and make any necessary adjustments, before transferring into the Scripts directory of the Flyway migration project as Undo scripts with the correct naming in the filename.

The Create Undo Script task

For this particular system to work, you will need to have done the forward migrations one at a time using the $CreateScriptFoldersIfNecessary task for every migration run (or using SCA to generate the artifacts as I’ve demonstrated in Using Flyway Teams with SQL Change Automation PowerShell Cmdlets).

As for all my articles, I’ll demo all this using an example Flyway project for the venerable Pubs database. It is intended to be useful as a playground for testing the features of Flyway Teams. It is here on Github.

Let’s have a look at the “Create Undo Script” task first. It is a very simple script block with a standard input of a hashtable with all the parameters you need. We first check that the information we need to do the job has been provided. Then we see if SQL Compare is installed and in the right place. If so, we need to find the version number of the previous version of the database. Having got that far, we then check if it is possible to compare the current version of the database with this previous version. To do this, we need to make sure that the previous version has the object-level scripts in its Source directory. If the object-level scripts aren’t there, we can’t compare the two versions.

We only need to generate this undo script once, unless the associated migration script is altered, so we check to see if the process is necessary by checking for the output synchronization file for the comparison by SQL Compare in the current version’s Scripts directory and only run it if it isn’t already there. Any errors or warnings are passed to the script block, and if it all went well, we report back the path to the script we generated.

Once this is all tested it can be added to our database build and migrate tasks. The current source is here on Github in the task library file, DatabaseBuildAndMigrateTasks.ps1.

The PowerShell callback script

Once we have this facility installed, we can invoke the task from a callback script. We simply adapt the current version of the afterVersioned__Build.ps1 file to add the script block. You’ll find it in the Scripts folder of the GitHub project (if it has DontDo prefix, you’ll just need to remove it).

We call the $CreateUndoScriptIfNecessary task the end of the script after the environment variables are converted for use. The only other tasks that must be in the chain are $FetchAnyRequiredPasswords and $GetCurrentVersion. The rest can be commented out if you don’t need them.

These callback routines use a shared task library, engineered as a ‘harness’. The reason for managing all the tasks in a ‘harness’ is that a callback script isn’t as easy to debug as an ordinary migration script. In this design, the callback just executes a list of tasks in order, and you simply add a task to the list after you’ve debugged and tested it and placed in the DatabaseBuildAndMigrateTasks.ps1 file.

This means that you need just one callback script, even if you have multiple chores to perform for each migration run. Otherwise, you drift into having to manage a stack of scripts all on the same callback, each having to gather up and process parameters, or pass parameters such as the current version from one to another.

In this design, each task is passed a standard ‘parameters’ object. The parameter object is passed by reference so that each task can add value to the data in the object, such as passwords, version number, errors, warnings and log entries.

All parameters are passed by Flyway. It does so by environment variables that are visible to the script. You can access these directly, and this is probably best for tasks that require special information passed by custom placeholders, such as the version of the RDBMS, or the current variant of the version you’re building

Trying it out

To try it out, simply run a migration on one of your flyway-controlled databases. I used the UNDOBuild.ps1 script in the project directory. Just fill out your $details like this:

…And then run the migrate. I migrated in steps, first to V1.1.10 (to generate the object level scripts and other artifacts for that version), and then to V1.1.1:

The result should be a migrated database, at V1.1.11 in this example, and at the $CurrentUndoPath location (C:\Users\Phil\Documents\GitHub\PubsAutoUndo\1.1.11, in my case), you should find all the artifacts we saved, including the object-level source and various reports for V1.1.11 and, in the Scripts folder, both a build script for the version, and an undo script to get back to V1.1.10:

Conclusions

With Flyway Teams, you have an excellent way of managing all the essential tasks that accompany the build of a new version. With any enterprise-scale database development, there are several essential tasks: archiving, reporting, reviewing, testing and liaison. Nobody has ever managed to reduce the complexity of the database lifecycle, so automation is likely to be the best way of speeding the process and allowing developers to concentrate on providing added functionality and resilience.

The generation of UNDO scripts is a special case of the migration script. It merely migrates back to the previous version. If you have a comparison/synchronization tool, you don’t need an automation script to produce a migration, but it just removes an extra chore. It is worth doing because the consistent and universal use of UNDOs allow you a somewhat alarming freedom to rewrite the history of past migrations, but it is a freedom that makes certain development operations such as merges a lot easier.