Problem using SQLite :memory: with NHibernate

Question

I use NHibernate for my dataacess, and for awhile not I've been using SQLite for local integration tests. I've been using a file, but I thought I would out the :memory: option. When I fire up any of the integration tests, the database seems to be created (NHibernate spits out the table creation sql) but interfacting with the database causes an error.

Has anyone every gotten NHibernate working with an in memory database? Is it even possible? The connection string I'm using is this:

Data Source=:memory:;Version=3;New=True 

Answer 1

A SQLite memory database only exists as long as the connection to it remains open. To use it in unit tests with NHibernate:
1. Open an ISession at the beginning of your test (maybe in a [SetUp] method).
2. Use the connection from that session in your SchemaExport call.
3. Use that same session in your tests.
4. Close the session at the end of your test (maybe in a [TearDown] method).

Answer 2

I was able to use a SQLite in-memory database and avoid having to rebuild the schema for each test by using SQLite's support for 'Shared Cache', which allows an in-memory database to be shared across connections.

I did the following in AssemblyInitialize (I'm using MSTest):

• Configure NHibernate (Fluently) to use SQLite with the following connection string:

  FullUri=file:memorydb.db?mode=memory&cache=shared 

• Use that configuration to create a hbm2ddl.SchemaExport object, and execute it on a separate connection (but with that same connection string again).

• Leave that connection open, and referenced by a static field, until AssemblyCleanup, at which point it is closed and disposed of. This is because SQLite needs at least one active connection to be held on the in-memory database to know it's still required and avoid tidying up.

Before each test runs, a new session is created, and the test runs in a transaction which is rolled back at the end.

Here is an example of the test assembly-level code:

[TestClass] public static class SampleAssemblySetup {     private const string ConnectionString = "FullUri=file:memorydb.db?mode=memory&cache=shared";     private static SQLiteConnection _connection;      [AssemblyInitialize]     public static void AssemblyInit(TestContext context)     {         var configuration = Fluently.Configure()                                        .Database(SQLiteConfiguration.Standard.ConnectionString(ConnectionString))                                        .Mappings(m => m.FluentMappings.AddFromAssembly(Assembly.Load("MyMappingsAssembly")))                                        .ExposeConfiguration(x => x.SetProperty("current_session_context_class", "call"))                                        .BuildConfiguration();          // Create the schema in the database         // Because it's an in-memory database, we hold this connection open until all the tests are finished         var schemaExport = new SchemaExport(configuration);         _connection = new SQLiteConnection(ConnectionString);         _connection.Open();         schemaExport.Execute(false, true, false, _connection, null);     }      [AssemblyCleanup]     public static void AssemblyTearDown()     {         if (_connection != null)         {             _connection.Dispose();             _connection = null;         }     } } 

And a base class for each unit test class/fixture:

public class TestBase {     [TestInitialize]     public virtual void Initialize()     {         NHibernateBootstrapper.InitializeSession();         var transaction = SessionFactory.Current.GetCurrentSession().BeginTransaction();     }      [TestCleanup]     public virtual void Cleanup()     {         var currentSession = SessionFactory.Current.GetCurrentSession();         if (currentSession.Transaction != null)         {             currentSession.Transaction.Rollback();             currentSession.Close();         }          NHibernateBootstrapper.CleanupSession();     } } 

Resource management could improve, I admit, but these are unit tests after all (suggested improvements welcome!).