Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Unit Testing TSQL

Is there anybody out there writing unit tests for their TSQL stored procedures, triggers, functions ... etc.

I've recently started making database and restores and installs part of our automated Cruise Control build process. Now I'm thinking about taking it to the next level where we do the install, then run through a list of stored procedure tests etc.

I was going to just roll my own using MsBuild Extensions to invoke the tests. However I'm aware of http://www.tsqltest.org/ and http://tsqlunit.sourceforge.net/. I'm also aware that TFS has sql testing.

I just wanted to see what people in the real world are doing and if they have any suggestions.

Thanks

like image 996
Ryu Avatar asked Apr 02 '09 17:04

Ryu


People also ask

What is unit testing in SQL?

SQL unit testing is a testing method which allows us to test the smallest, atomic programmable part of a database object. SQL unit testing plays a key role in the modern database development cycle because it allows us to test individual parts of the database objects work as expected.

How is SQL used in testing?

Various SQL statements are used to develop the Test cases. The most common SQL statement, which is used to perform DB testing, is the Select statement. Apart from this, various DDL, DML, DCL statements can also be used. Example − Create, Insert, Select, Update, etc.


2 Answers

The critical parts:

  • Make it automated and integrated with your build/test (so you have a green or red from your build)
  • Make it easy to add a new test
  • Keep your tests up-to-date

Advanced:

  • test failure conditions in your code
  • make sure your tests clean up after themselves (TSqlTest's example scripts use @beforeCount and @afterCount variables to validate the clean-up)
like image 96
Rob Garrison Avatar answered Oct 20 '22 02:10

Rob Garrison


Stored procedures. I generally include test queries in comments in the SP header, and record correct results and query times. This still leaves it as a manual exercise, however.)

Functions. Again, put SQL statements in the header with the same info.

Triggers. I avoid them for a number of reasons, one of them being that they are so hard to test and debug for so little benefit compared to putting the same logic in another tier. It's like asking how to test for Referential Integrity.

This is still a manual process, however. But since I think one should intentionally design SQL artifacts to be totally uncoupled (e.g. no SPs calling SPs, same with functions, and another strike against triggers IMHO) it's relatively less complex.

like image 22
dkretz Avatar answered Oct 20 '22 03:10

dkretz