CRUD Operations In Node.JS API Using SQL Server

In this article, we will learn about how we can perform CRUD operations in Node.js API using Microsoft SQL Server.

If you are looking for Node.JS CRUD With MongoDB/Mongoose please click here.

Prerequisites:

• Basic knowledge of Node.js
• Code editor like Visual Studio 2019

If you’re new in Node.js? You must have to follow the link given below to set up a new Node.js API in Visual Studio.

How To Create Simple Node.js API In Visual Studio

Create, Read, Update, and Delete operations in Node.js API

First, we create a StudentInfo table with given fields in the SQL Server.

Firstly, right-click the project name in the Solution Explorer and select Add -> New Folder (Database).

Now, right-click the folder name (Database) in the Solution Explorer and select Add -> New File… (dbConnection.js).

Open the dbConnection.js file and add the code in it.

exports.dbConnection = function () {
    var dbConfig = {
        user: "sa", // SQL Server Login
        password: "thecodehubs", // SQL Server Password
        server: "DESKTOP-78L71550", // SQL Server Server name
        database: "Student" // SQL Server Database name
    };
    return dbConfig;
};

Now, we have to install a package to connect SQL Server.

Install the mssql package with the NPM:

Right-click on the npm from Solution Explorer -> Install New npm Packages…

Search and install mssql package.

Open the index.js file from the routes folder and add the code in it.

'use strict';
var express = require('express');
var router = express.Router();

var sql = require("mssql");
var dbConfig = require('../Database/dbConnection');

/* Get All Students */
router.get('/', function (req, res) {
    sql.connect(dbConfig.dbConnection()).then(() => {
        return sql.query("SELECT * FROM StudentInfo;");
    }).then(result => {
        res.send(result.recordset);
    }).catch(err => {
        res.status(500).send("Something Went Wrong !!!");
    })
});

/* Add Student */
router.post('/addStudent', function (req, res) {
    sql.connect(dbConfig.dbConnection()).then(() => {
        return sql.query("INSERT INTO StudentInfo VALUES('" + req.body.Name + "', " + req.body.Age + ")");
    }).then(result => {
        res.status(200).send("Student Added Successfully.");
    }).catch(err => {
        res.status(415).send("Something Went Wrong !!!");
    })
});

/* Delete Student */
router.get('/deleteStudent/:ID', function (req, res) {
    sql.connect(dbConfig.dbConnection()).then(() => {
        return sql.query("DELETE FROM StudentInfo WHERE ID = " + req.params.ID);
    }).then(result => {
        res.status(200).send("Student Deleted Successfully.");
    }).catch(err => {
        res.status(500).send("Something Went Wrong !!!");
    })
});

/* Edit Student */
router.get('/editStudent/:ID', function (req, res) {
    sql.connect(dbConfig.dbConnection()).then(() => {
        return sql.query("SELECT * FROM StudentInfo WHERE ID = " + req.params.ID);
    }).then(result => {
        res.send(result.recordset);
    }).catch(err => {
        res.status(500).send("Something Went Wrong !!!");
    })
});

/* Update Student */
router.post('/updateStudent', function (req, res) {
    sql.connect(dbConfig.dbConnection()).then(() => {
        return sql.query("UPDATE StudentInfo SET [Name] = '" + req.body.Name + "', Age = " + req.body.Age + " WHERE ID = " + req.body.ID);
    }).then(result => {
        res.status(200).send("Student Updated Successfully.");
    }).catch(err => {
        res.status(500).send("Something Went Wrong !!!");
    })
});

module.exports = router;

• Get All Students, returns all the records from the StudentInfo table.

Output:

• Add Student, inserts a new record in the StudentInfo table.

Output:

• Delete Student, removes a record from the StudentInfo table.

Output:

• Edit Student, returns a single record from the StudentInfo table based on ID, later it can be used to update Student Info.

Output:

• Update Student, updates a selected record in the StudentInfo table.

Output:

You can download code from here. Please give your valuable feedback and if you have any questions or issues about this article, please let me know. Vision Infotech is a leading backend development company in India and the United States, assisting clients in hire backend developers in India and achieving their business objectives quickly. Our Dedicated Programmers for Hire have a lot of experience designing complicated functional protocols. They also have extensive hands-on knowledge with the most up-to-date technology, allowing you to create bespoke, secure, and robust backend layers for your website and applications.

Also, check File Upload With Node.JS API And Angular 9