Build REST API with Node.js using SQL Server

Gururaj Jewargi

5 Comments

Join the conversation! Your thoughts help the community grow.

JOHN T5y ago
Hi Gururaj, don't know if this thread is still open. I am just starting with React and working out on data updates SQL server, etc, and this looks like a nice thin way to get a REST API between web pages and a db. I had success and also a little trouble with your commands. I am already set up with webpack and using the webpack localhost:8080 for just webpage-starter (non-db) updates ok, but I don't know if webpack-starter will mix with what you're doing here. If you anyone can clear up the below questions that would be excellent, thanks. this looks like it installed ok (0 vulnerabilities anyway), not sure: PS C:\REACT_SETUP\webpack-starter> npm install mssql npm WARN [email protected] requires a peer of react@^16.0.0 || ^17.0.0 but none is installed. You must install peer dependencies yourself. npm WARN [email protected] requires a peer of [email protected] but none is installed. You must install peer dependencies yourself. npm WARN optional SKIPPING OPTIONAL DEPENDENCY: [email protected] (node_modules\fsevents): npm WARN notsup SKIPPING OPTIONAL DEPENDENCY: Unsupported platform for [email protected]: wanted {"os":"darwin","arch":"any"} (current: {"os":"win32","arch":"x64"}) npm WARN optional SKIPPING OPTIONAL DEPENDENCY: [email protected] (node_modules\jest-haste-map\node_modules\fsevents): npm WARN notsup SKIPPING OPTIONAL DEPENDENCY: Unsupported platform for [email protected]: wanted {"os":"darwin","arch":"any"} (current: {"os":"win32","arch":"x64"}) + [email protected] added 32 packages from 127 contributors and audited 1578 packages in 137.541s 133 packages are looking for funding run `npm fund` for details found 0 vulnerabilities -------------------- Import express module in index.js file - did this ok with your code ---------------- Run the NodeJs with the command node index -- don't understand this, tried running this, got error: PS C:\REACT_SETUP\webpack-starter> node index internal/modules/cjs/loader.js:883 throw err; ^ at Function.Module._resolveFilename (internal/modules/cjs/loader.js:880:15) at Function.Module._load (internal/modules/cjs/loader.js:725:27) at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:72:12) at internal/main/run_main_module.js:17:47 { code: 'MODULE_NOT_FOUND', requireStack: [] } tried this, got a bunch of help info, invalid command evidently: PS C:\REACT_SETUP\webpack-starter> npm index cheers, jt
Amit Mohanty6y ago
Nice article
Ashutosh Gupta7y ago
Good one..
Bikesh Srivastava7y ago
Well explained.
Sourav Kumar Das7y ago
Nice article.

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)
}
})