How to TDD that the correct results are returned

Question

I'm starting a new project, and trying very very hard to use TDD to drive the design. I've been pushing for years, and finally got approval to spend the extra time on this project to use it while I learn how to do it properly.

This is a new module, to tie into an existing system. Currently, all data access happens through webservices, which for the most part are just a thin wrapper over database stored procedures.

One requirement is that for a given store, I return all Purchase Orders that are considered valid for this application. A PO is considered valid if it's ship date falls withing a given range from the stores opening date (this is for new stores).

Now, I can't put this logic in the application code, as I'm not going to bring back a million POs just to get the dozen that apply to could apply to this store given the constraint above.

I was thinking, I could pass the date range to a GetValidPOs proc, and have it use those values to return the valid POs. But, what if we add another requirement to what is considered a valid PO?

And how do I test this and verify it keeps working? We are not using an ORM, and it's unlikely to happen. And I can't call the DB in my test.

I'm stuck.

My other thought, is have some mocks that return valid data, others that return some bad data, and have the local repository throw an exception if bad data occurs, and test that the exception gets thrown if invalid data is returned by GetValidPOs proc (or the mock used in testing).

Does this make sense? Or is there a better way?

UPDATE: I am able to use EF it would seem. Now I just need to figure out how to use it, and make it testable, while still being able to rely on stored procedures, and the difficulty of having data scattered across several databases.

Answer 1

This is a major downside of stored procedures in the age of TDD. They have some real upsides, even now, but by definition any test that exercises a stored proc is not a unit test; it's an integration test at best.

The usual solution, assuming the architecture cannot change to use an ORM instead, is to not put these tests in the unit test suite; instead, put the tests in an integration suite. You can still run the test whenever you like to verify it works, but because the cost inherent in setting up the test (initializing a DB with the proper test data) is high and it touches resources your build-bot's unit-test agent may not have access to, it shouldn't be in the unit test suite.

You can still unit-test code that requires the data, by abstracting anything you cannot unit-test (ADO.NET classes) into a DAO class that you can then mock. You can then verify that the expected calls are made by consuming code and reproduce real-world behavior (such as finding no results) allowing testing of various use cases. However, the actual setting up of the SqlCommand to call the stored proc is pretty much the very last thing you can unit-test, by severing command creation from command execution and mocking the command executor. If this sounds like a lot of separation of concerns, it can be; remember, "there is no problem that cannot be solved by another layer of indirection, except for having too many layers of indirection". At some point you must say "enough; I just can't unit-test this, we'll do it in integration".

Other options:

• Test the stored proc using a "short-lived" DBMS instance like SQLite. It's usually easier to do this when using an ORM, but the test can then be done "in-memory" (or with a pre-set database file included with the test suite). Still not a unit test, but it can be run with a high degree of isolation (the DBMS is part of the running process, and not something you connect to remotely that may be in the middle of someone else's conflicting test suite). The downside is that changes to the stored proc can happen in production without the test reflecting the change, so you have to be disciplined about making sure the change is first made in a test environment.

• Consider upgrading to an ORM. An ORM with a Linq provider (virtually all the ones in common use have one) would allow you to define the query as a Linq statement; that statement can then be given to a mocked Repository that has an in-memory collection of test data to apply it to. You can thus verify the query is correct without even touching the DB (you should still run the query in an integration environment, to test that the Linq provider can correctly digest the query).

Answer 2

My advise is to divide and conquer. Forget about database and persistence for the time being and concentrate on testing fake implementations of your repositories or data access objects.

Now, I can't put this logic in the application code, as I'm not going to bring back a million POs just to get the dozen that apply to could apply to this store given the constraint above.

I would mock the repository that returns purchase orders. Create a mock with twenty odd purchase orders.

I was thinking, I could pass the date range to a GetValidPOs proc, and have it use those values to return the valid POs. But, what if we add another requirement to what is considered a valid PO?

Stub a call to GetValidPOs so that it calls your mock, rather than database procedure.

And how do I test this and verify it keeps working? We are not using an ORM, and it's unlikely to happen. And I can't call the DB in my test.

You need a unit-test to make sure correct data is returned from a mock.

You also need an integration test to make sure correct data is returned from a database. Integration test would require some configuration and clean-up. For example, before running integration test, seed your database by running a script. Verify that your script has worked. Query the database by calling your stored procedures. Verify that your results are correct. Clean up the database.

My other thought, is have some mocks that return valid data, others that return some bad data, and have the local repository throw an exception if bad data occurs, and test that the exception gets thrown if invalid data is returned by GetValidPOs proc (or the mock used in testing).

As I already said, you need a mock that returns at least some data that you can query.

When you query data you want to make sure that your system can handle exceptions gracefully. Therefore you mock behaviour so that it throws exceptions in certain scenarios. You then write tests to ensure that your system can handle these exceptions gracefully.

Answer 3

Just as unit testing Java or Javascript means writing unit tests using the Java language for java, and unit testing Javascript functions with Javascript, writing automated tests to drive you to write stored procedures mean the unit test library you're looking for is based on stored procedures.

Said another way, use stored procedures to test stored procedures because:

• since you're developing in the procedure language, you should have the skill to write your tests in the procedure language
• writing tests in your procedure language will increase your skill at the procedure language which in turn will help your product development
• you'll have direct access to all the tools your DB provides and you can use those tools too keep your unit tests simple as possible
• unit tests that are stored in the same DB as the procedures they are testing will be fast (closest thing to unit test like speeds) because you aren't crossing system boundaries

Just like TDD in an OO languange, you want your unit test to setup only a row or so of data to test what it needs for the procedure (minimalism, only have what your simple tests needs). The result of this is that you'll have several simple unit tests for each stored procedure. These simple tests will be easier to maintain than having complicated tests that depend on a large dataset that doesn't easily map back to what the test actually needs.