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