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.

  1. var express = require('express');
  2. var bodyParser = require("body-parser");
  3. var app = express();
  4. app.use(function (req, res, next) {
  5. res.header("Access-Control-Allow-Origin", "*");
  6. res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
  7. res.header('Access-Control-Allow-Methods', 'PUT, POST, GET, DELETE, OPTIONS');
  8. next();
  9. });
  10. app.use(bodyParser.urlencoded(extended: true ));
  11. const ProfileData = require('./controller/profile')
  12. app.use('/', ProfileData)
  13. app.listen(5000, function () {
  14. console.log('Server is running..');
  15. });
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.

  1. // DB.js config for your database
  2. const sql = require('mssql')
  3. const config = {
  4. user: 'abcd',
  5. password: 'abcd',
  6. server: "localhost",
  7. database: "profile"
  8. }
  9. const poolPromise = new sql.ConnectionPool(config)
  10. .connect()
  11. .then(pool => {
  12. console.log('Connected to MSSQL')
  13. return pool
  14. })
  15. .catch(err => console.log('Database Connection Failed! Bad Config: ', err))
  16. module.exports = {
  17. sql, poolPromise
  18. }
Step 3: create a new table called tblProfile in SQL server.

Insert the below data into the tblProfile table

  1. insert into tblProfile (name,lastname,email,password) values ('Raju','B','[email protected]','test')
  2. insert into tblProfile (name,lastname,email,password) values ('Sachin','S','[email protected]','test')
  3. insert into tblProfile (name,lastname,email,password) values ('Suraj','S','[email protected]','test')
Create a Store Procedure for insert profile
  1. CREATE procedure [dbo].[InsertProfile]
  2. (
  3. @name varchar(50),
  4. @lastname varchar(50),
  5. @email varchar(50),
  6. @password varchar(50)
  7. )
  8. AS
  9. BEGIN
  10. insert into tblProfile (name,lastname,email,password) values( @name, @lastname, @email, @password)
  11. END
Create Store Procedure for Delete profile
  1. CREATE procedure [dbo].[DeleteProfile]
  2. (
  3. @id int
  4. )
  5. AS
  6. BEGIN
  7. delete from tblProfile where id=@id
  8. 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.

  1. const express = require('express')
  2. const router = express.Router()
  3. const { poolPromise } = require('../connection/db')
  4. const sql = require('mssql')
  5. router.get('/ApiProfileGet', async (req, res) => {
  6. try {
  7. const pool = await poolPromise
  8. const result = await pool.request()
  9. .query('select * from tblProfile',function(err, profileset){
  10. if (err)
  11. {
  12. console.log(err)
  13. }
  14. else {
  15. var send_data = profileset.recordset;
  16. res.json(send_data);
  17. }
  18. })
  19. } catch (err) {
  20. res.status(500)
  21. res.send(err.message)
  22. }
  23. })
Run the NodeJs apps by using command Node index

Below is the Post API for the profile.

Below is the example with Postman

  1. router.post('/ApiProfilePost', async (req, res) => {
  2. try {
  3. const pool = await poolPromise
  4. const result = await pool.request()
  5. .input("name", sql.VarChar(50), req.body.name)
  6. .input("lastname", sql.VarChar(50), req.body.lastname)
  7. .input("email", sql.VarChar(50), req.body.email)
  8. .input("password", sql.VarChar(50), req.body.password)
  9. .execute("InsertProfile").then(function (recordSet) {
  10. res.status(200).json({ status: "Success" })
  11. })
  12. } catch (err) {
  13. res.status(400).json({ message: "invalid" })
  14. res.send(err.message)
  15. }
  16. })
Below is the Delete API for profile
Example:
  1. router.delete('/ApiProfileDelete', async (req, res) => {
  2. try {
  3. const pool = await poolPromise
  4. const result = await pool.request()
  5. .input("id", sql.Int, req.body.id)
  6. .execute("DeleteProfile").then(function (err, recordSet) {
  7. res.status(200).json({ status: "Success" })
  8. })
  9. } catch (err) {
  10. res.status(500)
  11. res.send(err.message)
  12. }
  13. })