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 to Damian Cancel reply

Your email address will not be published.

Related Blog Posts
Natively Compiled Java on Google App Engine
Google App Engine is a platform-as-a-service product that is marketed as a way to get your applications into the cloud without necessarily knowing all of the infrastructure bits and pieces to do so. Google App […]
Building Better Data Visualization Experiences: Part 2 of 2
If you don't have a Ph.D. in data science, the raw data might be difficult to comprehend. This is where data visualization comes in.
Unleashing Feature Flags onto Kafka Consumers
Feature flags are a tool to strategically enable or disable functionality at runtime. They are often used to drive different user experiences but can also be useful in real-time data systems. In this post, we’ll […]
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, […]