Blisteringly Fast Integration Tests With NHibernate and SQLite

One thing I love about using NHibernate as my O/RM is being able to push the database schema from the domain.  This lets me create the database from scratch for each integration test fixture and get it into the required state.  Creating the database with NHibernate is quick and simple.  Hell, here is the code to do it:

Configuration cfg = container.Resolve<Configuration>();  
SchemaExport export = new SchemaExport(cfg);  
export.Execute(true, true, false, true);

Nifty, eh?

But tests that hit the database are sloooow, and these quickly become tests that are not ran. Tests that query the database are slow, and because I regenerate the whole database for each TestFixture, they are reeeeally slow.  I guess one solution would be to not run the integration tests locally, but I don't like this option and I  believe tests should be ran as often as possible.

Luckily; NHibernate provides a number of RDBMS providers.  My first port of call was SQLServer CE, but after the first test run and it failing on anything with a formula I quickly abandoned it.

Enter SQLite, "the most widely deployed SQL database engine in the world".  Awesome.

A quick modification to to the NHibernate and the tests were up and running as fast as a pig to...

Setting everything up was quick and painless. 

First: grab the required assemblies from http://sqlite.phxsoftware.com/ and add a reference. 

Second: set up NHibernate; personally I use the Castle NHibernate facilities and Binsor to configure Castle.  Below is the settings I use:

facility NHibernateFacility:
	configuration:
		@isWeb = false, @useReflectionOptimizer = false
		factory:
			@id = 'nhibernate.factory'
			settings(keymap, item: 'item'):
				provider = 'NHibernate.Connection.DriverConnectionProvider'
				connection.driver_class = 'NHibernate.Driver.SQLite20Driver'
				dialect = 'NHibernate.Dialect.SQLiteDialect'
				connection.connection_string = 'Data Source=intranet.db;Version=3;New=True'
				cache.use_second_level_cache= 'false'
				show_sql = 'true'
			assemblies = [ Assembly.Load("Intranet") ]
facility TransactionFacility

And that is pretty much it.  One gotcha I'm aware of is SQLite on a 64-bit OS.  I haven't experienced this first hand but I will try and dig out some information. Oh, and for the life of me, I can't get it to work in-memory but I'll be posting about this later as soon as I get a solution.

However I digress; there is another part to this awesome story: the Continuous Integration server.  The way I see it, SQLite is perfect for running the tests locally where speed is of the essence and the CI server can take the hit of running against SQL Server.  So let's get a build script that swaps the NHibernate config. 

To accomplish this I used three boo files:

• container.boo
  This is my main boo file, it sets up all my classes into Windsor
• sql_server_facilities.boo
  This is the Sql Server version of the facilities file that configures NHibernate to use Sql Server
• sql_lite_facilites.boo
  As above, but for SQLite

The container.boo looks something like this:

import System.Web.Mvc from System.Web.Mvc
import Spark.Web.Mvc
import Spark
import Spark.FileSystem
import Invocas.Tools.Binsor.Macros from Invocas.Tools

import file from sql_lite_facilities.boo

LoadFacilities()

for type in AllTypesBased of IController("Invocas.Intranet"):
    	component type.FullName, type:
    		lifestyle Transient
    		log

In the container file, I include the sql_lite_facilities.boo file.  This, as you have not doubt figured out, stores the configuration for the facilities.:

import System
import System.Reflection

import Castle.Facilities.AutomaticTransactionManagement
import Castle.Facilities.FactorySupport from Castle.MicroKernel
import Castle.Facilities.NHibernateIntegration from Castle.Facilities.NHibernateIntegration

def LoadFacilities():
	LoadNHibernate()

def LoadNHibernate():
	facility NHibernateFacility:
		configuration:
			@isWeb = false, @useReflectionOptimizer = false
			factory:
				@id = 'nhibernate.factory'
				settings(keymap, item: 'item'):
					provider = 'NHibernate.Connection.DriverConnectionProvider'
					connection.driver_class = 'NHibernate.Driver.SQLite20Driver'
					dialect = 'NHibernate.Dialect.SQLiteDialect'
					connection.connection_string = 'Data Source=:memory:;Version=3;New=True'
					cache.use_second_level_cache= 'false'
					show_sql = 'true'
				assemblies = [ Assembly.Load("Invocas.Intranet") ]
	facility TransactionFacility

The sql_server_facilities.boo is essntially the same, but eith the provider, driver, dialect and connection string correctly set:

provider = 'NHibernate.Connection.DriverConnectionProvider'
connection.driver_class = 'NHibernate.Driver.SQLite20Driver'
dialect = 'NHibernate.Dialect.SQLiteDialect'
connection.connection_string = 'Data Source=:memory:;Version=3;New=True'

By default, I usually keep the file to include the facilities.boo file set to SQLite and use NAnt to set the correct file.  Below is what's needed for the NAnt file:

<property name="common.booFileName" value="sql_lite_facilities" overwrite="true" readonly="true" />
<property name="common.defaultString" value="sql_lite_facilities" overwrite="true" readonly="true" />
....

<target name="modifyBoo">
	<property name="booContainerFile" value="${build.dir}/container.boo" />
	<property name="valueToFind" value="${common.defaultString}"/>
	<property name="valueToReplace" value="${common.booFileName}"/>
	<script language="C#">
		<imports>
			<import namespace="System.Text.RegularExpressions"/>
			<import namespace="System.IO"/>
		</imports>
		<code>
			<![CDATA[
			public static void ScriptMain(Project project) {
				StreamReader reader = File.OpenText(project.Properties["booContainerFile"]);
				string file = String.Empty;
				try {
					Regex exp = new Regex(project.Properties["valueToFind"]);
					file = reader.ReadToEnd();
					file = exp.Replace(file, project.Properties["valueToReplace"]);
				} finally {
					reader.Close();
				}
				
				TextWriter tw = new StreamWriter(project.Properties["booContainerFile"]);
				try {
					tw.WriteLine(file);
				} finally {
					tw.Close();
				}
			}
				]]>
		</code>
	</script>
</target>

The modifiyBoo target must be called before running the unit tests  but I'm sure you gathered that.  To supply a new boo file, simply use the following:

nant -D:common.booFileName=sql_server_facilities

And there you have, super fast SQLite integration tests locally, and SQL Server tests on the CI server.  Zoom Zoom!