Grails Database Migration

In this guide we will learn how to use the Grails Database Migration Plugin

Authors: Nirav Assar, Sergio del Amo

Grails Version: 4.0.1

1 Grails Training

Grails Training - Developed and delivered by the folks who created and actively maintain the Grails framework!.

2 Getting Started

In this guide you are going to learn how to use the Grails Database Migration Plugin. We will create an application with simple domain classes and extend them to achieve the following:

Baseline the database for database migration
Change a column to nullable
Add columns to an existing table
Redesign the table and migrate the existing data

2.1 What you will need

To complete this guide, you will need the following:

Some time on your hands
A decent text editor or IDE
JDK 1.8 or greater installed with JAVA_HOME configured appropriately

2.2 How to complete the guide

To get started do the following:

Download and unzip the source

Clone the Git repository:
git clone https://github.com/grails-guides/grails-database-migration.git

The Grails guides repositories contain two folders:

initial Initial project. Often a simple Grails app with some additional code to give you a head-start.
complete A completed example. It is the result of working through the steps presented by the guide and applying those changes to the initial folder.

To complete the guide, go to the initial folder

cd into grails-guides/grails-database-migration/initial

and follow the instructions in the next sections.

You can go right to the completed example if you cd into grails-guides/grails-database-migration/complete

3 Writing the Application

We are going to write a simple application involving a class Person. The Person class will initially have its own attributes that also contain information for an address. As we evolve the domain we will split attributes into its own Address domain class. We will use Grails Database Migration Plugin to manage these transitions.

3.1 Install Database

Create a MySQL Database

Let’s setup a physical database with MySql, instead of relying on the default H2 in memory database.

Go to MySql to install a database
Create your admin access with id of root and password root
Open the MySql Command Line Client

Run these commands in the command line client in order to create and use the database. The show tables command should return an empty set.

Setup Grails to use a MySQL database

> create database dbmigration character set utf8 collate utf8_general_ci;
> use dbmigration;
> show databases;
> show tables;

Now we need to configure the Grails application to point to the new dbmigration database. We will be editing the build.gradle and application.yml files.

build.gradle

dependencies {
...
    runtime 'mysql:mysql-connector-java:5.1.36'
...
}

grails-app/conf/application.yml

dataSource:
    pooled: true
    jmxExport: true
    driverClassName: com.mysql.jdbc.Driver
    dialect: org.hibernate.dialect.MySQL5InnoDBDialect
    username: root
    password: root
environments:
    development:
        dataSource:
            dbCreate: none (1)
            url: jdbc:mysql://localhost:3306/dbmigration?useUnicode=yes&characterEncoding=UTF-8

1	`dbCreate` defines whether we want to auto-generate the database from the domain model. We set it to `none` since we are going to manage the management of the database schema with Grails Database Migration Plugin.

3.2 Domain Class

Create a domain class in the appropriate package:

grails-app/domain/demo/Person.groovy

package demo

import grails.compiler.GrailsCompileStatic

@GrailsCompileStatic
class Person {

    String name
    Integer age

}

3.3 Install Database Migration Plugin

To install the Grails Database Migration Plugin we need to add to build.gradle:

build.gradle

buildscript {
   dependencies {
      ...
        classpath 'org.grails.plugins:database-migration:3.1.0.RC1'
   }
}

dependencies {
    ...
    compile 'org.grails.plugins:database-migration:3.1.0.RC1'
    compile 'org.liquibase:liquibase-core:3.6.1'
}

Tell Gradle about the migrations folder location too. Make sure this configuration is BEFORE the dependencies configuration, so that the folder declaration takes effect:

build.gradle

sourceSets {
    main {
        resources {
            srcDir 'grails-app/migrations'
        }
    }
}

Checkout the Grails Database Migration Plugin documentation.

3.4 Database Migration In Action

Database migrations are schema changes to the database while preserving the existing data. Without a tool to manage database migrations, teams may rely on manual sql, error prone communication processes, and costly risk management to implement solutions. The database migration plugin lets you manage structural changes made to the database. It automates incremental changes, makes them repeatable, visible and trackable. You can commit these changes in to source control.

The general workflow involved in using the plugin is as follow:

Baseline

Define the current state of the domain
Create the database from the changelog using liquibase
Set configuration options in the application to use the database migration plugin

Development Workflow

Make changes to domain objects
Use the plugin to generate changelog additions for the database
Update the database using the plugin

3.5 Baseline for Database Migration

Instead of using GORM schema auto-generation, the database schema is going to changed with Liquibase; the database migration tool which the Grails Database Migration Plugin uses.

We want to run migrations on startup and our migrations are going to be located in changelog.groovy

grails-app/conf/application.yml

...
grails:
    plugin:
        databasemigration:
            updateOnStart: true
            updateOnStartFileName: changelog.groovy
...

The plugin comes with several commands, one of those commands, dbm-generate-gorm-changelog generates an initial changelog with a Groovy DSL file from current GORM classes

> grails dbm-generate-gorm-changelog changelog.groovy

This will generate a changelog such as:

grails-app/migrations/changelog.groovy

databaseChangeLog = {

    changeSet(author: "Nirav Assar (generated)", id: "1497549057046-1") {
        createTable(tableName: "person") {
            column(autoIncrement: "true", name: "id", type: "BIGINT") {
                constraints(primaryKey: "true", primaryKeyName: "personPK")
            }

            column(name: "version", type: "BIGINT") {
                constraints(nullable: "false")
            }

            column(name: "age", type: "INT") {
                constraints(nullable: "false")
            }

            column(name: "name", type: "VARCHAR(255)") {
                constraints(nullable: "false")
            }
        }
    }
}

You may see the below INFO log statement. It is a non-error:

INFO 7/24/17 11:29 AM: liquibase: Can not use class org.grails.plugins.databasemigration.liquibase.GormDatabase as a
Liquibase service because it does not have a no-argument constructor

Move the initial changelog to its own file and reference it from the main changelog file.

$ cp grails-app/migrations/changelog.groovy grails-app/migrations/create-person-table.groovy

Replace the content of changelog.groovy with:

grails-app/migrations/changelog.groovy

databaseChangeLog = {
    include file: 'create-person-table.groovy'
}

Apply the migration:

$ grails dbm-update

The database tables are created:

> show tables;

Tables in dbmigration

DATABASECHANGELOG

DATABASECHANGELOGLOCK

person

The tables DATABASECHANGELOG, DATABASECHANGELOGLOCK are used by the Database Migration Plugin to keep track of database migrations.

The person table correspond to the Person Domain Class.

> describe person;

Field

Type

Null

Key

Default

Extra

bigint(20)

Pri

<null>

auto_increment

version

bigint(20)

<null>

age

int(11)

<null>

name

varchar(255)

<null>

3.6 Make Column Nullable

In this section we will do a simple change to make a column nullable. The age column currently requires a value. We make it nullable, and proceed to migrate the database to reflect that.

In the Person domain object, make the age attribute nullable:

grails-app/domain/demo/Person.groovy

package demo

import grails.compiler.GrailsCompileStatic

@GrailsCompileStatic
class Person {

    String name
    Integer age

    static constraints = {
        age nullable: true
    }

}

Note that making the change in the domain object does not affect the database. We must generate additions to the changelog.groovy for the change to take affect. Run the next command:

> grails dbm-gorm-diff change-age-constraint-to-nullable.groovy --add

A new include statement has been added to changelog.groovy

grails-app/migrations/changelog.groovy

databaseChangeLog = {
    include file: 'create-person-table.groovy'
    include file: 'change-age-constraint-to-nullable.groovy'
}

An individual changeset is created too:

grails-app/migrations/change-age-constraint-to-nullable.groovy

databaseChangeLog = {

    changeSet(author: "Nirav Assar (generated)", id: "1497551594095-1") {
        dropNotNullConstraint(columnDataType: "int", columnName: "age", tableName: "person")
    }
}

if we run the migration

$ grails dbm-update

The column age in the person table is nullable as expressed in the constraint:

Field

Type

Null

Key

Default

Extra

bigint(20)

Pri

<null>

auto_increment

version

bigint(20)

<null>

age

int(11)

YES

<null>

name

varchar(255)

<null>

3.7 Add Attributes

We will add some attributes to the Person class for an address to demonstrate our second migration.

grails-app/domain/demo/Person.groovy

package demo

import grails.compiler.GrailsCompileStatic

@GrailsCompileStatic
class Person {

    String name
    Integer age
    String streetName
    String city
    String zipCode

    static constraints = {
        age nullable: true
        streetName nullable: true
        city nullable: true
        zipCode nullable: true
    }


}

If we generate a changeset for these changes:

> grails dbm-gorm-diff add-address-fields-to-person.groovy --add

The command adds a new include statement to changelog.groovy

grails-app/migrations/changelog.groovy

databaseChangeLog = {
    include file: 'create-person-table.groovy'
    include file: 'change-age-constraint-to-nullable.groovy'
    include file: 'add-address-fields-to-person.groovy'
}

An individual changeset is created too:

grails-app/migrations/add-address-fields-to-person.groovy

databaseChangeLog = {

    changeSet(author: "Nirav Assar (generated)", id: "1497552798178-1") {
        addColumn(tableName: "person") {
            column(name: "city", type: "varchar(255)")
        }
    }

    changeSet(author: "Nirav Assar (generated)", id: "1497552798178-2") {
        addColumn(tableName: "person") {
            column(name: "street_name", type: "varchar(255)")
        }
    }

    changeSet(author: "Nirav Assar (generated)", id: "1497552798178-3") {
        addColumn(tableName: "person") {
            column(name: "zip_code", type: "varchar(255)")
        }
    }
}

if we run the migration

$ grails dbm-update

New columns streetName, city, zipCode are created in the person table;

$ describe person

Field

Type

Null

Key

Default

Extra

bigint(20)

Pri

<null>

auto_increment

version

bigint(20)

<null>

age

int(11)

YES

<null>

name

varchar(255)

<null>

city

varchar(255)

YES

<null>

street_name

varchar(255)

YES

<null>

zip_code

varchar(255)

YES

<null>

3.8 Redesign Tables

Let’s say we would like to redesign Person to split the address fields into its own domain object. The idea behind this would be that now a Person can have many Address 's. When doing this type of domain object redesign we have to consider a few aspects:

The database table schema definition will change
Existing data in the table will have to be split amongst the new database tables created
We can write custom sql in the changelog files to transfer existing data

The image below depicts the redesign:

The Person and Address domain objects can be coded as below:

grails-app/domain/demo/Person.groovy

package demo

import grails.compiler.GrailsCompileStatic

@GrailsCompileStatic
class Person {

    String name
    Integer age
    static hasMany = [addresses: Address]

    static constraints = {
        age nullable: true
    }


}

grails-app/domain/demo/Address.groovy

package demo

import grails.compiler.GrailsCompileStatic

@GrailsCompileStatic
class Address {
    Person person
    String streetName
    String city
    String zipCode

    static belongsTo = [person: Person]

    static constraints = {
        streetName nullable: true
        city nullable: true
        zipCode nullable: true
    }
}

Run the database migration command that will compare the new domain objects to the existing database and generate the liquibase declarations to migrate the schema:

> grails dbm-gorm-diff create-address-table.groovy –add

The command adds a new include statement to changelog.groovy

grails-app/migrations/changelog.groovy

databaseChangeLog = {
    include file: 'create-person-table.groovy'
    include file: 'change-age-constraint-to-nullable.groovy'
    include file: 'add-address-fields-to-person.groovy'
    include file: 'create-address-table.groovy'
}

An individual changeset is created too:

grails-app/migrations/create-address-table.groovy

    changeSet(author: "Nirav Assar (generated)", id: "1497553930799-1") {
        createTable(tableName: "address") {
            column(autoIncrement: "true", name: "id", type: "BIGINT") {
                constraints(primaryKey: "true", primaryKeyName: "addressPK")
            }

            column(name: "version", type: "BIGINT") {
                constraints(nullable: "false")
            }

            column(name: "city", type: "VARCHAR(255)")

            column(name: "person_id", type: "BIGINT") {
                constraints(nullable: "false")
            }

            column(name: "street_name", type: "VARCHAR(255)")

            column(name: "zip_code", type: "VARCHAR(255)")
        }
    }
    changeSet(author: "Nirav Assar (generated)", id: "1497553930799-2") {
        addForeignKeyConstraint(baseColumnNames: "person_id", baseTableName: "address", constraintName: "FK81ihijcn1kdfwffke0c0sjqeb", deferrable: "false", initiallyDeferred: "false", referencedColumnNames: "id", referencedTableName: "person")
    }
    changeSet(author: "Nirav Assar (generated)", id: "1497553930799-3") {
        dropColumn(columnName: "city", tableName: "person")
    }

    changeSet(author: "Nirav Assar (generated)", id: "1497553930799-4") {
        dropColumn(columnName: "street_name", tableName: "person")
    }

    changeSet(author: "Nirav Assar (generated)", id: "1497553930799-5") {
        dropColumn(columnName: "zip_code", tableName: "person")
    }

We are going to add manually a changeset to move the existing data from the old tables to the new.

The final version of create-address-table.groovy looks like:

grails-app/migrations/create-address-table.groovy

    changeSet(author: "Nirav Assar (generated)", id: "1497553930799-1") {
        createTable(tableName: "address") {
            column(autoIncrement: "true", name: "id", type: "BIGINT") {
                constraints(primaryKey: "true", primaryKeyName: "addressPK")
            }

            column(name: "version", type: "BIGINT") {
                constraints(nullable: "false")
            }

            column(name: "city", type: "VARCHAR(255)")

            column(name: "person_id", type: "BIGINT") {
                constraints(nullable: "false")
            }

            column(name: "street_name", type: "VARCHAR(255)")

            column(name: "zip_code", type: "VARCHAR(255)")
        }
    }
    changeSet(author: "Nirav Assar (generated)", id: "1497553930799-2") {
        addForeignKeyConstraint(baseColumnNames: "person_id", baseTableName: "address", constraintName: "FK81ihijcn1kdfwffke0c0sjqeb", deferrable: "false", initiallyDeferred: "false", referencedColumnNames: "id", referencedTableName: "person")
    }
    (1)
    changeSet(author: "a488338 (generated)", id: "migrate-person-data") {
        sql("""insert into address (version, person_id, street_name, city, zip_code)
              select 0, id, street_name, city, zip_code from person""")
    }
    changeSet(author: "Nirav Assar (generated)", id: "1497553930799-3") {
        dropColumn(columnName: "city", tableName: "person")
    }

    changeSet(author: "Nirav Assar (generated)", id: "1497553930799-4") {
        dropColumn(columnName: "street_name", tableName: "person")
    }

    changeSet(author: "Nirav Assar (generated)", id: "1497553930799-5") {
        dropColumn(columnName: "zip_code", tableName: "person")
    }

1	The changeset we added, executes after the creation of the `Address` table but before the dropping of columns from the `Person` table.

Run the migration

$ grails dbm-update

The person table looks like:

$ describe person

Field

Type

Null

Key

Default

Extra

bigint(20)

Pri

<null>

auto_increment

version

bigint(20)

<null>

age

int(11)

YES

<null>

name

varchar(255)

<null>

The address table looks like:

$ describe address

Field

Type

Null

Key

Default

Extra

bigint(20)

Pri

<null>

auto_increment

version

bigint(20)

<null>

person_id

bigint(20)

MUL

<null>

city

varchar(255)

YES

<null>

street_name

varchar(255)

YES

<null>

zip_code

varchar(255)

YES

<null>

4 Summary

To summarize this guide, we learned how use the database migration plugin to change column names, add columns, and potentially redesign tables while migration existing data. It is important to note that database migration consists of a typical workflow:

Make changes to Domain objects.
Generate the changelog which will identify database structure differences between the existing database and the edited domain objects.
Consider any existing data to migrate.
Execute the database migration scripts.

5 Do you need help with Grails?

Object Computing, Inc. (OCI) sponsored the creation of this Guide. A variety of consulting and support services are available.

OCI is Home to Grails

Meet the Team