Updating a field from a migration

Question

I am adding a field using the migrations plugin:

databaseChangeLog = {
    changeSet(author: "me", id: "add publish_date") {
        addColumn(tableName: "book") {
            column(name: "publish_date", type: "timestamp") {
                constraints(nullable: "true")
            }
        }
    }
}

I would also like to update publish_date for some Books. One way of doing it is by using sql.execute("UPDATE book SET publish_date = ____ WHERE year = 2012") but this doesn't seem very database agnostic... and I'd like it to be.

I'm thinking that using Book domain in the migration would ensure that the migration works for different databases. Can anyone comment if this is sane/possible?

changeSet(author: "me", id: "add publish_date") {
    grailsChange {
        change {
            Book.findAllByYear(2012).each { book ->
                book.publishDate = _____
                book.save()
            }
        }
    }
}

What are the options if you wanted to keep the migrations database agnostic?

Answer 1

The way to do it with Liquibase + the plugin is

update(tableName: 'book') {
   column name: 'publish_date', value: '____'
   where 'year = 2012'
}

This has similar problems to the raw SQL approach since it may be different for different databases, but Liquibase doesn't have something like Hibernate's HQL. You can limit changesets to only apply to specific databases, so you can create one for each database that you use (e.g. a different type in prod than dev) and only the correct one will run.

The problem with GORM is that the scripts are Groovy classes and won't compile with different versions of the code, so if someone gets behind or if a new developer builds the database by running all of the migrations, they'll fail with compilation errors.

So you need to choose which approach is the least bad.