Unit/Integration Testing my DAL

Question

So I've done some research on this but I couldn't quite come to a conclusion, so I figured I'd ask you guys to see if I could get some other opinions.

All of my database access is currently done through stored procedures. I am going to make no direct calls using custom queries. Because of this, my DAL is going to be pretty simple. It's basically going to just contain a bunch of methods that more or less interface out the different stored procedures in the database. It will always be in sync with the procedures that are there, and never call into the database any other way.

I'm not sure if this is the greatest way to do things, and I am aware of the advantages and disadvantages of only using stored procedures, but it's just the way I've chosen. I think it will be the cleanest in the long run.

But I want to test this DAL. I want to test this at a low level opposed to only testing it via the Business Objects that are going to be tied to these calls. I figure doing that will give me confidence that the procedures are working correctly, and than at a high level I can just mock this out and test business logic - but I'm fighting with how I test this stuff.

If I write unit tests by the book, I would mock out the actual calls to the database and just make sure that they are getting called, or create some stubs or whatever that return fake data and do it that way (both make sure the function is actually called). However, what good is this if the only thing these methods are doing are taking parameters and calling a stored procedure? All my mocks would be doing would be making sure I'm calling the method from the test more or less, and that seems like a huge waste of time and not really that effective.

Now if I integration test this stuff, I could be dealing with real test data, real database calls, which is fine, but then it wouldn't be 100% considered a unit test, and since within the Business Objects I'm mocking these calls out, I'd technically never have official unit tests for this stuff, only integration ones. Does this all make sense?

Basically, this seems fine to do for me. Have integration tests for the DAL, and have that be the only time the DAL itself is tested. When I unit test the business logic and mock out the DAL, I'd know it handles data correctly, and that would be comforting enough for me. What I'm asking is, am I approaching this correctly? Is there anything else you guys do that I am missing here that would shine some light on this stuff?

Any feedback is much appreciated :)

Answer 1

Your approach sounds correct. Just be aware that integration tests still need to be repeatable, so you'll have to have a fairly static test database that has all of the tables/views/stored procedures synched to your production codebase. It can be a pain to maintain that, especially with multiple developers. You'll end up with situations where a previous integration tests breaks because someone else went and added a row to a table for one of their integration tests.

One approach I've seen to handle that is, rather than having a "fairly static" database, you have a completely empty database that has all of its objects and data constructed as part of the test setup, and cleared out as part of the teardown. That represents a higher maintenance cost and a non-trivial amount of initial setup work, but may work better if your database schema changes frequently.

Answer 2

If I write unit tests by the book, ... However, what good is this if the only thing these methods are doing are taking parameters and calling a stored procedure?

Get a better book.

Now if I integration test this stuff, I could be dealing with real test data, real database calls, which is fine, but then it wouldn't be 100% considered a unit test,

By whom?

You're testing stored procedures. Those are your unit. It's not an integration test, except in the sense that you have to "integrate" the database with something else to test it.

[Also, the very fact that you need to ask this question is a hint that important code in stored procedures is a bad idea. Your testing is made much harder by putting code in to the database. "I think it will be the cleanest in the long run." is countered by your question on how to properly test them. If you get rid of them, you don't have the question on how test them.]

Here's the point "Unit" does not have a single, standard, formal definition.

Store procedures can be the unit of unit testing.

You can use unit testing techniques to test your database. Just be sure that it's a completely repeatable, fully automated test. The test must include a standard database configuration that's loaded prior to the test so that the test works with one set of data -- and only one set of data.

It's still unit testing, because you've defined your unit of code to be the so

Answer 3

I agree with both DBM and S.Lott.

The unit under test can be anything that needs testing. Defining that only classes can be units is narrow minded.

I suggest either connecting to an empty DB and filling with data in the test set-up phase, then either emptying it or deleting it in the test clean-up phase. or creating a copy of a constant DB, connecting to it in the test set-up phase, then deleting in the test clean-up phase.

It is important to do this per test, so that the tests are truly independent.