JasonDentler.com

While doing research for my next post, I ran across a recurring problem. Everyone wanted to use an in-memory SQLite database for their NHibernate unit tests.

Evidently, there’s no way to get back to the in-memory database in it’s current state after all of the connections to that database have been closed. When you open another connection, you get a fresh blank database.

Here’s the solutions I found while searching around.

• Ayende just uses a single NHibernate session, which usually only uses a single SQLite DB connection. In fact, he just uses one session per test fixture. I’m not that smart and I don’t know enough about the internals of NHibernate to be confident that I’m really testing what I think I’m testing. Using a fresh session and database for each test would make me a lot more confident.
• Krzysztof Kozmic has resorted to using a SQLite database stored on disk. While it may be faster than blowing away and recreating a SQL Express database dozens of times, it’s still slower than using an in-memory database. Variations of this idea include storing the database on a MS-DOS 5.0-style RAM disk drive to recover some of the speed lost by using file I/O. Genius! Except, I’m not going to reconfigure my PC for a unit test. Sorry, it’s just not portable enough. That’s why the idea was rated down on Stack Overflow.
• Someone (gotta love the anonymous bloggers!) suggested building a custom NHibernate DriverConnectionProvider and overriding the GetConnection and CloseConnection functions so that once opened, the connection would never be closed. This seems a bit extreme to me, and doesn’t provide a mechanism to get a fresh database when we need one.

Using the DriverConnectionProvider idea of maintaining a single connection, but with a controlled scope, I came up with this:

Imports FluentNHibernate.Cfg
Imports FluentNHibernate.Cfg.Db
Imports FluentNHibernate.Mapping
Imports NHibernate
Imports NHibernate.Tool.hbm2ddl
Imports System.Data.SQLite

Public Class SQLiteDatabaseScope(Of TClassFromMappingAssembly)
    Implements IDisposable

    Private Const CONNECTION_STRING As String = "Data Source=:memory:;Version=3;New=True;"

    Public Sub New()
        BuildConfiguration()
    End Sub

    Private m_Connection As SQLiteConnection
    Private m_SessionFactory As ISessionFactory

    Private Sub BuildConfiguration()
        m_SessionFactory = Fluently.Configure() _
                            .Database(GetDBConfig()) _
                            .Mappings(AddressOf GetMappings) _
                            .ExposeConfiguration(AddressOf BuildSchema) _
                            .BuildSessionFactory()
    End Sub

    Private Function GetDBConfig() As FluentNHibernate.Cfg.Db.IPersistenceConfigurer
        Return SQLiteConfiguration.Standard _
                    .ConnectionString(Function(cs As ConnectionStringBuilder) cs.Is(CONNECTION_STRING))
    End Function

    Private Sub GetMappings(ByVal x As MappingConfiguration)
        x.FluentMappings _
            .AddFromAssemblyOf(Of TClassFromMappingAssembly)() _
            .ExportTo(".")
    End Sub

    Private Sub BuildSchema(ByVal Cfg As NHibernate.Cfg.Configuration)
        Dim SE As New SchemaExport(Cfg)
        SE.Execute(False, True, False, GetConnection, Console.Out)
    End Sub

    Private Function GetConnection() As System.Data.SQLite.SQLiteConnection
        If m_Connection Is Nothing Then
            m_Connection = New SQLiteConnection(CONNECTION_STRING)
            m_Connection.Open()
        End If
        Return m_Connection
    End Function

    Public Function OpenSession() As ISession
        Return m_SessionFactory.OpenSession(GetConnection)
    End Function

    Private disposedValue As Boolean = False        ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: free other state (managed objects).
                If m_Connection IsNot Nothing Then m_Connection.Close()
                m_Connection = Nothing
            End If

            ' TODO: free your own state (unmanaged objects).
            ' TODO: set large fields to null.
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class

It’s the same idea as the DriverConnectionProvider, but with a different scope. We create and open a connection and maintain it for the lifespan of the SQLiteDatabaseScope object. So far, I haven't found an instance where NHibernate closes the connection.

Each instance of SQLiteDatabaseScope uses a different in-memory database, thanks to the New=True property in the connection string. You should be able to run your tests in parallel. You can’t do that with any of the other solutions.

The generic type parameter should be set to one of your fluent mapping classes. It’s used to tell Fluent NHibernate which assembly to scan for fluent mappings.

It’s extremely easy to use.

Imports NUnit.Framework

<TestFixture> _
Public Class SomeNHibernateTestFixture

    <Test>
    Public Sub SomeNHibernateTest()
        Using Scope As New SQLiteDatabaseScope(Of CourseMapping)
            Using SessionOne = Scope.OpenSession
                ' Do some stuff here...
            End Using

            Using SessionTwo = Scope.OpenSession
                ' Do some more stuff using the same in-memory SQLite DB
            End Using
        End Using
    End Sub

    <Test>
    Public Sub AnotherNHibernateTest()
        Using Scope As New SQLiteDatabaseScope(Of CourseMapping)
            Using SessionThree = Scope.OpenSession
                ' Do some stuff here to a new, freshly-built database...
	    End Using
        End Using
    End Sub

End Class

Jason
- Concerned the problem is not really that simple.

Download the SQLiteDatabaseScope code in Visual Basic or C#. GoDaddy doesn’t like you to download .vb or .cs files, so don’t forget to take off the .txt extension before you add it to your project.