Friday, 25 July 2014

Database Unit Testing and Learning TSQL

Did you know that you can write database unit tests for SQL Server?

There is a framework called tSQLt that helps you to do it.

There is also a tool from Red Gate Software called SQL Test that provide a nice UI on top of it, integrated in SQL Server Management Studio.


What about using tests for learning TSQL?

I think that it is not unreasonable to design a TSQL course where each lesson is made of a set of tests that you need to make pass. This idea of learning by making tests pass is used in the nand2tetris course that I am doing with the friends of the Cambridge Programmer's Study Group.

In this post, I try to create an example using tSQLt.

The database I use is from the Training Kit: Querying Microsoft SQL Server 2012.

Installing tSQLt

First run the following SQL statements.


Execute the tSQLt.class.sql script provided by tSQLt.

Creating the test

First, you need to create a test class that we call Tests.


Then, we create a test.
The framework tSQLt allows you to create fake tables but in this case the test use the actual table HR.Employees and the actual data contained in it.


Running the test

You can run the test individually using the tSQLt.Run or alternatively you can run all the tests in the database using tSQLt.RunAll
You can also create a keyboard shortcut to run all the tests easily.

You can see that the test fail!


Make the test pass and learn

You can make the test pass simply adding the DISTINCT keyword in the select statement.  

Run the tests and they all pass.


I really like the idea of learning by making tests pass.

What do you think?


2 comments:

  1. It's really a good idea. Tipically, this king of "Learn by Doing" is called Koans. In few words, a Koan is a set of progressive exercises to help you learn something, where each exercise was expressed in terms of test.
    Some examples:
    - Learn a language: http://clojurekoans.com/
    - Learn a library: https://github.com/approvals/ApprovalTests.Net.Koans

    ReplyDelete
  2. I think it is a very effective way of learning. It makes the learning process fun!

    ReplyDelete

What you think about this post? I really appreciate your constructive feedback (positive and negative) and I am looking forward to start a discussion with you on this topic.