Unit testing NHibernate-based data access code using SQLite

Code that interacts with a database, either directly or through an Object-Relational Mapping (ORM) library like NHibernate, is often quite difficult to test. This blog post looks at unit testing data access code using FluentNHibernate mappings, but it should apply just as well to vanilla NHibernate and other ORMs.

NHibernate provides interfaces for everything, which you theoretically could use to provide mocks to your code under test, but mocking complex 3rd-party library classes like this is not necessarily a good idea: you’d need to know exactly how your code was using the NHibernate API in order to do so. It’s best to think of NHibernate as part of your standard library and provide a real NHibernate session to your code under test.

The problem with this is that a real NHibernate session needs a real database. If you want to test with the same DB as your production code, this means keeping a database server running for your test, getting data into it, working out how to cope with multiple tests using the same database, etc… the resulting test runs are going to be slow and a lot of work to maintain.

Enter SQLite. It’s a single-DLL relational database management system (RDBMS) that can create a database from scratch for each test (so no worrying about setting up fixtures for tests running in parallel), in a single file on disk or in memory (the latter of which is *fast*). Using SQLite for unit tests is nothing new, but there are a lot of configuration options to consider, and I thought it would be worth documenting the end result that we arrived at when doing this sort of testing on our previous project.

The setup

SQLite has an ADO.NET adaptor, and you can download both together here.

Assuming that sessionFactory and configuration are member fields of your test class, a basic test fixture’s setup method would look something like:

sessionFactory = Fluently.Configure()
.Database(SQLiteConfiguration.Standard.InMemory())
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<FooMap>())
.ExposeConfiguration(c => configuration = c)
.BuildSessionFactory();

new SchemaExport(configuration).Execute(true, true, false, connection, null);

Let’s just have a quick look at what this is doing:

• The SQLiteConfiguration line is just a prettified way of specifying a SQLite database with the connection string “Data Source=:memory:;Version=3;New=True;”
• The next line adds our Fluent NHibernate mappings
• We store the configuration in a member field so we can access it later, in particular to set up our database schema

Obviously a SQLite database is just an empty database, so we need to set up some tables for our code to use. This is done using NHibernate’s schema export tool in the final line. This is a really neat tool that lets you generate your database schema from your mappings (this has nothing to do with Fluent and works just as well with standard HBM XML files, or annotation-based mappings). It also enables (and predates) something like the EF code-first approach, but that’s another blog post…

The problem

The above will work and we can go ahead and use the sessionFactory, but there’s a problem. When we close the session, we’ll lose our whole database. This is because, by default, NHibernate uses one connection per session, and SQLite ties the lifetime of an in memory database to the connection (this might be useful for allowing you to run your tests in parallel).

Now, you could work around this by having each unit test run within a single session, but this isn’t a very good test of persistence (NHibernate uses session caches extensively). What we really want to do is use one session to set up our data, provide a second session to our code under test (or possibly allow it to use as many sessions as it likes), and open up a fresh session at the end to check that everything got persisted properly. This is particularly clear if you’re following the arrange, act, assert structure for your unit tests.

There are a couple of ways around this at the SQLite level: You could use an on-disk database or you could use connection pooling, but the first of these is *much* slower (which is significant if you have a lot of tests – and we don’t want anything to put you off writing lots of tests), the latter complicates test cleanup, and both have implications should you ever want to run tests in parallel.

The solution

The solution we found was to re-use the NHibernate connection (and therefore the database) for multiple sessions. You can do this by adding the following line to your test setup code (apologies for the slightly nasty cast here, but I couldn’t find a way to avoid it)…

And making sure you reuse that connection to get a new session, e.g.:

On our project, we created an an abstract base class for any tests that needed an in-memory database, containing the connection configuration and a protected OpenSession method that returned new sessions using the connection. You might do things slightly differently if you need to test code that depends on ISessionFactory rather than ISession (you could create a mock ISessionFactory and set it up to perform the line above when its OpenSession method is called). Don’t forget to close your connection in your test fixture’s teardown method.

Incidentally, Jason Dentler (the guy who wrote the NHibernate 3.0 Cookbook, which is quite good) has a blog post addressing the same issues. He rejects the same workarounds that we do (and provides a little more detail why, in case you’re interested) but comes up with a different end solution. However, it’s quite a bit more complicated than our solution and I’m not sure the extra complexity adds much value (which is consistent with what he says himself in the comments section of his blog post).