NHibernate unit testing with SQLite in-memory DB
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
- Concerned the problem is not really that simple.