Phil Factor

15 October 2021

• Flyway
• Undo migrations
• Testing migration scripts

Secrets of Flyway Teams Undo

The Undo migration file, in Flyway Teams, is like the naughty sister of the versioned migration file that is used to migrate a database from one version to the next. The undo migration must be written in parallel with the associated migration file and it does the reverse: it migrates the database from the current version to the previous one.

There is nothing too radical about this. Seasoned database developers will always have an undo script for every deployment, patch or synchronization, usually generated by a schema comparison tool rather than being hand-cut. Creating these undo scripts as part of development will ensure that they are well-tested by the time they are needed, if ever. Where Flyway is a bit radical is that you write an ‘undo’ for every migration, rather than just for every release candidate. Flyway allows you to slide back down through several versions in an undo run, and it provides a whole range of special callbacks to allow you to do various maintenance tasks, as part of the undo ‘run’.

As for versioned migrations, Flyway also executes versioned undo migrations within a transaction, unless you specify otherwise. This makes their development a bit easier. Wrapping the undo migration in a transaction means that Flyway will automatically rollback the effects of a failed undo (assuming your RDBMS allows this) and so there is much less risk of leaving the database in an indeterminate state. The versioning ensures that undo migrations aren’t executed against the wrong version (an undo script called U1.1.6__Add_Tags, for example, can only ever be executed by Flyway on V1.1.6 of the target database). These are valuable features because it means that the guard clauses required to prevent accidental damage from a ‘wild undo’ are thereby less necessary. If you are likely to execute an undo file outside the Flyway safety net, then you should consider using guard clauses to make the script idempotent.

Creating an undo file

Before creating your undo files, you need to decide what aspects of the database are covered by the undo. For example:

• Should it undo both data and metadata changes? If so, are you going to use the same undo script when updating a production database? Database Deployments and Rocky Rollback Horrors: Where Does the New Data Go? describes the potential pitfalls.
• What are your strategies to deal with a failed migration? See Database Updates: Roll Back or Fix Forward.
• How are you going to deal with the potential chaos of a migration that fails at some unknown point in the script and can’t be rolled back? See Dealing with Failed SQL Migrations in MariaDB or MySQL.

Probably, the complications of preserving data represent the biggest difficulty for an undo script, and it is here that you most obviously need to appreciate the details of what changes the versioned migration file actually makes to the current metadata and data.

Development uses for undo files

If migration files are always applied in the same order and are immutable, then errors in the migration process are less likely. The trouble comes when, with the best possible motivations, the developers need to subvert this simple system by adding files to the migration run that are only executed under certain circumstances, are executed repeatedly, or are executed out of version-order. The more complicated your migration runs, the more errors happen, and the more likely you are to need undo to reverse out of a development blind alley.

If you create a migration and write its twin sister undo at the same time, then testing is more relaxed. You can repeatedly test a migration, undo it, correct it, run it again and retest until it is satisfactory. No longer is a migration such an irrevocable act.

The most obvious benefit of creating the undo files is to be able to produce a range of versions on the one database, one after another, for debugging, without ever having to do a complete run of all the historic migration files. You can also use it to quickly back off a branch version that is giving grief or test a particular version.

Once you have undo files, the more likely you are to think of a use for them: they provide another way of debugging and give more freedom of movement for development. Branch-based development with Flyway suddenly seems freer, less cast in concrete.

Generating undo SQL Files

Most of the time, your undo script will combine metadata and data synchronization, where appropriate. When I’m working on SQL Server, I use SQL Compare to generate undo files for metadata changes. (See Automating the creation of Flyway UNDOs).

To make this even easier, I provide a PowerShell library that includes functions that will generate script folders and object-level build scripts for every database version, which SQL Compare can then use as a representation of a source or target (see Creating Database Build Artifacts when Running Flyway Migrations for details).

For RDBMS other than SQL Server, Oracle and MySQL, there aren’t always the equivalent tools to help with this, and they generally require the two actual databases rather than the build scripts. It helps to know the differences, and the scripts can then be generated more easily. My ‘diff’ PowerShell cmdlets will report all differences visible to the ODBC interface, which will help, though it can’t record changes in constraints.

I’m not so bothered about undoing data changes, during development, as I always use development data that I can quickly regenerate. However, when required, SQL Data compare can help generate data undo scripts. The most common problem with undoing data changes happens when a table is deleted as part of a migration without provision for restoring it on an undo. The biggest difficulty happens when you update a production database, adding new columns or refactoring existing columns, and then, after a period, discover a serious problem and need to roll back to the previous version. In the meantime, users will have done transactions that must be preserved. I discuss solutions to this problem in Database Deployments and Rocky Rollback Horrors: Where Does the New Data Go?

Generating undo scripts with SQL Compare

When you generate an undo script for a SQL Server database with SQL Compare UI, for use in Flyway, you need to set the options correctly to generate a script that doesn’t create a transaction within which to execute. You will generally need to edit script that you generate to ensure that data is preserved or deleted correctly, while preventing errors involving duplicate keys. There are many things to watch out for. For example, if deleting rows in a table that relies on an IDENTITY field for a primary key, it can be important to also reset the identity initial value using:

Another difficulty you’ll come across is system-generated constraint names. These are a nuisance because they have a different name every time. Obviously, it is nice to avoid them by explicit naming of all your constraints, but, hell, they happen. If you need, for example, to delete or alter such as constraint you need to search the metadata by the fixed part of the name with wildcards for the rest and find the actual name of the critter. It will need dynamic SQL and the EXEC command to make the undo.

You can avoid some of these difficulties by setting the appropriate SQL Compare options. Tastes vary, but I use ‘NoErrorHandling, NoTransactions, ThrowOnFileParseFailed, ForceColumnOrder, ObjectExistenceChecks, DecryptPost2kEncryptedObjects, DoNotOutputCommentHeader, IgnoreDatabaseAndServerName, IgnoreFileGroups, IgnoreFillFactor, IgnoreTSQLT, IgnoreUserProperties, IgnoreWhiteSpace, IgnoreWithElementOrder‘ (avoid spaces in the command line).

Finally, you need to get source and target assigned correctly, and make sure that you save the result as a Unicode file.

Idempotent undo scripts

If you are always executing your undo file within a transaction, which is Flyway’s default, you are unlikely to have to rescue a migration from an undetermined state. However, some database systems such as MySQL can’t roll-back metadata operations. If your migration fails in this case, you can end up with the database at some murky intermediate state between two versions. To rescue the database, you need an undo script that is idempotent, so you can execute it and it just mops up all the damage it finds and restores it to the initial version. It does this by doing each DDL undo operation, such as undoing a column change or table split, only if it hasn’t already been done. For this reason, I make a habit of using idempotence in scripts that can be executed more than once without harm or errors. SQL Compare will do this if you use the ‘ObjectExistenceChecks‘ switch. You can then add checks to see whether the next operation has already been done.

Undo files in action

In this article, I’ve provided a sample Flyway Teams project, on GitHub, for a SQL Server database (Pubs). It includes undo files for every migration, so that you can try it out. You’ll need a Flyway Teams license, but you can get a temporary one for free.

The project directory I’ve provided is designed to be a general “Flyway Teams playground” so it has various other files that we won’t use in this article (callback scripts, baseline migration scripts) alongside the versioned migration and undo files that we will be using. You just need to give these other files a prefix that prevents Flyway from running them (I use DontDo).

The following PowerShell script handles all the connection and authentication securely and allows you to do interactive work with Flyway. I’m using parameter splatting rather than environment variables to provide Flyway with everything it needs (although environment variables are just as good). Obviously, you can set up all the variables in a Flyway.conf in the current working directory, but I don’t like credentials in files. The same goes for Environment variables though there is less risk there. The most intrusive part of the script is a routine that sets a placeholder that can tell you the capabilities of the version of SQL Server hosting the database. For this demonstration, we’ll concentrate on the Undo facilities.

As a flourish, this script does an info action. Then you can, of course, do any action that you need, as long as you include the parameters, (@FlywayUndoArgs as a splat, $FlywayUndoArgs as a variable).

Flyway @FlywayUndoArgs migrate might be a good place to start, and it is interesting to experiment with doing undo statements that simply undoes the current version, or to undo back to a specified version with an undo run, by using the target specification.

To get a feel for the sort of things that suddenly become possible, try altering a migration that has already been executed successfully. For example, migrate to 1.1.9, undo to 1.1.6, edit V1.1.7__AddIndexes.sql to add in the statement:

Now run migrate again. You’ll find that you can do it, as long as you’ve ‘undone’ back to a previous version (1.1.6 in this example).

However, it is quite easy to do something that will make migrations and undos that were working perfectly before to suddenly fail. With great power comes great opportunities to cause unexpected problems.

Conclusions

Undo migrations are a useful addition to Flyway for a database developer. For a team, I see their main use as part of a source control system that allows branches and merges. This facility is, of course, also useful during deployment and for staging where one would otherwise be using a rollback script of some sort, but it needs to be used with care.

Where there is an undo facility, it means that a versioned migration script is no longer immutable. This doesn’t matter so much for a small team, but bigger teams working with Flyway would soon need a branch/merge strategy to deal with the predicament of someone else in the team undoing your carefully constructed migration run and changing something in a previous migration, with the consequence that what you consider the current version has changed. Worse, it isn’t entirely obvious how to resolve it without losing work somewhere in the process. However, if you can do this sort of thing safely, it is a great advantage.