MSSQL testing is not that difficult
When I started testing Microsoft SQL Server database in my previous role, there was not much documentation available in general. So I had to spend extra hours to understand and build an end to end test.
Purpose of this article is to share the knowledge on how to write automation tests for MSSQL Server. Instead of writing queries in sql developer or Azure data studio the entire end to end test can be automated using node.js
I would use Programmatic Mocha Test approach, setup is available in the previous article.
git clone https://github.com/puneetpunj/programmatic-mocha-framework.git
Just for the purpose of this artcile I will spin up a new database to get connection details and show the actual test, otherwise if you already have SQL Server running, feel free to skip this section and use your own connection details.
Setup SQL Server Database
Prerequisite — Docker
Let’s start by creating
cd programmatic-mocha-framework// create new file and name it as dockerfile
Copy this code in dockerfile
Execute below docker commands and your SQL Server database will be up and running. For more details check out Microsoft documentation
docker build -t sqlserverimage .docker run -d -p 60666:1433 --name mssql1 sqlserverimagedocker ps
Add Data To Database
Download Azure Data Studio and use below connection details
Login Type: SQL Login
Once loggged in successfully, execute below queries in order to add some data rows in your database. Refer to Microsoft docs for more details.
// Start by creating new database and name it as TutorialDBUSE master
IF NOT EXISTS (
WHERE name = N'TutorialDB'
CREATE DATABASE [TutorialDB];
IF SERVERPROPERTY('ProductVersion') > '12'
ALTER DATABASE [TutorialDB] SET QUERY_STORE=ON;
// Create new table in above database --- Create a new table called 'Customers' in schema 'dbo'
-- Drop the table if it already exists
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
DROP TABLE dbo.Customers;
-- Create the table in the specified schema
CREATE TABLE dbo.Customers
CustomerId int NOT NULL PRIMARY KEY, -- primary key column
Name nvarchar(50) NOT NULL,
Location nvarchar(50) NOT NULL,
Email nvarchar(50) NOT NULL
// Add rows -- Insert rows into table 'Customers'
INSERT INTO dbo.Customers
([CustomerId], [Name], [Location], [Email])
( 1, N'Orlando', N'Australia', N''),
( 2, N'Keith', N'India', Nfirstname.lastname@example.org'),
( 3, N'Donna', N'Germany', Nemail@example.com'),
( 4, N'Janet', N'United States', Nfirstname.lastname@example.org')
Once data is inserted successfully execute
select * from dbo.Customers
Build a library file to interact with Database
First of all, add mssql package by running
npm i mssql
Create new file in
lib directory and name it as
db-connect.js. Typically, you won’t hardcode credentials in this file, they must be defined in .env or parameter store or secrets manager but for the sake of this example I have added them in this file itself. Copy below code to db-connect.js
Add New Test File
One of the biggest advantage of the programmatic approach is the clear segregation of tests based on initiative/program and run them independently. Way to achieve this is by adding new file
programmatic directory. Once file is created, copy below code:
Update Execution File to Include New Test
execute-tests.js would look like as below:
Execution command will remain same
npm run test
Thanks for reading, code is available on git here.