Free Database Unit-Testing Framework for SQL Server

By:   |   Comments (7)   |   Related: > Testing


Problem

As a database developer I would like to implement an advanced and cost-effective database unit testing framework that can help my team in early bug detection and does not require context switching from SQL to any other tool/language and is suitable when all my database development work is done through SSMS (SQL Server Management Studio).  How can I accomplish this?

Solution

The solution is to get your database(s) equipped with tSQLt and start writing and running unit tests for your database(s).

Before jumping straight to the implementation I would like to answer some of the questions that might be in your mind before you finally decide to start using tSQLt Test Framework.

Why tSQLt for advanced database unit-testing?

There are number of reasons to choose tSQLt as mentioned at tsqlt.org for database unit-testing and some of them are as follows:

  1. You can write tests in T-SQL, as tSQLt itself is written in SQL so no more context switching from SQL to any other tool for testing since you can create and run tests staying within SQL. What a time saver!
  2. Tests can be grouped and different groups of tests can run independently. No need to spend time in an advanced search to find out your lonely unit test lost in piles of tests, just locate the group where it is present and you are done.
  3. Most importantly it provides true isolation for objects under test which is mandatory for a solid database unit-testing. For example if we are unit testing a stored procedure called updateBookStock() which calls another stored procedure addLog() within the main procedure (to log the information about the user who is updating the stock), then unit-test for updateBookStock() written in tSQLt is strictly going to test the main procedure without bothering to test addLog() since the purpose of the test is to go for a single unit.
  4. The tests run in Transactions there by saving the time to write extra clean up scripts as the objects will automatically be cleaned up.
  5. The tSQLt testing framework can integrate test cases with continuous integration servers there by becoming part of CI/CD work flow.
  6. This tSQLt testing framework facilitates advanced database unit testing framework such as cross-database objects testing and offer much more than traditional testing frameworks.

How is tSQLt Testing Framework Cost-Effective?

Apart from third party tools that use tSQLt as an underlying unit-testing framework, tSQLt itself is an open-source project which also means it is free to use commercially.

So, even budget constraints cannot be in your way if you wish to start using tSQLt for your database unit testing.

Obviously you may not get the fancy Graphical User Interface and ease of use with many other features that are offered by third party tools which use tSQLt as their base, but technically there is nothing that stops you using tSQLt directly just like the way you use SQL.

It also depends on your level of expertise, and again most importantly budget, so if you do not have enough budget to buy third party tools for database unit testing you can still have luxury of using tSQLt without compromising the quality and standards, but some level of expertise is expected.

About Creating and Running tSQLt Tests

Creating a tSQLt database unit-tests requires the following steps:

  1. Create a Test Class first
  2. Test Class is created by creating a Schema
  3. Schema helps you to group all your relevant test together
  4. Create a Test under the Test Class (schema)
  5. TSQLT unit-test is created as a stored procedure

Is not it easy to guess now, running a tSQLt test is as simple as running a stored procedure.

So the good news is there is nothing new apart from the SQL when it comes to tSQLt tests. You create Test Class exactly as you create a Schema, you create tests as stored procedures in the same Schema and you run tests as stored procedures.

tSQLt with Built-in TDD (Test Driven Development) Support

One thing worth mentioning here is that tSQLt framework architecture has been optimized to use for Test-Driven Development (TDD) that means you focus on requirements only and other than that no extra coding or testing.

That also means you write your tests according to the requirements that (tests) fail first and then you refactor your tests to get them through until they reflect the requirements provided things do not go out of scope.

tSQLt Implementation Scenario

In order to implement tSQLt consider the following scenario:

You want to implement tSQLt as your database unit-testing framework while all your database development, debugging, testing, deployment and refactoring is done through SSMS (SQL Server Management Studio).

Database Unit-Testing with tSQLt using SSMS

Now that your team has finally decided to use tSQLt for advanced database unit-testing you are going to start implementing the testing framework on your database(s) which are managed through SSMS.

According to tsqlt.org tSQLt Test Framework is compatible with SQL Server 2005 and all versions above it.

Using OfficeSuppliesSample Database (Mimicking Transactional Database)

We are using OfficeSuppliesSample database to mimic a transactional database which contains a simple version of customer-product-order scenario for office supplies:

  1. Customer
  2. Product
  3. Orders

Apart from the above three objects (tables) there is a procedure to add customer and view to see the orders.

Note: I have freshly created the sample database (OfficeSuppliesSample) to mimic traditional online sales systems in a simplest manner and it is possible that this database sample can also be referenced in upcoming articles/tutorials/tips so please feel free to get familiar with it.

Creating and Populating OfficeSuppliesSample Database

Open SSMS (SQL Server Management Studio) and run the following script to create OfficeSuppliesSample Database: 

-- This script creates OfficeSuppliesSample database and its related objects 
-- Note: The script is free to use and is provided AS IS without warranty of any kind
 
PRINT 'OfficeSuppliesSample database setup begins...'
IF NOT EXISTS (select * from  sys.databases where name='OfficeSuppliesSample')  
Create Database OfficeSuppliesSample;
GO
 
USE OfficeSuppliesSample;
 
-- (1) Drop tables if already exist
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Orders') DROP TABLE  Orders
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Customer') DROP TABLE  Customer
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Product') DROP TABLE  Product;
GO
 
-- (2) Create Product table
CREATE TABLE [dbo].[Product] (
    [ProductID] INT             IDENTITY (1, 1) NOT NULL,
    [Name]      VARCHAR (50)    NULL,
    [Stock]     INT             NULL,
    [Price]     DECIMAL (10, 2) NULL,
    CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC)
 
);
 
-- (3) Create Customer table 
Create Table Customer
(CustomerID INT IDENTITY(1,1),
Name VARCHAR(70),
Email VARCHAR(320),
Phone VARCHAR(50)
CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO
 
-- (4) Create Orders table 
CREATE TABLE [dbo].[Orders] (
    [OrderID]    INT             IDENTITY (1, 1) NOT NULL,
    [OrderDate]  DATETIME2 (7)   NULL,
    [CustomerId] INT             NULL,
    [ProductId]  INT             NULL,
    [Quantity]   INT             NULL,
    [TotalPrice] DECIMAL (10, 2) NULL,
    CONSTRAINT [PK_Orders_OrderID] PRIMARY KEY CLUSTERED ([OrderID] ASC), 
    CONSTRAINT [FK_Orders_Customer] FOREIGN KEY (CustomerID) REFERENCES [Customer]([CustomerID]), 
    CONSTRAINT [FK_Orders_Product] FOREIGN KEY ([ProductID]) REFERENCES [Product]([ProductID])
ON DELETE CASCADE 
ON UPDATE CASCADE 
);
GO
 
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS V WHERE V.TABLE_NAME='OrdersView') DROP VIEW [dbo].[OrdersView];
GO
 
CREATE VIEW [dbo].[OrdersView]
AS SELECT O.OrderID,OrderDate,C.Name as Customer,P.Name as Product,O.Quantity,O.TotalPrice FROM Orders O
INNER JOIN Customer C
ON C.CustomerId=O.CustomerId
INNER JOIN Product P
ON P.ProductID=O.ProductId;
GO
 
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES R WHERE R.ROUTINE_NAME='AddCustomer') 
Drop PROC AddCustomer;
GO
 
CREATE PROC AddCustomer 
@Name VARCHAR(70),
@Email VARCHAR(320),
@Phone VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
 
INSERT INTO [dbo].[Customer]
           ([Name]
           ,[Email]
           ,[Phone])
     VALUES
           (@Name
           ,@Email
           ,@Phone)
END
GO

PRINT 'OfficeSuppliesSample database and objects created successfully!'



USE OfficeSuppliesSample;

-- (1) Populating Customer table
SET IDENTITY_INSERT [dbo].[Customer] ON
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (1, N'Kirby Datta', N'kirby.datta@companytest1  .com', N'+441632960687')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (2, N'Asif Khan', N'asif.khan@companytest2  .com', N'+441632960430')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (3, N'Dionna Zabala', N'dionna.zabala@companytest3  .com', N'+441632960709')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (4, N'Anil Mehta', N'anil.mehta@companytest4  .com', N'+441632960428')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (5, N'Toney Delozier', N'toney.delozier@companytest5  .com', N'+441632960111')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (6, N'Suanne Strawder', N'suanne.strawder@companytest6  .com', N'+441632960028')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (7, N'Ardis Harrison', N'ardis.harrison@companytest7  .com', N'+441632960244')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (8, N'Edison Paiva', N'edison.paiva@companytest8  .com', N'+441632960712')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (9, N'Erinn Mettler', N'erinn.mettler@companytest9  .com', N'+441632960342')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (10, N'Ruthanne Norby', N'ruthanne.norby@companytest10 .com', N'+441632960820')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (11, N'Nakesha Onan', N'nakesha.onan@companytest11 .com', N'+441632960099')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (12, N'Karleen Kwong', N'karleen.kwong@companytest12 .com', N'+441632960145')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (13, N'Sadaf Khan', N'sadaf.khan@companytest13 .com', N'+441632960425')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (14, N'Sanora Perkinson', N'sanora.perkinson@companytest14 .com', N'+441632960560')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (15, N'Delilah Thornsberry', N'delilah.thornsberry@companytest15 .com', N'+441632960891')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (16, N'Sherrell Valentine', N'sherrell.valentine@companytest16 .com', N'+441632960340')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (17, N'Veronica Fry', N'veronica.fry@companytest17 .com', N'+441632960848')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (18, N'Christin Brault', N'christin.brault@companytest18 .com', N'+441632960313')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (19, N'Gloria Hammer', N'gloria.hammer@companytest19 .com', N'+441632960820')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (20, N'Carlton Mcbride', N'carlton.mcbride@companytest20 .com', N'+441632960873')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (21, N'Kirby Datta', N'kirby.datta@companytest1  .com', N'+441632960687')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (22, N'Asif Khan', N'asif.khan@companytest2  .com', N'+441632960430')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (23, N'Dionna Zabala', N'dionna.zabala@companytest3  .com', N'+441632960709')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (24, N'Anil Mehta', N'anil.mehta@companytest4  .com', N'+441632960428')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (25, N'Toney Delozier', N'toney.delozier@companytest5  .com', N'+441632960111')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (26, N'Suanne Strawder', N'suanne.strawder@companytest6  .com', N'+441632960028')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (27, N'Ardis Harrison', N'ardis.harrison@companytest7  .com', N'+441632960244')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (28, N'Edison Paiva', N'edison.paiva@companytest8  .com', N'+441632960712')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (29, N'Erinn Mettler', N'erinn.mettler@companytest9  .com', N'+441632960342')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (30, N'Ruthanne Norby', N'ruthanne.norby@companytest10 .com', N'+441632960820')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (31, N'Nakesha Onan', N'nakesha.onan@companytest11 .com', N'+441632960099')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (32, N'Karleen Kwong', N'karleen.kwong@companytest12 .com', N'+441632960145')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (33, N'Sadaf Khan', N'sadaf.khan@companytest13 .com', N'+441632960425')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (34, N'Sanora Perkinson', N'sanora.perkinson@companytest14 .com', N'+441632960560')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (35, N'Delilah Thornsberry', N'delilah.thornsberry@companytest15 .com', N'+441632960891')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (36, N'Sherrell Valentine', N'sherrell.valentine@companytest16 .com', N'+441632960340')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (37, N'Veronica Fry', N'veronica.fry@companytest17 .com', N'+441632960848')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (38, N'Christin Brault', N'christin.brault@companytest18 .com', N'+441632960313')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (39, N'Gloria Hammer', N'gloria.hammer@companytest19 .com', N'+441632960820')
INSERT INTO [dbo].[Customer] ([CustomerId], [Name], [Email], [Phone]) VALUES (40, N'Carlton Mcbride', N'carlton.mcbride@companytest20 .com', N'+441632960873')
SET IDENTITY_INSERT [dbo].[Customer] OFF

-- (2) Populating Product table
SET IDENTITY_INSERT [dbo].[Product] ON
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (1, N'Pen', 23, CAST(1.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (2, N'Pencil', 52, CAST(5.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (3, N'Eraser', 62, CAST(5.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (4, N'Highlighter', 77, CAST(4.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (5, N'Permanent marker', 31, CAST(3.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (6, N'Scissor', 76, CAST(2.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (7, N'Paper clip', 57, CAST(4.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (8, N'Binder clip', 154, CAST(3.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (9, N'Stapler', 87, CAST(2.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (10, N'Glue stick', 74, CAST(5.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (11, N'Envelope (letter size)', 36, CAST(2.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (12, N'Sticky notes', 198, CAST(3.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (13, N'Notepad', 88, CAST(5.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (14, N'Printer paper', 80, CAST(3.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Product] ([ProductID], [Name], [Stock], [Price]) VALUES (15, N'Tape Dispenser', 197, CAST(2.00 AS Decimal(10, 2)))
SET IDENTITY_INSERT [dbo].[Product] OFF


-- (2) Populating Orders table
SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (1, N'2017-05-24 00:00:00', 16, 7, 17, CAST(68.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (2, N'2017-05-13 00:00:00', 13, 1, 12, CAST(12.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (3, N'2017-01-20 00:00:00', 10, 8, 46, CAST(138.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (4, N'2017-05-03 00:00:00', 12, 3, 59, CAST(295.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (5, N'2017-01-16 00:00:00', 13, 7, 31, CAST(124.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (6, N'2017-03-08 00:00:00', 5, 4, 49, CAST(196.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (7, N'2017-01-25 00:00:00', 11, 2, 44, CAST(220.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (8, N'2017-02-02 00:00:00', 19, 3, 33, CAST(165.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (9, N'2017-02-02 00:00:00', 16, 7, 43, CAST(172.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (10, N'2017-03-03 00:00:00', 8, 6, 26, CAST(52.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (11, N'2017-01-07 00:00:00', 1, 10, 37, CAST(185.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (12, N'2017-04-13 00:00:00', 8, 10, 25, CAST(125.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (13, N'2017-02-27 00:00:00', 13, 4, 57, CAST(228.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (14, N'2017-02-11 00:00:00', 8, 4, 27, CAST(108.00 AS Decimal(10, 2)))
INSERT INTO [dbo].[Orders] ([OrderID], [OrderDate], [CustomerId], [ProductId], [Quantity], [TotalPrice]) VALUES (15, N'2017-02-22 00:00:00', 14, 3, 52, CAST(260.00 AS Decimal(10, 2)))
SET IDENTITY_INSERT [dbo].[Orders] OFF

PRINT 'OfficeSuppliesSample databse objects Customer, Product and Orders populated successfully!'

Test Running OfficeSuppliesSample Database

Now that setup scripts to create and populate sample database have run successfully, let’s have a look at the data.

Test Running OfficeSuppliesSample Database

Staying connected to OfficeSuppliesSample database run the following script: 

SELECT [OrderID], [OrderDate], [Customer], [Product], [Quantity], [TotalPrice] FROM OrdersView
OfficeSuppliesSample database Orders Table - Description: Pre-Populated Orders Table

The sample database is up and running and ready for tSQLt implementation.

Download tSQLt

The first step to implement tSQLt is to download it from tSQLt.ORG

tSQLt Download Button - Description: Download tSQLt version

Please see below attached tSQLt zip file downloaded quite a while ago.

Install the tSQLt Script on Main Database (OfficeSuppliesSample)

Next you have to run tSQLt.class.sql included in the tSQLt zip file on the main database (OfficeSuppliesSample).

Running tSQLt.class.sql - Description: Installing the tSQLt Script on Main Database (OfficeSuppliesSample)

The following message will show up as a result of successfully script run:

tSQLt successful installation - Description: tSQLt successful installation screenshot

Congratulations! TSQLT has been installed successfully.

Now refresh the database to see the following tSQLt objects installed on your database:

tSQLt Tables - Description: tSQLt Tables screenshot

Note: Although on tSQLt website it is mentioned that CLR needs to enabled on your Development Server but it seems that this is no more required for installing the latest version

Running tSQLt Tests (When No Tests Written)

As mentioned earlier, running tSQLt tests is simply running a stored procedure.

The procedure tSQLt.RunAll when called will run all the tSQLt Tests straight away as shown below: 

USE OfficeSuppliesSample
-- Run all tSQLt Tests
EXEC tsqlt.RunAll
Running tSQLt Tests - Description: When no tests written yet.

Since no tests are written yet, so no results to see.

Create a New Test Class (BasicTests)

Let us now create a Test Class first by writing the following code against main transactional database (OfficeSuppliesSample): 

USE OfficeSuppliesSample
 
-- Create a test class 
EXEC tSQLt.NewTestClass @ClassName = N'BasicTests'

Real Time Scenario for Object Exists Tests

Now keeping in mind the main tables of OfficeSuppliesSample what if you have been asked about the very basic requirements about the system as follows:

In your system (database) a Customer must be able to place an Order of a Product.

At the very basic level we need to make sure that the following objects must exist:

  1. Customer (table)
  2. Product (table)
  3. Orders (table)

When I say at the very basic level or micro-level we have to make sure things are fine at a unit level and that is when unit-testing comes into action.

So we are going to create three unit tests now to check if above mentioned objects exist.

Create a test to check if Customer table exists

The script to create a test to check if Customer table exists is as follows: 

USE OfficeSuppliesSample;
GO
CREATE PROCEDURE [BasicTests].[test if Customer table exsits]
AS
BEGIN
-- Check if Customer table exists or not
EXEC tSQLt.AssertObjectExists @ObjectName = N'dbo.Customer' 
END;
GO

Create a test to check if Product table exists

The script to create a test to check if Product table exists is as follows: 

USE OfficeSuppliesSample;
GO
CREATE PROCEDURE [BasicTests].[test if Product table exsits]
AS
BEGIN
-- Check if Product table exists or not
EXEC tSQLt.AssertObjectExists @ObjectName = N'dbo.Product'
END;
GO

Create a test to check if Orders table exists

The script to create a test to check if Orders table exists is as follows: 

USE OfficeSuppliesSample;
GO
CREATE PROCEDURE [BasicTests].[test if Orders table exsits]
AS
BEGIN
-- Check if Orders table exists or not
EXEC tSQLt.AssertObjectExists @ObjectName = N'dbo.Orders' 
END;
GO

Under the stored procedures of the database (OfficeSuppliesSample) in SSMS the newly created tests can be seen now:

BasicTests created - Description: This screenshot is about basic object exists tests created successfully.

Run All Tests

Now the code below will run all the tests in all the test classes (Schemas) as follows: 

EXEC tSQLt.RunALL
tSQLt Test Results - Description: This screenshot is about the tSQLt test results

All tests passed successfully so we can say that the tables that would be required if an end-user has to place an order of a product using our system are there, so the preliminary requirements are met, but more solid unit-tests are required to fulfill the requirements completely which are beyond the scope of this tip.

OfficeSuppliesSample Deployment Scenarios

The deployment scenarios for the databases that use tSQLt tests is beyond the scope of this tip, but it is worth mentioning a few things.

It is very important that you should run all your tSQLt tests prior to deploying your database on Production or Test Environment.

The tests should pass before the database gets qualified for deployment to the next phase Test/QA/Production however, tSQLt code should only be linked with the development version of the database.

Run the following script to uninstall tSQLt: 

EXEC tsqlt.Uninstall
Next Steps
  • Delete a table from OfficeSuppliesSample and see if all tests still pass or fail
  • Try few times all the steps from creating of OfficeSuppliesSample from scratch to running tSQLt Tests
  • Create a new Test Class and write a unit-test for AddCustomer stored procedure
  • Create any sample database and add tSQLt to it and create and run tests and uninstall tSQLt
  • Explore the official tSQLt website to develop basic and advanced skills of creating and running tSQLt tests
  • Please keep watching MSSQLTips.com as more tips about tSQLt including integration with modern database tools are on their way


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Haroon Ashraf Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, December 15, 2017 - 3:18:39 PM - Haroon Ashraf Back To Top (74065)

I would like to add more to my comments about enabling CLR and setting Database Trustworthy Property On.

Provided the above two things are never applied on Production Server for safety and security purposes, by no means applying them on dev/test environment  should be considered a drawback or downside of using tSQLt because of the following reasons:

  1. Set Trustworthy Database property is no more required in latest version of tSQLt
  2. Enabling CLR is fine on your local dev machine assuming it contains test (dummy) data on which GDPR (General Data Protection Regulation) does not apply
  3. The standard practice is to write and run unit-tests on development server which contains your deveopmenr database and once all the tests are run successfully and database has gone through QA/Test then the database version that gets deployed to Production server does not (and must not) contain tSQLt classes and tests. That's why CLR must not be enabled on Production Server and same is true for Trustworthy database property which makes the process smooth and dev/test/production cycle running.

Please refer to my next tip where tSQLt is implemented using SQL Server Data Tools making the process more transparent.

 

 

Friday, December 15, 2017 - 11:50:23 AM - Haroon Ashraf Back To Top (74056)

Thank you for your valued comments.

Please note that in order to install tSQLt successfully on your database, the following two things are required:

  1. CLR must be enabled
  2. Set the database TRUSTWORTHY ON

As far as setting the database TRUSTWORTHY ON settings are concerned they are not required in the latest version of tSQLt.

http://tsqlt.org/748/tsqlt-v1-0-5873-27393-release-notes/

Secondly, CLR strict security feature mentioned in your comments works for SQL Server 2016 and upward 

SQL Server 2016 and upward

USE master; 

GO 
EXEC sp_configure 'show advanced option', '1'; 
RECONFIGURE;
GO

EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;
GO

For SQL Server 2005 - SQL Server 2014 CLR can be enabled as follows:

EXEC sp_CONFIGURE 'show advanced options' , '1';
GO
RECONFIGURE;
GO
EXEC sp_CONFIGURE 'clr enabled' , '1'
GO
RECONFIGURE;
GO

Please bear in mind it is not recommended to run these scripts which enable CLR on Production Server(s) plus database unit-testing assumes that all the tests are written and run on development server which can either be sandboxed environment (SQL instance installed on local dev machine contains test data for individual developer) or dev environment (SQL instance installed on dev machine shared by all the development team and contains test data).

 

Friday, December 15, 2017 - 9:20:02 AM - Frank Monroe Back To Top (74047)

Thank you for your article. I got me going on tSQLt.

I had to do the following before running tSQLt.class.sql:

USE master; 
GO 
EXEC sp_configure 'show advanced option', '1'; 
RECONFIGURE;
GO

EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;
GO

Hope this may help someone.

 

 

 

Friday, December 1, 2017 - 6:24:27 AM - Haroon Ashraf Back To Top (73517)

Thank you for your valued feedback.

Please stay in touch with MSSQLTips.com since more tips about using advanced database unit-testing framework (tSQLt) are on their way.

 

 

 

Thursday, November 30, 2017 - 1:16:07 PM - Adeela Back To Top (73481)

 Hello dear author 

brillia effort it's so well explained and useful

 

Thursday, November 30, 2017 - 11:11:25 AM - Haroon Ashraf Back To Top (73478)

Hi Charles,

Thank you for your comments.

I did a couple of revisions before getting there as I have learned a good article does not let you lose neither interest nor focus despite the fact there is always room for improvement.

Kind Regards,

Haroon

 

Thursday, November 30, 2017 - 9:51:39 AM - Charles Rossi Back To Top (73475)

 Hi Haroon, Very nicely worded doc easy to understand and very useful. Thanks.

 














get free sql tips
agree to terms