When developing a web application, it is often important to make structural changes to the database backing it, as well as keep track of these changes and make it easy for other developers to make the same ones. For this, database migrations are the tool to use. They give you this support and functionality, as well as many other things such as rolling back database changes and generating data for newly created non-nullable columns. If you are familiar with the Rails web framework, then you've probably used these before, but these don't come natively using Grails. This post aims to give a quick overview of getting up and running with them, as well as writing your own custom migrations for specific use cases.
Getting Started
The first step is to add the dependency for the Grails Database Migration Plugin to your BuildConfig.groovy file.
plugins {
runtime ":database-migration:1.3.8"
}
Then, in your DataSource.groovy file, you'll want to disable Grails' automatic update of your database for all environments:
environments {
development {
dataSource {
// Change this:
dbCreate = "create-drop"
// To this:
dbCreate = "none"
}
}
// Repeat for test and production
// ...
}
Afterwards, you'll want to generate the initial changelog which is essentially a dump of your database in its current state, in migration form. Migrations with Grails can be either Groovy or XML format, but using the Groovy format has its advantages (XML files are unable to include Groovy files). To generate the initial changelog, issue the following command:
# Generate using Groovy
grails dbm-generate-changelog changelog.groovy
# Generate using XML
grails dbm-generate-changelog changelog.xml
This will create the changelog.groovy (or changelog.xml) file in the grails-app/migrations folder.
This is great if you've already created your database, but what if you haven't? If you're starting from scratch and Grails hasn't autogenerated the database for you, you can issue the following command to generate the initial changelog from your domain classes.
grails dbm-generate-gorm-changelog changelog.groovy
Now we are ready to start performing migrations.
After creating a migration, you'll always want to update the database, and then mark all changes as executed in the database. You can do that with the following commands:
# Update the database
grails dbm-update
# Mark changes as executed
grails dbm-changelog-sync
Generating and Using Migrations
Every time you make a change to your schema, such as adding a field to a domain class, creating a new domain class, etc., you'll need to create and run a migration. After making a schema change, you'll want to issue the following command to generate a new migration to be run:
# Replace name-of-migration with your name, such as create-user-class.groovy
grails dbm-gorm-diff name-of-migration.groovy -add
This particular command would create the name-of-migration.groovy file in the grails-app/migrations folder. To add this file to the list of migrations, we append the -add option. This will add the include file: 'name-of-migration.groovy' line in your initial changelog file. This is so that whenever a new developer or you try to run the migrations from scratch, they'll all execute sequentially in the order that they were created. You can open up your newly created migration and check it out. Here's an example of one that I created for a Room domain class:
class Room {
String name
Integer roomNumber
String description
static constraints = {
description(nullable: true)
}
}
// Database migration for the creation of the Room class
databaseChangeLog = {
changeSet(author: "cscarborough (generated)", id: "1393858050907-1") {
createTable(tableName: "rooms") {
column(name: "id", type: "number(19,0)") {
constraints(nullable: "false", primaryKey: "true", primaryKeyName: "roomsPK")
}
column(name: "version", type: "number(19,0)") {
constraints(nullable: "false")
}
column(name: "name", type: "varchar2(255 char)") {
constraints(nullable: "false")
}
column(name: "room_number", type: "number(10,0)") {
constraints(nullable: "false")
}
column(name: "description", type: "varchar2(255 char)")
}
}
}
Now we can go ahead and run it:
grails dbm-update
grails dbm-changelog-sync
This is the basic usage for the migrations plugin. Things aren't always this simple though. Let's take a look at creating a custom migration.
Creating a Custom Migration
There are many different reasons you may need to write a custom migration. One of the reasons I use this for time and time again, is when a non-nullable column is added to a table that already has data in it. After adding the field to your domain class, if you generate the migration, you'll see something like this:
databaseChangeLog = {
changeSet(author: "cscarborough (generated)", id: "1393249516089-1") {
addColumn(tableName: "rooms") {
column(name: "max_capacity", type: "number(10,0)") {
constraints(nullable: "false")
}
}
}
}
If you run this migration, you will receive a very large stacktrace, complaining about adding a NOT NULL column to a table with data in it. We can resolve this by creating a custom migration. The idea is that we want to create the column as nullable, set some default data, then add our not null constraint. Here's how that looks:
databaseChangeLog = {
changeSet(author: "Casey Scarborough", id: "201404020858-add-max-capacity-to-rooms") {
addColumn(tableName: "rooms") {
column(name: "max_capacity", type: "number(10,0)")
}
grailsChange {
change {
sql.execute("UPDATE rooms SET max_capacity = 100")
confirm "Successfully set default value for max_capacity."
}
}
addNotNullConstraint(tableName: "rooms", columnName: "max_capacity")
}
}
Whenever writing your own migrations, your id can be whatever you like, but I prefer a timestamp and a short description of the migration. You can also add a call to confirm, which will display a message when the change successfully completes.
Now, if you re-run the migration it will succeed.
Custom Migration Examples
The following are some examples of common custom migrations:
Dropping a Table or Column
databaseChangeLog = {
changeSet(author: "Casey Scarborough", id: "201404020905-drop-column") {
dropColumn(tableName: "rooms", columnName: "max_capacity")
dropTable(tableName: "crappy_table")
confirm "Successfully dropped column and table."
}
}
Renaming a Table or Column
databaseChangeLog = {
changeSet(author: "Casey Scarborough", id: "201404020907-rename-column") {
renameTable(oldTableName: "rooms", newTableName: "room")
renameColumn(tableName: "room", oldColumnName: "room_number", newColumnName: "room_no")
confirm "Successfully renamed table and column."
}
}
Changing a Column's Data Type
databaseChangeLog = {
changeSet(author: "Casey Scarborough", id: "201404020909-modify-data-type") {
modifyDataType(tableName: "rooms", columnName: "description", newDataType: "varchar2(1000 char)")
confirm "Successfully updated the description column."
}
}
Deleting Records
databaseChangeLog = {
changeSet(author: "Casey Scarborough", id: "201404021049-delete-records") {
delete(tableName: "rooms", whereClause: "room_number < 100")
confirm "Successfully removed all rooms with room number less than 100."
}
}