Thursday, January 3, 2008

Testing with an in-memory Database

Persistence tests are slow, and hard to make repeatable. The repeatabity problem is primarily in dealing with setup to a known state and/or teardown to return to a known state.

Even if you do write setup and teardown to put data into the DB and remove it, things like automatically generated ids are somewhat painful to reset so that the next run generates the same ones again.

But HSQLDB allows creation of a database that lives only in memory, creating a fresh empty database on every run.

Last weekend, I decided to start playing with HSQLDB in unit testing, and it looks promising. I'll probably end up using it at work as well, though some of our tests may continue to hit a real DB matching what we use in production.

To create an in-memory HSQLDB for plain old JDBC access is pretty trivial. If I were doing this, I'd probably make a test helper class of some sort that had something like

public Connection getConnection() throws Exception {
return DriverManager.getConnection(

to setup a database, and include methods to execute queries and updates. Data table definition could then be done with an update query, likely called from a JUnit test's setUp() method, and of course other queries would be used to retrieve data for assertions.

In the tearDown(), you might want a call to another helper method which shuts down the database. In the bit if experimentation I did, my helper class had the connection as a field conn set by the method above (which I actually made private) and another helper method

public void shutdown() throws SQLException {

Statement st = conn.createStatement();

called from the JUnit tearDown(). You don't need to delete any data though - it just disappears, as it was all in memory only. You might even be able to get away with skipping this altogether and just let the im-memory DB go out of scope and be garbage-collected.

But most of our code doen't use JDBC directly.

We use Hibernate for most persistence, and as it was new to me when I started at Cyrus last July, I've been playing with it off-hours to improve my familiarity with it. And this was what actually prompted me to start playing with HSQLDB.

As I was playing with both the XML mappings and Annotation mappings, I ended up with
a hierarchy of test helpers. Using only one or the other this might be simplified.

The root helper class was abstract:

public abstract class HibernateTestHelper {
protected Configuration config;
protected SessionFactory factory;

protected void init(Class... classes) {
factory = config.buildSessionFactory();

protected abstract void addClasses(Class... classes);

private void setupHSQLDB() {

helper methods for sessions, transactions, queries, etc. omitted...


Subclasses set things up a little differently for Annotation or XML mappings:

public class HibernateAnnotationTestHelper extends HibernateTestHelper {

public HibernateAnnotationTestHelper(Class... classes) {
config = new AnnotationConfiguration();
protected void addClasses(Class... classes) {
for (Class clazz : classes) {

public class HibernateXmlTestHelper extends HibernateTestHelper {

public HibernateXmlTestHelper(Class... classes) {
this.config = new Configuration();

protected void addClasses(Class... classes) {
for (Class clazz : classes) {

This allows for JUnit setUp() methods creating the appropriate helper for the mapping style in use mapping only the classes of interest to the test, and populating appropriate data.

Assorted helper methods are omitted from the abstract base helper class published here. They're pretty simple and obvious, and I suspect in actual use they might get a bit more elaborate, particularly in support for removing the test data setup logic from the JUnit setUp() methods. I might publish more detail on a revisit.

Something like this is likely to end up in our test code base at work. It seems faster than using a real database, and it's certainly nice to not have to tear down data.


Ariel Valentin said...

This in an interesting approach. I would say that there are a few things that make database testing challenging and the worst of them being around the things we do not know.
For example, our team has a legacy database that contains a large amount of business logic in things like triggers, which would not really rear it's ugly head if one where using HSQLDB or SQLite. Some RDBMS systems also do not check constraints until one actually attempts to commit the database transaction and you start running into issues where unit tests start to pass, but a manual functional review fails.
xUnit Test Patterns addresses some of the issues our team was facing with database id's, cleaning up the database, and test style in general. I highly recommend it:

Don Roby said...

I feel your pain, Ariel. ;-)

With my previous employer I also used databases with logic in triggers and stored procedures, and these would certainly need other patterns than in-memory database for testing. However there were less tests there for other reasons...

Luckily in our current code-base, we have none of these, and most of our persistence tests could probably be safely converted to use HSQLDB. The primary risk would be missing database-specific issues, as production does not use HSQLDB.

The "xUnit Test Patterns" book is indeed quite good and was recently added to the Cyrus Boston bookshelf. I didn't look for DB stuff specifically when I read it, but I'll certainly look back at it for ideas on any parts where just going to in-memory doesn't seem right.