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 🙂
Best Answer
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.