Building RESTful APIs using Node JS, Express JS, and MS SQL Server 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: Simple Easy to use/implement Easy to build Uniform interface The REST API is always independent of the type of platform or languages 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. Advantages: Quick & easy development High performance Run on single thread to handle multiple concurrent requests Easy to write API and interaction code Streaming support Monitoring possibilities Authentication support 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. Getting Started Prerequisites: Node JS 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 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 Note: The complete code reference can be downloaded from the below link, https://github.com/avinashl3175/Vm_BlogContent/blob/master/Node_API_Demo.zip Share About Avinash Avinash Kumar is working at Vmokshagroup as a Dot Net Developer. He has done Bachelor of Technology in Computer Science from Magadh University and has a strong passion towards Coding and learning new technologies. He enjoys listening songs, browsing the internet, and playing cricket. View all posts by Avinash 34 comments on “Building RESTful APIs using Node JS, Express JS, and MS SQL Server” renscrab March 23, 2019 3:39 pm 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 BatsHub IT Solutions March 17, 2019 2:43 pm 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 Mandar K March 13, 2019 1:02 pm 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 manoj February 11, 2019 2:59 pm Hi, I am trying to get filtered dataset from database where I have to pass multiple dynamic parameters. Reply Miley Cyrus January 31, 2019 11:43 am Love your resources! I appreciate your details and depth writing style and all your eye cache points was so awesome. Reply mohit January 19, 2019 3:59 pm how can I add authentication and authorization on my restapi like others social networking sites or payment gateway providers Reply Steven Pollock January 5, 2019 9:52 am I like the basic example, an add-on might be to authenticate the API call. Reply Sachin January 5, 2019 1:24 am nice article. explain everything properly and easy manner. Reply read more October 26, 2018 6:18 pm 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 pIccosoft September 7, 2018 11:49 am Your blog is very nice.. Thanks for sharing your information… Reply Vinodkumar Mallah September 1, 2018 3:34 pm The program is it working? I am not able to run it Please let me know the steps to run this project. Reply sahil kashetwar August 13, 2018 12:16 am 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 Christine August 6, 2018 12:19 am executeQuery is missing a command to close the SQL connection Reply spurtcommerce July 20, 2018 4:40 pm Your blog is very nice… Thanks for sharing your information… Reply piccosoft July 18, 2018 3:03 pm Your blog is very nice…Thanks for sharing your information… Reply Rinny July 13, 2018 1:38 am 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 wael August 21, 2018 4:25 am ME either , get and put works but post did not work here Reply Amogh July 6, 2018 6:31 pm 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 shoukat June 22, 2018 7:27 pm Sir i follow the same step but my server are not running Reply Skbhati June 5, 2018 4:05 pm Hi Sir, Please check { "code": "ELOGIN", "originalError": { "message": "Login failed for user 'ABC'.", "code": "ELOGIN" }, "name": "ConnectionError" } Reply piccosoft May 19, 2018 12:13 pm Your blog is very nice… Thanks for sharing your information… Reply Sree April 13, 2018 8:09 am 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 Steve K March 30, 2018 4:32 am 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 website development melbourne March 22, 2018 10:19 pm Nice Blog! Thank you so much for sharing this kind of wonderful things. Reply hire-laravel-developer March 16, 2018 5:06 pm This website will really help everyone in providing the tools and information necessary for the people to develop and improve their website. Reply Aakash February 2, 2018 6:45 pm How do you protect your .js files being downloaded at client end. ? Reply Naresh Kukreti August 12, 2017 4:17 pm How to use mssql query to search by non primary key columns (i.e email or other columns) Reply kike June 4, 2017 8:07 pm 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 kike June 4, 2017 8:10 pm 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 Mohit Sudhera May 17, 2017 9:39 pm 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 sergeos May 9, 2017 5:08 pm Is it right code? res.send(res); Reply Avinash Kumar May 12, 2017 3:41 pm 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 Kshyatisekhar Panda May 5, 2018 2:50 pm 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 Ezequiel July 4, 2018 1:36 am Thanks, your answer is right Reply Leave a Reply Cancel reply Your email address will not be published. Required fields are marked * Name * Email * Website Comment 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>
renscrab March 23, 2019 3:39 pm 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
BatsHub IT Solutions March 17, 2019 2:43 pm 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
Mandar K March 13, 2019 1:02 pm 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
manoj February 11, 2019 2:59 pm Hi, I am trying to get filtered dataset from database where I have to pass multiple dynamic parameters. Reply
Miley Cyrus January 31, 2019 11:43 am Love your resources! I appreciate your details and depth writing style and all your eye cache points was so awesome. Reply
mohit January 19, 2019 3:59 pm how can I add authentication and authorization on my restapi like others social networking sites or payment gateway providers Reply
Steven Pollock January 5, 2019 9:52 am I like the basic example, an add-on might be to authenticate the API call. Reply
read more October 26, 2018 6:18 pm 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
pIccosoft September 7, 2018 11:49 am Your blog is very nice.. Thanks for sharing your information… Reply
Vinodkumar Mallah September 1, 2018 3:34 pm The program is it working? I am not able to run it Please let me know the steps to run this project. Reply
sahil kashetwar August 13, 2018 12:16 am 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
Christine August 6, 2018 12:19 am executeQuery is missing a command to close the SQL connection Reply
spurtcommerce July 20, 2018 4:40 pm Your blog is very nice… Thanks for sharing your information… Reply
Rinny July 13, 2018 1:38 am 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
Amogh July 6, 2018 6:31 pm 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
Skbhati June 5, 2018 4:05 pm Hi Sir, Please check { "code": "ELOGIN", "originalError": { "message": "Login failed for user 'ABC'.", "code": "ELOGIN" }, "name": "ConnectionError" } Reply
Sree April 13, 2018 8:09 am 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
Steve K March 30, 2018 4:32 am 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
website development melbourne March 22, 2018 10:19 pm Nice Blog! Thank you so much for sharing this kind of wonderful things. Reply
hire-laravel-developer March 16, 2018 5:06 pm This website will really help everyone in providing the tools and information necessary for the people to develop and improve their website. Reply
Aakash February 2, 2018 6:45 pm How do you protect your .js files being downloaded at client end. ? Reply
Naresh Kukreti August 12, 2017 4:17 pm How to use mssql query to search by non primary key columns (i.e email or other columns) Reply
kike June 4, 2017 8:07 pm 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
kike June 4, 2017 8:10 pm 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
Mohit Sudhera May 17, 2017 9:39 pm 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
Avinash Kumar May 12, 2017 3:41 pm 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
Kshyatisekhar Panda May 5, 2018 2:50 pm 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
