In-Memory DBs for TDD: H2 versus HSQLDB

Justin Gordon, October 27, 2008

Use of in-memory databases can dramatically improve your ability to write xUnit tests. Should one also use H2 or HSQLDB for xUnit testing? Or some other Java open source database? My examples for the Dependent Object Framework support both H2 and HSQLDB. It is relatively simple to switch between them. Mostly, it's a matter of switching the JDBC connection string.

Criteria for doing database TDD:

The functionality desired is

1. FAST. H2 claims to be the fastest In a simple test I did, H2 and HSQLDB performed almost identically.


2. DB2 or Oracle compatiblity support. H2 claims to have both: Oracle and and MS SQL Server Compatiblity Mode. If this works well, this is a huge win.


3. Stability: HSQLDB seems somewhat better supported as HSQLDB appears to be used in more commercial applications, so it may be better supported. In the past, Hibernate support seemed more stable. Much of this difference is probably attributable to HSQLDB being older.


4. Ease of use: H2 slightly beat out HSQLDB in this category with a simpler install that provides a shortcut to starting an in-memory database and a browser session with a
   nice interface. Couldn't be any simpler than this! Setting up HSQLDB was definitely a bit trickier, and the UI is definitely better in H2.
   

Common DB features not required for TDD:

1. Scalability
   
2. Concurrency
   
3. Stability under high load
   
4. Data integrity
   
   

Those features are absolutely critical to your production application. However, TDD test suites run in single user mode and the tests do not even need to persist the data.

Annecdotes

I know of one commercial software company that completely relies on H2 for their unit tests, while they deploy on Oracle.

References

H2

HSQLDB


Miscellaneous

Article on H2 by H2 creator: Mueller states that H2 is faster than HSQLDB for most
operations and is architecturally superior. Since he wrote the original HSQLDB, he'd be well qualified to comment. Here's the summary straight out of infoq:

HSQLDB creator Thomas Mueller has released 1.0 final of H2, his pure Java database successor to HSQLDB.
H2's focus is to be best database for the lower end (low number of concurrent connections, embedded usage). H2's features are comparable to MySQL and PostgreSQL; it has views, subqueries, triggers, clustering, role based security, encryption, user defined functions, disk and in-memory usage, embedded and client-server usage, referential integrity, scrollable result sets, schema support, transaction isolation. There are a few tools like the browser based Console application (now with auto-complete). A few things are still missing: H2 currently only offers table-level locking, full outer joins are not supported yet, the ODBC driver is only 'experimental' so far, and the standard API for distributed transactions (two-phase commit) is incomplete, however for most use cases these may not be critical.

Here's a good comparison of open source databases by David Leung, based on the following criteria:

• FREE or Open Source
• Multiple platform support
• Easy to install and use
• Can be bundle in code so little or no extra installation is required
• Can be scalable, i.e. load balancing, size limit, etc.
• Reliable, i.e. backup, replication, etc.
• Efficient, i.e. indexing, fast search, etc.

He recommends, in the following order:

1. H2
2. Derby
3. HSQLDB
4. MySQL
5. PostgreSQL

Summary
It is not so critical which in-memory database you use as it is to simply try out using an in-memory database for your xUnit tests. H2 is a good starting point, especially with it's Oracle and SQL Server compatibility modes.