What is a good workflow for database migration in Grails?

Question

I want to use the database-migration grails plugin for database migration. When I start my Grails app the first time all the database tables are created automatically. The production setting in my DataSource.groovy is:

production {


    dataSource {
        dbCreate = "update"
        url = "jdbc:mysql://localhost/myapp?useUnicode=yes&characterEncoding=UTF-8"
        username = "test"
        password = "test"
        dialect = org.hibernate.dialect.MySQL5InnoDBDialect
        properties {
           validationQuery = "select 1"
           testWhileIdle = true
           timeBetweenEvictionRunsMillis = 60000
        }
    }
}

In my config.groovy I set:

grails.plugin.databasemigration.updateOnStart = true
grails.plugin.databasemigration.updateOnStartFileNames = ['changelog.groovy']

When I add properties to my domain classes I need to adjust the changelog file. What is the best way to do database migration in this case? What are the steps I have to do when I add or remove columns?

Arriaga

Answer 1

As you're probably aware, the dbcreate directive is not recommended for production use:

You can also remove the dbCreate setting completely, which is recommended once your schema is relatively stable and definitely when your application and database are deployed in production.

So keep in mind that you will need to remove this (or set to 'none').

---

Initial Baseline Workflow

1. Define current state
2. Create database from change log or mark as up-to-date
3. Set config options

The first step is to get the changelog to reflect the current state. If you've got an existing database, you want to use that to define the baseline. Otherwise, use GORM to define the tables.

These commands will generate a baseline for your database. Also I choose to use the groovy DSL format rather than liquibase XML, because readability.

Existing Database

If you've got a production database with data already, its a little bit tricky. You will need to access the database or a copy of it from your grails environment. If you manipulate a copy, you will need to apply the updates back to your production (and potentially manage it as a planned outage).

The command is:

grails [environment] dbm-generate-changelog changelog.groovy

...where environment optionally specifies the dev/test/prod/custom environment the database is defined as.

Following that, mark the database as 'up-to-date' with regards to the changelog:

grails [environment] dbm-changelog-sync

Then reapply the database to production, if neccesary.

New Database

If you don't have an existing database (or don't care):

grails dbm-generate-gorm-changelog changelog.groovy

Then, to create the database from the changelog:

grails [environment] dbm-update

Configuration

You've already correctly got the options set:

grails.plugin.databasemigration.updateOnStart = true
grails.plugin.databasemigration.updateOnStartFileNames = ['changelog.groovy']

These options simply mean that the plugin will attempt to apply the changes to the database when the application starts.

---

Development Workflow

1. Make changes to domains
2. Generate changelog identifying differences
3. (Backup and) Update the database

So now you've got a database up-to-date, and you're smashing out changes to the domain classes, adding new ones and changing validation properties.

Each time you want to record your changes, you want to compare your GORM classes to what exists in the database, and create a new changelog file to record the difference:

grails [environment] dbm-gorm-diff [meaningful name].groovy --add

Here environment is the database you are comparing against, and meaningful name should reflect in some way the change being applied (perhaps a JIRA issue key, or a version number, or a description).

The --add flag will insert an include statement in changelog.groovy.

If you've configured updateOnStart, then you're done! Otherwise, to manually process the update, reuse the command:

grails [environment] dbm-update

---

RTFM

• Plugin documentation - Getting Started
• Plugin documentation - General Usage
• Confile's answer above points to a good tutorial that goes into detail about manual changes to changelogs
• Liquibase documentation - Changesets (Uses the XML format, but useful for understanding concepts)

Frodine

Answer 2

The approach that I would use is to migrate every table to a Grails domain with the mapping (very important!) properly set.

Then leave Grails to create the database the first time and then populate it with a previous backup of the database you want to migrate.

After this set Grails config to update the database every time it starts.

I know it seems a little bit messy but if I´ve to do it I would´ve do it this way.

Hope it helps :)

Pissed

Answer 3

I found a very good tutorial, which explains the solution to my problem:

Grails Db Migration Tutorial

Arriaga

Please provide a summary of the tutorial here so that if the link becomes dead, the solution would still be available here. – Cecil

Answer 4

Workflow consists of following steps:

1) Install the plugin using the command grails install-plugin database-migration

2) After setting up the plugin run the command:

grails dbm-generate-gorm-changelog changelog.groovy or changelog.xml

By default it will generate a file on location grails-app/migrations/changelog.groovy or .xml

3) set dataSource dbcreate='none'

3) Now, run

grails dbm-changelog-sync

this will create a table name databasechangelog and will insert entries according to your existing schema.

Thats it.

Enarthrosis