Any advice on testing sprocs that feed into an API?
I'm currently testing a Web API that delivers JSON content that I am validating the results for with defined JSON files. This works well. We know what the request is, can develop a query that should give us similar results, and verify against those results within a JSON structure. All well and good.
This started a discussion on whether or not we test the underlying Sprocs that feed into the API. In general the API does some dynamic query building that basically queries a set of stored procedures that run against a schema I will call CustData. We do have tests that check for data insertion, as well as the stored procedures that populate all the tables in the CustData schema.
I can see where its necessary to test the stored procedures that return the data from CustData, but since we know what's in the table already and the WebAPI basically delivers the data the point was made that we would be testing the stored procedures twice. Especially since the stored procedures list is intended to grow, its a question of do we spend the time on the stored procedures and the Web APIs, doing double testing (which is ok in my opinion if we can get the time for it) or do we treat the middle like a black box and just deal with the data insertion and the Web APIs (which also meets some task requirements).
Anyone in a similar environment able to throw some examples on this scenario?
Without knowing how complicated your stored procedures and Web APIs are, it is hard to know whether my environment is similar to yours.
Your tests serve two purposes: to find bugs, and to narrow down where the bug might be. If someone changes the Web APIs to use the stored procedures in a new way, and the change is buggy, it may be unclear whether the problem is in the stored procedures or the Web API. You might ask yourself how you would go about narrowing down a problem in that situation. If you believe your current tests would suffice, you should not spend time writing more. If you aren't sure, you might want to unit-test the stored procedures separately.
Your choices are not black and white. You could unit-test stored procedures that are complicated or non-obvious. You could choose to not unt-test stored procedures that are simple and obvious.