How do I unit test persistence?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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

Answer 7

Dan,

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.

Answer 8

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.

Answer 9

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.

Regards,

Rob G

Answer 10

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

Answer 11

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.

Answer 12

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

Answer 13

For .Net unit testing mocks I recommend Typemock Isolator.

You can have a look at Ayende too.

Answer 14

Try http://code.google.com/p/hibernatemock/