As a novice in practicing test-driven development, I often end up on a quandary as to how to unit test persistence to a database.

I know that technically this would technically be an integration test (not a unit test), but I want to find out the best strategies for the ff:

  1. Testing queries.
  2. Testing inserts. How do I know that it is the insert that has gone wrong if it fails? I may test it by inserting and then querying -- how do I know that it isn't the querying that went wrong?
  3. Testing updates and deletes -- same as testing inserts

What are the best practices doing these?

+3  A: 

You do the unit testing by mocking out the database connection. This way, you can build scenarios where specific queries in the flow of a method call succeed or fail. I usually build my mock expectations so that the actual query text is ignored, because I really want to test the fault tolerance of the method and how it handles itself -- the specifics of the SQL are irrelevant to that end.

Obviously this means your test won't actually verify that the method works, because the SQL may be wrong. This is where integration tests kick in. For that, I expect someone else will have a more thorough answer, as I'm just beginning to get to grips with those myself.


I would also mock the database, and check that the queries are what you expected. There is the risk that the test checks the wrong sql, but this would be detected in the integration tests

David Sykes

Regarding testing SQL: I am aware that this could be done, but if I use an O/R Mapper like NHibernate, it attaches some naming warts in the aliases used for the output queries, and as that is somewhat unpredictable I'm not sure I could test for that.

Should I just, abandon everything and simply trust NHibernate? I'm not sure that's prudent.

Jon Limjap
+1  A: 

The problem I experienced when unit testing persistence, especially without an ORM and thus mocking your database (connection), is that you don't really know if your queries succeed. It could be that you your queries are specifically designed for a particular database version and only succeed with that version. You'll never find that out if you mock your database. So in my opinion, unit testing persistence is only of limited use. You should always add tests running against the targeted database.

I agree, while I've never been a TDD purist I definitely see alot of the benefits of TDD but to ignore reality and not truly test your records going in and out of your database you have no idea what your application is truly going to do in the wild. So many times before I'd get a "bug" assigned to me that my code wasn't working and I'd run my unit tests and find out another developer stomped all over my sproc (evil evil sprocs) and never cared to run my unit tests to see they busted up the whole system.
Chris Marisic
+1  A: 

For NHibernate, I'd definitely advocate just mocking out the NHibernate API for unit tests -- trust the library to do the right thing. If you want to ensure that the data actually goes to the DB, do an integration test.


Technically unit tests of persistance are not Unit tests they are Integration tests.

With C# using mbUnit, you simply use the SqlRestoreInfo and RollBack attributes

[SqlRestoreInfo(<connectionsting>, <name>,<backupLocation>]
public class Tests

public void Setup()


public void TEST()
//test insert.

The same can be done in NUnit, excpet the attribute names differ slighty.

As for checking if your query was succeful, you normally need to follow it with a second query to see if the database has been changed as you expect.



The "second query" quandary is precisely the thing I want to avoid.

I guess it's really impossible to have a truly atomic unit test for persistence.

Jon Limjap
+8  A: 

Look into DB Unit. It is a Java library, but there must be a C# equivalent. It lets you prepare the database with a set of data so that you know what is in the database, then you can interface with DB Unit to see what is in the database. It can run against many database systems, so you can use your actual database setup, or use something else, like HSQL in Java (a Java database implementation with an in memory option).

If you want to test that your code is using the database properly (which you most likely should be doing), then this is the way to go to isolate each test and ensure the database has expected data prepared.

Mike Stone
+3  A: 

Hi Jon,

I have written a post here concerning unit testing the data layer which covers this exact problem. Apologies for the (shameful) plug, but the article is too long to post here.

I hope that helps you - it has worked very well for me over the last 6 months on 3 active projects.


Rob G

+11  A: 

As Mike Stone said, DbUnit is great for getting the database into a known state before running your tests. When your tests are finished, DbUnit can put the database back into the state it was in before you ran the tests.

DbUnit (Java)


Josh Brown

I usually create a repository and use that to save my entity, then retrieve a fresh one. Then I assert that the retrieved is equal to the saved.

Thomas Eyde

@Josh Brown: DbUnit.NET sounded cool, but my first impression is that all it is a mock repository, substituting an XML DataSet for your database. Sounds like a lot of work to keep two schema's in sync. Also, it doesn't sound like it does much for testing stored procedures.


For .Net unit testing mocks I recommend Typemock Isolator.

You can have a look at Ayende too.