• Flyway
• Variants, Localization
• Database Testing and Quality

Try Before You Commit in Flyway

Flyway applies each SQL migration script, or chain of migrations if you wish it, in a separate transaction. It will roll back that transaction, and therefore the migration, if it receives an error message on its connection to the database. Otherwise, it commits the transaction and produces the new version.

If we then need to roll the database back to its starting version, even if no error is detected, then the conventional way to do this is by restoring a backup. You run the migration on a test server. and then run the restore. You could even use a container. If it is a small database, you could simply create a ‘variant’ of the version you need to test, run the migration to prove it works, use the Flyway clean command to empty the database, then recreate the variant at the original version.

However, any time you don’t want the hassle of this sort of “build-up and tear-down” routine, you can exploit the way Flyway uses transactions to roll back one or more migrations. You can throw an error from SQL that Flyway will intercept and will then cause the migration or migration chain to roll back. This is a very useful feature.

If you are using an RDBMS that can rollback DDL within a transaction (such as DB2, PostgreSQL, Derby, EnterpriseDB or SQL Server, then Flyway should be able to rollback the transaction cleanly, in case of failure. Flyway will not then change the original version number because the database will have rolled back to that point, so it is still valid.

Raising a SQL Exception to abort a migration

Let’s imagine that you are adding your unit and integration tests directly into your migration scripts. You need to do something when those tests fail. You can easily ‘throw’ an error in most RDBMSs, using RAISERROR (SQL Server), RAISE_ERROR (DB2 and Derby) or RAISE EXCEPTION (PostgreSQL). However, you would probably want an override, such as one that forces a migration to be applied even if a test fails (for example, we could just use error handling techniques, like TRY…CATCH to ‘bury’ the error). More likely though is that you’ll want an override that rolls back the transaction, and therefore the migration, even if the tests pass. This is rather easy to do, simply by adding a Flyway placeholder to a SQL callback and then setting its value at runtime, as I’m about to demonstrate shortly.

Please note that if you keep a transaction open for a long time, and it starts making considerable changes to the database, all these changes would only apply to that connection until you commit, but you will eventually start locking tables and thereby blocking other processes. It doesn’t take long for other users of the same database to get irritated. Team users of Flyway will know all about this, which is why I developed a team-based approach using branches that avoids the problem.

How to raise a SQL Error/Exception

In our case, we aren’t throwing an exception only if a test fails. We will throw an exception whatever happens. Sure, we can still use the syntax supported by the RDBMS, but you wouldn’t want to make any edit to the migration to make this happen. Instead, we’ll use a ${MaybeRollback} placeholder that can be set at runtime. We simply add the placeholder to the file, and set its initial value, via the command line, environment variable or in the flyway.conf file in the current working directory (project directory). We then change the runtime value of the placeholder, using one of the same three options. This runtime change doesn’t show up as a change to the migration file, so Flyway allows it.

We’d set the default value to “do nothing”, so that the migration would, if error free, succeed. The obvious place to do this would be in the config file:

• config file entry

• environment variable

• command-line (Windows)

Then, if we wish to abort the migration run at the end of the current migration, even if it is error free, we deliberately raise an error at runtime, regardless, by changing the value of the ${MaybeRollback} placeholder as follows (these examples are for SQL Server):

• config file entry

• environment variable

• command-line (Windows)

…though you don’t have to use that actual message. If you don’t like typing anything more than Flyway Migrate, then you’d likely use an environment variable to provide the code to raise the SQL exception. I much prefer the command line because I usually do this sort of work in PowerShell scripts.

Using a callback script to raise a SQL Exception

If we have an RDBMS that supports DDL transactions, you also run afterEach… callback scripts within the same transaction as the preceding migration script. This means that it is possible to opt to rollback the transaction, and therefore the migration, from an afterEach… callback script. The advantage to this is that you can use the one device to control any migration in the project without any extra code in the migration.

In contrast, the callback scripts that are executed after a run, such as afterVersioned or afterMigrate are executed after the transaction is committed.

Trying all this out

Let’s demonstrate this. I’ve created a variant of the Pubs Database, called ‘Rollback’.

To the Flyway.conf in the project folder, we add a line setting the value of our MaybeRollback placeholder to a good, neutral ‘do nothing’ line:

We now create a file called afterEachMigrate__MaybeRollback.sql, and place it in the Migrations’ folder. It has nothing but the line…:

Now you just do a migration. We’ll clean the database and migrate to V1.1.1:

Executing SQL callback: afterEachMigrate - MaybeRollback
Successfully applied 1 migration to schema [dbo], now at version v1.1.1 (execution time 00:01.868s)
Executing SQL callback: afterMigrate - Add Version EP
WARNING: DB: Recording the database's version number - 1.1.1 (SQL State: S0001 - Error Code: 0)
Executing SQL callback: afterMigrate - ApplyTableDescriptions
WARNING: DB: Adding the descriptions for all tables and columns (SQL State: S0001 - Error Code: 0) 
PS <path-to>\Pubs\Branches\Develop\Variants\Rollback>

This all went well, and you’ll notice that it ran the afterEachMigrate script within same transaction as the migration and before it committed the migration, which happens at the line ‘Successfully applied 1 migration to schema [dbo], now at version v1.1.1 (execution time 00:01.868s)‘. By contrast, the two afterMigrate scripts are executed with their own connections, once the migration was committed

Now, we are going to check what happens when we specify a rollback using our placeholder. We’ll start again with a clean, and we’ll ask Flyway to migrate to V1.1.1. However, we’ll force Flyway to rollback its transaction within the afterMigrate file:

The output (truncated somewhat) looks like this:

Successfully validated 19 migrations (execution time 00:00.063s)
Current version of schema [dbo]: null
Migrating schema [dbo] to version "1.1.1 - Initial Build"
WARNING: DB: Creating types (SQL State: S0001 - Error Code: 0)
WARNING: DB: Creating [dbo].[employee] (SQL State: S0001 - Error Code: 0)
WARNING: DB: Creating index [employee_ind] on [dbo].[employee] (SQL State: S0001 - Error Code: 0)
WARNING: DB: Creating primary key [PK_emp_id] on [dbo].[employee] (SQL State: S0001 - Error Code: 0)
WARNING: DB: Creating [dbo].[jobs] (SQL State: S0001 - Error Code: 0)
WARNING: DB: Creating primary key [PK__jobs__6E32B6A51A14E395] on [dbo].[jobs] (SQL State: S0001 - Error Code: 0)
…<truncated>…
WARNING: DB: Adding foreign keys to [dbo].[sales] (SQL State: S0001 - Error Code: 0)
WARNING: DB: Adding version number (SQL State: S0001 - Error Code: 0)
WARNING: DB: Altering permissions on TYPE:: [dbo].[empid] (SQL State: S0001 - Error Code: 0)
WARNING: DB: Altering permissions on TYPE:: [dbo].[id] (SQL State: S0001 - Error Code: 0)
WARNING: DB: Altering permissions on TYPE:: [dbo].[tid] (SQL State: S0001 - Error Code: 0)
Executing SQL callback: afterEachMigrate - MaybeRollback
flyway : ERROR: 
At line:1 char:1
+ flyway migrate -target="1.1.1"  "-placeholders.MaybeRollback=Raiserro ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (ERROR: :String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
Migration afterEachMigrate__MaybeRollback.sql failed
----------------------------------------------------
SQL State  : S0001
Error Code : 50000
Message    : I'm sorry Phil, I'm afraid I can't do that
Location   : .\migrations\afterEachMigrate__MaybeRollback.sql 
(C:\Users\Tony.Davis\Documents\GitHub\FlywayDevelopments\Pubs\Branches\develop\Variants\Rollback\.\migrations\afterEachMigrate__MaybeRollback.sql)
Line       : 1
Statement  : Raiserror ('I''m sorry Phil, I''m afraid I can''t do that',16,1)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: I'm sorry Phil, I'm afraid I can't do that

One’s first reaction in seeing this ‘red sea’ is to think it’s gone wrong, but no. The migration succeeded but then, before committing its transaction, Flyway ran the AftgerEachMigrate callback, which raised the error and caused a rollback. The exception message is the one we specified ‘I’m sorry Phil, I’m afraid I can’t do that’.

Flyway info tells us that version 1.1.1 failed. So, let’s migrate the database to version 1.1.2

Migrating schema [dbo] to version "1.1.1 - Initial Build"
Executing SQL callback: afterEachMigrate - MaybeRollback
Migrating schema [dbo] to version "1.1.2 - Pubs Original Data"
Executing SQL callback: afterEachMigrate - MaybeRollback 
Successfully applied 2 migrations to schema [dbo], now at version v1.1.2 (execution time 00:02.388s)
Executing SQL callback: afterMigrate - Add Version EP
WARNING: DB: Recording the database's version number - 1.1.2 (SQL State: S0001 - Error Code: 0)
Executing SQL callback: afterMigrate - ApplyTableDescriptions
WARNING: DB: Adding the descriptions for all tables and columns (SQL State: S0001 - Error Code: 0) 
Current version of schema [dbo]: 1.1.2

You can see more clearly here that it is running our afterEachMigrate__MaybeRollback.sql file after every migration. These AfterEachMigrate… SQL callback scripts are executed after every migration file, and within the transaction before it is committed. Therefore, when the placeholder is set to raise an error, it will strike at the end of the first migration so none of the others in the chain will even be executed.

Conversely, it is only running the afterMigrate SQL callback scripts files after the whole migration run completes, and after the transaction has been committed.

And now we’ll attempt to do a run up to version 1.1.8, but this time we specify that we want to rollback:

Migrating schema [dbo] to version "1.1.3 - UseNVarcharetc"
WARNING: DB: The database update succeeded (SQL State: S0001 - Error Code: 0)
Executing SQL callback: afterEachMigrate - MaybeRollback
flyway : ERROR: Migration afterEachMigrate__MaybeRollback.sql failed
At line:2 char:1
+ flyway migrate -target="1.1.8"  "-placeholders.MaybeRollback=Raiserro ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (ERROR: Migratio...back.sql failed:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
----------------------------------------------------
SQL State  : S0001
Error Code : 50000
Message    : I'm sorry Phil, I'm afraid I can't do that
Location   : .\migrations\afterEachMigrate__MaybeRollback.sql 
(<path-to>\Pubs\Branches\Develop\Variants\Rollback \.\migrations\afterEachMigrate__MaybeRollback.sql)
Line       : 1
Statement  : Raiserror ('I''m sorry Phil, I''m afraid I can''t do that',16,1)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: I'm sorry Phil, I'm afraid I can't do that

We’ve ended up still at 1.1.2, according to Flyway info:

Schema version: 1.1.2
+-----------+---------+------------------------------+--------+---------+----------+
| Category  | Version | Description                  | Type   | State   | Undoable |
+-----------+---------+------------------------------+--------+---------+----------+
|           |         | << Flyway Schema Creation >> | SCHEMA | Success |          |
| Versioned | 1.1.1   | Initial Build                | SQL    | Success | Yes      |
| Versioned | 1.1.2   | Pubs Original Data           | SQL    | Success | Yes      |
| Versioned | 1.1.3   | UseNVarcharetc               | SQL    | Pending | Yes      |
| Versioned | 1.1.4   | RenameConstraintsAdd tables  | SQL    | Pending | Yes      |
| Versioned | 1.1.5   | Add New Data                 | SQL    | Pending | Yes      |
| Versioned | 1.1.6   | Add Tags                     | SQL    | Pending | Yes      |
| Versioned | 1.1.7   | Add Indexes                  | SQL    | Pending | Yes      |
| Versioned | 1.1.8   | AddEditions                  | SQL    | Pending | Yes      |
| Versioned | 1.1.9   | AddconditionalVersion        | SQL    | Ignored | Yes      |
| Versioned | 1.1.10  | AddAddressesPhonesEtc        | SQL    | Pending | Yes      |
| Versioned | 1.1.11  | AddProcedureWithTest         | SQL    | Pending | Yes      |
| Versioned | 1.1.12  | AddTestData                  | SQL    | Pending | Yes      |
+-----------+---------+------------------------------+--------+---------+----------+

Extending the technique

If you want to use this technique to stop the migration if it fails a test embedded within a migration file, Flyway will leave the version at the point of the last successful migration. You can opt, with the flyway.group config option, to group all pending migrations together in the same transaction when applying them. The version will then be the original version, at beginning of the run. In these circumstances, you would need to set flyway.group=true; in your flyway.conf file. By default, it is false.

We are likely to use this technique if we need to check out the code with some tests. You run the tests at the end of the migration, and if there is something that isn’t right you rollback the migration run very simply by raising the SQL Exception with an appropriate message that informs the user which test failed. You don’t need the callback to do this, but the migration file will have to contain the logic that decides whether the migration is rolled back.

Conclusions

One might think that it would be a good built-in feature to rollback a migration even if it succeeds, just to prove that the migration itself would run. However, I hope I’ve demonstrated that it is already so easy to do that it is better to think that it is just one of the clever things that Flyway, together probably with placeholders and the afterEach… callbacks, can already do for you. The other problem is that you cannot support this in the full range of RDBMSs supported by Flyway.

Although the method of using afterEach callbacks with placeholders to raise the SQL exception is neat, you generally wouldn’t need it: usually, you’d want a rollback of a migration as a result of running a performance test as part of the migration, and for that, you merely need to raise a SQL Exception within the file, if the test fails. Less complicated.

Tools in this post

Flyway

DevOps for the Database

Find out more