Cory Foy on Database Unit Testing

| by James Vastbinder Follow 0 Followers on Oct 21, 2007. Estimated reading time: less than one minute |

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.  

Rate this Article

Adoption Stage

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

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.

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?

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.


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 ( 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.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

4 Discuss