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 {
        Class.forName("org.hsqldb.jdbcDriver");
        return DriverManager.getConnection(
                              "jdbc:hsqldb:mem:testdb",
                              "sa",
                              "");
    }

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();
        st.execute("SHUTDOWN");
        conn.close();
    }

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) {
        setupHSQLDB();
        addClasses(classes);
        factory = config.buildSessionFactory();
    }

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

    private void setupHSQLDB() {
        config.setProperty("hibernate.dialect", 
                           "org.hibernate.dialect.HSQLDialect").
               setProperty("hibernate.connection.driver_class", 
                           "org.hsqldb.jdbcDriver").
               setProperty("hibernate.connection.url", 
                           "jdbc:hsqldb:mem:testdb").
               setProperty("hibernate.connection.username", 
                           "sa").
               setProperty("hibernate.connection.password", 
                           "").
               setProperty("hibernate.connection.pool_size", 
                           "1").
               setProperty("hibernate.connection.autocommit", 
                           "true").
               setProperty("hibernate.cache.provider_class",
                           "org.hibernate.cache.HashtableCacheProvider").
               setProperty("hibernate.hbm2ddl.auto", 
                           "create-drop").
               setProperty("hibernate.show_sql", 
                           "true");
    }

/*
   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();
        init(classes);
    }
    protected void addClasses(Class... classes) {
        for (Class clazz : classes) {
            ((AnnotationConfiguration)config).addAnnotatedClass(clazz);
        }
    }
}

public class HibernateXmlTestHelper extends HibernateTestHelper {

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

    protected void addClasses(Class... classes) {
        for (Class clazz : classes) {
            config.addClass(clazz);
        }
    }
}

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.