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.

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.

StudentInfo Table

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:

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

Output:

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

Output:

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

Output:

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

Output:

Update Student

 

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.

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

1 Comment

  1. Osenkata

    Hey good text but, old answer. DotNet Core Entity like a should using nodeJS library. I want innovator touch, I think me too.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories