Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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 :)

share|improve this question
    
Similar question: stackoverflow.com/q/9002688/38368 –  Danny Varod Feb 5 '12 at 21:31

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
    
I like that idea. The thing to note with my setup though is that I can create/add data and remove all of it at the end through the same procedures that I am testing. So as long as they work, the database will be naturally cleared out at the end, and naturally have data to work with throughout the entire process. This is one of the main reasons why I like having all data access and manipulation done through stored procedures. Not sure if this is a perfect way of doing things, but it would work. –  Scott Feb 5 '12 at 18:38
2  
@Scott It sounds like you're coupling your tests, which isn't a good practice. Each test should be runnable in isolation, with no dependency on previous tests having completed. –  Daniel Mann Feb 5 '12 at 18:52
    
That is a very good point, didn't really cross my mind. So basically, even though my tests can create a situation for the other one fine, it would make more sense to have a setup/tear-down pair that set the database up so the tests that were dependent on data could run against that, instead of depending on other tests filling it in? Obviously all of the tests won't need data to be there as CRUD is a common thing among them, however for the ones that do. –  Scott Feb 5 '12 at 19:56
    
Read everyone's, but you were the firs and seemingly fitting with everyone else. Plus, I've already started implementing things in this fashion. Thanks DBM. –  Scott Feb 6 '12 at 3:05

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.