For anyone who is having issues with in-memory SQLite databases and NHibernate 2, check this post out!

I have been playing around recently with a bit of code that I needed a database to hit against to test it. The code was using NHibernate to directly access the database, so I couldn’t just mock a layer and fake it, I really needed a relational database to access. So, I began to look around the net, and I found several posts (including this one by Oren Eini) where people have referenced using an in memory SQLite database to test against. Most of them were a bit older, and most of them were using NHibernate 1.2, but they were a good enough starting point where I could get all of my code setup. The problem was that once I got all of it setup, I was still getting exceptions from SQLite stating that it couldn’t find the table when I went to query it. The exact exception I was getting was this:

NHibernate.ADOException: could not execute query [ select person0_.PersonId as PersonId0_, person0_.FirstName as FirstName0_, person0_.LastName as LastName0_ from People Person0_ ] —> System.Data.SQLite.SQLiteException: SQLite error no such table: People

Before I go into how I finally resolved this error, let me first go through my setup. It is very close to Oren’s post above, but there are slight differences that may trip you up, so I will post most of this. First of all I am using NHibernate 2.0.1 and I am using System.Data.SQLite 1.0.60.0.

One thing to point out, I am on an x64 machine, so make sure that if you are that you are using the System.Data.SQLite.DLL file in the x64 folder when running your application. But in the unit tests, I’m assuming since NUnit is a 32-bit app, I had to reference the 32-bit System.Data.SQLite.dll.  Otherwise you’ll get some nonsensical errors that will take a while to figure out. Trust me, I know. 🙂

My TestFixture base class looks pretty similar to Oren’s, only for NHibernate 2.0 the config is a bit different:

public class NHibernateInMemoryTestFixtureBase
{
    protected static ISessionFactory sessionFactory;
    protected static Configuration configuration;

    public static void InitalizeSessionFactory(params Assembly[] assemblies)
    {
        if (sessionFactory != null)
            return;

        var properties = new Dictionary<string, string>();
        properties.Add("connection.driver_class", "NHibernate.Driver.SQLite20Driver");
        properties.Add("dialect", "NHibernate.Dialect.SQLiteDialect");
        properties.Add("connection.provider", "NHibernate.Connection.DriverConnectionProvider");
        properties.Add("connection.connection_string", "Data Source=:memory:;Version=3;New=True;");

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

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

    public ISession CreateSession()
    {
        ISession openSession = sessionFactory.OpenSession();
        IDbConnection connection = openSession.Connection;
        new SchemaExport(configuration).Execute(false, true, false, true, connection, null);
        return openSession;
    }
}

Okay, so you can see here we are doing the same thing, we have two methods… One which which does the heavy lifting of setting up the SessionFactory, and a second which retrieves a new session and exports the schema to the in-memory database when we call it. Simple. Quite elegant, and I wish I had thought of it! In the test fixture our code is going to look like this:

[TestFixture]
public class PersonFixture: NHibernateInMemoryTestFixtureBase
{
    private ISession session;
    private System.Data.SQLite.SQLiteCommandBuilder cmd = new SQLiteCommandBuilder();
    [TestFixtureSetUp]
    public void TestFixtureSetUp()
    {
        InitalizeSessionFactory(typeof(Person).Assembly);
    }

    [SetUp]
    public void SetUp()
    {
        session = this.CreateSession();
    }

    [TearDown]
    public void TearDown()
    {
        session.Dispose();
    }

    [Test]
    public void CanSaveAndLoadPerson()
    {
        var person = new Person();
        person.FirstName = "Justin";
        person.LastName = "Etheredge";
        session.Save(person);
        session.Flush();

        session.Evict(person);

        IQuery query = session.CreateQuery("from Person");
        IList<Person> people = query.List<Person>();

        foreach (Person loopPerson in people)
        {
            Console.WriteLine("{0} {1}", loopPerson.FirstName, loopPerson.LastName);
        }
    }
}

Same deal as you saw in Oren’s post. Only with one big difference. Mine doesn’t work. So, why doesn’t it work? Well, I’m glad you asked.

It took me a while to track this one down, because the error I was receiving was telling me that the table could not be found. So I had incorrectly assumed that the database itself was not being correctly created. Once I started thinking that this wasn’t the problem I started looking at the connection lifetime and I noticed that after my call to “Flush” the connection was closed. Well, in our situation this is very bad, since we are using an in-memory database, this is wiping our database out!

I did a bit of research and found that in NHibernate 1.2 the concept of “Connection Release Modes” was introduced. Prior to this connections were held until the session was closed. With “Connection Release Modes” there are currently three options “auto”, “on_close”, and “after_transaction”. The default is “auto” which is equivalent to “after_transaction”. Well, since in NHibernate 2.0 transactions are now required, and are implicit if you don’t specify one, the call to “Flush()” was causing a transaction to be created and committed which resulted in my connection being closed and my database going “poof” into thin air. Then when I went to query it, the connection was reopened, the database was gone, and I got a “no such table” error. How quaint.

The fix ended up being quite simple though, I just needed to set my “connection.release_mode” setting to “on_close” which according to the nHibernate docs is “left for backward compatibility, but its use is highly discouraged”. Well, I hope that they don’t remove this in a future version because it will cause a few issues with those of us who are using in-memory databases for testing purposes.

The test fixture base class that we defined above just has a single added line, but I’ll go ahead and pasted the whole thing in again here:

public class NHibernateInMemoryTestFixtureBase
{
    protected static ISessionFactory sessionFactory;
    protected static Configuration configuration;

    public static void InitalizeSessionFactory(params Assembly[] assemblies)
    {
        if (sessionFactory != null)
            return;

        var properties = new Dictionary<string, string>();
        properties.Add("connection.driver_class", "NHibernate.Driver.SQLite20Driver");
        properties.Add("dialect", "NHibernate.Dialect.SQLiteDialect");
        properties.Add("connection.provider", "NHibernate.Connection.DriverConnectionProvider");
        properties.Add("connection.connection_string", "Data Source=:memory:;Version=3;New=True;");
        properties.Add("connection.release_mode", "on_close");

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

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

    public ISession CreateSession()
    {
        ISession openSession = sessionFactory.OpenSession();
        IDbConnection connection = openSession.Connection;
        new SchemaExport(configuration).Execute(false, true, false, true, connection, null);
        return openSession;
    }
}

So, there you have it, a full working example of an in-memory SQLite database running with NHibernate 2.0. Sweet.