In-memory DBMS's for unit testing

Question

I am looking for satisfactory options for unit testing my .NET DAL classes; since they're DAL classes, they access the database directly using ADO.NET. Presently I use an instance of a MSSQL database for the testing, but was wondering what faster options there are---since unit tests need to run as quickly as possible, an in-memory solution would be ideal.

I should also mention that I have tied myself to TSQL since I'm only ever going to be using a Microsoft platform.

Answer 1

Given that you state:

I should also mention that I have tied myself to TSQL since I'm only ever going to be using a Microsoft platform.

Then Using SqlServer compact Edition may work well for your needs. It will not operate entirely in memory but can operate in a readonly mode (where no edits occur to the main database file so it can be used by multiple tests at once)

There are a few gotchas, no stored procedures are supported, a few data types need to be translated and certain data types have tight limits (notably varchar which can only go to 4000 characters) Linq to Sql also is not properly supported.

Nonetheless I have used a SqlServer Compact Edition as an almost entirely drop replacement for the proper Sql Server database with good results.

Answer 2

Is SQL Server really the bottleneck for your unit tests?

I mean:

1. Have you profiled your unit tests (with something like SQL Profiler). Are they all slow? Are a few slow? Why?
2. Are your unit tests doing too much? Is the setup and teardown code too heavy?
3. If SQL is your bottleneck, Have you considered a mocking framework, so you mock out all your SQL calls.

Answer 3

I would recommend using the same database for your unit tests as for production. You really don't need some weird difference shooting you in the foot when you're debugging a live issue.

If you have a look at the really big unit test suite for NHibernate, you'll see that it uses SQL Server (disk based), and the tests run surprisingly quickly. It's even more impressive consider that there's a lot more table creation / deletion going on than the average set of unit tests, which isn't what SQL Server is optimized for.

Answer 4

I heard that there is software to mount ramdisk in windows (can't remember url, sorry).

It could be interesting to create test databases on that.

Answer 5

I've found SQLite to be the best option. Though, I'm using nHibernate, but it's zero config so it only takes a sec to set up. Though, you do have to be aware that these types of engines typically lack a few things you might need (for example, SQLite blows up when you have spaces in table names if you're using an ADO provider)

Granted, @TopBanana is right about some of the issues with not using an "actual" database. However, an in-memory RDBMS is perfect for those kinds of tests you want to run really quickly (e.g. check-in tests for incremental or CI builds).

The other huge advantage is that you don't have to worry about setup or tear down. It's incredibly unproductive to have your check-in fail because developer A broke your dev database ;-)