Grails Database Migration Plugin

Grails Database Migration Plugin is very useful for managing SQL database for your grails application. The most useful aspect of it is that it can generate SQL  scripts from current GORM. In my previous job, we used MyBatis to manage Database, however. With MyBatis we had to create SQL scripts manually and to make it according to GORM was very time consuming, error prone and not efficient process.

In this space, i will cover the installation, setup and everyday use of Grails Database Migration Plugin.

Pre-Condition: SQL Db setup and installed

Install & Setup DataBase Migration Plugin

1. Installation.

To install Grails Database Migration Plugin, run the following command:

grails install-plugin database-migration
-Or-

Add the line ‘runtime “:database-migration:1.1″‘ in the ‘plugin’ code section in grails-app/conf/BuildConfig.groovy just like this:

To verify, open Dependency report (‘grails open dep-report’) and see if there DB migration plugin listed just like this:

2. Generate Initial Script

Next, we are going to generate intial migration script:

grails dbm-generate-changelog changelog.groovy

This will generate ‘changelog.groovy’ containing SQL scripts to create current GORM state and save it in ‘grails-app/migrations’ folder

Note:You may choose to run ‘grails dbm-changelog-sync’ that will record that the changes have already been applied if you don’t wish to dump tables and test this script.

3. Update Datasource

Last, we need to change the app DataSource to inform GORM that it does not need to ‘create’, ‘update’, or do anything with the Database at the application startup since we are going to use DB migration plugin for ‘create’, ‘update’ the app database. So, update variable ‘dbCreate’ in ‘grails-app/conf/DataSource.groovy’ to nothing just like the following:

Using Database Migration Plugin

Test Run

At any given time, to test Database migration plugin and scripts do the following:

1. Drop all tables in the Database.

You can do that manually or utilize Migration plugin by running the following command

grails dbm-rollback-to-date  1900-01-01

2. Roll back the record. ONLY If you drop tables manually you need to update migration record log, so that it appears no migrations scripts have run so far. To do that run the following command:

grails dbm-drop-all

If you have run ‘dbm-rollback-to-date’ as specified above, it would have done this step for you already

3. Run the Scripts and Check Database

To run all of the migration scripts call the following command:

grails dbm-update

This will run any script that is registered in the migration log as hasn’t been run. In our case, we drop all the records, so it will run all migration scripts

Note: At any time, you can check the migration plugin status of what migrations scripts have and have not been run by calling ‘grails dbm-status’. It will display only scripts that haven’t been run

Generating Migrations Scripts

After making changes in GORM all tested then run the following command:

grails dbm-gorm-diff --add filename.groovy

This will generation and register the script as well as include for the filename in the main changelog for you. You are done. Other developer will get your new script and run ‘dbm-update’ to update their database accordingly

Other Useful Migration Commands

1. dbm-clear-checksum.  You can change any Migrations scripts manually but if you do, then don’t forget to run ‘grails dbm-clear-checksum’ afterwards to make the changes valid

2.  dbm-gorm-diff without ‘–add’. If you run only ‘grails dbm-gorm-diff’ without the ‘–add’ tag, it will display what are the current difference between GORM and your database

Run Migration Scripts at Start

Its all good running ‘dbm-update’ every single time you receive new migration script in your workspace , however. You are able to make it automatic at next time your grails application start by adding  the following in the conf/Config.groovy file:

grails.plugin.databasemigration.updateOnStart = true
grails.plugin.databasemigration.updateOnStartFileNames = ["changelog.groovy"]
grails.plugin.databasemigration.changelogLocation = 'grails-app/migrations'

This will detect what changes hasn’t been run from the change log and run appropriate migration scripts all automatically at grails app startup.

Running the migration script at startup including times you making changes in GORM (domain classes) will cause conflicts between GORM and database. Learn, how to solve the problem in post ‘Grails Environment Aware Framework

Issues Encountered

1. ‘Change Set changelog.groovy::[…] (generated) failed.  Error: Error executing SQL ALTER TABLE[…]

We got this error after cloning the project and running the migrations scripts.

It turned out that this error was caused because only on Windows when you generate initial migration script it includes references to specific database name.

To solve it, we manually deleted these references in the initial migration script. Afterwards don’t forget to run ‘dbm-clear-checksum’ to make changes valid

2. ‘Error: Error executing SQL CREATE INDEX `[…]: Incorrect index name’

This error seems to be specific to Database installation,setup or version. Either way, the solution was to manually updated migration script so that this index is created after the foreign key is added

As you can see it is the same index name as marked in above screenshot. So moving the changeSet 5 in front of the changeSet 4 and then updating the numbering(red color) solved the problem for us

Afterwards, don’t forget to run ‘dbm-clear-checksum’ to make the changes valid

3. Grails Migration plugin fails silently.

Most of the time this happens when there is syntax error in one of the migration scripts. I find myself often times deleting a migrations script and then forgetting to update the initial changelog. groovy. At the bottom, the changelog.groovy contains ‘include’ for each migrations script. If you delete some migration script but fail to update (remove the includes) the changelog.groovy, then migration plugin fails silently for ‘update’, ‘status’ and other cmd.

Debugging Grails Database Migration plugin

1. Enable logging. It may help troubleshoot your issue if you enable the Database Migration Plugin by adding following line in Config.groovy:

log4j = {
           ....
           debug 'grails.plugin.databasemigration', 'liquibase'
    }

Useful Sources:

COUNTDOWN TO GRAILS 2.0: DATABASE MIGRATIONS

Working with the Grails Database Migration Plugin

Database Migration Plugin

Leave a Reply

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