My primary skill-set is T-SQL development, but I also code in C# and I have been lucky enough to be able to use those .NET skills on several projects where test-driven development (TDD) has been an integral part of the development lifecycle. I’ve grown to appreciate the benefits of following this practice but it has always frustrated me that many database developers can’t or won’t utilise TDD when writing T-SQL.

In this article, we will look at why that might be, and try to overcome some of the objections to implementing test-driven database development. After a brief explanation of what TDD is (and what it is not), we will explore some of the challenges and benefits of this practice.

What is test-driven development?

Sometimes also called test-first development, Test-driven Development (TDD) is the practice of writing re-runnable unit tests for each new feature or bug fix before writing or changing any production code. This is an iterative process that follows these steps:

• Write a test to prove that the feature behaves as expected and then run the test, it is expected to fail.
• Write just enough production code to pass the test
• Re-run the new feature test and also all the other unit tests to prove that the new feature now works as expected and equally important, hasn’t broken anything else.

This cycle is repeated, for any other likely or edge-case inputs to the original method and for each new feature. Periodically, both production and test codebases are reviewed to remove any duplication, to simplify the code or to improve performance by means of a process known as refactoring.

In .NET tests are usually written either using a third party unit testing framework or within the Integrated Development Environment (IDE) if it has built-in support for unit testing (e.g. Visual Studio Team Edition). These tools facilitate running tests singly, in batches or running all tests at once, outputting information about failing tests.

What is test-driven development not?

First, TDD is not a panacea to all ills. Following this practice will not result in completely bug free code. A developer may write tests for all the use cases he can think but once the application goes into UAT or production, other edge cases may emerge that will highlight new bugs. However, TDD will definitely reduce the incidence of such bugs.

TDD is also NOT integration testing. Nor is it about system, functional or load testing or user acceptance testing or any other type of testing other than low level unit testing.

What are the benefits of test-driven development?

Early Bug Notification

All good developers test their code but, in the database world, this often consists of manual tests or one-off scripts. Using TDD you build up, over time, a suite of automated tests that you or any other developer can re-run at will. This is valuable when a change you’ve made unexpectedly introduces a bug somewhere else.

I am sure you’ve all been there, I know I have. You spend ages debugging and trying to recreate a fault only to find that the underlying cause is a change made months previously. And, as always with bugs, it can take hours or even days to find the break and usually only minutes to write the fix. So when you make a code change, the earlier you find out that it is a breaking change, the fresher that code will be in your memory and the faster you can fix it.

Better Designed, Cleaner and More Extensible Code

I have found two ways that TDD improves the quality of my code.

Firstly, it makes me think more carefully about how the code will be used and how it is to interact with other modules; resulting in better design decisions and more easily maintained code. By writing tests first, I find myself writing smaller, discrete code modules that have just a single responsibility rather than monolithic procedures with multiple responsibilities. This makes the code simpler for me and others to understand and also makes it easier to extend in future.

Secondly, it forces me to focus on only writing production code to pass tests which are themselves based on a concrete user story or requirement. So I actually write less production code, but work faster. As developers, we are all guilty of adding in features that we think might be useful at some point in the future – but which are often never actually used. This leads to bloated, harder to maintain code and wasted time. TDD leads to cleaner code, maximising the amount of work not done.

Confidence to Refactor

How many times have you picked up a piece of code and thought, if I was to re-write that now I could do it “this” way and it would be so much faster/cleaner/simpler? Without a suite of automated unit tests, no matter how careful you are, there is a risk associated with that refactoring that you will introduce bugs. So you decide to leave well alone, as does the developer after you and the next one and so on. The result can be an overly complex and fragile codebase that everyone is afraid to touch in case they break something.

Having a set of automated tests gives you more confidence that if you refactor the code, you are more likely to be warned of any breaks you’ve introduced and can fix them before release. Ultimately, using TDD properly should result in faster, more extensible code with fewer bugs that can be updated or altered with minimal risk.

Good for Team Work

Over time, unit tests serve as a form of documentation, showing how a developer intended or expected a particular feature to be used. Many agile teams subscribe to the principle that no single developer “owns” any part of the code; all the code is owned by the whole team and anyone with the knowledge can make changes as necessary.

Good unit test coverage helps developers who are unfamiliar with the code to understand it: This gives them the confidence to make changes. This means that delivery deadlines don’t slip while the team waits for a particular developer to make time or return from holiday before completing some critical piece of work.

The fact that it is easier for any team-member to pick up and work on the code also aids knowledge-sharing, thereby making the team more effective overall. This also helps to protect the business from the loss of key staff members.

Good for Developers

What would you rather spend your time doing? Do you want to be wading through thousands of lines of code, half of which may be redundant, spending days or hours debugging, trying to find difficult to pin down faults? Or would you rather be designing and developing new features that you can have confidence in and that are easy to extend and tune in the future? Yes, TDD will mean you spend more time writing tests, but you will also spend a lot less time debugging. You will write cleaner, less buggy code that you can be proud of and you will get more job satisfaction.

Exposure to agile techniques like TDD also often appears in job descriptions so if you take the time to learn how to do this for T-SQL development, you will have a highly desirable skill that will help your CV stand out from the crowd.

What are the objections to implementing TDD?

In my experience, even in the most agile teams, TDD for databases, if implemented at all, is often the last thing to be put into action. If you’re in a team that does do TDD for databases, I apologise – I obviously haven’t worked with you yet…

Let’s face it; following TDD for databases is hard. These are some of the more common objections I hear and the reasons why I don’t think that they are real issues.

No time or desire to learn yet another language or tool set just to be able to write tests

There are several tools that are available now which will allow you to write and run all your tests in T-SQL and/or with a few mouse clicks. tSQLt is a free, open source, unit testing framework for SQL Server 2005+ (and is my personal favourite). SQL Test, a graphical unit test add-in for SQL Server Management Studio, will run tSQLt by default. So you don’t need to learn any new languages and you can write your tests in the same IDE that you write your production code. Visual Studio Team Edition (DBPro) is another option if your team already has the Developer edition licences.

Writing all those tests takes too long and slows down development

It is true that you will spend more time on upfront design and development due to writing unit tests, possibly up to 50% longer – especially at first. But you will spend a lot less time debugging in the long run and will also end up writing less production code because you will only write code to pass tests and those tests will be driven by established requirements.

It takes too long to set up reference data

With regard to reference data, there is no getting away from the need to do this; we are unit testing a database after all. Both DBPro and tSQLt offer support for setting up test data although I prefer the tSQLt approach as you can choose to effectively ignore specific foreign keys when setting up test data or writing tests. This means that the only tables you need to pre-populate with test data are those referenced by each test – no additional reference data need be added. There are also some established approaches you can use such as the Test Data Builder pattern to save time. I’ve blogged about how to adapt this pattern to T-SQL here.

Writing tests for all the legacy code doesn’t make business sense

I agree entirely with this statement. I am not proposing that you immediately down tools and start writing hundreds of regression tests. The only time you will need to think about writing tests for existing code is either when a bug is identified in that code or when you are changing that code to support a new feature request. Doing this does make sound business sense, as working software is a primary measure of success and unit tests help you achieve that.

Lack of knowledge or experience in test-first development

This comes down to experience and a willingness to learn. Fortunately there are some good resources out there to help you get to grips with database TDD in general and tSQLt specifically. The official tSQLt User Guide is a good place to start and I have written a series of detailed posts about how to use tSQLt to write a number of different types of unit test.

In Summary

Actually, I think that one of the biggest challenges to implementing TDD is getting started in the first place, especially on an existing project with lots of pre-existing code. Whilst in the long term, using TDD will reduce the amount of time you need to spend debugging, when you first start practicing TDD, you will be slower. You will need extra time to write the tests, whilst struggling to come to terms with a new programming paradigm and you will still need to spend time debugging legacy code.

But bite the bullet and you will reap the rewards. Start using TDD on a smaller database project, or within just one team as a pilot. You will need management buy-in when you first start, it will slow you down to begin with, but the team will get back up to speed as they grow their knowledge and experience and as the quality of released code improves.

100% test coverage might be a nice goal in an ideal world but is not realistic when you first start out. It is better to cover the 20% of your code that is most likely to cause problems than to not write tests at all just because you can’t achieve 100% coverage immediately. Once you’ve got started and have some momentum, then you can then look at increasing coverage.

I am not sure whether productivity actually increases as a result of using TDD for database development. Some teams claim they are more productive, some say they’re a little slower. Personally, my productivity is about the same. However, code produced using TDD will be of a measurably higher quality so it is worth the pain.

Using TDD, your code will be less fragile, better designed and with fewer bugs. It will be easier to share knowledge and workload within and between teams and individual developers will be better motivated with higher levels of job satisfaction. Ultimately, we are in the business of delivering working software and TDD helps us do exactly that.

In the next article, I will look at tSQLt in more detail and explain how it meets the needs of the database developer who is looking for the same functionality offered by more mature .NET testing frameworks.