HSQLDB, H2 and Java Unit Testing

I ran into a situation in a production environment where highly tested methods were failing on database errors, and came to think there must be a scenario I missed somewhere.  After scrubbing for clues, I finally found that there was an existing test and it flew right past the error I got from MySQL.  It turns out that our test database, HSQLDB running in-memory, was less strict (or more forgiving) when it came to enforcing not-null columns.  Oops.

HSQLDB, or HyperSQL Database Engine, has been around for a long time and is embedded as the database provider for OpenOffice.org 3.2.  It is freely available under a modified BSD license and is implemented in several not-so-shabby applications including Jira, Spring Framework and Liquibase.  As a developer, I have used HSQLDB for its in-memory or flat file database formats when testing or prototyping.  However, I never expected to run into a situation where setting not-null on a column would not be honored.

I began looking for an answer in the Hibernate irc channel, thinking perhaps the database configuration was off or that I had my syntax wrong on that column.  That is when Steve Ebersole, a Hibernate hero, explained to me that he too was experiencing random unexpected problems with HSQLDB and he was looking into switching to H2.  In my mind, if Steve Ebersole from Hibernate says "these are the kinds of reasons we are moving to h2 for our testing db," I should probably follow suit.

H2, or H2 Database Engine, was developed initially in 2004 by Thomas Mueller -- the same guy who initially wrote HSQLDB -- and is available for free under either a modified Mozilla Public License or the Eclipse Public License.  "H2" actually stands for Hypersonic 2 but really has no ties to or any shared code with HSQLDB.  It is written in pure Java, is very fast, supports emulation modes for several database engines (MySQL, Oracle, DB2, even HSQLDB), the documentation on the website is clean and easy to navigate, and ... it enforces not-null columns.

Integrating H2 into OpenMRS was easy; all it really required was adding a jar and changing the base context-sensitive test class to reference it instead of HSQLDB.  To make it work the way HSQLDB had previously, I had to use an additional parameter in the JDBC url: DB_CLOSE_DELAY=-1.  This makes the database stay open to improve performance.  I also had to add an @After annotation to the base test class cleanup method so that H2 started with a clean dataset for each test.  Then came the testing.

Initially I found upwards of sixty errors and/or failures, and quickly got the list down to 10 errors and 12 failures after fixing a few Hibernate configuration file issues regarding column length; H2 fails on attempts to insert text exceeding column lengths.  I found that the majority of the problems were mitigated by ensuring data was properly configured in the individually loaded datasets for the failing tests; some tests relied solely on data from the custom data set and did not account for what existed in the standard data set either adding to or being overwritten by the custom data.

At this point, I feel like I can actually rely on my tests to properly mimic my production environment.  This is a huge relief, and hopefully the HSQLDB group can get their database engine up to snuff soon so the supported apps will not run into these issues.  For me, H2 is the way to go.