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.

Reine

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.

Osswald

I think the Linq to Sql has been implemented since then? – Blinni

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

Panoply

Here is the link to the SQLite ado.net provider sqlite.phxsoftware.com . To use the in-memory db use a connection string with :memory:. IMO, you should only test your DAL with a db during CI builds. Upper layers should get a DAL mock without db. – Elda

Just one problem - SQLite doesn't talk TSQL! – Outsert

Whoops, sorry. Maybe he should try SQL Server Compact then - snurl.com/au31q it can cope with TSQL to some extend - read the comparison here: snurl.com/au3d0 . It is not an in-memory db but a lightweight DB which doesn't need a service but only DLLs.It still writes to the disk however. – Elda

I wrote almost the same as ShuggyCoUk did. But I guess it's okay because I provided the links. – Elda

Answer 3

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.

Modiolus

What happens when you use a mocking framework? Let say i have a PersonService. That PersonService has a method GetAllPersons to return all persons. In that method i have unity provide an implementation of IPersonDataAcces which has the method GetAllPersons(). In production, the implementation goes to sql server and gets all persons. What happens if i use a mocking framework? I guess i also have unity give me an implementation of IPersonDataAcces, but then a 'mocked' one? Then what happens if i call the GetAllPersons() method? And what do i assert when testing the PersonService? – Brumfield

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.

Tailor

I have actually tried this route with ImgDisk. I did a create / destroy database for every test. In my scenario this was not so much faster than having the database on SSD (to slow). So now I am looking into using in memory SQLite or the usual route with DI and mocks. – Haughty

Answer 5

I had similar challenges with Oracle and we did the following:

• made sure that we had that real unit tests didn't touch the db, but used mocks for services instead

• Tagged DB tests that actually needed Oracle versus the tests that could run against HSQLDB or H2 or any other in memory database. So we can run them separately.

• With the tests that actually used Oracle features we used a normal Oracle instance that ran on a RAM disc.

This made the tests considerable faster.

Inlay

It only address the performance issue of unit test. But ideally, I need an easier way to code in the test data in the unit test just like I register any stub implementation for depending methods. Preparing the data in real DB is a bit complex. – Rida

Answer 6

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.

Outsert