Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ


Choose your language

InfoQ Homepage News Cory Foy on Database Unit Testing

Cory Foy on Database Unit Testing


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


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.

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

Community comments

  • Excellent article - too bad abot MS pricing

    by Steve Macdonald,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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

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