A few weeks ago, I was faced with the challenge of testing how an application interacted with a database without mangling the data within it. What my company of employment had been doing prior was doing all database testing within a transaction and rolling back at the end. The issue with this approach was that behaviors like cascading wouldn’t occur, thus making the tests less than useful.

SQLite

Enter SQLite. SQLite is a basic, lightweight sql database wrapped into a single library. A benefit of its use is that SQLite can create an in-memory database that cleans up after all connections are closed. Its excellent for testing. Starting with a clean slate each test run can provide for consistent, reproducible results.

Prerequisites

NHibernate comes with support for SQLite from the NHibernate.Driver.SQLite20Driver class. However, you must provide two other files for it to work. First, sqlite3.dll, an unmanaged library of SQLite; include it in your test project and ensure “copy to output directory” is marked. Second, System.Data.SQLite, a managed library that allows ADO.NET to interact with SQLite; include this as a reference in your test project.

A Base Class

The following is a basic example of how to set up a base class for in memory database testing. Any tests that require database interaction should inherit from this class and will have the Session object available. Sessions last as long as each test fixture. Also, typeof(Plan) refers to the type of any of your domain classes.

 	public class InMemoryDatabaseTest
	{
		private static Configuration _configuration;
		private readonly object _baton = new object();
		private readonly ISessionFactory _sessionFactory;
		protected readonly ISession Session;

		public InMemoryDatabaseTest()
		{
			if (_configuration == null)
				lock (_baton)
					if (_configuration == null)
					{
						typeof(VersionDate2).GetField("_sqlType",BindingFlags.Static | BindingFlags.NonPublic).SetValue(null,new SqlType(DbType.DateTime));
						_configuration = new Configuration()
							.SetProperty(Environment.ReleaseConnections,"on_close")
							.SetProperty(Environment.ProxyFactoryFactoryClass,
							             "NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle")
							.SetProperty(Environment.TransactionStrategy, "NHibernate.Transaction.AdoNetTransactionFactory")
							.SetProperty(Environment.CacheProvider, "NHibernate.Caches.SysCache.SysCacheProvider, NHibernate.Caches.SysCache")
							.SetProperty(Environment.ConnectionDriver, typeof (SQLite20Driver).AssemblyQualifiedName)
							.SetProperty(Environment.ConnectionProvider, "NHibernate.Connection.DriverConnectionProvider")
							.SetProperty(Environment.Isolation, "ReadCommitted")
							.SetProperty(Environment.ConnectionString, "Data Source=:memory:;Version=3;New=True;")
							.SetProperty(Environment.ShowSql,"True")
							.SetProperty(Environment.Dialect, typeof (SQLiteDialect).AssemblyQualifiedName)
							.AddAssembly(Assembly.GetAssembly(typeof (Plan)));

			_sessionFactory = _configuration.BuildSessionFactory();
			Session = _sessionFactory.OpenSession();
			var schemaExport = new SchemaExport(_configuration);
			schemaExport.Execute(false, true, false, Session.Connection, null);
		}

		public void Dispose()
		{
			Session.Dispose();
		}
	}

Additional Features

Support for Schemas

SQLite doesn’t provide native support for schemas. We use SQL Server, and this led to a problem when any of our mappings pointed to tables in anything other than the dbo schema. A simple fix to this is to replace all periods with underscores prior to creating your session factory. This gives the desired behavior without requiring any modification to your mapping files.

                    if (_configuration == null)
                    {
                        _configuration = new Configuration()
                        //Configuation...
                        foreach (PersistentClass classMapping in _configuration.ClassMappings)
                        {
                            if (classMapping.Table.Name.Contains("."))
                                classMapping.Table.Name = classMapping.Table.Name.Replace(".", "_");
                        }
                    }

Working with AutoMockContainer

If you’re a user of Moq-Contrib’s AutoMockContainer (it must be patched to work with the latest version of Moq), you can register the Session object into the container to have it provided instead of a mocked session. It’s as simple as adding the following after creating your session object.

            MockContainer.Register(Session);

Testing with NHProf

If you use NHibernate Profiler (if you don’t, you should be), your SQLite database interaction can be monitored using that as well. Add an AssemblyInitialize (or your testing framework’s variant) to your InMemoryDatabaseTest class and all interactions will be profiled.

		[AssemblyInitialize]
		public static void AssemblyInit(TestContext testContext)
		{
			NHibernateProfiler.Initialize();
		}