Show Navigation

Grails Database Migration

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

Authors: Puneet Behl, Nirav Assar, Sergio del Amo

Grails Version: 6.0.0-RC1

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:

or

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.

INFO: Please read through MySQL Documentation to configure database user for upto-date instructions.

Setup Grails to use a MySQL database

> CREATE USER 'devel'@'%' IDENTIFIED BY 's3cur3';
> CREATE DATABASE devDb character set utf8 collate utf8_general_ci;
> GRANT ALL ON data.* TO 'devel'@'%';
> USE devDb;
> SHOW DATABASES;
> SHOW TABLES;

Now we need to configure the Grails application to point to the new devDb database. We will be editing the build.gradle, application.yml files, export environment variables MYSQL_USER, and MYSQL_PASSWORD.

build.gradle
dependencies {
...
    runtimeOnly 'mysql:mysql-connector-java:5.1.36'
...
}
grails-app/conf/application.yml
dataSource:
    driverClassName: com.mysql.jdbc.Driver
    dialect: org.hibernate.dialect.MySQL5InnoDBDialect
    username: '${MYSQL_USER}'
    password: '${MYSQL_PASSWORD}'
    pooled: true
    jmxExport: true
environments:
    development:
        dataSource:
            dbCreate: none (1)
            url: jdbc:mysql://localhost:3306/devDb?useUnicode=yes&characterEncoding=UTF-8
$ export MYSQL_USER='devel'
$ export MYSQL_PASSWORD='s3cur3'
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/grails/dbmigration/Person.groovy
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=package]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=import]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=class]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=properties]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=closeClass]

3.3 Install Database Migration Plugin

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

build.gradle
buildscript {
   dependencies {
      ...
   }
}

dependencies {
    ...
    implementation 'org.grails.plugins:database-migration:4.2.0'
}

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

  1. Define the current state of the domain

  2. Create the database from the changelog using liquibase

  3. Set configuration options in the application to use the database migration plugin

Development Workflow

  1. Make changes to domain objects

  2. Use the plugin to generate changelog additions for the database

  3. 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

$ ./gradlew runCommand "-Pargs=dbm-generate-gorm-changelog changelog.groovy"

This will generate a changelog such as:

grails-app/migrations/changelog.groovy
databaseChangeLog = {

    changeSet(author: "behl (generated)", id: "1687423705657-1") {
        createTable(tableName: "person") {
            column(autoIncrement: "true", name: "id", type: "BIGINT") {
                constraints(nullable: "false", 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:

$ ./gradlew runCommand "-Pargs=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

id

bigint(20)

NO

Pri

<null>

auto_increment

version

bigint(20)

NO

<null>

age

int(11)

NO

<null>

name

varchar(255)

NO

<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.

400

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

grails-app/domain/grails/dbmigration/Person.groovy
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=package]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=import]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=class]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=properties]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=openConstraints]
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=ageConstraints]
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=closeConstraints]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=closeClass]

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 effect. Run the next command:

$ ./gradlew runCommand "-Pargs=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: "behl (generated)", id: "1687423992173-1") {
        dropNotNullConstraint(columnDataType: "int", columnName: "age", tableName: "person")
    }
}

if we run the migration

$ ./gradlew runCommand "-Pargs=dbm-update"

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

Field

Type

Null

Key

Default

Extra

id

bigint(20)

NO

Pri

<null>

auto_increment

version

bigint(20)

NO

<null>

age

int(11)

YES

<null>

name

varchar(255)

NO

<null>

3.7 Add Attributes

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

400
grails-app/domain/grails/dbmigration/Person.groovy
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=package]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=import]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=class]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=properties]
    String streetName
    String city
    String zipCode

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=openConstraints]
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=ageConstraints]
        streetName nullable: true
        city nullable: true
        zipCode nullable: true
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=closeConstraints]


Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=closeClass]

If we generate a changeset for these changes:

$ ./gradlew runCommand "-Pargs=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: "behl (generated)", id: "1687424134696-1") {
        addColumn(tableName: "person") {
            column(name: "city", type: "varchar(255)")
        }
    }

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

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

if we run the migration

$ ./gradlew runCommand "-Pargs=dbm-update"

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

> describe person

Field

Type

Null

Key

Default

Extra

id

bigint(20)

NO

Pri

<null>

auto_increment

version

bigint(20)

NO

<null>

age

int(11)

YES

<null>

name

varchar(255)

NO

<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:

  1. The database table schema definition will change

  2. Existing data in the table will have to be split amongst the new database tables created

  3. We can write custom sql in the changelog files to transfer existing data

The image below depicts the redesign:

400

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

grails-app/domain/grails/dbmigration/Person.groovy
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=package]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=import]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=class]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=properties]
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=hasMany]

Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=openConstraints]
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=ageConstraints]
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=closeConstraints]


Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Person.groovy[tag=closeClass]
grails-app/domain/grails/dbmigration/Address.groovy
Unresolved directive in <stdin> - include::/home/runner/work/grails-database-migration/grails-database-migration/complete/grails-app/domain/grails/dbmigration/Address.groovy[indent=0]

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:

$ ./gradlew runCommand "-Pargs=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

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
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

$ ./gradlew runCommand "-Pargs=dbm-update"

The person table looks like:

> DESCRIBE person

Field

Type

Null

Key

Default

Extra

id

bigint(20)

NO

Pri

<null>

auto_increment

version

bigint(20)

NO

<null>

age

int(11)

YES

<null>

name

varchar(255)

NO

<null>

The address table looks like:

> DESCRIBE address

Field

Type

Null

Key

Default

Extra

id

bigint(20)

NO

Pri

<null>

auto_increment

version

bigint(20)

NO

<null>

person_id

bigint(20)

NO

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:

  1. Make changes to Domain objects.

  2. Generate the changelog which will identify database structure differences between the existing database and the edited domain objects.

  3. Consider any existing data to migrate.

  4. 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