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

Just a wild guess, but is the sql output by NHibernate using a command unsupported by sqlite?

Also, What happens if you use a file instead of memory? (System.IO.Path.GetTempFileName() would work i think...)

Answer 2

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 3

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.

Answer 4

I am doing it with Rhino Commons. If you don't want to use Rhino Commons you can study the source do see how it does it. The only problem I have had is that SQLite does not support nested transactions. This forced me to change my code to support integration testing. Integration testing with in memory database is so awesome, I decided it was a fair compromise.

Answer 5

If you add Pooling=True;Max Pool Size=1 to your connection string, you'll get the same connection (and therefore database) each time.

Answer 6

I hade alot off problems with SQLite memory database. So now we are using SQLite working with files on a ramdrive disk.