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

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