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 the 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 the 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 generate 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 the 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 the 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 add the column with type datetime but its default value is initialized to 0000-00-00 00:00:00. Due to which grails throws an exception on server startup. So these fields need 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")
}
}
