+972 52-548-6969
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
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...
Maybe try Firebird SQL? I've been playing with it for the last little while, seems to work for me.
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.
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
@Srdjan,
The last SharpHSQL release that I can find is 2004, on gotdotnet, that is not encouraging when choosing a database.
@Tom,
Can I run firebird in memory??
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."
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.
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/
How about a ramdisk?
You can place embedded DB to virtual disk in memory.
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.
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
VistaDB? It costs though http://www.vistadb.net/default.asp
Ayende, yes, according to the docs you can run FirebirdSQL in memory. I haven't done so though.
Tom,
I have been unable to find the documentation regard it, can you supply a link to it?
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.
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
Comment preview