Test Driven Database Development

| by Deborah Hartmann Preuss Follow 0 Followers on Oct 04, 2006. Estimated reading time: 1 minute |
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.

Rate this Article

Adoption Stage

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Yes and there's more by Max Guernsey

I agree with all of that. Everything that Scott is saying we need is absolutely necessary. ...and there's more, too. We have to go beyond just applying the practices we've developed for software and build new practices tailored to the needs of databases.

I'm writing a series of articles expanding on what we know about agility to try and make it fit into the database world.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

1 Discuss

Login to InfoQ to interact with what matters most to you.

Recover your password...


Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.


More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.


Stay up-to-date

Set up your notifications and don't miss out on content that matters to you