Oct 10, 2013

Reverse-Engineer Old Databases With Grails

When dealing with a legacy database, especially a large legacy database, the task of creating Grails domain objects may be daunting. Developers experienced with Hibernate may have used Hibernate Tools to reverse-engineer a database and create Hibernate mappings automatically. Of course, tools such as this never work perfectly, requiring work on your part to adjust the automatically-created mappings.

In the Grails world, the reverse engineering plugin wraps the Hibernate tools to create Grails domain objects automatically from existing tables. Experienced developers may have forgotten about this plugin, though, since it was stuck at a pre-2.x Grails release for a long time.

Using the reverse-engineering plugin is remarkably easy.

First, create a project. I’d recommend creating a new throw-away project just for the purposes of generating domain classes. That way, you can pick and choose which classes to pull into your project.

grails create-app dbrev

Then, set up your database JDBC driver. You may need to copy a jar file into your lib directory, or set up a library dependency in BuildConfig.groovy.

Next, configure the connection in grails-app/conf/DataSource.groovy. I usually create a new Grails environment just for the purposes of reverse engineering, such as one named extract. In this environment, you do not want to blow away your database. So, be sure you do not have dbCreate set to ‘create-drop’. You’ll want something like the following:

dbCreate = null

You should also define the Hibernate dialect for your database.

After you set up the database connection, add the db-reverse-engineer plugin to your application in BuildConfig.grooy. Add the following line to the plugins section:

runtime ':db-reverse-engineer:0.5'

Before you get started, double-check the version number in case a new release is out. And make sure your project includes the mavenCentral() repository.

You probably want to configure the db-reverse-engineer plugin, even if you just want to define the Java package for your code. In Config.groovy, you can define lots of parameters as defined in the excellent db-reverse-engineer documentation at http://grails-plugins.github.com/grails-db-reverse-engineer/docs/manual/index.html.

For example:

grails.plugin.reveng.packageName='com.objectpartners.dbrev'

If you use something other than the default schema for the user account to the database, you’ll need to configure grails.plugin.reveng.defaultSchema or grails.plugin.reveng.defaultCatalog as needed. If you don’t set this correctly, the plugin nicely lists the available schemas and catalogs.

I’d suggest trying just one table to start out, to get familiar with how the reverse engineering works. To do so, define the includeTables configuration value to a list holding just the table name in which you are interested. For example:

grails.plugin.reveng.includeTables=['employee']

You can later add more table names to this list.

To make sure the plugin gets installed, run a command such as refresh-dependencies:

grails refresh-dependencies

You can verify the plugin is installed with the list-plugins command:

grails list-plugins

You are now ready to flame on. Run the following command in your project:

grails -Dgrails.env=extract db-reverse-engineer

In this example, the extract environment is the special Grails environment mentioned above.

When dealing with some legacy tables, especially on SQL Server, you may have problems with tables that have no explicitly-defined primary key. In that case, the db-reverse-engineer script may throw an exception. The one-character fix listed with the following issue report (http://jira.grails.org/browse/GPREVERSEENGINEER-12)) takes care of the problem.

If you re-run the database reverse engineering after changing the configuration, you need to tell the plugin to overwrite existing files in your Config.groovy by setting the following value:

grails.plugin.reveng.overwriteExisting=true

View the plugin information at http://www.grails.org/plugin/db-reverse-engineer.

 

About the Author

Eric Foster-Johnson profile.

Eric Foster-Johnson

Principal Technologist

Eric has decades of industry experience in designing and developing complex enterprise software, including designing and developing Grails and Java EE solutions to tough client problems. He has experience leading development teams, mentoring developers, and helping troublesome projects get back onto a success track. He has lead teams in both traditional and agile settings.

One thought on “Reverse-Engineer Old Databases With Grails

  1. Sam says:

    Thank you for you post. I followed the steps you provided and they worked for the first trial. Thanks for Burt Beckwith came up with this great plugin and tutorial.

  2. Damian says:

    Hi thanks for the tut. I seem to have everything working in the project however when i run the reverse engineer script it creates no domains though saying it was successful. Would appreciate any help. Have checked if there is a simple table in db and have included table in config.
    Cheers
    Damian

    1. Damian says:

      Figured it out, i forgot the environment url overrid the datasource and the environment url was wrong

Leave a Reply

Your email address will not be published.

Related Blog Posts
A security model for developers
Software security is more important than ever, but developing secure applications is more confusing than ever. TLS, mTLS, RBAC, SAML, OAUTH, OWASP, GDPR, SASL, RSA, JWT, cookie, attack vector, DDoS, firewall, VPN, security groups, exploit, […]
Building Better Data Visualization Experiences: Part 1 of 2
Through direct experience with data scientists, business analysts, lab technicians, as well as other UX professionals, I have found that we need a better understanding of the people who will be using our data visualization products in order to build them. Creating a product utilizing data with the goal of providing insight is fundamentally different from a typical user-centric web experience, although traditional UX process methods can help.
Kafka Schema Evolution With Java Spring Boot and Protobuf
In this blog I will be demonstrating Kafka schema evolution with Java, Spring Boot and Protobuf.  This app is for tutorial purposes, so there will be instances where a refactor could happen. I tried to […]
Redis Bitmaps: Storing state in small places
Redis is a popular open source in-memory data store that supports all kinds of abstract data structures. In this post and in an accompanying example Java project, I am going to explore two great use […]