Griffon: to SQL or NoSQL

A common question asked in the Griffon mailing list is: can GORM be used with Griffon? Sadly the answer is no, not yet. However this doesn't mean there's no persistence support for Griffon at all, quite the opposite, there are 17 active plugins at the moment with a few more in the pipeline.

Regardless of where you stand in the SQL/NoSQL debate Griffon has you covered. Let's begin with what could be said is the most familiar type of storage so far: relational databases.

First stop is the Datasource plugin. This little guy enables access to any java.sql.DataSource compatible datastore. An additional configuration file named DataSource.groovy will be added to the configuration directory of your application when you install this plugin. If the name rings a bell with Grails' conventions wait until you see its contents as created by the default template
dataSource { driverClassName = 'org.h2.Driver' username = 'sa' password = '' tokenizeddl = false // set this to true if using MySQL or any other // RDBMS that requires execution of DDL statements // on separate calls pool { maxWait = 60000 maxIdle = 5 maxActive = 8 } } environments { development { dataSource { dbCreate = 'create' // one of ['create', 'skip'] url = 'jdbc:h2:mem:gsql-sample-dev' } } test { dataSource { dbCreate = 'create' url = 'jdbc:h2:mem:gsql-sample-test' } } production { dataSource { dbCreate = 'skip' url = 'jdbc:h2:mem:gsql-sample-prod' } } }

Quite similar to Grails' own DataSource.groovy -- that happened by design of course. But the good news do not stop there. This plugin injects a new dynamic method to controllers by default; you can opt to inject this method to other artifacts as well, as long as you configure that option . This method understands the multiple datasource option supported by the plugin. Oh, did I fail to mention this plugin supports more than one DataSource from the get go? Well, it does Here's how typical invocations look when the method has been injected to a Service
import groovy.sql.Sql class SampleService { boolean doLogin(String username, String password) { withSql { String dataSourceName, Sql sql -> sql.firstRow('SELECT id FROM users WHERE username = ? AND password = ?', [username, MD5.encode(password)]) != null } } }

Not bad, not bad at all. Did you know you can rewrite the whole thing in plain Java? Let me show you how it can be done
import java.sql.*; import groovy.sql.Sql; import griffon.util.CallableWithArgs; import org.codehaus.griffon.runtime.core.AbstractGriffonService; import static griffon.plugins.datasource.DataSourceConnector; public class SampleService extends AbstractGriffonService { public boolean doLogin(final String username, final String password) { return DataSourceConnector.getInstance().withSql(new CallableWithArgs<Boolean>() { public Boolean call(Object[] args) { Sql sql = (Sql) args[1]; try { Connection conn = sql.getConnection(); String q = "SELECT id FROM users WHERE username = ? AND password = ?"; PreparedStatement stmnt = conn.prepareStatement(q); stmnt.setString(1, username); stmnt.setString(2, MD5.encode(password)); ResultSet rs = stmnt.execute(); rs.first(); return rs.getObject(1) != null; } catch(SQLException e) { return false; } catch(NullPointerException e) { return false; } } }); } }

Definitely more verbose that Groovy but gets the job done as well. This plugin is usually used in tandem with the GSQL plugin as the latter enables a data bootstrap mechanism similar to GORM's. At this point some of you may be thinking "OK, I can make raw SQL calls to any datasource but why would I do that if there are plenty of ORM solutions out there?". And you may be right. Love it or hate it, Hibernate is perhaps the most well known ORM in the Java space. This is the same technology that GORM uses under the covers to do its thing. There's as a matter of fact, an incubating hibernate plugin; we hope we can release it soon. In the meantime you may want to explore other options

• ActiveJDBC - a port of Rails' ActiveRecord. This is a good fit as long as your database is MySQL, Postgresql, Oracle or H2. For all others you're out of luck.
• Ebean - a light, JPA compatible alternative.
• MyBatis - formerly know as iBatis. A long standing option for those that think Hibernate is too big.
• Carbonado - made by Amazon. It can also query BerkeleyDB data stores.

Moving onto NoSQL, we now know that Carbonado may work with BerkeleyDB. If you're thinking there's a high chance that BerkeleyDB could be used separate from Carbonado then you're right. The following list enumerates all NoSQL options currently supported by Griffon via plugins:

• BerkeleyDB
• CouchDB
• Memcached
• Riak
• Redis
• Terrastore
• Voldemort
• Neo4j
• Db4o
• Neodatis

This is by no means an exhaustive coverage of all NoSQL options that either have a Java or REST based API. We chose those two constraints as they allow us to avoid dealing with platform dependent libraries. If you're aware of other choices that should be in this list then please drop us a message at the mailing lists and we'll see what can be done about it.

Keep on Groovying!