CODEWITHSUNDEEP
sql-serverunit-testingtdd
Jon Limjap
Jon Limjap

Reputation: 95432

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.

Upvotes: 50

Views: 9727

Answers (10)

Dan
Dan

Reputation: 29365

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.

Upvotes: 1

Thomas Eyde
Thomas Eyde

Reputation: 3934

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.

Upvotes: 1

cchantep
cchantep

Reputation: 9168

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.

Upvotes: 2

Rytmis
Rytmis

Reputation: 32047

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.

Upvotes: 2

RobertTheGrey
RobertTheGrey

Reputation: 8685

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

Upvotes: 3

Josh Brown
Josh Brown

Reputation: 53073

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

Upvotes: 16

Mike Stone
Mike Stone

Reputation: 44613

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.

Upvotes: 18

dlinsin
dlinsin

Reputation: 19600

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.

Upvotes: 2

David Sykes
David Sykes

Reputation: 49832

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

Upvotes: 1

Rytmis
Rytmis

Reputation: 32047

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.

Upvotes: 4

Related Questions