Custom Groovy Changes In Grails DbMigration (Part I)

Grails has a wonderful plugin to manage database changes & state in production server, known by  Grails Database Migration Plugin. This plugin helps by managing database changes while developing a grails application. The plugin uses the Liquibase library.

This plugin manages changes of database structure in a very consistent manner, avoiding inconsistencies, communication issues, and other problems by representing database migration scripts in text format, either using groovy DSL or liquibase XML. This also helps to maintain the change log migration files in source control like Git.
You can read more here in documentation of db migration plugin. If you are new to database migration plugin & don’t know how to use the migration plugin and write basic migration script please read Getting Started. (See this blog post for quick start.)

This plugin can generates most of the database change logs automatically by using liquibase build-in changes. But there are situations & changes where this plugin can’t help you out to write the changes.

For example: To rename a column ( a field in grails domain class), this plugin generates a script which removes the column with old name & creates a new column, which is not acceptable when there are records in the production database.
Another example can be where you have to migrate some data from one table to another table without repeating the code & in proper way. Similarly there are many more situations where you could not depend on this plugin’s auto generated code.
These situations can be handled by writing database changes using Groovy code (as long as you are using Groovy DSL file format). These changes use the grailsChange closure name and are contained in a changeSet closure like standard built-in tags. See Groovy Changes to read more about this.
Here I’m providing some scenarios where you can easily migrate your data & database structure without data redundancy & loss of data by writing custom grails db migration scripts which the plugin will not generate for you.

Case 1: Updating Some Records

Suppose you have added a not null field in a domain class & want to add default value or add/update data to the other fields based on some conditions. Here you can write:

1.1) Using Liquibase classes:

import liquibase.statement.core.UpdateStatement

databaseChangeLog = {

    // Some other changeSets

    changeSet(author: "Shashank (generated)", id: "1381499382647-13") {
        addColumn(tableName: "ufile") {
            column(name: "file_group", type: "varchar(255)") {
                constraints(nullable: "false")
            }
        }
    }

    changeSet(author: 'Shashank Agrawal', id: 'file-uploader-10112013-1')  {
        grailsChange {
            change {
                def statements = []

                // Example 1: Updating file_group column with some value based on where clause
                statements << new UpdateStatement('my_database_name', 'ufile')
                        .addNewColumnValue('file_group', "customerLogo")
                        .setWhereClause(" id < 8")

                statements << new UpdateStatement('my_database_name', 'ufile')
                        .addNewColumnValue('file_group', "blogLogo")
                        .setWhereClause(" id in (13,14)")

                statements << new UpdateStatement('my_database_name', 'ufile')
                        .addNewColumnValue('type', "LOCAL")

                statements << new UpdateStatement('my_database_name', 'ufile')
                        .addNewColumnValue('date_created', new Date())

                sqlStatements(statements)   // Executing all statements at once

                confirm 'Initializing default value'    // Optional method call to display message

            }
        }
    }

}

1.2) Using SQL Query:

databaseChangeLog = {

    // Other changesets

    changeSet(author: "Shashank Agrawal", id: "07-13-2013-01") {
        grailsChange {
            change {
                sql.execute("update ufile set type = ‘LOCAL’ where id > 14")
                confirm 'Updating records.'
            }
        }
    }

}

Case 2: Inserting records to a table

Suppose you need to migrate some data from one table to another table or you simply need to add data to table.
import liquibase.statement.core.InsertStatement


databaseChangeLog = {

    changeSet(author: "Shashank Agrawal", id: "my-custom-unique-id-for-each-changeSet-11212013-1") {
        grailsChange {
            change {
                def statements = []

                // Iterating through each row in table.
                sql.eachRow('select * from old_table_name') {

                    def insertStatement = new InsertStatement("my_database_name", "new_table_name")
                            .addColumnValue("id", it.id)
                            .addColumnValue("version", 0)
                            .addColumnValue("date_created", it.date_created)
                            .addColumnValue("last_updated", new Date())
                            .addColumnValue("some_other_field", it.id)
                            .addColumnValue("user_id", 0)

                    statements.add(insertStatement)

                }

                sqlStatements(statements)   // Executing every statement at one shot

                confirm 'Adding records to new table' // Optional line. Just for message.
            }
        }
    }

}

Case 3: Using Grails domain directly to update

There are situations where when we add some new data fields in a domain class table which already has records.
For example: If you added a date field. In this case, grails adds the column with type datetime but its default value is initialized to 0000-00-00 00:00:00. Due to which grails throws exception on server startup. So these fields needs to be initialized before server startup. Here is an example:
import liquibase.statement.core.*
import com.some.domain.Post

databaseChangeLog = {

    // Auto generated change sets which adds column to the post table.

    grailsChange {
        change {
            Post.list().each { postInstance ->
                postInstance.dateCreated = new Date()
                postInstance.lastUpdated = new Date()
                postInstance.isActive = true
                postInstance.save(failOnError: true, flush: true)
            }
        }
    }

}

Case 4: Increasing field size for a text field

databaseChangeLog = {

    changeSet(author: 'Shashank', id: 'post.description.051020123') {
        comment { 'increase description to 1400 chars' }
        modifyDataType(tableName: 'post', columnName: 'description', newDataType: 'varchar(1400)')
    }

}

Case 5: Dropping a column

databaseChangeLog = {

    changeSet(author: "Shashank", id: "1319193290536-6") {
        dropColumn(columnName: "canPublish", tableName: "post")
    }

}
About CauseCode: We are a technology company specializing in Healthtech related Web and Mobile application development. We collaborate with passionate companies looking to change health and wellness tech for good. If you are a startup, enterprise or generally interested in digital health, we would love to hear from you! Let's connect at bootstrap@causecode.com

Leave a Reply

Your email address will not be published. Required fields are marked *

STAY UPDATED!

Do you want to get articles like these in your inbox?

Email *

Interested groups *
Healthtech
Business
Technical articles

Archives