Testing Microsoft SQL Server with JavaScript

MSSQL testing is not that difficult

Puneet Punj
Apr 1 · 3 min read
Photo by Johny vino on Unsplash

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 mssql package.

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

Let’s start by creating dockerfile in programmatic-mocha-framework

cd programmatic-mocha-framework// create new file and name it as dockerfile
touch dockerfile

Copy this code in dockerfile

dockerfile for SQL Server

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
Confirmation of Docker Container running SQL Server

Add Data To Database

Server: localhost,60666
Login Type: SQL Login
User: sa
Password: Stupidpassword123!

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
GO
IF NOT EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TutorialDB'
)
CREATE DATABASE [TutorialDB];
GO
IF SERVERPROPERTY('ProductVersion') > '12'
ALTER DATABASE [TutorialDB] SET QUERY_STORE=ON;
GO
// 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;
GO
-- 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
);
GO
// Add rows -- Insert rows into table 'Customers'
INSERT INTO dbo.Customers
([CustomerId], [Name], [Location], [Email])
VALUES
( 1, N'Orlando', N'Australia', N''),
( 2, N'Keith', N'India', N'keith0@adventure-works.com'),
( 3, N'Donna', N'Germany', N'donna0@adventure-works.com'),
( 4, N'Janet', N'United States', N'janet1@adventure-works.com')
GO

Once data is inserted successfully execute select * from dbo.Customers

Select query returns all 4 records

Build a library file to interact with Database

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

db-connect.js

Add New Test File

db-test.js

Update Execution File to Include New Test

execute-tests.js

Execute Tests

Output of mssql execution

Thanks for reading, code is available on git here.

JavaScript In Plain English

New articles every day.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store