The Inquisitive Coder – Davy Brion's Blog

The usage of a real database in tests is a topic that’s been debated countless times before. There are probably just as many people who feel that you should always use a real database in tests as there are people who feel that you should avoid it at all costs. Some will use an in-memory database during tests, some will use stubbed data layers which use pure in-memory storage, some will mock the data layer.

No matter which way you prefer, i think there is at least one thing that you really have to test on the real database. With ‘real’ i obviously don’t mean the actual database that your application will use in production, but it should be the exact same database (vendor and version). So what is it? Queries! Practically every query in your application deserves one or more tests. You should test that your query returns results that it should return, and that it doesn’t return results that it shouldn’t return. And this should preferably be done on the real database, even if you use an in-memory database for your other tests. Suppose you’re using NHibernate to use SQLite during your tests… sure, you’re testing against a database, but how valuable are those tests when your application will be using SQL Server or Oracle at runtime? Every generated query could potentially (and in many cases quite likely) be different on another database and you really ought to make sure you don’t get bitten by that.

If you’re using a database with a well known set of data during tests, then this is pretty easy. Just call the method which will execute the query (either through an ORM, or some code that calls a stored procedure, or some plain ADO.NET code with a sql string, whatever…) and verify that the result contains the expected data and that it doesn’t contain data that it shouldn’t have.

If you’re using an empty database for each test, insert some records (and any records they need to exist) that should be retrieved by the query. Also insert some records that should not be retrieved by the query. Call the method which will execute the query and verify your expectations on the results. And don’t forget to get rid of the data you just inserted, either by deleting it in a fool-proof manner or by rolling back the transaction.

This does take some effort, but it’s usually well worth it. If you do this for most queries in your application, you’ll (normally) end up with very good coverage (and i don’t mean the code-coverage kind) of your datalayer, which in turn gives you more confidence to mock your datalayer in your other tests, if you’d want to do that.