Cory Foy on Database Unit Testing

Posted by James Vastbinder on Oct 21, 2007

Sections

Process & Practices,

Development

Topics

.NET ,

Agile ,

Artifacts & Tools ,

Unit Testing

Tags

SQL Server 2008 ,

Database

Unit testing at the database level has always been tough.  Until recently most developers working at the database level simply avoided unit testing their logic and stored procedures.  In this InfoQ article, Cory Foy demonstrates how to implement Test Driven Development within the database for SQL Server.

With the techniques outlined in his article and Visual Studio for Database Professionals, developers:

• know where the true representation of the database is stored
• are enabled to modify the schema and forward changes to be modified to a DBA for approval
• are able to make changes to the schema while understanding the impact of their changes
• provided the ability to unit test stored procedures including how to generate test data

There is one caveat, currently this process only works with SQL Server databases.  

1. Back to top

   Excellent article - too bad abot MS pricing

   by Steve Macdonald

   This article shows that MS has come a long way with their tools. The problem is that unless you work for a well-heeled organization MS has priced the Team stuff out of reach for most people.

   Reply

2. Back to top

   Support for Oracle and Multiple vers of SQLServer Using one set of Scripts?

   by Sean Creedon

   If it supported Oracle and you could share the unit tests, then this would be a real plus for people that write products that need to run on multiple databases.
   
   If not Oracle is it easy to test across multiple versions of SQL Server?

   Reply

3. Back to top

   Re: Support for Oracle

   by Cory Foy

   Hi Sean,
   
   From what I understand the team is working to create a version which will use a provider model so that other DBMS can be used.
   
   Out of the box DBPro works on both 2000 and 2005. The scripts should be able to run against any datasource - the offline schema and tests are in separate projects.
   
   Cory

   Reply

4. Back to top

   Intro another DB unit testing tool

   by harry har

   Good article!
   
   Now I am glad to share one database unit testing tool. It is named as AnyDbTest (www.anydbtest.com). AnyDbTest Express edition is free of charge.
   
   I know some guys are using DbUnit or other xUnit test framework to perform DB unit testing. I also tried to use them in my projects, but at last I had to give up these tools because I must keep focus on the database rather than switch to be as application developer.
   
   AnyDbTest is declarative style testing tool. We will not need to program at all. What we do is to express what we want to test, rather than how to test. We only need to configure an Xml test file to tell AnyDbTest what we want to test. Rather than painstakingly writing test code for xUnit test framework. So AnyDbTest is the right choice for DBA or DB developers.
   
   Features specific to AnyDbTest:
   *Writing test case with Xml, rather than Java/C++/C#/VB test case code.
   *Many kinds of assertion supported, such as StrictEqual, SetEqual, IsSupersetOf, Overlaps, and RecordCountEqual etc.
   *Allows using Excel spreadsheet/Xml as the source of the data for the tests.
   *Supports Sandbox test model, if test will be done in sandbox, all database operations will be rolled back meaning any changes will be undone.
   *Unique cross-different-type-database testing, which means target and reference result set can come from two databases, even one is SQL Server, another is Oracle.

   Reply

• Older >