Ayende @ Rahien

It's a girl

Unit Testing Database: Embedded vs. In Memory..

A while ago I extolled the benefits of using an in-memory database for tests. Now, the only in memory database that I know of (that has a ADO.Net provider) is SQLite. SQLite is a great database, except for one tiny issue. It is has a really weak support for dates, requiring your to jump through multiply hops to do anything even slightly interesting with dates.

Since I am mostly working on business applications, strong support for dates is a crucial issue for me. With great relucance, I moved my tests to use SqlCE embedded database. This is still an order of magnitude faster than going to a remote server, but it was much slower than running the whole test in memory, never touching the disk.

The speed difference became acute when I tried to run many Unit Tests (vs. Integration Tests) using the embedded DB, it was far too slow to be a real unit test framework.

For this reason, I now have two ways of using database from unit tests, using In Memory for most tests, and using Embedded DB when dates are important. You can check the code here.

Anyone can recommend a .NET accessible in memory database with good dates handling?

Comments

Srdjan
02/25/2007 05:27 PM by
Srdjan

did you try SharpHSQL?

It can be set to run in memory...

I use it that way. Not without issues but could serve your needs...

Tom Opgenorth
02/25/2007 08:29 PM by
Tom Opgenorth

Maybe try Firebird SQL? I've been playing with it for the last little while, seems to work for me.

Josh Robb
02/25/2007 09:39 PM by
Josh Robb

What about setting up a unit test for the date based operations that fail in SQLLite - then everyone can run them in their favourite embedded DB and see who supports what.

It would also document the basic functionality that you consider necessary.

Bil Simser
02/25/2007 10:12 PM by
Bil Simser

Not sure if your SQLite is referring to what I'm talking about here but SQL Server 2005 Compact Edition is pretty nice for in-memory databases and just needs a couple of dlls to include for release.

You can check it out here:

http://www.microsoft.com/sql/editions/compact/default.mspx

Ayende Rahien
02/26/2007 06:00 AM by
Ayende Rahien

@Srdjan,

The last SharpHSQL release that I can find is 2004, on gotdotnet, that is not encouraging when choosing a database.

Ayende Rahien
02/26/2007 06:01 AM by
Ayende Rahien

@Tom,

Can I run firebird in memory??

Ayende Rahien
02/26/2007 06:07 AM by
Ayende Rahien

Josh, I'll try to do that.

The basic idea is that I really need to have some way to ask:

"Give me all the records within a specified range from this date" and "Give me all the records where the child record is 3 days more recent than that parent record."

Ayende Rahien
02/26/2007 06:09 AM by
Ayende Rahien

Bil,

SQL CE is an embedded DB, which means that it writes to a file and runs in-process.

To my knowledge, there is no way to run a database entirely in memory.

If there was such a way, that would be the perfect DB.

schorsch
02/26/2007 08:22 AM by
schorsch

Maybe Bamboo.Prevalence is what you're searching.

http://sourceforge.net/projects/bbooprevalence/

CodeProject article: http://www.codeproject.com/dotnet/ObjectPrevalenceWithBambo.asp?df=100&forumid=24434&exp=0&select=1617384&fr=26

XSD->Bamboo Migration Kit: http://www.users.bigpond.net.au/meyn/bamboo/

Marvin
02/26/2007 11:58 AM by
Marvin

How about a ramdisk?

Bratukhin Andrey
02/26/2007 03:47 PM by
Bratukhin Andrey

You can place embedded DB to virtual disk in memory.

Juice
02/26/2007 06:35 PM by
Juice

Ayende, have you got sql ce to work with the aggressive connection release mode in NHibernate? I keep getting errors on inserts using the identity Id generator. I think the problem is that the identity is attempted to be selected on a different connection than the conncetion that issued the insert.

Ayende Rahien
02/26/2007 07:49 PM by
Ayende Rahien

I just checked, and it looks like a bug in the dialect.

Please file a JIRA about it.

At the meantime, you can disable aggressive release mode

Mike
02/26/2007 09:31 PM by
Mike

VistaDB? It costs though http://www.vistadb.net/default.asp

Tom Opgenorth
02/27/2007 12:52 AM by
Tom Opgenorth

Ayende, yes, according to the docs you can run FirebirdSQL in memory. I haven't done so though.

Ayende Rahien
02/27/2007 06:04 AM by
Ayende Rahien

Tom,

I have been unable to find the documentation regard it, can you supply a link to it?

Tom Opgenorth
02/27/2007 06:55 AM by
Tom Opgenorth

Hmmmm, I may have to recant. I could have sworn that while troubleshooting an issue with Firebird and ActiveRecord that I saw a connection string for using an in-memory database instead of disk based. However, for the life of me I can't find it now. I'll keep looking for it and if I see anything I'll be sure to let you know. Sorry.

Kevin
02/27/2007 09:09 AM by
Kevin

If your test database is small, you can try usng TempDB in local SQL Server and config TempDB fully in memory.

Just a thought.

Kevin

Comments have been closed on this topic.