Build REST API with Node.js using SQL Server

This blog shows how to build a REST API with Node.js using SQL Server

Introduction

Node.js is a great platform to build REST API. It’s open-source and absolutely free. Additionally, Node.js is built on JavaScript on the server. Please follow the steps to create this Node.js API

Configure Node.js API, Initial Steps:

Install express module with the command line npm install express

Install MS SQL with the command line npm install mssql

Step 1: Below is the screenshot for Index.js file is the route js.

Create a new folder called connection to place a DB.js

Create a new folder called controller to place the name of the Js where you want to create APIs

node_modules contains related files for NodeJs where it is the default folder in Node.js after initializing NPM packages.

Index.js

Import express module in index.js file. It contains many features for web & mobile application of body-parser and CORS etc.

var express = require('express');
var bodyParser = require("body-parser");
var app = express();
app.use(function (req, res, next) {
res.header("Access-Control-Allow-Origin", "*");
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
res.header('Access-Control-Allow-Methods', 'PUT, POST, GET, DELETE, OPTIONS');
next();
});
app.use(bodyParser.urlencoded(extended: true ));
const ProfileData = require('./controller/profile')
app.use('/', ProfileData)
app.listen(5000, function () {
console.log('Server is running..');
});

Run the NodeJs with the command node index

Step 2: Create DB.js file inside of  the connection folder and configure DB connection. An example is in the below screen shot.

Always use ConnectionPool for good practice. With ConnectionPool, there is no need to create open and close for MSSQL connection for each request and response.

// DB.js config for your database
const sql = require('mssql')
const config = {
user: 'abcd',
password: 'abcd',
server: "localhost",
database: "profile"
}
const poolPromise = new sql.ConnectionPool(config)
.connect()
.then(pool => {
console.log('Connected to MSSQL')
return pool
})
.catch(err => console.log('Database Connection Failed! Bad Config: ', err))
module.exports = {
sql, poolPromise
}

Step 3: create a new table called tblProfile in SQL server.

Insert the below data into the tblProfile table

insert into tblProfile (name,lastname,email,password) values ('Raju','B','[email protected]','test')
insert into tblProfile (name,lastname,email,password) values ('Sachin','S','[email protected]','test')
insert into tblProfile (name,lastname,email,password) values ('Suraj','S','[email protected]','test')

Create a Store Procedure for insert profile

CREATE procedure [dbo].[InsertProfile]
(
@name varchar(50),
@lastname varchar(50),
@email varchar(50),
@password varchar(50)
)
AS
BEGIN
insert into tblProfile (name,lastname,email,password) values( @name, @lastname, @email, @password)
END

Create Store Procedure for Delete profile

CREATE procedure [dbo].[DeleteProfile]
(
@id int
)
AS
BEGIN
delete from tblProfile where id=@id
END

Step 4: create profile.js file inside of controller folder

Import DB.js files to the inside of profile.js file to connect DB activity.

Note: NodeJs framework is a single-threaded and asynchronous. For the best practice, we have to use async/await. For more information. visit this URL: https://www.geeksforgeeks.org/using-async-await-in-node-js/.

Below is the Get API for the profile

We can fetch data using SQL query.

const express = require('express')
const router = express.Router()
const { poolPromise } = require('../connection/db')
const sql = require('mssql')
router.get('/ApiProfileGet', async (req, res) => {
try {
const pool = await poolPromise
const result = await pool.request()
.query('select * from tblProfile',function(err, profileset){
if (err)
{
console.log(err)
}
else {
var send_data = profileset.recordset;
res.json(send_data);
}
})
} catch (err) {
res.status(500)
res.send(err.message)
}
})

Run the NodeJs apps by using command Node index

Below is the Post API for the profile.

Below is the example with Postman

router.post('/ApiProfilePost', async (req, res) => {
try {
const pool = await poolPromise
const result = await pool.request()
.input("name", sql.VarChar(50), req.body.name)
.input("lastname", sql.VarChar(50), req.body.lastname)
.input("email", sql.VarChar(50), req.body.email)
.input("password", sql.VarChar(50), req.body.password)
.execute("InsertProfile").then(function (recordSet) {
res.status(200).json({ status: "Success" })
})
} catch (err) {
res.status(400).json({ message: "invalid" })
res.send(err.message)
}
})

Below is the Delete API for profile

 

Example:

router.delete('/ApiProfileDelete', async (req, res) => {
try {
const pool = await poolPromise
const result = await pool.request()
.input("id", sql.Int, req.body.id)
.execute("DeleteProfile").then(function (err, recordSet) {
res.status(200).json({ status: "Success" })
})
} catch (err) {
res.status(500)
res.send(err.message)
}
})