Fluent NHibernate Unit Testing Using SQLite

Summary

So I’ve been producing a lot of blog posts on NHibernate lately. I’m attempting to evaluate NHiberate against other ORMs like Entity Framework. This blog post will focus no basic unit testing of NHibernate. I’m using Fluent with my NHibernate, but much of this knowledge still applies to straight NHibernate.

SQLite

Before I started putting together my first unit test, I consulted my vast reference library of information, also known as Google. As I did my initial research into how everyone else was unit testing NHibernate, I stumbled across a lot of articles talking about a tiny database driver known as SQLite. There’s a .NET version that you can use with Visual Studio by searching for it in the NuGet package manager. The first thing I had to work out is how to connect to SQLite and return an ISession object identical to what I was getting with MSSQL. This is what I came up with:

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHibernate.Tool.hbm2ddl;

namespace UnitTestNHibernate
{
    public static class SQLLiteSessionFactory
    {
        private static ISessionFactory _sessionFactory;
        private static ISessionFactory SessionFactory
        {
            get
            {
                if (_sessionFactory == null)
                {
                    _sessionFactory = Fluently.Configure()
                    .Database(SQLiteConfiguration
                            .Standard
                            .InMemory()
                            .UsingFile("facultydata.db")
                    )
                    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<NHibernateUnitTestSQLLite.Class>())
                    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<NHibernateUnitTestSQLLite.Teacher>())
                    .ExposeConfiguration(cfg => new SchemaExport(cfg).Create(true, true))
                    .BuildSessionFactory();
                }
                return _sessionFactory;
            }
        }
        public static ISession OpenSession()
        {
            return SessionFactory.OpenSession();
        }
    }
}

I’m using the facultydata database from my previous examples (I’m somewhat lazy and I don’t want to create new databases and tables to demonstrate these things if I don’t have to). Anyway, I will not be testing multiple databases in this blog post (primarily because I haven’t been able to make it work or fake it in SQLite).

The .InMemory() setting causes the database to be created in memory only. No file. If you don’t include this flag, then a database named “facultydata.db” will be created in the bin directory of your project.

The .ExposeConfiguration setting will cause all the tables used by this application to be created in the SQLite database when a session is opened. Therefore, when you create your unit tests, you only have to insert some data and call your method under test.

The Unit Tests

I wrote one unit test just to access tables in Sqlite and see how it will perform. Here is the test I wrote:

[TestMethod]
public void test_sqlite_database_using_fluent_nhibernate_directly()
{
    using (ISession session = SQLLiteSessionFactory.OpenSession())
    {
        session.Save(new Teacher() { Name = "Adam" });
        session.Save(new Teacher() { Name = "Joe" });

        session.Save(new Class() { Name = "MATH 101", TeacherId = 1 });


        var query = (from t in session.Query<Teacher>()
                        join c in session.Query<Class>() on t.Id equals c.TeacherId
                        select t).ToList();

        Assert.AreEqual(1, query.Count);
    }
}

As you can see I just tossed two records in the Teacher table and one record in the Class table. Then I joined them together. Of course, one of the classes does not have a teach assigned (I don’t have any constraints on my SQLite database in this example), therefore one of the teacher records will not join and only one record will be produced. But that’s OK, this is just a proof of concept test anyway.

Next, I need to be able to test a method in the main project by breaking dependency of the object and using the test session. So I wrote an object to read all classes and I created two constructors to handle normal access to MSSQL server and test access using SQLite. Here’s what the object looks like:

using System;
using System.Collections.Generic;
using System.Linq;
using NHibernate;
using NHibernate.Linq;

namespace NHibernateUnitTestSQLLite
{
    public class ReadAllClasses
    {
        ISession _session;

        public ReadAllClasses()
        {
            _session = MSSQLSessionFactory.OpenSession();
        }

        public ReadAllClasses(ISession _testSession)
        {
            _session = _testSession;
        }

        protected void Dispose(bool disposing)
        {
            if (_session is IDisposable)
            {
                ((IDisposable)_session).Dispose();
            }
        }

        public List<Class> Execute()
        {
            // quick and dirty LINQ query
            var query = (from c in _session.Query<Class>() 
                         select c).ToList();

            return query;
        }
    }
}

As you can see, the first constructor uses the MSSQLSessionFactory object to get an ISession and the second constructor allows a unit test to pass an ISession that can be created before calling this method. This object was created in the main project (not the unit test project).

This is how I wrote the unit test to call this method:

[TestMethod]
public void test_method_in_fluent_nhibernate_project()
{
    ISession session = SQLLiteSessionFactory.OpenSession();

    session.Save(new Class() { Name = "MATH 101", TeacherId = 1 });
    session.Save(new Class() { Name = "CSC 100", TeacherId = 1 });

    ReadAllClasses readAllClasses = new ReadAllClasses(session);
    List<Class> result = readAllClasses.Execute();

    Assert.AreEqual(2, result.Count);
}

In my unit test, I created a session and passed it as a parameter to ReadAllClasses(). This sets the class up to use the SQLite database instead of the default MSSQL database.

If you want to create this project from the ground up, you’ll need to add a NuGet package for NHibernate, then FluentNHibernate, then SQLite. You can delete the Entity Framework dlls that are installed by NuGet. Even so, the project will still be 112 Megabytes in size, with very little code. My suggestion to anybody who wants to deploy an application into a production system, is to move all the needed dlls into a third party directory, then re-add the references and remove all the NuGet “fluff”.

You can download my sample code here:

NHibernateUnitTestSQLLite.zip

Leave a Reply