How do I unit test persistence?

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

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

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

What are the best practices for doing these?

---

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.

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.

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)

DbUnit.NET

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 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.

Regards,

Rob G

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.

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.

For JDBC based projects, my Acolyte framework can be used: http://acolyte.eu.org . It allows to mockup data access you want to tests, benefiting from JDBC abstraction, without having to manage a specific test DB.

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

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:

    [TestFixture]
    [SqlRestoreInfo(<connectionsting>, <name>,<backupLocation>]
    public class Tests
    {
      
        [SetUp]
        public void Setup()
        {
        
        }

        [Test]
        [RollBack]
        public void TEST()
        {
           //test insert. 
        }
    }

The same can be done in NUnit, except the attribute names differ slightly.

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

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