Unit testing a database

Database testing is often ignored by development teams, under the assumption that testing the application tests the database sufficiently. Richard Fennell gave an interesting presentation at SQLBits recommending formal unit testing of SQL Server using TSQLUnit, part of the xUnit family.

Although most developers (hopefully!) test database objects as they develop them, the testing is usually informal and undocumented, and rarely repeated. Richard suggested that using a framework such as TSQLUnit encouraged tests that would be consistent and repeatable, i.e. all tests could be performed on a regular basis to ensure that nothing that used to work has been broken by a later change.

TSQLUnit is extremely easy to install. The zip file download contains a SQL file called tsqlunit.sql which installs 5 tables and 10 stored procedures. That's it!

Writing tests is also simple, though I've yet to try testing any very complicated objects. Tests can be grouped together into TestSuites, and fixtures (data required for the test) can be created with a corresponding setup procedure for each TestSuite. Tests are run inside transactions, so any data you insert will be cleared when the transaction rolls back.

If you have the inclination then you can write tests to check any database object, security, static data, etc.

Richard also showed how to use Data Dude for unit testing a database, but it's hard to beat the simplicity of TSQLUnit.

Thanks to Richard for a great talk.