At a very first glance, bringing data from a database looks cool, but the more we get ourselves involved with it the more complex it seems. Connection instances, queries, stored procedures, pooling, multiple recordsets all of these will knock on our door sooner or later; instead of avoiding them let's face our destiny. Although it's not that horrifying, but a little bit of professionalism to handle these objectives will bring wonders to our application.

In this article we will be solely focused on how to make connection form NodeJS to MSSQL and perform a CRUD. Let us jump into setting up our SQL Server database and then NodeJS API project.

Setting SQL Server Database

Create a database (i.e. foo_db) from Microsoft SQL Server Management Studio and then run the following script to create a table named Employee with some data.

/****** Object:  Table [dbo].[Employee]    Script Date: 9/2/2020 2:15:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Code] [varchar](50) NOT NULL,
	[Name] [varchar](50) NULL,
	[Job] [varchar](50) NULL,
	[Salary] [int] NULL,
	[Department] [varchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee] ON 

INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (1, N'CT7207', N'Bently Smith', N'Manager', 40000, N'Operations')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (2, N'CT7210', N'Isla Morris', N'Director', 80000, N'Operations')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (3, N'CT7202', N'Allen Green', N'Salesman', 15000, N'Sales')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (4, N'CT7208', N'Xavier Campbell', N'Analyst', 50000, N'Research')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (5, N'CT7209', N'Ethan Kumar', N'Analyst', 50000, N'Research')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (6, N'CT7201', N'John Marshal', N'Clerk', 20000, N'Accounting')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (7, N'CT7205', N'Ethan Almaas', N'Salesman', 15000, N'Sales')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (8, N'CT7211', N'Natalie Robinson', N'Salesman', 15000, N'Sales')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (9, N'CT7212', N'Earl Rose', N'Salesman', 15000, N'Sales')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (10, N'CT7206', N'Ilija Seifert', N'Clerk', 20000, N'Accounting')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (11, N'CT7204', N'Annette Burke', N'Clerk', 20000, N'Accounting')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (12, N'CT7203', N'Fernando Gordon', N'Salesman', 15000, N'Sales')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (13, N'CT7213', N'Catherine Foster', N'Salesman', 15000, N'Sales')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (14, N'CT7207', N'Josh', N'Manager', 40000, N'Operations')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (15, N'CT7207', N'Paul', N'Manager', 40000, N'Operations')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (18, N'CT7207', N'Jim Wong', N'Manager', 40000, N'Operations')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (21, N'CT7207', N'Harry Potter', N'Manager', 30000, N'Operations')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (23, N'CT7207', N'Tony Stark', N'Manager', 20000, N'Operations')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (24, N'CT7207', N'Steve Rogers', N'Manager', 20000, N'Operations')
INSERT [dbo].[Employee] ([Id], [Code], [Name], [Job], [Salary], [Department]) VALUES (25, N'CT7547', N'Tom Holland', N'Manager', 20000, N'Operations')
SET IDENTITY_INSERT [dbo].[Employee] OFF

Additionally, I have added a scripts folder which contains foo_db schema. One can grab it, paste and run to create the tables with data.

Initializing Project

After creating an empty folder and running “npm init” command; proceed with executing the following commands to install necessary packages. Check out more about mssql package.

npm i express
npm i mssql
npm i dotenv
npm i -D nodemon

The project structure will be as follows:

node-mssql-api
|-- api
	|-- employees.js
|-- definitions
	|-- employees.rest
|-- node_modules
|-- .env
|-- index.js
|-- package.json

Create .env file with following values:

# Application
NODE_ENV=development
PORT=3000

# Database
SQL_DRIVER=SQL Server
SQL_SERVER=your_sql_server
SQL_DATABASE=your_database
SQL_UID=your_uid
SQL_PWD=your_password

With the help of dotenv package we can access the .env file's environment variables.

Starting NodeJS API

Start your API server with following boilerplate code:

const express = require('express')
const dotenv = require('dotenv')

const app = express();

dotenv.config();

app.use(express.json());
app.use(express.urlencoded({ extended: false }));

app.get('/', (req, res) => {
    res.send('
?? Teaming up with NodeJS and SQL Server
');
});

app.use('/api/employees', require('./api/employees'));

app.listen(process.env.PORT, () => {
    console.log(`Server started running on ${process.env.PORT} for ${process.env.NODE_ENV}`);
});

Run your project with following command and hit http://localhost:3000/ on your browser to check your project is up and running:

nodemon index

Setting up routes

In the api/employees.js file, first we will configure our employees endpoint routes.

Create employee API endpoints:

const express = require('express')

const router = express.Router();

router.get('/:id', async (req, res) => {
	// get single employee
});
router.get('/', async (req, res) => {
	// get all employee
});
router.post('/', async (req, res) => {
	// create employee
});
router.put('/:id', async (req, res) => {
	// update employee
});
router.delete('/:id', async (req, res) => {
	// delete employee
});

module.exports = router;

Setting up MSSQL

Now let us address the elephant in the house and switch our focus to make a connection to our foo_db database.

Import the package for mssql:

const mssql = require('mssql')

Create SQL Server configuration object and a connection pool instance of mssql package:

const config = {
    driver: process.env.SQL_DRIVER,
    server: process.env.SQL_SERVER,
    database: process.env.SQL_DATABASE,
    user: process.env.SQL_UID,
    password: process.env.SQL_PWD,
    options: {
        encrypt: false,
        enableArithAbort: false
    },
};
const pool = new mssql.ConnectionPool(config);

Here we are getting the configuration values from .env environment file using process.env. Moving on to perform CRUD operations.

Performing CRUD operations

In every CRUD operations, we have to do some common method calls in the following sequence:

• Connect to database, calling the connect() method.
• Get a request object, calling the request() method.
• Executing the SQL query, calling the query() method based on request object.

If we are having a single connection pool instance, then the connect () method won't open a new connection to the database, instead it will make use of existing open connection. This is pool connection is handled by mssql package itself.

Read All Employees

router.get('/', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request().query(`SELECT * FROM Employee ORDER BY Id DESC`);
        const employees = result.recordset;

        res.json(employees);
    } catch (error) {
        res.status(500).json(error);
    }
});

The result returned form query() method is as follows:

{
	recordsets: [
		[...]
	],
	recordset: [...],
	output: {},
    rowsAffected: [23]
};

recordsets: The recordsets property returns set of arrays which holds the data obtained after the query execution. In case of executing stored procedures we run multiple queries and obtain multiple result sets. In that scenario the recordsets will hold multiple arrays of data. In the above read all operation, we are executing a single query so will get single recordset and the recordsets property will have only one single array of employees.

recordset: The recordset property is an array which returns data in a single array obtained from executing the query.

output: Is an object which holds the output values returned from the executed query if there are output parameters passed in the request.

rowsAffected: Returns the number of rows affected by the query; in case of a SELECT operation, it will show the number of rows returned from executing the SELECT query, for INSERT, UPDATE, DELETE it will show the number of rows affected by it. This property is an array of numbers, where the first element will show the number of affected rows for the first recordsets and so on.

Read Single Employee

router.get('/:id', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request()
            .input('Id', req.params.id)
            .query(`SELECT * FROM Employee WHERE Id = @Id`);
        const employee = result.recordset.length ? result.recordset[0] : null;

        if (employee) {
            res.json(employee);
        } else {
            res.status(404).json({
                message: 'Record not found'
            });
        }
    } catch (error) {
        res.status(500).json(error);
    }
});

Here we are passing the id parameter to the query with the help of input() method and can access it inside query using '@'. Make sure to use input() method to pass the values of query as it will prevent from SQL injection attack. The mssql module has built-in SQL injection protection as stated MSSQL SQL Injection.

In case when our query will return a single row, then we can directly make use of 0th index on recordset.

Create Employee

router.post('/', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request()
            .input('Code', req.body.Code)
            .input('Salary', req.body.Salary)
            .input('Job', req.body.Job)
            .input('Department', req.body.Department)
            .input('Name', req.body.Name)
            .query(`
                INSERT INTO Employee (Code, Salary, Job, Department, Name) 
                OUTPUT inserted.Id 
                VALUES (@Code, @Salary, @Job, @Department, @Name);
            `);
        const employee = req.body;
        employee.Id = result.recordset[0].Id;
        res.json(employee);
    } catch (error) {
        res.status(500).json(error);
    }
});

In order to check a successful INSERT operation we can make use of rowsAffected property of the result object, and if the rowsAffected is 0 then no new row is added. Also, we can obtain the created employee id from the INSERT query using 'OUTPUT inserted.Id'. Now the recordset will only have one object with property Id of the newly added row.

Update Employee

router.put('/:id', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request()
            .input('Id', req.params.id)
            .query(`SELECT * FROM Employee WHERE Id = @Id`);

        let employee = result.recordset.length ? result.recordset[0] : null;
        if (employee) {
            await pool.request()
                .input('Id', req.params.id)
                .input('Code', req.body.Code)
                .input('Salary', req.body.Salary)
                .input('Job', req.body.Job)
                .input('Department', req.body.Department)
                .input('Name', req.body.Name)
                .query(`
                    UPDATE Employee SET
                        Code = @Code, 
                        Salary = @Salary, 
                        Job = @Job, 
                        Department = @Department, 
                        Name = @Name
                    WHERE Id = @Id;
                `);

            employee = { ...employee, ...req.body };

            res.json(employee);
        } else {
            res.status(404).json({
                message: 'Record not found'
            });
        }
    } catch (error) {
        res.status(500).json(error);
    }
});

Here we first check whether the employee which needs to be updated is exist or not. If not, then will return 404, otherwise will run the UPDATE query in the same manner we have done for INSERT. Lastly, will return the updated employee object as the response.

Delete Employee

router.delete('/:id', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request()
            .input('Id', req.params.id)
            .query(`SELECT * FROM Employee WHERE Id = @Id`);

        let employee = result.recordset.length ? result.recordset[0] : null;
        if (employee) {
            await pool.request()
                .input('Id', req.params.id)
                .query(`DELETE FROM Employee WHERE Id = @Id;`);
            res.json(employee);
        } else {
            res.status(404).json({
                message: 'Record not found'
            });
        }
    } catch (error) {
        res.status(500).json(error);
    }
});

Here also we first check whether the employee which needs to be deleted is exist or not. If not, then will return 404, otherwise will run the DELETE query.

Entire code of employees.js file is shown below:

api/employees.js

const express = require('express')
const mssql = require('mssql')

const router = express.Router();

const config = {
    driver: process.env.SQL_DRIVER,
    server: process.env.SQL_SERVER,
    database: process.env.SQL_DATABASE,
    user: process.env.SQL_UID,
    password: process.env.SQL_PWD,
    options: {
        encrypt: false,
        enableArithAbort: false
    },
};
const pool = new mssql.ConnectionPool(config);

router.get('/:id', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request()
            .input('Id', req.params.id)
            .query(`SELECT * FROM Employee WHERE Id = @Id`);
        const employee = result.recordset.length ? result.recordset[0] : null;

        if (employee) {
            res.json(employee);
        } else {
            res.status(404).json({
                message: 'Record not found'
            });
        }
    } catch (error) {
        res.status(500).json(error);
    }
});
router.get('/', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request().query(`SELECT * FROM Employee ORDER BY Id DESC`);
        const employees = result.recordset;
        console.log(employees.length);

        res.json(employees);
    } catch (error) {
        res.status(500).json(error);
    }
});
router.post('/', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request()
            .input('Code', req.body.Code)
            .input('Salary', req.body.Salary)
            .input('Job', req.body.Job)
            .input('Department', req.body.Department)
            .input('Name', req.body.Name)
            .query(`
                INSERT INTO Employee (Code, Salary, Job, Department, Name) 
                OUTPUT inserted.Id 
                VALUES (@Code, @Salary, @Job, @Department, @Name);
            `);
        const employee = req.body;
        employee.Id = result.recordset[0].Id;
        console.log(result);
        res.json(employee);
    } catch (error) {
        res.status(500).json(error);
    }
});
router.put('/:id', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request()
            .input('Id', req.params.id)
            .query(`SELECT * FROM Employee WHERE Id = @Id`);

        let employee = result.recordset.length ? result.recordset[0] : null;
        if (employee) {
            await pool.request()
                .input('Id', req.params.id)
                .input('Code', req.body.Code)
                .input('Salary', req.body.Salary)
                .input('Job', req.body.Job)
                .input('Department', req.body.Department)
                .input('Name', req.body.Name)
                .query(`
                    UPDATE Employee SET
                        Code = @Code, 
                        Salary = @Salary, 
                        Job = @Job, 
                        Department = @Department, 
                        Name = @Name
                    WHERE Id = @Id;
                `);

            employee = { ...employee, ...req.body };

            res.json(employee);
        } else {
            res.status(404).json({
                message: 'Record not found'
            });
        }
    } catch (error) {
        res.status(500).json(error);
    }
});
router.delete('/:id', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request()
            .input('Id', req.params.id)
            .query(`SELECT * FROM Employee WHERE Id = @Id`);

        let employee = result.recordset.length ? result.recordset[0] : null;
        if (employee) {
            await pool.request()
                .input('Id', req.params.id)
                .query(`DELETE FROM Employee WHERE Id = @Id;`);
            res.json(employee);
        } else {
            res.status(404).json({
                message: 'Record not found'
            });
        }
    } catch (error) {
        res.status(500).json(error);
    }
});

module.exports = router;

This completes our CRUD operations for employee endpoint. Will proceed with calling these endpoints with the help of amazing extension REST Client of VS Code.

Calling the API using VS Code REST Client

As explained in the project structure we have added a folder named "definitions" in which we have a file named "employees.rest", this file will hold REST calls to our employee endpoints. The REST Client is pretty straight forward to understand, and like any other REST tool, it allows to create API call definitions. Our API calls will be as follows:

definitions/employees.rest

#### Employee API

#### Get All Employees
GET http://localhost:3000/api/employees

#### Get Employee
GET http://localhost:3000/api/employees/1

#### Add Employee
POST http://localhost:3000/api/employees
content-type: application/json

{
    "Id": 0,
    "Code": "CT8000",
    "Name": "Bucky Barns",
    "Job": "Manager",
    "Salary": 30000,
    "Department": "Operations"
}

#### Update Employee
PUT http://localhost:3000/api/employees/30
content-type: application/json

{
    "Id": 30,
    "Code": "CT9000",
    "Name": "Bucky Barns",
    "Job": "Manager",
    "Salary": 30000,
    "Department": "Operations"
}

#### Delete Employee
DELETE http://localhost:3000/api/employees/30