TDD Principles for Database Development
Success in object-oriented agile development is currently being achieved with a technique known as test driven development (TDD). In database development however, TDD practices are not wide-spread and development teams struggle with applying the TDD principles to the SQL language. This is a problem, because it leads to poorly tested code. In turn, not having the appropriate test cases, makes it difficult to improve your existing database design. Not implementing TDD practices in the database, overtime, leads to a decaying architecture and can hinder the evolution of the overall application.
This presentation will discuss ways to enable software developers to use TDD to develop in databases. Beginning with a case study of a successful project that employed database TDD, presenters will then provide examples on specific TDD techniques that borrow from similar techniques in OO development. In addition to an overview of writing simple database test cases, you will see demonstrations on the effective use of techniques like mock procedures, fake tables and stub records. Guidance will also be provided on situations in which these techniques are inappropriate.
After a recent presentation on this topic that we held at the Philly Area .NET Code Camp, we updated and summarized our techniques in the following article: www.sqlity.net. Our presentation will cover examples similar to those in this article. The 90-minute presentation was met with great response in terms of the questions asked and the depth of the discussion. It led us to believe that this topic warrants further discussion and exposure in the agile and database communities.
We first began exploring database TDD on an Extract, Load, Transform (ETL) project 4 years ago. We faced both technical challenges and skepticism. The complex table relations made it difficult to create and maintain test data. A continually evolving schema initially made tests fragile. Very large, tightly coupled stored procedures made isolating and testing functionality difficult. The initial technical challenges fueled the skepticism. However, creative adapting of OO unit test concepts to the database proved that DB-TDD could be successful even in a highly complex project.
After the team became proficient with the methodologies, their velocity steadily increased. Analysts are now specifying requirements for an application that is almost entirely developed in SQL stored procedures. Developers, utilizing the techniques we’ll discuss, can isolate individual behaviors for unit testing. As a result, performance enhancements could be safely made without changing the functionality; the team discussed requirements using concrete, testable examples; and refactoring and maintaining the code became less risky.
Update: We moved this from the “Breaking Acts” to the “Committing to Quality” stage, based on the comments. We are looking for further feedback regarding the stage.
The techniques we will discuss include:
Fake Tables - a technique for isolating the data and the structures needed to test a behavior, even when the database contains constraints, relationships, and existing data
Stub Records - a method for reducing the fragility of test cases as the schema evolves
Mock Procedures - the ability to check the parameters passed to a procedure or function and specify the outputs for specific test cases
For each technique, we will explain how they borrow from OO methodology and lead an open discussion about their implications and when they may be inappropriate.
We will close with some thoughts and questions about the future direction of DB-TDD.
Introduction (2 min)
Case study (5 min)
Introductory example (8 min)
Fake tables
- What are fake tables? (8 min)
- Examples (5 min)
- Pros/Cons, Implications (8 min)
Stub Records
- What are stub records? (8 min)
- Examples (5 min)
- Pros/Cons, Implications (8 min)
Mock Procedures
- What are mock procedures? (8 min)
- Examples (5 min)
- Pros/Cons, Implications (8 min)
The future of DB TDD (8 min)