This article talks about unit testing SQL database in Azure Data Studio using the tSQLt testing framework.
This article also encourages both beginners and professionals to standardize their SQL database unit testing with tSQLt because of its native compatibility (being written in SQL) and immense flexibility regardless of the database development tool in use.
About tSQLt
If you are a stranger to tSQLt then your database development and testing journey have probably not begun yet but if you are an experienced database developer and never had an opportunity to come across tSQLt then perhaps getting tSQLt in your squad of tools could change the way you think you can test objects.
There may be many SQL database unit testing frameworks available in the market today but tSQLt clearly stands out as it remains the underlying framework behind one of the most acclaimed third-party database unit testing tools available today.
The top three less discussed features are as follows:
- Easy to setup
- Easy to use/reuse
- Easy to reset
Easy to setup
You can easily install tSQLt by simply running SQL script against your database.
Easy to use/reuse
You can straight away use tSQLt (once installed) to write unit tests just like the way you write and run SQL stored procedures.
Easy to reset
It can very easily be reset or uninstalled and this is very handy when your development database needs to be deployed to the next target environment whether it be QA/Production without the (unit) testing code.
To learn more about tSQLt please go through the following articles:
- Conventional SQL Unit Testing with tSQLt in Simple Words
- 10 Most Common SQL Unit Testing Mistakes
- Why you should cleverly name Database Objects for SQL Unit Testing
About Azure Data Studio
Azure Data Studio can be called a fast-track database development tool because of the vast extensions’ network that you can instantly use to speed up database development to become more productive in less time.
However, not many database enthusiasts are aware of the fact that you can also run unit tests against your database using the same Azure Data Studio (you used to create your database) with ease provided you do have some background knowledge and skills to do so.
That’s what we are going to learn in this article.
Database Unit Testing in Azure Data Studio: Walkthrough
Please try to draw a picture in your mind (and then on a piece of paper) about how to achieve all this before we actually, go through the steps as it is always good to compare your proposed idea with the actual solution.
Prerequisites
To fully understand the article by implementing the examples, the readers are assumed to be familiar with the following things:
- Database and database unit testing concepts
- tSQLt familiarity
- Azure Data Studio familiarity
- T-SQL scripting
- Azure Data Studio is installed
- A local or remote SQL server instance is installed
Plan of Action
In order to achieve our objective (to be able to successfully write and run unit tests against a database) we are considering the following planned steps:
- Open/Start Azure Data Studio
- Connecting to the SQL instance
- Creating a SQL database
- Installing tSQLt framework
- Testing tSQLt framework
- Creating a database object without any functioning
- Creating a unit test for the object
- Running the unit test object to see if gets failed
- Modifying the database object to add functionality
- Running the unit test object to see if gets passed
- Cleaning the SQL database from tSQLt at the end of the tests
Open/Start Azure Data Studio
Let us start by opening the Azure Data Studio:
Please ensure that you have the latest updates installed and the tool has no known issues at the time of using it.
Connecting to the SQL instance
The next step after you have opened the Azure Data Studio is to connect to your locally or remotely installed SQL server instance. Please click on the Connections section of the sidebar to open it:
Next click on the installed SQL server instance to connect to it:
A green light next to your SQL instance name indicates that you are successfully connected now.
Creating a SQL database (SampleLaptops)
Click on the Database node to expand it followed by clicking on the System Databases node. Right-click on the master database under System Databases and select the New Query option:
Please create a database called SampleLaptops with a table Laptop by writing the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
-- Create a new database called 'SampleLaptops' -- Connect to the 'master' database to run this snippet USE master GO -- Create the new database if it does not exist already IF NOT EXISTS ( SELECT [name] FROM sys.databases WHERE [name] = N'SampleLaptops' ) CREATE DATABASE SampleLaptops GO USE SampleLaptops -- Create a new table called '[Laptop]' in schema '[dbo]' -- Drop the table if it already exists IF OBJECT_ID('[dbo].[Laptop]', 'U') IS NOT NULL DROP TABLE [dbo].[Laptop] GO -- Create the table in the specified schema CREATE TABLE [dbo].[Laptop] ( [LaptopId] INT NOT NULL IDENTITY(1,1), -- Primary Key column [Name] NVARCHAR(50) NOT NULL, CONSTRAINT PK_Laptop_LaptopId PRIMARY KEY (LaptopId) ); |
Press F5 to execute the code. Refresh the Databases node to view the recently created database:
Installing tSQLt framework
Once the database is successfully created the next step is to install the tSQLt framework. Please download the latest version of tSQLt and then open tSQLt.class.sql after unzipping the folder.
Run the script (tSQLt.class.sql) against the sample database:
A successful tSQLt installation is shown above.
Testing tSQLt framework
Now test run tSQLt by running the following T-SQL script against LaptopsSample database:
|
1 2 |
-- Run all tSQLt tests EXEC tsqlt.RunAll |
The output is as follows:
Obviously, you have not written any tests yet so you would not expect any tests but this successful dry run proves that tSQLt is all well and ready to be used.
Creating a SQL database object without any functioning
Let us create a stored procedure AddLaptop without any code for the time being with the help of the following script:
|
1 2 3 4 5 6 7 8 9 10 |
-- Create the stored procedure in the specified schema without any functionality CREATE PROCEDURE dbo.AddLaptop @Name NVARCHAR(50) /*parameter name*/ AS BEGIN -- body of the stored procedure SELECT 'Todo' END GO |
The output is as follows:
The stored procedure does run but is not doing the job it is intended to do.
Creating a unit test for the object
Create a unit test class first by running the following script (against the sample database) as a first step to start unit testing your database:
|
1 2 3 4 5 |
-- Creating test class LaptopTests CREATE SCHEMA [LaptopTests] Authorization dbo GO EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='LaptopTests' |
Create a unit test (in the form of a stored procedure) as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
-- Test to check working of the object: AddLaptop CREATE PROCEDURE [LaptopTests].[test to check AddLaptop adds laptop to the table] AS -- Assemble EXEC tSQLt.FakeTable @TableName='dbo.Laptop',@Identity=1 -- Fake Customer table Create TABLE [LaptopTests].[Expected] -- Create expected table ( [LaptopId] INT NOT NULL, [Name] NVARCHAR(50) NOT NULL, ) INSERT INTO LaptopTests.Expected -- Insert data into exepcted table (LaptopId,Name) VALUES (1, N'White Laptop') -- Act EXEC dbo.AddLaptop N'White Laptop' -- Run AddLaptop procedure which adds new Laptop to Laptop table SELECT * INTO LaptopTests.Actual FROM dbo.Laptop -- Put the records from the Laptop table into the Actual table -- Assert (compare expected table with actual table results) EXEC tSQLt.AssertEqualsTable @Expected='LaptopTests.Expected',@Actual='LaptopTests.Actual' |
Refresh SampleLaptops database and see the newly created unit test under the Stored Procedures folder:
Running the unit test object to see it getting failed
It is time to run the SQL unit test which is expected to fail because we have not yet modified the object (stored procedure) to be able to perform the required job of adding a laptop table. Let’s run the tests:
|
1 2 |
-- Run tSQLt tests EXEC tSQLt.RunAll |
The output is as follows:
Modifying the database object to add functioning
Now we need to modify the stored procedure to add the desired functionality to it:
|
1 2 3 4 5 6 7 8 9 10 |
-- Alter the stored procedure to add the code ALTER PROCEDURE [dbo].[AddLaptop] @Name NVARCHAR(50) -- add stored procedure parameters here AS BEGIN -- body of the stored procedure INSERT INTO dbo.Laptop (Name) VALUES (@Name) END |
Execute the script to see the following output:
Running the unit test object
We have modified the stored procedure to add the required code so that it should be able to add new laptop records in the table now.
However, the best way to check (whether the procedure works fine or not) is to run the unit test and see if it gets passed or failed:
|
1 2 |
-- Run tSQLt tests after stored procedure has been modified to perform required functioning EXEC tSQLt.RunAll |
The results are as follows:
Cleaning the SQL database from tSQLt at the end of the tests
You can clean the database if you are done with all the tests which is a safe practice before deploying your database to the target environments.
However, please remember cleaning the database will uninstall tSQLt and remove all your unit tests from the database so you must put your database unit tests under source control to be retrieved as when required.
As this is a demo database you can also clean it to rerun the examples as many times as you like because the more you practice these examples the more you are going to be confident.
So, if you are happy to reset the database by removing the tSQLt framework and the unit tests you created then run the following command:
|
1 2 3 4 5 6 |
-- Reset tSQLt framework by deleting all the tSQLt files including any unit tests --EXEC tSQLt.Uninstall -- NOTE: -- Please uncomment the above code to wipe off (delete) all tSQLt files and unit tests -- It is highly recommended to save your unit tests before running the above command |
Conclusion
Congratulations, you have just learned to write and run unit tests against a SQL database in Azure Data Studio along with resetting the database by removing tSQLt files along with any unit tests created.
Table of contents
He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.
He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.
His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.
View all posts by Haroon Ashraf
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023
- Common use cases of SQL SELECT Distinct - February 2, 2023