Test Driven Database Development

Scott Ambler thinks it's time to start adapting well accepted code quality practices to database development.  Many organization do (or should) consider data a corporate asset: if they are implementing functionality in the database via stored procedures, stored functions, or even OO code, then shouldn't a Test Driven approach provide value there just as it does on the application server?  Ambler's experience shows the answer is "yes".

Ambler's article "Test Driven Database Development" [pdf] appears on page 4 of the September issue of TASSQuarterly magazine put out by the Toronto Association of Systems and Software Quality.  Just as developers take a Test Driven Design-based approach to developing application code, Ambler says TDD can also be applied to the database schema - he calls it TDDD.

TDD is an evolutionary approach to development which combines test-first development and refactoring. With a test-first approach to development you write a test before you write just enough production code to fulfill that test. Refactoring is a disciplined way to restructure code where you make small changes to your code to improve your design, making the code easier to understand and to modify.  Ambler does acknowledge that database refactorings are conceptually more difficult than code refactorings.

Is it possible to develop a regression test suite for the database?  What kind of tests would be written against a database schema? Ambler suggests regression tests for...

• Scaffolding code (e.g. triggers or updateable views) which support refactorings,
• Database methods such as stored procedures, functions, and triggers,
• Existence of database schema elements (tables, procedures, ...)
• View definitions,
• Referential integrity (RI) rules,
• Default values for a column,
• Data invariants for a single column, and
• Data invariants involving several columns.

He goes over the basics of TDD, and steps through a simple example. The article concludes with a list of database testing tools, including some developed by the open source software (OSS) community and a few commercial tools.

Scott W. Ambler is Practice Leader Agile Development within IBM’s Methods group. He is the author of several books, including the award winning Agile Database Techniques and the recently released Refactoring Databases: Evolutionary Database Design (Addison Wesley 2006). Further information is available on his Agile Data site.