Running custom SQL in Grails database migration plugin changesets

Under the hood, the grails database migration plugin uses Liquibase. Liquibase offers the ability to insert arbitrary SQL in changesets.

This post highlights how to use the sql and sqlFile commands in liquibase to run arbitrary URLs in database migrations.

This feature is useful if you want to set default values for existing data in the database. It can also use to load data snapshots or ensure specific rows exist in your database.

This is also useful to pre-populate databases on certain beta cloud solutions that surprisingly won’t let you access their databases externally ( cough, CloudFoundry ).

Running Sql statements

The SQL command in Liquibase runs inline SQL commands.

We use this feature mostly to insert default values to our existing data. So if you’re adding a field to the database that is not nullable, you can modify your changesets to alter the existing data.

The syntax is as follows:

  sql("${ sql command }")

Used in a changeset, it looks like this:

changeSet(author: "tomas", id: "1305821637932-1") {
  addColumn(tableName: "book") {
    column(name: "flammable", type: "BIT") {
       constraints(nullable: "false")
    }
  }
  sql("update book set flammable = true")
}

Using SqlFile

Similarly, you can run entire SQL files via the Liquibase SqlFile command. This is useful if you want to preload some data you obtained via mysqldump or if the changes you want to make are fairly large.

The syntax is as follows:

  sqlFile( path: "${ path to SQL file relative to changelog.groovy }")

Used in a changeset, it looks like this:

changeSet(author: "tomas", id: "1305821637932-2") {
  sqlFile( path: "books.sql")
}

We find that it looks for the file in the same directory where your changelog.groovy file exists. So by default, grails-app/conf/migrations/ . You can also alter this path so that all your sql files live inside a folder and call via path: ‘sql/books.sql’.

About these ads

One thought on “Running custom SQL in Grails database migration plugin changesets

  1. Pingback: GroovyMag - the magazine for Groovy and Grails developers about Groovy programming and Grails development

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