One of the more difficult regions to test in an application is the data access layer. It is difficult to test for several reasons:

• It is usually complicated - fetching data effectively is not something trivial in many cases.
• It can be highly dependant on the platform you are using, and moving between platforms can be a PITA.
• It is usually hard to mock effectively.
• Database by their natures keep state, tests should be isolated.
• It is slow - we are talking out of process calls at best, remote system calls at worst.

I am a big fan of NHibernate, and I consider myself fairly proficent in mocking, and I find it very hard to mock data access code effectively, and that is when NHibernate already provides a very easy set of interfaces to work with.

The issue is not with calls like this:

This is very easy to mock.

The issue is with calls like this one:

Trying to mock that is going to be... painful. And this is a relatively simple query. What is worse are a series of queries, which work together to return a common result. When my setup code crossed the 500 lines of highly recursive mocking just to give the test a reasonable place to work with, I knew that I had an issue.

I could break it up to more rigid interface, but that completely ignore the point of being flexible. The above query is hard coded, but pretty often I find myself building those dynamically, which is not possible using rigid (but more easily mockable) interfaces.Please note that I am not talking about the feasability of mocking those, I have done it, it is possible, if lenghty, I am talking about maintainability and the ability to read what was the intention after six months has passed. Bummer, isn't it?

Then I thought about SQLite. SQLite, despite their documnetations shortcoming, is a lightwieght database engine that supports an in memory database. What is more, NHibernate already supports it natively (which saved me the effort :-) ). SQLite is an in-process database, and in-memory databases are wiped when their connections are closed. So far we removed two major obstacles, the statefulness of the databasess, and the inherent slowdowns we are going across process/machine boundaries. In fact, since we are using entirely in-memory database, we don't even touch the file system :-).

But we have the issue of moving between platforms. We can't just port the database to SQLite just for testing. Or can we?

First, let us define what we are talking about. I am not going to performance testing (except maybe SELECT N+1 issues) on SQLite, this require the production (or staging) database with a set of tools to analyze and optimize what we are doing.

So, if we ruled perf testing from the set of scenarios we are looking for, we don't need large amounts of data. NHibernate will create a schema for us, free of charge, and it can handle several databases transperantly. We don't need to mock anything, it looks like we are golden.

I got the following code:

Initialize the framework, create a session, and run. Notice that the test doesn't care what it is working against. It just test that we can test/load an entity. Let us look at the base class:

Here we just initialize NHibernate with an in memory connection string and a SQLite provider. Then, when we need to grab a session, we make sure to initialize the database with our schema. Disposing the session closes the connection, which frees the database.

So far we handled the following issues: Slow, Stateful, Hard to mock, platform dependant. We have seen that none of them apply to the issue at hand. Now, what about the last one, testing complicate data fetching strategies?

Well, that is what we do here, aren't we? In this case, true, we aren't doing any queries, but it is the prinicpal that matters. Looking at a database through NHibernate tinted glasses, they look pretty much the same. And a querying strategy that works on one should certainly work on another (with some obvious exceptions). I am much more concerend about getting the correct data than how I get it.

The beauty here is that we don't need to do anything special to make this happen. Just let the tools do their work. To use Active Record with this approach, you need replace the calls to the configuration with calls to ActiveRecordStarter, and that is about it.

Even though those tests execute code from the business logic to the database, they are still unit tests. To take Jeremy's Qualities of a Unit Test as an example, unit tests should be:

• Atomic - each test gets each own database instance, they can't affect each other.
• Order indepenent and isolated - same as above, once the test finished, its database is back to the Great Heap in the Sky.
• Intention Revealing - throw new OutOfScopeException("Issue with test, not the technique");
• Easy to setup - check above for the initial setup, afterward, it is merely an issue of shoving stuff into the database using NH's facilities, which is very easy, in my opinion.
• Fast - It is an in memory database, it is fast. For comparison, running this test on SQL Server (locahost) runs at about 4.8 seconds (reported from TestDrive.Net, and include all the initialization) running it on SQLite results in 3.4 seconds. Running an empty method on TestDriven.Net takes about 0.8 seconds. Most of the time is spent in the initial configuration of NHibernate, though.

Hope this helps....