How to Create RESTful API using Nodejs and SQL Server

Node.js is a powerful JavaScript framework built on Google Chrome’s JavaScript V8 Engine. It is used to develop I/O intensive web applications like video streaming sites, single-page applications, etc. Node.js is open source and used by thousands of developers around the world.

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

Step 1:

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','raju00@gmail.com','test')  
insert into tblProfile (name,lastname,email,password) values ('Sachin','S','Sachin00@gmail.com','test')  
insert into tblProfile (name,lastname,email,password) values ('Suraj','S','Suraj00@gmail.com','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.

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 Node.js 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)  
}  
}) 

Thank you for reading !

#node #api #rest #sql #sql-server