How to Regression Test a Relational Database

Scott Ambler, of The Elements of Java Style fame, has a nice article on database regression testing: How to Regression Test a Relational Database. Scott is a leading evangelist in the small, but growing, movement for regression testing databases.

The emphasis of the article is on low-level, unit style, regression tests. The idea is that database developers, those developing within the database (e.g. stored procedures) as well as against the database (applications reading and persisting data), can identify narrowly focused functional "slices" to configure for automated regression testing. In most cases, this involves first installing data as part of the test setup, then triggering the functionality that is the subject of the test, and finally comparing the actual results against the expected results.

The article makes a number of important points that are particularly relevant to enterprise software development:

• There's a lot of specialization amongst the groups. Typically, one team is responsible for developing the software, while a totally different team is tasked with testing and Quality Assurance.
• Frequently, the QA specialists do not have the necessary knowledge or skills to sensibly test the application and its data. But it's their job, so they have the inclination to at least try.
• Frequently, the development team has the knowledge, but often not the skills, to test the application and the data. But it's not their job so they a lot of times they are not really inclined to think too hard about the problem, which is why they never developed the skills.
• Therefore, most enterprise shops do not really do database testing right now.

I think most software professionals who have been at it for a while would agree that unit testing, whether application code or database artifacts, is a very productive technique. The idea that it's much less expensive to find and fix flaws early in the SDLC, rather than in the final stages of full integrated or end-user testing, is a well established wisdom. However, it seems to me that Scott gave short shrift to high-level, fully-integrated, functional testing. In my experience, it's not possible to have a blanket of low-level Unit tests that supply perfect test coverage with respect to the final functionality. Even with the best possible Units, in a complex application there are so many high level interactions amongst all the discrete components or modules, many of those interactions with subtle and in some cases unintended couplings and side-effects, that high-level end-stage functional testing is also necessary. In other words, the best defense is a multi-layered "defence in depth".