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.


  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.



  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


  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

//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");

//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);
                  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);
                                     else {

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

//POST API"/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);

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

 app.delete("/api/user /:id", function(req , res){
                var query = "DELETE FROM [user] WHERE 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.


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,


Displaying Listings Similar to Search Results

Searching for similar things/places based on some parameters is quite normal in the current online world. Therefore, displaying listings that are similar to what users are looking for is a must for businesses and throws a challenge for developers. Here, I have explained a scenario taking Real Estate web application as an example.


A real estate web application has multiple property listings where a user will search for one property and would like to explore similar properties in that particular area. In this scenario, we need to display all the properties based on Geo Location and Zip Code.


 Save the Geo-Location (Latitude and Longitude) and Zip Code of the Property while it is listing.

Property Listing and Validation

Step 1:

Before updating any property, we should get inputs from user:

1) Street Line 1

2) Street Line 2

3) City

4) State

5) Country

6) Zip Code

Step 2:

Get Geo Location (i.e. Latitude and Longitude)

a) By Address:

//calling Google maps API for fetching Geo-Location Based on address

"" + <FullAddress> + "&sensor=true&key=" + <GoogleApiKey>

Similar Search Results

b) By Zip Code and Country Code:

//calling Google maps API for fetching Geo Location Based on Country Code and Zip code

"" + <Country> + "|postal_code:" +  <pincode> + "&sensor=true&key=" + <GoogleApiKey>;

Step 3:

Validating Zip Code:

The two Geo Locations needs to be compared by taking approximate round off values, and if both of them matches then we allow a user to move to the next step else we should clear zip code field and give an alert message like ‘wrong zip code entered’.

Step 4:

Save the Latitude and Longitude into the Address Table.



To display similar listings when a user clicks on similar property option, the below process needs to be followed:

Step 1:

We will get below input parameters of the clicked property listing:

1) Latitude

2) Longitude

3) Price Range (Min and Max)

4) Zip Code

Step 2: (Optional based on application requirement)

In addition to Step 1, we should add two more input parameters

1) Distance Unit ( Constant  : 111.045 km per degree & 69 statute miles per degree or 60 nautical miles per degree & 552 furlongs per degree)  ~ 12.92297284

2) Radius (Km Radius) ~ 05.0

Step 3:

By gathering input parameters from step 1 & 2, call custom API, get dataset and bind back to the UI.

The Custom API is responsible for below operations

1) Taking Input parameters

2) Querying Database and fetching data from property table based on the input parameters

3) Sending the result dataset back to the client


End user will be able to see various Similar Property Listings which falls under below criteria of the clicked property

1) Within Price Range (Min & Max)

2) Within 5 Km Radius of the Geo Location (Latitude and Longitude)

3) With Same Zip Code


The above process can be used for various applications to implement similar search. However, the search criteria can be changed based on the requirement.

Automating Deployment of AWS Lambda

AWS Lambda is a compute service that lets you run code without provisioning or managing servers. AWS Lambda executes your code only when needed and scales automatically, from a few requests per day to thousands per second.

Deploying Codes into AWS Lambda through AWS Web Console is insecure and time taking, since every time user needs to login into AWS Lambda console and then they have to upload the zip file or need to provide AWS S3 file path manually. To overcome this issue, there is a very simpler way to automate the deployment process as shown in the below flow chart.


Setup and Configuration:

Step 1: Create an AWS Lambda function and deploy the below zip file into it,

Step 2: Enable Lambda trigger with AWS S3 bucket when putObject method is invoked. Both S3 and Lambda Function should be in same region.

Step 3: Enable Versioning inside AWS S3 Bucket.

Step 4: Create a config.json file using any of the below configurations,

a) For Deployment into a new Lambda Function:


"accessKeyId" : "< AccessKeyId >",

"secretAccessKey": < SecretAccessKey >",

"region": "<Region>",

"lambdaFunctionName" : "<LamdaFunctionName>",

"lambdaFunctionType":  "new",


"lambdaRole":"<ARN Name>",




b) For Deployment into an old Lambda Function:


"accessKeyId" : "< AccessKeyId >",

"secretAccessKey": < SecretAccessKey >",

"region": "<Region>",

"lambdaFunctionName" : "<LamdaFunctionName>",

"lambdaFunctionType":  "old"


Step 5: Make a zip file containing following file(s)

a) Lambda code written in nodejs (index.js)

b) Node Modules folder (node_modules)

c) Other Relevant files


Deploying into AWS Lambda:

You can deploy the lambda code into any of the existing Lambda function or a new Lambda function anytime.

Steps needs to follow

Step 1: Push config.json file into AWS S3 bucket where trigger event mapping is done.

Step 2: Make the visibility of config.json file as public.

Step 3: Push <Lambda>.zip file into AWS S3.

Step 4: Deployment of lambda function will be done according to your config.json file. To verify it, go to the AWS Cloud Watch Console and go through the logs.

Note:  Copying files into AWS S3 can be done in two ways,

a) Copying file by login into AWS S3 Web Console.

b) By invoking putObject() method by using AWS SDK (Platform Independent).


The Zip file pushed into AWS S3 will be deployed into AWS lambda according to the configuration file (config.json). The automation of lambda function deployment can be configured for any AWS account.

How to generate random, realistic & reliable data for your application?

The most crucial part of the application development process is generating larger amounts of test data that resembles production environment. Because, in production, it may end up messier when a number of users knocking the app and filling the database with data. Therefore, it is challenging and needs extensive knowledge to overcome the issues with random data generation. However, advanced tools like Mockaroo will help solve the data generation problems efficiently.

Find out briefly what the challenges in the development process are and how to combat these challenges using Mockaroo.

Challenges in Application Development, Testing and Actual Deployment: 

  • Quick generation of abundant, reliable and realistic data
  • Lot of manual efforts for test engineers in populating test data and avoiding repetitive test data
  • Requires support for multiple data types (mail address, street address, Bitcoin Address, Blank, Null, country, currency, date, sequence, GUID, various versions of name including European and Chinese, lat / long, etc.) to load test data
  • Generating realistic data in multiple formats (CSV, JSON, SQL and Excel Formats, etc.)
  • Needs to load realistic data promptly without any programming skills



Mockaroo is the best tool that fits profoundly to address these challenges. It is a realistic test data generator that lets you generate up to 1000 rows in SQL, CSV, JSON and Excel formats. To extent this data limit, one can choose from their range of pricing plans.

It supports 74 different data types, where each type provides relevant sample data that is used to populate the field.

data types

Testing realistic data has two distinct advantages:

  1. It mimics the production environment and allows you identify the challenges you may face in real-time and thus helping to make it more robust.
  2. While demonstrating the app features to other users, realistic data makes it easier and quicker to understand.

Using Mockaroo will help you allay from technical aspects of testing such as learning mock data libraries, performing stress testing, etc. You can focus more on the application development, and leave the rest to this unique tool. It allows downloading higher amounts of randomly generated test data based on your specifications, and let you upload the data into the test environment using CSV or SQL formats with no programming.

How it Works:

Step 1: Go to Mocakroo official website

Step 2: Open your Table Schema

table schema


Step 3: Enter Field Name and Type similar to your Table Schema.

field name


Step 4: Enter value for Rows i.e. how many records you want to generate. Select output formats like JSON, SQL, Excel or CSV. You can either download or preview your data. Data preview will be displayed like this

code preview


Step 5 (Optional): It also provides a REST API GET method through which you can download your data programmatically.

Mockaroo is an outstanding tool in the application development process for those who want quick and efficient random data generation.