Haroon Ashraf
Test running tSQLt

SQL Database unit testing in Azure Data Studio with tSQLt

April 8, 2021 by

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:

  1. Easy to setup
  2. Easy to use/reuse
  3. 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:

  1. Conventional SQL Unit Testing with tSQLt in Simple Words
  2. 10 Most Common SQL Unit Testing Mistakes
  3. 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:

  1. Database and database unit testing concepts
  2. tSQLt familiarity
  3. Azure Data Studio familiarity
  4. T-SQL scripting
  5. Azure Data Studio is installed
  6. 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:

  1. Open/Start Azure Data Studio
  2. Connecting to the SQL instance
  3. Creating a SQL database
  4. Installing tSQLt framework
  5. Testing tSQLt framework
  6. Creating a database object without any functioning
  7. Creating a unit test for the object
  8. Running the unit test object to see if gets failed
  9. Modifying the database object to add functionality
  10. Running the unit test object to see if gets passed
  11. 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:

Starting 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:

Connecting to the SQL instance which is currently disconnected

Next click on the installed SQL server instance to connect to it:

Connected SQL instance

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:

Creating a new query against the master database

Please create a database called SampleLaptops with a table Laptop by writing the following code:

Press F5 to execute the code. Refresh the Databases node to view the recently created database:

Sample database created successfully

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:

Installing tSQLt framework

A successful tSQLt installation is shown above.

Testing tSQLt framework

Now test run tSQLt by running the following T-SQL script against LaptopsSample database:

The output is as follows:

Test running tSQLt

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:

The output is as follows:

Running the procedure without any functionality

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:

Create a unit test (in the form of a stored procedure) as follows:

Refresh SampleLaptops database and see the newly created unit test under the Stored Procedures folder:

Newly created database unit test using tSQLt

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:

The output is as follows:

The test has failed as the object is yet to be defined in terms of its working.

Modifying the database object to add functioning

Now we need to modify the stored procedure to add the desired functionality to it:

Execute the script to see the following output:

Stored procedure has been modified to add the functionality.

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:

The results are as follows:

Database unit test has passed this time

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:

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

tSQLt – A Forgotten Treasure in Database Unit Testing
Conventional SQL Unit Testing with tSQLt in Simple Words
Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing
10 Most Common SQL Unit Testing Mistakes
Why you should cleverly name Database Objects for SQL Unit Testing
Three Standard SQL Unit Tests you can write against any Stored Procedure
Creating SQL Unit Testing Utility Procedures with tSQLt
SQL Unit Testing Data Warehouse Extracts with tSQLt
The Concept of Test-Driven Data Warehouse Development (TDWD) with tSQLt
Using tSQLt for Test-Driven Data Warehouse Development (TDWD)
SQL database hotfix testing with tSQLt
Test-driven database hotfix development (TDHD) with SQL unit test based framework (tSQLt)
Three ways you can add tSQLt to your SQL database projects
SQL Database unit testing in Azure Data Studio with tSQLt

Haroon Ashraf
Development, SQL unit testing, Testing

About Haroon Ashraf

Haroon’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional. 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

182 Views