Database migrations are an important facet of web development. Preserving existing data while seamlessly adding new functionality and tables is critical when making incremental database changes to production applications. Without a tool to manage database migrations, teams rely on manual sql, error prone communication processes, and costly risk management to implement solutions.
What is a Database Migration
First let's define database migration. Simply put, database migrations are changes to a database that is already running in production, and the customer wants to retain the data for future releases. If this is not the case, then it is sufficient to not really consider it a database migration, and thus, you can rely on GORMs dbCreate configuration.
GORM dbCreate has Problems
If you haven't already realized it, hibernate's dbCreate feature has its problems. It does not support database migrations robustly. Some basic database changes work, such as adding a completely new table or adding new columns, but that is it. Briefly, here are some problems with dbCreate. I will leave in depth research up to the reader:
- Does not delete columns
- No conversion of datatypes
- No data migration
- Cannot change a constraint on a column
- Cannot add a column with not-null constraint on existing table
- Rows read and updated will not be able to update ever again
Manually managing database migration is a flat out nightmare. Believe me, I have lived it and I realize it fully now that a tool is being used to manage migrations. Coordination between developers becomes huge overhead, as well as the communication between the developers and the DBA. Manually generating sql is a pain, mired in the fact that you are often creating the same type of sql over and over again. In addition, some of the sql will become messy and cryptic and thus hard to understand over time. Then you have the burden of ordering separate sql files for execution, or the alternate solution of creating some very large sql file that is impossible to understand.
The Case for a Controlled Database Migration plan
Adopting a tool adds structure and process to managing database changes. Teams can rely on a tool for communication of changes, rather then email. We also need to treat databases with version control. This is the same respect we give to code, and databases deserve it as well. Most importantly, database migrations enable agility and meeting customer needs, and we want to perform them well.
Examples
In this blog, we will run through three sample migrations. The sample domain objects used are Person and Address. We will start off with a Person object and eventually end up with a Person object that has many Addresses.
- Migration 1 - make column nullable
- Migration 2 – add address columns
- Migration 3 – Add Address Table
Let's assume we have a grails app up and running. To install the plugin, execute
grails install-plugin database-migrationLet's also assume we have a basic Person class with a few attributes. The app is already in production and has data in it. We now would like to migrate.
Migration 1 - make column nullable
The change we want to make is to make age nullable. But before we make the change, we need to do some initialization for liquibase.
Initialization Step
- Remove the dbCreate attribute from DataSource.groovy. Hibernate will no longer handle db changes.
- Create a baseline changeset.
grails dbm-generate-gorm-changelog changelog.groovy
- Establish the baseline in persistence. This will store the version history of the database in a table. This is a one time setup.
grails dbm-changelog-sync
Now we can proceed with domain object changes. The Person class should be edited to become this:
package dbmigration.poc class Person { String name Integer age static constraints = { name() age(nullable: true) } }
The next step is to create a changeset file and append it to changelog.groovy. The '--add' parameter appends an include statement into the changelog.groovy
grails dbm-gorm-diff change-age-constraint-to-nullable.groovy --add
Configure Automigration on startup
Up to this point, we have only created DSL files with migration scripts. We have not actually done any migrations. We have the option to execute data migration on startup. This is very convenient as no DBA has to be involved when deploying war. The plugin will automatically execute the migration script. In order to do so, we must configure these properties in Config.groovy
grails.plugin.databasemigration.updateOnStart = true grails.plugin.databasemigration.updateOnStartFileNames = ['changelog.groovy‘]
Run the war and you will see that the data table for the Person.age column become nullable.
Migration 2 - add address columns
The second example for migration demonstrates adding columns to a single table. Let's assume the Person needs address fields. We will simply add them to the current table. The naive assumption is that a Person will only have address. This obviously is not true in most circumstances, but it sets us up nicely for a more complicated third migration.
Make Domain Change
Edit the Person class to become this:
package dbmigration.poc class Person { String name Integer age String streetName String city String zipCode static constraints = { name() age(nullable: true) streetName(nullable:true) city(nullable:true) zipCode(nullable:true) } }Create a changeset file:
grails dbm-gorm-diff add-address-fields-to-person.groovy --addNow you can deploy the war again.
Migration 3 - add Address table
To demonstrate a slightly more complicated scenario, let's assume we need to expand the domain to allow a Person to have multiple Addresses. Thus, Person to Address will have a one to many relationship.
The domain classes:
package dbmigration.poc class Person { static hasMany = [addresses: Address] String name Integer age static constraints = { name() age(nullable: true) } } package dbmigration.poc 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) } }Generate the changset:
grails dbm-gorm-diff create-address-table.groovy –add
Consider Migration of Data
Now we must consider the data that remains in the Person table. It must be transferred to the Address table. Liquibase cannot predict the intent of data migration. Liquibase has no way knowing the semantic meaning of data, or where you intend to migrate it. This type of information has to be provided by the developer. Thus, right after the creation of the address table in the create-address-table.groovy script, we have to add this:
changeSet(author: "a488338 (generated)", id: "migrate-person-data") { sql("""insert into address (id, version, person_id, street_name, city, zip_code) select hibernate_sequence.NEXTVAL, 0, id, street_name, city, zip_code from person""") }Notice we used the sequence value for id and also added the version value to the sql.
Steps Illustrated
In summary we can illustrate the steps a developer must do in order to conduct migration with the plugin.
The plugin will become part of Grails 2.0, according to the roadmap.
Este blog é uma representação exata de competências. Eu gosto da sua recomendação. Um grande conceito que reflete os pensamentos do escritor. Consultoria RH
ReplyDeleteNirav I noticed the reverse of the syntax for add change log file:
ReplyDeletegrails dbm-gorm-diff create-address-table.groovy –add
should be
grails dbm-gorm-diff -–add create-address-table.groovy
do you agree?
Will this work with Grails 1.3.x relase?
ReplyDeleteReally these data are helpful one for one who is doing study on data migration.Thanks for the post.
ReplyDeleteData Migration
It is very informative and helpful keep updates we also provide online training on
ReplyDeleteSAP OS DB MIGRATION ONLINE TRAINING.anybody search os db migration details please click on above link.
Interesting Article
ReplyDeleteSpring Hibernate Online Training | Hibernate Training in Chennai
Hibernate Online Training | Java Online Training | Java EE Online Training
very nice and informative blog
ReplyDeletedot net training in chennai
java training in chennai
ReplyDeleteThanks for sharing this valuable information.
java projects in chennai
dotnet projects in chennai
ns2 projects in chennai