patternMajor
Unit testing of stored procedures
Viewed 0 times
storedprocedurestestingunit
Problem
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
And then comes the problem of constantly changing data. In the case of an
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?
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#21065, answer score: 31
Revisions (0)
No revisions yet.