Category: .Net

Good unit tests should, among other things, be easily repeatable and independent. If you're testing against a full blown MySQL or MSSQL server it can become difficult to easily repeat a test if you rely on certain records holding certain Ids, and the independent nature of the test is impacted as it's not well isolated from a specific server.

Testing against an SQLite database can help alleviate these issues as the flatfile database can be created and thrown away after each test, making it easy to repeat a test on fresh data each time.

So let's look at how to go about setting this up.

The Standard SessionFactory

In order to get an NHibernate session normally in my application, I use a provider class to provide access to a SessionFactory:
public static class SessionFactoryProvider
{
private static ISessionFactory sessionFactory;
private static Object lockObj = new Object();

return sessionFactory.OpenSession();
}
}

This will normally live in the assembly that contains all the data access logic, and will used through dependency injection.

The Test SessionFactory

To get an SQLite database for testing, we need to create it in a slightly different way, specifying a filename for SQLite to use, and providing a way to tear it down after use:
namespace DITests
{
public static class TestSessionFactoryProvider
{
private static ISessionFactory sessionFactory;
private static string dbFile;

if (File.Exists(dbFile))
{
File.Delete(dbFile);
}
}
}
}

Something To Test

Now that the test session factory is setup, we need something to test, so here's a very simple user class, mapping and repository:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NHibernate;
using NHibernate.Linq;

public void Save(User user)
{
using (ITransaction t = session.BeginTransaction())
{
session.Save(user);
t.Commit();
}
}
}
}

The Test

And finally, we can setup some tests to run. Because the user repository takes an ISession in its constructor, we can easily change which SessionProviderFactory we're using, allowing us to drop in our test one in order to test against SQLite:
using System;
using System.Collections.Generic;
using NUnit.Framework;
using MVC3DI.Models;

[Test]
public void TestTryLoadUsers()
{
var users = repo.GetUsers();
Assert.AreEqual(3, users.Count);
Assert.AreEqual("Frank", users[0].Name);
Assert.AreEqual("Bob", users[1].Name);
Assert.AreEqual("Bert", users[2].Name);
}
}
}

This is a pretty straight forward test which fires up the database, creates a few records, and then reads them back in order to verify that they were saved correctly.

Limitations

The big limitation with this method is that you can only test code that is database agnostic. This should be fine if you're only strictly accessing records through NHibernate as that will take care of creating the correct SQL for you, but if you write any SQL yourself with keywords specific to a certain provider, it will quickly fall over when run against SQLite.

There's not a whole lot that can be done about this; you could extract the database specific functions to a more specific implementation, but then that would need testing too, which you can only do against the database it's targeted at, so that seems a little pointless. Which leaves you with either not testing that code or not writing it in the first place!

Summary

There is some debate as to the value of testing at the database level, especially when you're not testing against the database that will be used in production, but personally I think it can still be useful in verifying that mappings are working correctly and to test any basic logic that may live down at that level.

Using SQLite as a replacement during testing ensures your tests will run quickly and be independant of any outside requirements. Whilst the downside to this is that you can't test vendor specific SQL with this method, it's generally best to avoid this anyway when using an ORM such as NHibernate.