Post
Ask Question

Unit Test (2) --- TSQLT

Note: this article is published on 07/17/2023

This series of articles will discuss Unit Test

This article will discuss the application of Unit Tests to SQL Server database development.

Introduction

This is the content of this article.

  • A - Introduction
  • B - Why Unit Test
  • C - Why tSQLt as Unit Test Tool
  • D - tSQLt Instruction List
  • E - tSQLt Installation and Implementation
  • F - tSQLt TUTORIAL

Why Unit Test

In the previous article, Unit Test (1) --- Basic, we have discussed "Why Unit Test", in E. We will give an example to demo the benefits of unit tests intuitively.

Suppose we have an application it has 10,000 independent testing points. Assume we have a QA team 4 people. They can test 100 independent testing points every working day for each person. Therefore, each person needs to test in 25 days, 5 weeks, to finish 2,500 testing points, 4 people will finish all 10,000 testing points within 25 days, 5 weeks.

i.e., for each app delivery, after the app is delivered to the QA team, they 4 people need 5 weeks to test the whole app. Say, after the testing, whether the app has been delivered to the client or not, a bug is found and needs to be fixed. The fix is simple: just one day or several days of work. However, when delivering the app to QA again, because we do not know how the small bug affects the whole app, typically, we need to do another full cycle testing, sometimes called regression testing. We need to check the 10,000 independent checking points one by one. i.e., we need the QA team to work another 5 weeks.

Consider if we have unit tests, i.e., at each testing point, we have implemented a unit test, assuming we have a DevOps pipeline to run the unit test in each code change automatically. Then when any changes are checked in, the DevOps pipeline will run automatically to trigger the unit test. The test runs in seconds or minutes, at most hours. If they are good, all testing points have been passed. i.e., we can make the regression test done in a very short time, almost immediately

This is what the unit test will do.  

C - Why tSQLt for SQL server

We realize, in fact, we have already used some kind of unit test in our SQL stored procedure, such as 

When any given condition is met, it will trigger to print an error message. For example, when the following condition is met:

This error message will be triggered:

This kind of Unit Testing method does satisfy our unit test goal to some degree. However, it has some shortages: such as,

  • Not standardized, different developers might develop a suite of different unit testing methods in some style;
  • When a different developer writes the unit testing independently, they might repeat the work the other has done.
  • When they write the unit tests themselves, they might need to write some stubs or mocks (simulated functions to support the unit testing) by themselves.

.We can consider that when we implemented the Dispose Design Pattern, we could implement an IDisposable interface and follow the general pattern, or we can design the dispose pattern by the way of ourselves. If we use the pattern, that will be easier and universal.

In general, we can say because tSQLt allows you to implement unit tests in T-SQL, you don't have to switch between various tools to create the code and unit tests. tSQLt also provides the following features to make it easier to create and manage unit tests:

  • Tests are automatically run within transactions – this keeps tests independent and reduces any cleanup work
  • Tests can be grouped together within a schema – allowing you to organize your tests and use common setup methods
  • Output can be generated in plain text or XML – making it easier to integrate with a continuous integration tool
  • The framework gives you the ability to fake tables and views and create stored procedure spies – allowing you to isolate the code being tested.

Even if you have a large code base and no existing unit tests, you can start introducing tests now to make your database code more robust to change.

D - tSQLt Instruction List

E - tSQLt Installation and Implementation

1, Downloading: the zip file, 

After unzip:

2, Execute the PrepareServer.sql file (in the unzip files).

PrepareServer.sql automatically enables CLR and installs a server certificate that allows the installation of the tSQLt CLR.

 3, Execute the  Example.sql file. (in the unzip files). --- Create an example database:

 4, Execute the tSQLt.RunAll command.--- run Run the example tests

Notice that one test is failing. Click here to continue on to fix the failing test and add your own tests.

5, Execute the tSQLt.class.sql script  (in the unzip files) to Install into the development database:

F - tSQLt TUTORIAL

1, Downloading: the demo zip file, 

After unzip:

2, Install: FinancialApp schema for Tutorial from demoApp.sql file (in demo zip)

Three Tables:

Three Stored Procedures:

Two Functions:

3, Install FinancialApp.UserInfo Table for Tutorial from demoExample3AlterTable.sql file (in demo zip)

One Table:

4, Install FinancialApp.Test Schema for Tutorial from demoTest.sql file (in demo zip)

Six Stored Procedures:

5, Run the FinancialApp.Test by demoExec.sql file (in demo zip)

Output:

 

References:


Similar Articles