Recently I started working on a project that used a lot of stored procedures for business logic. The SPs were written by DB rockers, but they were quickly becoming long, unwieldy and difficult to maintain. None-the-less, we had several requirements to be implemented that required either modifying these Stored procs, or even writing new ones (to fit into the existing reporting infrastructure).
In the search of ways to make this less painful, I came across T.S.T. – a unit testing framework for T-SQL. This provides a powerful assertion framework for T-SQL stored procs and functions, and provides other features such as grouping tests into suites and ability to setup and teardown. The tests are also automatically rolled back after recording their results, which means that your test data does not change the state of the database.
A few moments after playing with this, and I decided to do a test-run on production code. So far things look good. I am going to refer you to the site itself for more details of this framework, but these are some of my learnings –
- Stored Procedures are harder to test than OO code – there is no mocking/stubbing of stored procedure calls to reduce dependencies. Most of your tests start looking like integration tests.
- BDD works well with SPs especially if they house business or reporting logic. Writing Given-When-Then statements really helps when planning the tests
- Keep the tests manageable by breaking them into test steps (if you have done BDD you will this will come naturally to you). For each statement in the “Given.. When.. Then..” create a helper proc that does the actual work of either creating the test data or running the asserts (the actual call corresponding to the “when” can still remain in the test since that is generally a one line call).
- Reuse these test steps as much as possible, similar to how you would reuse Gherkin statements in BDD
- Keep a separate schema for your test procs or keep them in a separate database. Do not mix production code and testing code
- Try to keep your tests focussed on a single aspect.
Testing the Stored procs is hard, but the payoff is good. You can start modifying existing code base with a lot more confidence rather than having to rewrite everything in the application layer (and in the process losing some performance advantages as well). Well written SPs can be tested easily, and difficulty in testing an SP can signal that there may be some refactoring needed. Never-the-less, it should never be impossible to write good tests to cover your database code.