• Blog
  • Building Restful Apis Using Node Js Express Js And Ms Sql Server

Building RESTful APIs using Node JS, Express JS, and MS SQL Server

RESTful APIs

REST (Representational State Transfer) is web standards based architecture and uses HTTP Protocol. A REST Server simply provides access to resources and REST client accesses and modifies the resources using HTTP protocol. REST uses various representations to represent a resource like text, JSON, and XML but JSON is the most popular one.

Why do we need RESTful Web Services?

REST is an architecture style for designing networked applications. REST is a lightweight alternative to mechanisms like RPC (Remote Procedure Calls) and Web Services (SOAP, WSDL, et al.). The World Wide Web itself, based on HTTP, can be viewed as an REST-based architecture. The entire modern web browsers are REST client. RESTful applications use HTTP methods (GET, POST, PUT, and DELETE) to perform CRUD operations.

Advantages:

  1. Simple
  2. Easy to use/implement
  3. Easy to build
  4. Uniform interface
  5. The REST API is always independent of the type of platform or languages
  6. Visible, reliable, and scalable

Why opt Node JS for building RESTful APIS?

Node.js is a powerful JavaScript framework built on Google Chrome’s JavaScript V8 Engine. It is used to develop I/O intensive web applications like video streaming sites, single-page applications, etc. Node.js is open source and used by thousands of developers around the world.

nodejsProcessingModel

Advantages:

  1. Quick & easy development
  2. High performance
  3. Run on single thread to handle multiple concurrent requests
  4. Easy to write API and interaction code
  5. Streaming support
  6. Monitoring possibilities
  7. Authentication support
  8. Lightweight, fast, and scalable

About Express JS:

Express is a flexible Node.js web application framework that provides a robust set of features to develop mobile and web applications. It facilitates the rapid development of Node based Web applications. Few core features of Express framework −

  • Allows setting up of middleware to respond to HTTP Requests.
  • Defines a routing table which is used to perform different actions based on HTTP Method and URL.

Allows to the dynamic rendering of HTML Pages based on passing arguments to templates.

Express JS node architecture

Getting Started

Prerequisites:

  1. Node JS
  2. MS SQL Server Database

App Setup & Execution:

Step 1: Create a package.json file.

{
  "name": "<Application Name>",//e.g.  – node_app
  "version": "0.0.0",
  "description": "<Description about your application>",
  "main": "server.js",
    "dependencies": {
        "express": "^4.14.0",
        "body-parser": "^1.15.2",
         “mssql”:”^ 3.3.0”
    },
    "scripts": {
    "start": "node server.js"
  },
  "author": {
    "name": "<Your Name>",
    "email": "<Your Email>"
  }
}

Note:  The package.json file can be created by using npm init command (recommended way).

Step 2: Create server.js file.

//Initiallising node modules
var express = require("express");
var bodyParser = require("body-parser");
var sql = require("mssql");
var app = express(); 

// Body Parser Middleware
app.use(bodyParser.json()); 

//CORS Middleware
app.use(function (req, res, next) {
    //Enabling CORS 
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Methods", "GET,HEAD,OPTIONS,POST,PUT");
    res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, contentType,Content-Type, Accept, Authorization");
    next();
});

//Setting up server
 var server = app.listen(process.env.PORT || 8080, function () {
    var port = server.address().port;
    console.log("App now running on port", port);
 });

//Initiallising connection string
var dbConfig = {
    user:  “<dbUserName>”,
    password: “<dbPassword>”,
    server: “<dbHost_URL>”,
    database:” <dbName>”
};

//Function to connect to database and execute query
var  executeQuery = function(res, query){             
     sql.connect(dbConfig, function (err) {
         if (err) {   
                     console.log("Error while connecting database :- " + err);
                     res.send(err);
                  }
                  else {
                         // create Request object
                         var request = new sql.Request();
                         // query to the database
                         request.query(query, function (err, res) {
                           if (err) {
                                      console.log("Error while querying database :- " + err);
                                      res.send(err);
                                     }
                                     else {
                                       res.send(res);
                                            }
                               });
                       }
      });           
}

//GET API
app.get("/api/user", function(req , res){
                var query = "select * from [user]";
                executeQuery (res, query);
});

//POST API
 app.post("/api/user", function(req , res){
                var query = "INSERT INTO [user] (Name,Email,Password) VALUES (req.body.Name,req.body.Email,req.body.Password”);
                executeQuery (res, query);
});

//PUT API
 app.put("/api/user/:id", function(req , res){
                var query = "UPDATE [user] SET Name= " + req.body.Name  +  " , Email=  " + req.body.Email + "  WHERE Id= " + req.params.id;
                executeQuery (res, query);
});

// DELETE API
 app.delete("/api/user /:id", function(req , res){
                var query = "DELETE FROM [user] WHERE Id=" + req.params.id;
                executeQuery (res, query);
});

Note: You can add multiple routes based on your requirement.

Step 3: Open CMD, execute npm install command.

Step 4:  After successful installation of node modules, run below command into CMD.

node server.js

CMD

The node server will start running into port number 8080 with corresponding API Routes; you can point to the below URL to test the application.

http://localhost:8080

Step 5: Testing REST APIs

We can use any of the REST Clients like Postman Chrome App or through any of the Programming languages like Java, C#, PHP, etc., to consume the APIs.

Here is the demonstration using Postman App

Postman App

Note: The complete code reference can be downloaded from the below link,

https://github.com/avinashl3175/Vm_BlogContent/blob/master/Node_API_Demo.zip

 

FacebookTwitterGoogle+Share
About Uday Kiran

Uday Kiran is an Senior software engineer at Vmoksha Technologies. He is passionate about exploring new automation technologies for cloud,mobile and web application . He loves reading novels, traveling, and trekking. He has done Master's at Andhra University.



36 comments on “Building RESTful APIs using Node JS, Express JS, and MS SQL Server”

  1. Baltazar

    To close the connection
    //Function to connect to database and execute query
    var executeQuery = function(res, query){
    sql.connect(dbConfig, (err) => {
    if (err) {
    console.log("Error while connecting database :- " + err);
    res.send(err);
    }
    else {
    // create Request object
    var request = new sql.Request();
    // query to the database
    request.query(query, function(err, rs) {
    if (err) {
    console.log("Error while querying database :- " + err);
    res.send(err);
    sql.close();
    }
    else {
    res.send(rs);
    sql.close();
    }
    });
    }
    });
    }

    Reply
  3. renscrab

    This code will only work for first request since you never close the db connection.

    And also it look like you don’t return the dedicated http codes but the error from sql server.

    You should (IMO) return the http code and log the sql errors. You should make executeQuery a promise / async function and handle / return specific http error in each route handle.

    Reply
  4. BatsHub IT Solutions

    At BatsHub we practice the shared tech stack and this is a great one to share with our team to let them know the basics who wants to try hands on Node. Site.

    Reply
  5. Mandar K

    I m trying to follow these steps. But getting error:

    ConnectionError: Login failed for user ‘domain\userName’.
    at Connection.tedious.once.err (~\Node_API_Demo\node_modules\mssql\lib\tedious.js:244:17)
    at Object.onceWrapper (events.js:315:30)
    at emitOne (events.js:116:13)
    at Connection.emit (events.js:211:7)
    at Connection.processLogin7Response (~\Node_API_Demo\node_modules\mssql\node_modules\tedious\lib\connection.js:1397:14)
    at Connection.message (~\Node_API_Demo\node_modules\mssql\node_modules\tedious\lib\connection.js:1932:14)
    at Connection.dispatchEvent (~\Node_API_Demo\node_modules\mssql\node_modules\tedious\lib\connection.js:1084:36)
    at MessageIO.messageIo.on (~\Node_API_Demo\node_modules\mssql\node_modules\tedious\lib\connection.js:984:14)
    at emitNone (events.js:106:13)
    at MessageIO.emit (events.js:208:7)
    code: ‘ELOGIN’,
    originalError:
    { ConnectionError: Login failed for user ”domain\userName’.
    at ConnectionError (~\Node_API_Demo\node_modules\mssql\node_modules\tedious\lib\errors.js:13:12)
    at Parser.tokenStreamParser.on.token (~\Node_API_Demo\node_modules\mssql\node_modules\tedious\lib\connection.js:735:29)
    at emitOne (events.js:116:13)
    at Parser.emit (events.js:211:7)
    at Parser.parser.on.token (~\Node_API_Demo\node_modules\mssql\node_modules\tedious\lib\token\token-stream-parser.js:27:14)

    Please assist

    Reply
  6. manoj

    Hi, I am trying to get filtered dataset from database where I have to pass multiple dynamic parameters.

    Reply
  7. Miley Cyrus

    Love your resources! I appreciate your details and depth writing style and all your eye cache points was so awesome.

    Reply
  8. mohit

    how can I add authentication and authorization on my restapi like others social networking sites or payment gateway providers

    Reply
  9. Steven Pollock

    I like the basic example, an add-on might be to authenticate the API call.

    Reply
  10. Sachin

    nice article. explain everything properly and easy manner.

    Reply
  11. read more

    Thanks , I’ve recently been looking for info approximately this topic for a long time and yours is the
    greatest I have discovered so far. However, what concerning the bottom line?
    Are you positive concerning the source?

    Reply
  12. pIccosoft

    Your blog is very nice.. Thanks for sharing your information…

    Reply
  13. Vinodkumar Mallah

    The program is it working? I am not able to run it
    Please let me know the steps to run this project.

    Reply
  14. sahil kashetwar

    The very first thing that i found here is that the NODEJS is framework.

    Actually it’s not an framework it’s server side Javascript run time environment which has multiple component which convert the javascript code into the machine code using the google open source V8 engine and uses the Event loop to execute the call back functions which you have written in your application…

    Other than this everything will be good…..
    Thanks for blog..

    Reply
  15. Christine

    executeQuery is missing a command to close the SQL connection

    Reply
  16. spurtcommerce

    Your blog is very nice… Thanks for sharing your information…

    Reply
  17. piccosoft

    Your blog is very nice…Thanks for sharing your information…

    Reply
  18. Rinny

    I kept some dummy values in my insert statement chto chek the post request is working or not . but nothing happening if i hit post url. even the log statements are not printing which means mu post method is not even hitting. but my get method is working.

    app.post(“/postuser “, function(req, res) {
    console.log(“post method”)
    var query = “INSERT INTO Persons VALUES (1,’sssss’, ‘wwwwww’)”;
    console.log(query);
    executeQuery(res, query);
    });

    Reply
    1. wael

      ME either , get and put works but post did not work here

      Reply
  19. Amogh

    Is it possible to use ‘?’ instead of ‘:’ (colon) while using get or post method with parameters?
    like in a normal query.
    for example:- ” app.put(“/api/user/:id”, function(req , res)”
    instread of this can i use app.put(“/api/user?id”, function(req , res)”

    Reply
  20. shoukat

    Sir i follow the same step but my server are not running

    Reply
  21. Skbhati

    Hi Sir,

    Please check


    {
    "code": "ELOGIN",
    "originalError": {
    "message": "Login failed for user 'ABC'.",
    "code": "ELOGIN"
    },
    "name": "ConnectionError"
    }

    Reply
  22. piccosoft

    Your blog is very nice… Thanks for sharing your information…

    Reply
  23. Sree

    Hi..
    thanks for sharing this.
    How do I pass multiple parameters in the below query for the where condition? Please help
    // DELETE API
    app.delete(“/api/user /:id”, function(req , res){
    var query = “DELETE FROM [user] WHERE Id=” + req.params.id;
    executeQuery (res, query);
    });

    Reply
  24. Steve K

    Thanks for this great tutorial. It pointed me in the direction I needed to create an Nodejs API

    I couldn’t get the POST and PUT requests to work and I finally realized that req.body.Name, req.body.Email, and req.body.Password are strings and as such the queries needed to be rewritten with single quotes around the values to work properly.

    Below are the changes I made. I separated the single and double quotes with a space here for clarity:


    //POST API
    app.post("/api/user", function(req , rs){
    var query = "INSERT INTO [user] (Name,Email,Password) VALUES (' " + req.body.Name + " ',' " + req.body.Email + " ',' " + req.body.Password + " ')";
    executeQuery (rs, query);
    });

    //PUT API
    app.put("/api/user/:id", function(req , rs){
    var query = "UPDATE [user] SET Name= ' " + req.body.Name + " ' , Email= ' " + req.body.Email + " ' WHERE Id= " + req.params.id;
    executeQuery (rs, query);
    });

    Reply
  26. hire-laravel-developer

    This website will really help everyone in providing the tools and information necessary for the people to develop and improve their website.

    Reply
  27. Aakash

    How do you protect your .js files being downloaded at client end. ?

    Reply
  28. Naresh Kukreti

    How to use mssql query to search by non primary key columns (i.e email or other columns)

    Reply
  29. kike

    Thanks a lot for sharing Avinash,

    I fix it this way, changing res parameter (response object) by res (recordset from database)

    request.query(query, function (err, rs) {
    if (err) {
    console.log("Error while querying database :- " + err);
    res.send(err);
    }
    else {
    res.send(res);
    }

    Reply
    1. kike

      sorry there was a typo in the code, this is the correct one:

      fix it this way, changing res parameter (response object) by rs (recordset from database)


      request.query(query, function (err, rs) {
      if (err) {
      console.log("Error while querying database :- " + err);
      res.send(err);
      }
      else {
      res.send(rs);
      }

      Reply
  30. Mohit Sudhera

    Hi Avinash,

    Thanks for sharing your knowledge.

    I am restricted to firefox to access node.js. Therefore have added node.js firefox plugin. After following the process you have mentioned, I am not able to see anything on UI. I am accessing the script through one of the servers in putty and trying to open localhost in windows. Am I making any mistake here?

    Reply
  31. sergeos

    Is it right code?
    res.send(res);

    Reply
    1. Avinash Kumar

      Hi Sergeos,

      The res object represents the HTTP response that an Express app sends when it gets an HTTP request. In other words, it is used for returning response from the server to client we used to write res.send(). res contains only method definitions so if you send back res object, it will throw below error,

      Converting circular structure to JSON

      Thus , res.send(res); is not right.

      Reply
    2. Kshyatisekhar Panda

      No..res.send(res) is not right.You can change it like below….
      request.query(query, function (err, result) {
      if (err) {
      console.log(“Error while querying database :- ” + err);
      res.send(err);
      }
      else {
      res.send(result);
      }
      });

      Reply
      1. Ezequiel

        Thanks, your answer is right

        Reply

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>