Building a simple REST API with NodeJS , Express and SQL server

How to Perform CRUD operation in Node JS using MS SQL Server

Table of Contents

• Initialize the project
• Create SQL Server configuration and connection
• App router
• App controller
• Starting the application
• Create/Add records
• Read all records
• Update data
• Delete records

Initialize the project

Create the package.json file for your project using below command.

c:\> cd sqlapi-demo
c:\sqlapi-demo> npm init

Then create project structure as give in this picture

CD to root project and run

npm install

example

How to execute code

To test application run below command on root folder

npm start

Example

App router

https://raw.githubusercontent.com/erpragatisingh/node-api/main/sample-CRUD-SQL/routes/route.js

const express = require(‘express’);const controller = require(‘../controller/controller’) const router = express.Router();router.get(‘/api/getAllData’, controller.getAllData);router.post(‘/api/addNewData’ , controller.addNewData);router.put(‘/api/updateData’,controller.updateData);router.delete(‘/api/deleteData’ , controller.deleteData); module.exports = router;

controller

const { sql,poolPromise } = require(‘../database/db’)const fs = require(‘fs’);
var rawdata = fs.readFileSync(‘./query/queries.json’);
var queries = JSON.parse(rawdata); 
class MainController { 
async getAllData(req , res){ 
try {
 const pool = await poolPromise const result = await pool.request() .query(queries.getAllData) res.json(result.recordset) 
} catch (error) 
{ res.status(500) res.send(error.message)
 } 
}
 async addNewData(req , res){ 
try {
 if(req.body.name != null && req.body.email != null && req.body.password != null) 
{
 const pool = await poolPromise const result = await pool.request() .input(‘user’,sql.VarChar , req.body.name) .input(‘email’,sql.VarChar , req.body.email) .input(‘password’,sql.VarChar,req.body.password) .query(queries.addNewUser) res.json(result) } else { res.send(‘Please fill all the details!’) } } catch (error) { res.status(500) res.send(error.message) } } async updateData(req , res){ try { if(req.body.password != null && req.body.name != null) { const pool = await poolPromise const result = await pool.request() .input(‘newPassword’,sql.VarChar , req.body.password) .input(‘userName’,sql.VarChar,req.body.name) .query(queries.updateUserDetails) res.json(result) } else { res.send(‘All fields are required!’) } } catch (error) { res.status(500) res.send(error.message) } } async deleteData(req , res){ try { if(req.body.name != null ) { const pool = await poolPromise const result = await pool.request() .input(‘userName’,sql.VarChar,req.body.name) .query(queries.deleteUser) res.json(result) } 
else
 { res.send(‘Please fill all the details!’) } } 
catch (error) 
{
 res.status(500) res.send(error.message) } }}
 const controller = new MainController()module.exports = controller;

https://raw.githubusercontent.com/erpragatisingh/node-api/main/sample-CRUD-SQL/controller/controller.js

Database

const sql = require(‘mssql/msnodesqlv8’) const config = { database: ‘api_demo’, server: ‘DESKTOP-51JVSDN’, driver: ‘msnodesqlv8’, options: { trustedConnection: true }} 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}

https://raw.githubusercontent.com/erpragatisingh/node-api/main/sample-CRUD-SQL/database/db.js

Query

{ “getAllData” : “SELECT TOP(5) * FROM [dbo].[user_info]”, “addNewUser” :”INSERT INTO [dbo].[user_info] (name,email,password) VALUES (@user,@email,@password) “, “deleteUser” : “DELETE FROM [dbo].[user_info] WHERE name = @userName”, “updateUserDetails” : “UPDATE [dbo].[user_info] SET password = @newPassword WHERE name = @userName”}

https://raw.githubusercontent.com/erpragatisingh/node-api/main/sample-CRUD-SQL/query/queries.json

Complete Salutation can be available at https://github.com/erpragatisingh/node-api/tree/main/sample-CRUD-SQL