Replacing GORM with Groovy Sql for Lightwieght Jobs

GORM, the Grails Object-Relational Mapping persistence framework provides a rational way to use Hibernate based on pojo (or groovy pogo) domain classes and basic conventions. Create a domain class, define some constraints, start up grails and viola! tables, columns, indexes are created without coding a line of SQL/DDL or creating annoying XML mappings.

But, what if you have a simple standalone process that requires single or multiple database access? Currently GORM has trouble working outside of grails, and creating a new grails project just to support a simple, no-client script is overkill. For that, I use groovy's database access classes in groovy.sql and a small custom library to easily access multiple data sources and provide simple query tools optimized for batch operations.

Here's an example: Tracking multiple UPS shipments. The application must access existing shipment requests from a local database, then query the UPS tracking remote service to discover shipment status. When a shipment is picked up or delivered, another local database is updated and a third system is updated via XML feed.

To summarize, the requirements are:

• provide simultaneous access to multiple databases
• query UPS tracking via HTTP requests
• create XML responses and write to a messaging system
  

Cron triggers this process multiple times during the working day. It runs on the production machine (1MB slice at slicehost), so should be kind to existing web and database applications serving the user base.

Design Implementation:
As always, I start with the tests. Using groovy test case, the database queries, inserts, and updates tests were created, then the target classes and methods. Queries are always the easiest, and updates are straight forward as well. Inserts with auto generated IDs are a bit tricker, but here is where groovy sql comes to the rescue.

Creating new tables presented a new problem: should I create SQL/DDL scripts? No! For this, it was easy enough to access the current grails application and create the new domain classes, run grails to have Hibernate create the new tables.

The groovy Sql class provides a method called executeInsert(). The magic in this method is that it returns an array of all auto-generated keys. So, if I'm working in MySql or Oracle or whatever, inserts all look the same--no database specific code required.

Database access defaults to the production environment but database tests must operate on the test database (similar to grails). This is easy to accomplish by simply overriding the default behavior in the test scripts.

Conclusions:

• Use groovy Sql for small, non-web applications that require single or multiple database access.
• Use Grails/GORM for all database/table creation and maintenance (no DDL scripts). This also has the additional benefit of enabling access to new tables through grails if required.
  
• Use executeInsert() to return auto-generated keys when inserting new rows.