Problem using SQLite :memory: with NHibernate

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).

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!).

We are using SQLite in memory for all our database tests. We are using a single ADO connection for the tests that is reused for all NH sessions opened by the same test.

1. Before every test: create connection
2. Create schema on this connection
3. Run test. The same connection is used for all sessions
4. After test: close connection

This allows also running tests with several sessions included. The SessionFactory is also created once for all tests, because the reading of the mapping files takes quite some time.

Edit

Use of the Shared Cache

Since System.Data.Sqlite 1.0.82 (or Sqlite 3.7.13), there is a Shared Cache, which allows several connections to share the same data, also for In-Memory databases. This allows creation of the in-memory-database in one connection, and use it in another. (I didn't try it yet, but in theory, this should work):

• Change the connection string to file::memory:?cache=shared
• Open a connection and create the schema
• Keep this connection open until the end of the test
• Let NH create other connections (normal behavior) during the test.