Sign up ×

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?

share|improve this question

4 Answers 4

up vote 21 down vote accepted

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.

share|improve this answer
+1000 Excellent article. –  Jon Seigel Jul 18 '12 at 15:56
Very good article indeed. Need more time to read it thoroughly. For now it is clear that i) very simple procedures don't need testing and that ii) implementing and maintaining such a test suite requires significant efforts (possibly comparable to implementing the proc itself). –  dezso Jul 18 '12 at 20:05

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.

share|improve this answer
Seen that, thanks. Does anybody have experiencie with it? –  dezso Jul 19 '12 at 7:16
Yes, quite a few folks nowadays. Subscribe to the mail list if you have questions. –  theory Jan 15 '14 at 17:13

If you would prefer your testing of the stored procedures to be done entirely on SQL, then have a look at

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.

share|improve this answer

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.
share|improve this answer
Firstly, thank you for your answer - I didn't expect any further advancement on this question... Secondly, I have to admit that you are right about the simple operations: even a two-column INSERT can produce a bug. If it is written so that the column order can be matched against the arguments then it may be OK, but then you are right, again: it is probably better keeping the whole app under the testing regime. –  dezso Feb 22 '13 at 21:13
@dezso It's a great question and a concept that needs a lot more exposure in the Database world. –  Leigh Riffel Feb 25 '13 at 13:19

Your Answer


