Unit testing of stored procedures

Question

I've been considering this for quite a long time now.

The basic question is: how to unit test stored procedures?

I see that I can set up unit tests relatively easily for functions in the classic sense (I mean they get zero or more arguments and return a value). But if I consider a real-life example of a seemingly simple procedure inserting a row somewhere, with a few triggers doing this and that before or after the insert, even defining the boundaries of a 'unit' is quite difficult. Should I test only the INSERT itself? That's fairly straightforward, I think–with relatively low value. Should I test the result of the whole chain of events? Apart from the question whether this is a unit test or not, designing a suitable test can be quite a strenuous job with lots of additional question marks arising on the way.

And then comes the problem of constantly changing data. In the case of an UPDATE affecting more than just a few rows, every potentially affected row must be included somehow in the test cases. Further difficulties with DELETEs and so on and so on.

So how do you unit test your stored procedures? Is there a treshold in complexity where it gets completely hopeless? What resources are needed for maintenance?

EDIT One more small question, based on AlexKuznetsov's answer: Or is there a treshold under which it is completely useless?

Answer 1

We've been doing this for almost five years, and we think that explicitly testing modifications is definitely doable, but it is quite slow. Besides, we cannot easily run such tests concurrently from several connections, unless we use separate databases. Instead, we should test modfications implicitly - we use them to build up at least some of the test data, and verify that our selects return expected results.

I've written an article entitled Close Those Loopholes: Lessons learned from Unit Testing T-SQL, as well as some blog posts

Regarding your question "Is there a treshold in complexity where it gets completely hopeless?", complex modules need tests much more than simple ones.

To simplify maintenance, we generate expected results, and we store them in separate files - that makes a huge difference.

Answer 2

Yes, you should test the whole chain of events as a unit. So, in your example with a procedure that inserts into a table and causes several triggers to fire, you should write unit tests that evaluate the procedure for various inputs. Each unit test should pass or fail depending on whether it returns the correct values, changes the state of tables correctly, creates the correct email, and even sends the correct network packets if it is designed to do such a thing. In short every effect the unit has should be verified.

You are correct, that designing unit tests takes some work, but most of that work has to be done to manually test the unit, you are just saving the work required to test the unit so that when a change is made in the future the testing can be just as thorough and significantly easier.

Changing data does make testing more difficult, but it doesn’t make testing less important and actually increases the value of unit testing as most the difficulties only have to be thought through once rather than every time a change is made to the unit. Saved datasets, inserts/updates/deletes that are part of the setup/teardown, and narrowly scoped operation can all be used to make this easier. Since the question isn’t database specific, details will vary.

There is no complexity threshold on the high or low end that should stop you from testing or unit testing. Consider these questions:

1. Do you always write bug free code?
2. Are small units always bug free?
3. Is it OK for a large unit to have a bug?
4. How many bugs does it take to cause a disaster?

Suppose you start a new job and are tasked with making an optimization to a small function used in many places. The entire application was written and maintained by an employee that no one even remembers. The units have documentation describing normal expected behavior, but little else. Which of these would you rather find?

• No unit tests anywhere in the application. After making the change you can do some manual testing against the unit itself to make sure it still returns the expected values in the documentation. You can then roll it out to production, cross your fingers and hope that it works (after all, you always write bug free code and an optimization in one unit could never effect another) or spend a massive amount of time learning how the entire application works so you can manually test every unit directly or indirectly effected.
• Unit tests throughout the application that run automatically daily or on demand. They check not just normal input values and their expected response, but also abnormal values and the expected exceptions that get raised. You make your change and run the unit test suite for the application immediately seeing that three other units no longer return expected results. Two of them are benign, so you tweak the unit tests to account for that. The third requires another slight tweak and a small new unit test. After make the changes the entire suite of tests succeeds and you roll out the change with confidence.

Answer 3

For PostgreSQL, check out pgTAP:

pgTAP is a suite of database functions that make it easy to write TAP-emitting unit tests in psql scripts or xUnit-style test functions.

Answer 4

If you would prefer your testing of the stored procedures to be done entirely on SQL, then have a look at http://tsqlt.org/

It is compatible with MS SQL 2005 SP2 and greater, and the advantage is the developers don't need to know C# or another language to implement the tests.

There is also facilities to do mock tables and views to help you achieve a re-runnable test suite.

Answer 5

In practice, "unit testing" in SQL has little value when compared to the value of actual, functional tests that run the stored procedure or function in the way intended and then examine the result. Often, this means running the tests on near production-like data and systems.

Unit tests can easily gloss over a problem that is in the logic. For very simple procedures and functions that have very little logic in them, and are not cross-database, unit tests are fine. The gold-standard should be functional tests. I'd be hard pressed to write, for example, a unit test for a job that handles complex orchestration tests, such as synchronizing user security across multiple databases.

At the end of the day, unit tests create A LOT of code - code that must be maintained - and don't offer the same value as a function test that actually tests for the correct outcome of a procedure or series of procedures.

Writing functional tests also creates code that must be maintained, but also provides code that most developers and DBAs understand. No offense intended, but t-sqlt is not that intuitive to use and has shortcomings in cross-database procedures - shortcomings that result in even more code being generated (code that must be maintained).

Less code is better. Code that is more impactful is better. Unit tests are popular in the dev world, and have bled into the SQL development world too, but ultimately if your SQL procedure to add a user to a database is breaking, it's pretty easy to have written a unit test for it that won't reveal the breakage.

Rigorous, functional tests that put a procedure through its paces are the best, integration tests for jobs that handle business critical data are de rigueur.