Omnia Mutantur

I’ve been building a personal project for a couple months now using Groovy on Grails. Through all this time we’ve been working with in-memory HSQL databases, but now that we’re ready to do a test release I began testing embedded Java databases to pick which one we would use.

My first candidate was Apache Derby, based on some good performance reports I’ve read. Unfortunately, I immediately ran into a problem, where objects weren’t being committed right away. I would execute something like:


new Animal(name:"Misingo").save()
def misingo = Animal.findByName("Misingo")


And while the save call would succeed, the findByName function would fail. I began digging through Google trying to find a work around, but the voice of reason in my head told me that if I was having to look for fixes for trivial tasks, I shouldn’t rely on it for anything major.

Dropping back to considering HSQL with file-based tables, I started researching HSQL performance and ran into a HSQL vs Derby, where a history of Derby bugs is mentioned and several benchmarks are pointed to. This same article brought the very promising H2 Database to my attention, which comes with some pretty impressive benchmarks.

I downloaded it and attempted to get H2 working with Grails 0.5.6, and got an easy enough to fix error:

Could not determine Hibernate dialect for database name [H2]

In order for it to be recognized, you’ll have to create a file hibernate/hibernate-dialects.properties containing the following string:


H2Dialect=H2


This got H2 running and working perfectly for my development tests, but it failed when attempting to use production:

could not get database metadata
org.h2.jdbc.JdbcSQLException: Table SYSTEM_SEQUENCES not found [42S02-55]

Google searching for “H2 SYSTEM_SEQUENCES” yielded only 3 pages, two of them in Japanese. Not promising. Some testing determined that if the data source’s dbCreate property is set to either create or create-drop everything went smoothly, but it failed when set to update.

I went back to the H2 site, and found the following on the FAQ:

Is it Reliable?

That is not easy to say. It is still a quite new product. A lot of tests have been written, and the code coverage of these tests is very high. Randomized stress tests are run regularly. But as this is a relatively new product, there are probably some problems that have not yet been found. Areas that are not 100% tested:

• Platforms other than Windows XP and the Sun JVM 1.4 and 1.5
• Data types BLOB, CLOB, VARCHAR_IGNORECASE, OTHER
• Cluster mode, 2-Phase Commit, Savepoints
• Server mode (well tested, but not as well as Embedded mode)
• Multi-Threading and using multiple connections
• Updatable result sets
• Referential integrity and check constraints, Triggers
• ALTER TABLE statements, Views, Linked Tables, Schema, UNION

[...]

Evidently H2 is not ready for prime time – at least for my purposes – but it was refreshing to see the developer be upfront about it. I’ll wait before using it in production, but my hat’s off to Mr. Thomas Mueller – the creator – for his work on creating his own DBMS from scratch (again, he was one of the original creators of the Hypersonic SQL Project, on which HSQLDB is based).

So where does this leave me? Back with HSQLDB. Its performance is good, it’s stable and provides everything I need. I guess I must be getting old if I’m deciding based on stability and ease, not on shininess, but sanity has prevailed.

And after all, I did give shiny new thing a fair shot.

Bonus content:

Ricardo Programming derby, grails, h2, hsql, Programming