Click here to monitor SSC
  • Av rating:
  • Total votes: 18
  • Total comments: 4
Greg Lucas

Test-driven Database Development – Why Bother?

16 March 2012

Database delivery patterns & practices

STAGE 2 BUILD AUTOMATION & TESTING

Test-Driven Development is a practice that can bring many benefits, including better design, and less-buggy code, but is it relevant to database development, where the process of development tends to me much more interactive, and the culture more test-oriented? Greg reviews the support for TDD for Databases, and suggests that  it is worth giving it a try for the range of advantages it can bring to team-working.

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.

This article is part of our database delivery patterns & practices series on Simple Talk.

Find more articles for version control, automated testing, continuous integration & deployment.

Greg Lucas

Author profile:

Greg M Lucas (blog | twitter) has worked with SQL Server for over twelve years, starting with v7, including experience as a production DBA and senior developer. He is currently working as a freelance Development DBA with a focus on design and development using T-SQL, C# and SSIS.

He is particularly interested in database change management best practice and in applying agile techniques, especially TDD, within the database development arena.

Greg now has a dedicated page on his blog where you can easily find all his articles and tutorials on test-driven database development with tSQLt at http://datacentricity.net/tsqlt.

Search for other articles by Greg Lucas

Rate this article:   Avg rating: from a total of 18 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Very Good Article
Posted by: Geovanny Hernandez (not signed in)
Posted on: Friday, March 23, 2012 at 1:04 PM
Message: I'm sorry for my bad english, but I enjoy this article, this topic in my opinion is very important, but isn't the neccesary attention. I don't know book about "TDD for SQL Server", it's a good project for person as Greg Lucas.

Regards

Subject: Thanks
Posted by: gregmlucas (view profile)
Posted on: Friday, March 30, 2012 at 12:14 PM
Message: Geovanny,

Thank you for the feedback. I'm glad you enjoyed the article.

Subject: What Would Dr. Codd Say?
Posted by: Robert young (view profile)
Posted on: Tuesday, April 10, 2012 at 10:07 AM
Message: I think he'd say: "Why all this chattering about code? Specify the schema to organic normal form, and all logic is DRI. The whole point of the RM is to embed the logic in the data, which, in a code driven development, is extracted into some code block and simply re-implemented. Cut out the middleman."

That's what I think he'd say. In a declarative world, the specification *is* the test. The literature, both academic and commercial, has been nibbling around the edges of declarative "programming" for some years, glibly ignoring the elephant on the coffee table (the RM, yes?).

Subject: Database Development
Posted by: Database Development (not signed in)
Posted on: Wednesday, April 11, 2012 at 4:13 PM
Message: UTSI offers Database Development services for new or existing systems. Our team of database developers has experience with a variety of databases.
<a href="http://www.universalutsi.com">Database Development</a>

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.