Data Access Test with Sqlite

I have mentioned briefly in previous post about 5 different approaches to write unit-test for data-access code. So now I will try to cover the first method in more detail. And among the 5, this method seems to be the most common one so far.
I will be using Sqlite as in-memory database. Some other arguably better alternative is available, that is if you are happy to fork out some extra bucks on VistaDB.
So let’s reemphasize the desired characteristics of a good unit test, especially in the context of data-access code.
1. Isolated. Database (by definition) persists any change to its state. However, this is usually not desirable in unit-test. A good test case runs in complete isolation from any changes made in other test-cases. I.e., any changes to the database from one test case should not be visible from other test-cases.
2. Repeatable. Regardless how many times a unit test is executed, a consistent result is expected. For this to happen, unit-test should not rely on presumption on external condition, especially shared database.
3. Fast. If your test fixture cannot be executed in every several minutes or so, there is only one thing that can possibly happen: developers will start abandoning it. And unfortunately, this is usually the case if you are connecting to database system from test code, where the whole test-suite can take up to an hour to execute, if you are lucky. No one can run it frequent enough to make it useful, and hence no one will keep on maintaining it.

The main reason why using in-memory database for unit-test is that it is incredibly fast! Both in restoring zero state, execution, and cleaning up. Speaking of which, let’s take a look on typical cycle of in memory database in unit-test.
We are going to have NHibernate to build the database schema from scratch before each test case, and dispose it at the end of the test case. Then start again with building the schema on clean database again for the next test-case. This way, we always have an empty sheet to work on for each test-case without affecting (or being affected by) any database changes in other test-cases.
Here is some NUnit test case example using NHibernate.

public class CustomerRepositoryTest: InMemoryDBTestFixture
	private ISession session;
	private CustomerRepository repository;
	public void FixtureSetUp()
	public void SetUp()
		this.session = this.CreateSession();
		this.repository = new CustomerRepository(
			new FakeSessionManager(session));
	public void TearDown()
	public void CanQueryCustomerByLastname()
		var customers = new List<Customer>(){
			new Customer(){
			new Customer(){
			new Customer(){
			new Customer(){
				FirstName="Yet Another",
		foreach(var cus in customers)
		foreach(var cus in customers)
		var loaded = repository.QueryByLastname("Griffin");
		Assert.That(loaded.Count, Is.EqualTo(2));
		AssertLoadedDataEqual(loaded[0], customers[0]);
		AssertLoadedDataEqual(loaded[1], customers[2]);
	private static void AssertLoadedDataEqual(Customer loaded, Customer saved)
		AssertThat(loaded, Is.NotEqualTo(saved)); // Make sure it's not cached data
		Assert.That(loaded.ID, Is.EqualTo(saved.ID));
		Assert.That(loaded.FirstName, Is.EqualTo(saved.FirstName));
		Assert.That(loaded.LastName, Is.EqualTo(saved.LastName));

The plumbing for initializing NHibernate and building in-memory database is managed by base-class InMemoryDBTestFixtureBase. It is a very common practice to have this kind of base class for all database test-fixtures within a project, so we we can turn our back on setting up test database and concentrate on testing what we care about. Let’s take a look at the base class code.

public abstract class InMemoryDBTestFixtureBase
	protected static ISessionFactory sessionFactory;
	protected static Configuration configuration;

	public static void InitialiseNHibernate(params Assembly [] assemblies)

		var prop = new Hashtable();
		prop.Add("hibernate.connection.driver_class", "NHibernate.Driver.SQLite20Driver");
		prop.Add("hibernate.dialect", "NHibernate.Dialect.SQLiteDialect");
		prop.Add("hibernate.connection.provider", "NHibernate.Connection.DriverConnectionProvider");
		prop.Add("hibernate.connection.connection_string", "Data Source=:memory:;Version=3;New=True;");

		configuration = new Configuration();
		configuration.Properties = prop;

		foreach (Assembly assembly in assemblies)
			configuration = configuration.AddAssembly(assembly);
		sessionFactory = configuration.BuildSessionFactory();

	public ISession CreateSession()
		var session = sessionFactory.OpenSession();
		new SchemaExport(configuration)
			.Execute(false, true, false, true, session.Connection, null);
		return session;

This base class takes care of configuring NHibernate with Sqlite in-memory provider, and registering all the assemblies where our hbm files are located. CreateSession method will load the new session with a fresh in-memory database, and it gets NHibernate to build it with the schema from those hbm files.

We have achieved our 3 objectives for isolation, repeatability, and speed. Additionally, compared to the other 4 unit-test approaches, in-memory database offers a unique advantage.
Each test-case is self sufficient: specifying its own pre-condition (initial data), and verifying the final outcome. Each test case is self explanatory in revealing the intention of the test. Test readers will find it remarkably easy to follow each of the test-cases independently without having to switch back and forth between Visual Studio and database IDE or dataset XML (as in the case with nDbUnit).
Having self-sufficient test-case might as well come as a disadvantage considering how bloated the test-code ends up, even for this rather simplistic example. In practice, this can get worse since you have to deal with populating the data for all chain of unrelated tables as well only to satisfy foreign key constraints when setting up initial data. Not to mention the frustration when the database schema changes every now and then. It is very likely that data initialization would typically take up majority of the test code, just to support mere couple lines of real test logic that we really care about.
Another disadvantage is that not all functionalities will work (or behave the same way) between in-memory and targetted database. Not to mention various subtle idiosynchracies with Sqlite.
And if you don’t use data-access framework that offers cross database portability (like NHibernate does), this approach is not even an option.


10 thoughts on “Data Access Test with Sqlite

  1. Well done. Henry!!

    You said : ” If your test fixture cannot be executed in every several minutes or so, there is only one thing that can possibly happen: developers will start abandoning it”
    It’s about time. What about debuging tool? If programmers start using VS debugging tool, they will forget TDD, do U agree?

  2. Debugger has its own place. Unit test is used to provide a safety net that gives you immediate feedback to changes that you make. If your unit-test is slow, developers will not run them every time they make a change to the code. They will wait until lunch break, or the next meeting (or the next YouTube time).
    Wheel of red/green/refactor is broken, and keeping all the test-code up-to-date suddenly becomes enormously expensive. As soon as someone starts ignoring 1 or 2 outdated test-code, people will completely stop running any test at all.
    This is known as one of xUnit anti patterns (

  3. @mfathur

    I’d say Unit-testing and using the debugger more effectively go hand in hand. I often will write a quick unit test to setup a scenario that I’m going to step through with the debugger to dissect. Alot easier then creating an executable command-line project or, god forbid, stepping through from the UI.

  4. @hendryluk

    Although I don’t value total DB isolation from test to test (meaning I don’t care if there’s junk data from a previous test as long as it doesn’t interfere with any other test), there is one way to do so without writing tons of teardown code is to just stomp on-top of the DB file with a clean DB file every test.

    We usually only do this once an execution from a SetUpFixture object (NUnit specific). It’s also were we override mappings since in-memory DBs don’t usually support cross-DB linking like SQL Server. Atleast VistaDB doesn’t…

  5. Regarding temptation to use debugger in lieu of slow unit-test, I don’t think that should be the case. If hundreds of test-cases take hours on an awfully slow unit-test harness, they would probably take weeks with hand debugging, and gallons of espresso.
    I agree though that TDD will never replace debugger. They’re not quite subtitutes. Debugging is a *discovery* activity, whereas unit-test acts merely as safety net. Unit-test won’t discover new bugs or make you jump higher, but it catches when you fall.
    I like the idea of clean DB file too. In fact, it has its own entry, 3rd row in the list 😉

  6. The SqlLite in-memory database sounds great in theory, but I can’t get it to persist many-to-many collections. Has anyone had success with this?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s