Skip to content
Storm

Back to news

Using SQLite with NHibernate for Unit Testing

Storm News.

26.05.2010 - 2 min read

Fast, automated unit testing is a vital component of any development project.  At Storm we use NUnit with TestDriven.NET from within Visual Studio to run our test suites as we develop.  We also use NHibernate with SQL Server for the data access layer of our applications.  To ensure each of our unit tests is atomic it is necessary to setup and teardown a new copy of the database for each test or series of tests.  With an SQL Server instance of a seperate box, this can be painfully slow once your test library reaches even a moderate size.  To speed things up, we’ve moved to in-memory SQLite.  Here’s how!

Configuring hibernate.cfg.xml for SQLite

You need to change your connection.driver_class, connection.connection_string and dialect to the SQLite specific values shown below.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory>
    <property name="connection.provider">
      NHibernate.Connection.DriverConnectionProvider
    </property>
    <property name="connection.driver_class">
      NHibernate.Driver.SQLite20Driver
    </property>
    <property name="connection.connection_string">
      Data Source=:memory:;Version=3;New=True;Pooling=True;Max Pool Size=1;
    </property>    
    <property name="adonet.batch_size">10</property>
    <property name="show_sql">true</property>
    <property name="dialect">NHibernate.Dialect.SQLiteDialect</property>
    <property name="use_outer_join">true</property>
    <property name="command_timeout">60</property>
    <property name="query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
    <property name="proxyfactory.factory_class">
      NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu
    </property>
  </session-factory>
</hibernate-configuration>

That should be that. Just make sure that you have System.Data.SQLite added as a reference to your test assembly. You can download the compiled binary from SourceForge.  Make sure you select the correct version, x86 or x64!

Warning: The first example I found on the net had the following driver_class:

<property name="connection.driver_class">NHibernate.Driver.SQLiteDriver</property>

This does not work with the lastest version of SQLite.  You need to use NHibernate.Driver.SQLite20Driver else you will get an exception similar to:

NHibernate.HibernateException : Could not create the driver from NHibernate.Driver.SQLiteDriver.  
----> System.Reflection.TargetInvocationException : Exception has been thrown by the target of an invocation.  
----> NHibernate.HibernateException : The IDbCommand and IDbConnection implementation in the assembly SQLite.NET
      could not be found. Ensure that the assembly SQLite.NET is located in the application directory or in the Global
      Assembly Cache. If the assembly is in the GAC, use <qualifyAssembly/> element in the application configuration file
      to specify the full name of the assembly.

Related news.