It’s been a busy few weeks as I’ve been preparing to present at the 2012 ACCU Conference in Oxford. This has become somewhat of a pilgrimage and for the second year running I’ve been accepted as one of the speakers; this time to talk about using TDD to develop databases. This was an excuse to piece together some of the ideas that I’ve been blogging about along with the experiences gained with my current client to present one way that can use tests to drive both the development and design of a database.
The talk started by walking you through the pre-requisites needed to embrace TDD, such as your own development sandbox and looked at the primary testing mechanism[*], i.e. unit testing SQL. I also spent a fair bit of time covering the notion of a database’s public interface as I personally feel that the modern RDBMS gives you the tools to build an abstraction layer to help decouple the client from the data model to grant freedom of implementation[#].
Naturally I explained the principles of TDD and did a 20 minute live coding session to show how I would use them to develop a reporting style stored procedure. For this I used my own SS-Unit testing framework and SQL Server Express; switching rapidly between test code & production code. With the main body of the talk in the bag I turned to the opportunities that TDD (although really unit testing) opens up such as continuous integration, continuous deployment and schema refactoring.
The PowerPoint slides are available on my web site here.
Just for the record this was the abstract that I submitted...
The modern day RDBMS is a complex product that offers so much more than just data persistence. The SQL language, with its vendor specific variants such as T-SQL, provides the ability to develop code in various forms to read, transform & write that data efficiently. This code requires constant testing right from its inception through its various incarnations until it is finally retired.
TDD is a technique that puts writing those tests at the front of the development process, whether that be because you’re writing new code or changing existing code. The knock-on effect of this approach is that your client-based perspective opens your eyes to potential variations in the implementation, and that is where the second ‘D’ in TDD turns from Development into Design. With a solid automated test suite and Continuous Integration under your belt too the world of refactoring opens itself up so that your database design can safely evolve.
This session looks at applying the same principles and disciplines used in other areas of system development to tame the ever increasing complexity that has arisen from the maturity of the RDBMS.
[*] TDD does not prescribe any particular type of testing but I believe that most people associate TDD with unit testing as it probably forms the lions share. I did point out that one of the benefits of a top-design and implementation is that you can start development with a system test and drill down from there.
[#] ORM tools like Entity Framework actually push you in the other direction which may be exactly what you want. But as I’ve said before, Enterprise culture has a habit of routing around nicely designed service layers and attempting to go straight to the heart of the data.