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