Preconditions with the Database Migration Plugin in Grails

We’ve been writing a few database migrations at the office lately, and have seen some issues regarding the syntax of HSQLDB used in development and test, and Postgres, used in production.

To solve some of these issues, we’ve resorted to using liquibase preconditions in some of our migrations.

In this post, I’ll give a brief rundown of the preconditions feature and how they look when used in combination with the database migration plugin and Grails.

What is a precondition?

A liquibase precondition is a piece of code that you can put in front of your liquibase / database migration plugin changesets.

They’re quite powerful little beasts. With a precondition, you can execute changes based on results of sql queries, domain class properties or database type. They allow you a deeper level of control on how your changesets are ran.

How do preconditions look like?

If you follow the liquibase documentation, you will see that the preconditions provided in the form of XML is:

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.8"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.8
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.8.xsd">
    <preConditions>
        <dbms type="oracle" />
        <runningAs username="SYSTEM" />
    </preConditions>

    <changeSet id="1" author="bob">
        <preConditions onFail="WARN">
            <sqlCheck expectedResult="0">select count(*) from oldtable</sqlCheck>
        </preConditions>
        <dropTable tableName="oldtable"/>
    </changeSet>
</databaseChangeLog>

Lucky for us, we can use the more compressed groovy DSL provided by the database migration plugin, so the above example should look something like

databaseChangeLog = {
  preConditions{
     dbms(type: 'oracle')
     runningAs(username: 'SYSTEM')
  }

  changeSet(author: "bob", id: "1") {
    preConditions(onFail: 'WARN'){
         sqlCheck(expectedResult='0', 'select count(*) from oldtable')
    }
    dropTable(tableName: 'oldtable')
  }
}

The one thing to point out here is that there are actually two preconditions that we are setting here. The first one, checking that the database is oracle and the user is ‘SYSTEM’, would apply to all the changesets in this log. The second one, which applies at the changeset with id 1, will only apply to that particular changeset.

By default, the preconditions will throw a warning if they are not met, but you can customize this behaviour.

I could, for example, tell the use that a foreign key constraint they wish to modify does not exist but mark the changeset as ran, which would like this:

preConditions( onFail: 'MARK_RUN', onFailMessage: 'Sorry, the foreign key BigMommasKey does not exist in the database' ){
   foreignKeyConstraintExists( schemaName: 'mySchema', foreignKeyName: 'BigMommaysKey' )
}

Available Preconditions

The set of default precondition checks in liquibase are fairly good, as they allow you to check for existence of tables, columns, views, indices, etc.

They also allow you to exclude preconditions from being executed against databases that might not support the SQL syntax that you are using by using the runningAs or dbms preconditions.

You can perform a custom sql query and check against the result via the sqlCheck precondition. This is useful if you want to make sure that a table is empty or want to make sure an expensive update operation is absolutely necessary.

You can also run a changeset only after another one has been ran via the changeSetExecuted precondition.

You can learn more about the features provided by reading the documentation on preconditions.

Combining preconditions

You can mix and match precondition checks with boolean logic, so your precondition might look like the following:

preconditions{
   or{
      and{
          dbms(type:'oracle')
          runningAs(username:'SYSTEM')
      }
      dbms(type:'mysql')
   }
}

Writing preconditions in Groovy.

A great feature of the database migration plugin is the ability to add new preconditions in Groovy via the grailsPrecondition mechanism. Grails precondition allows you to perform more complex checks that might not be easily written via a simple sqlCheck.

The structure of a grails precondition looks like this

preConditions{
   grailsPrecondition{
       check{
           // precondition code goes here
       }
   }
}

Within the precondition, you can use a simple assert statement, the message after the colon : is the message sent back.

assert y == y : 'value cannot be 237'

Or you can use the fail( message ) method to issue the message back after the precondition has failed. An example of this might look like the following:

   sql.eachRow( 'select * from lockable' ){
       if( it.lockable = true ){
            fail 'There are still locked items in the database' 
       }
   }

As you can see, grailsPreconditions provide another powerful way in your arsenal to ensure database integrity.

Summary

In this post, I have shown you the power of writing preconditions in your Grails database migrations. Preconditions provide a powerful way of deciding which changesets get executed and can save your bacon when moving across different databases.

2 thoughts on “Preconditions with the Database Migration Plugin in Grails

  1. burns

    The possilbe onFail values are:

    HALT Immediately halt execution of entire change log. [DEFAULT]
    CONTINUE Skip over change set. Execution of change set will be attempted again on the next update. Continue with change log.
    MARK_RAN Skip over change set, but mark it as ran. Continue with change log.
    WARN Output warning and continue executing change set/change log as normal.

    Notice that is is “MARK_RAN”, not “MARK_RUN” (like in your BigMammys example above).

    Reply
  2. burns

    You have an error in your sqlCheck example.

    changeSet(author: “bob”, id: “1”) {
    preConditions(onFail: ‘WARN’){
    sqlCheck(expectedResult=’0′, ‘select count(*) from oldtable’)
    }
    dropTable(tableName: ‘oldtable’)
    }

    the first argument to sqlCheck should be a map and not an assignement statement.

    changeSet(author: “bob”, id: “1”) {
    preConditions(onFail: ‘WARN’){
    sqlCheck(expectedResult:’0′, ‘select count(*) from oldtable’)
    }
    dropTable(tableName: ‘oldtable’)
    }

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s