Creating Simple API In Node.js

In this article, we are going to learn how to create a simple API in node js and using MS SQL as a data source on which we are going to perform CRUD operation.

In this article, we are going to learn how to create a simple API in node js and use MS SQL as a data source on which we are going to perform CRUD operations.

Simple API in Node js

This article will be written so that both a beginner and a professional can easily understand it.

Before starting with creating API let’s understand basics.

What is Node js?

  • An Open source, Cross-platform, and runtime environment for executing javascript code outside the browser.

What can we build using it?

  • We can build Web apps and APIS using node js.

Why node.js if we already have other frameworks?

  • It is super-fast and highly scalable e.g. PayPal, Uber, Netflix
  • Build twice as fast with fewer people
  • 33% fewer lines of code
  • 40% fewer files
  • 2x request/sec
  • 35% faster response

The open source library has a large ecosystem.

Javascript is everywhere (front + back).

You can write an entire application using javascript, no need to learn a new language.

Prerequisites

  1. Node js is installed [Download node js https://nodejs.org/en/ ]
  2. Next, we are going to use Visual Studio Code IDE for Development [ https://code.visualstudio.com/download ]
  3. SQL Server Database

Let’s start with Database first

I have created a sample database “TESTDB” with Products table.

Simple API in Node js

 

Products Table

Simple API in Node js

Creating Node js Application

After creating Database and tables next we are going to create Node js application.

Creating simple Directory

Simple API in Node js

After creating Directory, next, we are going to test Node. For testing that our installed node is working properly we are going to enter the command “node” after entering the command and it should enter into language shell mode as shown below.

Simple API in Node js

After creating a directory and how to use “node” command now let’s create package.json.          

What is a package. Json?

It is the file that contains the name of the application, the version and lists the packages that your project depends on. It allows you to specify the versions of a package that your project can use using semantic versioning rules. It makes your build reproducible, and therefore much easier to share with other developers. 

Reference Link: -  https://goo.gl/pXwGB2

Creating a package.json

For creating a package.json file we are going to first execute the command “npm init”. 

Simple API in Node js

After executing the command, it will begin to ask a question for generating the package. json file.

The first question it will ask is for application name; here I am going to enter “demoproductapi”, next it will ask for version.  It will be by default 1.0.0; after that it will ask for a description. I am going to enter “product API”, next it is going to ask entry point and here I am going to enter “server.js” as entry point to our application. Next we are not going to set test command, git repository, and keywords, and at last we are going to set author and license.

After that, it will show you what it will write to package.json file.

And finally, it will ask you if you want to generate the package.json file with  the setting you have set and if you say yes then it is going to create the file.

Simple API in Node js

Next, it has generated your package.json file.

Opening project in Visual Studio code from the command prompt

Now to open this file in Visual Studio code we are going to write “code .”

As you enter the command it will open Visual Studio code as you can see below.

Note

Color Theme Visual studio code IDE might be different but you can set your custom Color theme as you want.

Simple API in Node js

After opening the project in Visual Studio code, next we are going to install various packages which we require for creating the API.

All packages will be downloaded from npm (Node Package Manager).

Installing modules

  1. Express
    Fast, unopinionated, minimalist web framework for node.
    More Details :- https://www.npmjs.com/package/express
  1. body-parser
    Node.js is body parsing middleware.
    Parses incoming request bodies in a middleware before your handlers, available under the req.body property.
  1. Mssql
    Microsoft SQL Server client for Node.js
  1. joi
    Object schema description language and validator for JavaScript objects.

For installing package from Visual Studio code you can open a terminal in Visual Studio code using shortcut keys [ Ctrl + ‘~’]   

Simple API in Node js

Note The ‘--save ' option instructed NPM to include the modules inside of the dependencies section of your package.json automatically.

Command: - npm install --save express body-parser mssql joi

Simple API in Node js

After entering the command just click on the enter button to install modules.

After installing you will see all modules in the package.json file with versions in it.

Simple API in Node js

Next, we are going to add a js file with name server.js 

Adding Server.js file

In this part, we are going to add server.js file and in this file, we are going to create a simple server using an express framework, which will handle HTTP request.

For adding files just right click in explorer and select New File, then name your file as Server.js.

So far we have installed all modules now to use that module in a Node.js application we need to use the 'require' keyword.

Simple API in Node js

Now to run this application we are going to use shortcut [ Ctrl + ‘~’] to open Terminal.

Next, we are going to enter command node and file name.

  • Command: - “node server.js”.
Simple API in Node js

After you enter the command you can see the log which we have written; this indicates the application is running.

Note: - What is Callback?

A callback is a function called at the completion of a given task; this prevents any blocking and allows other code to be run in the meantime.

Reference:- https://docs.nodejitsu.com/articles/getting-started/control-flow/what-are-callbacks/

Create a simple service to understand

For creating a simple API, we are going to use an express framework which we have already downloaded.

The first API we are going to create is the get product API which will return a simple welcome message as a response in Json format.

For handling a get request we have used Http Get method, next we have provided a path for API “/product” after that, we have written a callback function which has 2 parameter requests and responses. We are going to use response parameter to send a response in Json format.

Simple API in Node js

Code Snippet for the first API

  1. var express = require('express');  
  2. var app = express();  
  3. var port = process.env.port || 1337;  
  4.   
  5. app.get("/product",function(request,response)  
  6. {  
  7.     response.json({"Message":"Welcome to Node js"});  
  8. });  
  9.   
  10. app.listen(port, function () {  
  11.     var datetime = new Date();  
  12.     var message = "Server runnning on Port:- " + port + "Started at :- " + datetime;  
  13.     console.log(message);  
  14. });  

After completing creating the API next, for testing how it works, we are going to use REST client “POSTMAN”. 

Using POSTMAN Rest Client to Test API

There are various REST clients available and you can use any of them to test API, for this demo I am going to use POSTMAN.

We are going to send Get Request, for doing that I am setting Request Method to Get type, further, I have entered localhost URL along with port number and API path http://localhost:1337/product. After setting request URL the final step is to set Content-Type to application/json and click on send button to send the request.

Simple API in Node js

After sending request we get a response which we have set in the API response.

Wow, we have created a simple API in node js.

Now we have learned how to create a simple API but we have written our entire code inside the server.js file which will get messy if we are going to create Add More APIs in it. To stop that we are going to create a separate database connection file and controllers file and in that file we are going to write code and export it such that we can access it anywhere.Simple API in Node js

Creating GET API

Simple API in Node js

Connect.js for Creating Database Connection for Using SQL Server

We have created a new folder with the name “connection” and in this folder, we are going to add a connect.js file.

After creating the file next, we are going to import “mssql” module for creating SQL connection, and finally, we are going to export this connection such that it can be used in other modules.

Simple API in Node js

Code Snippet

  1. var sql = require("mssql");  
  2. var connect = function()  
  3. {  
  4.     var conn = new sql.ConnectionPool({  
  5.         user: 'sa',  
  6.         password: 'Pass@123',  
  7.         server: 'SAI-PC',  
  8.         database: 'TESTDB'  
  9.     });  
  10.   
  11.     return conn;  
  12. };  
  13.   
  14. module.exports = connect;  

After adding connect.js for creating SQL connection file next we are going to add Controller folder and inside that folder, we are going to add Product Controller js file.

ProductController.js for creating Route

We are going to use Route to define all routes of a product in one router.

For example, all “/product” routes can be defined in one router, in future, if we have to add any new route to the product we can easily define in “/product” route, we are going to get all product-related routes at one place.

Simple API in Node js

Now we have added ProductController.js file next we are going to import module and create a route.

Loading required Modules

Simple API in Node js

Code Explanation

First we are going to load external module which we are required to do.

  1. var express = require('express');  
  2. var router = express.Router();  
  3. var sql = require("mssql");  
  4. var conn = require("../connection/connect")();  
  • Express: -  A web framework for creating API.
  • Router: -  Used to create a route.
  • SQL: - Microsoft SQL Server client for Node.js
  • Conn: - We are importing SQL connection from connect.js Class.

After importing the module, we have defined Anonymous functions and stored it in the “routes” variable.

Next we have defined route router.route('/').

After defining route, next, we have declared HTTP Method “Get” and written a callback.

  .get(function (req, res)

Note - connect

Create a new connection pool. The initial probe connection is created to find out whether the configuration is valid.

After that we are going to use Connect function for creating new connection pool.

  1. conn.connect().then(function ()   
  2.             {  

Next, after creating a connection, I have written a query to get all products from the database.

  1. var sqlQuery = "SELECT * FROM Products";  

After we have written the query we are going to create a new SQL request and pass your connection (conn) to it.

Then request (req) has a method query which takes command string as input. We are going to pass our sqlQuery to it, and the same query has a callback which will return a response.

  1. var req = new sql.Request(conn);  
  2.                 req.query(sqlQuery).then(function (recordset)   
  3.                 {  
  4.                     res.json(recordset.recordset);  
  5.                     conn.close();  
  6.                 })  

The response which you will get from mssql driver will be in Json format, and the Get API will return Json for all the products.

Finally, we have written catch method for catching expectation.

  1. .catch(function (err) {  
  2.                         conn.close();  
  3.                         res.status(400).send("Error while inserting data");  
  4.                     });  

After completing with creating a route just save ProductController.js 

Setting up middleware for handling route request in server.js

After creating ProductController.js, next we are going to import ProductController.js file in server.js.

  1. var express = require('express');  
  2. var app = express();  
  3. var port = process.env.port || 1337;  
  4.   
  5. var productController = require('./Controller/ProductController')();  

Now we have imported ProductController.js file. Next we are going to use app.use method for invoking our API.

Note

app.use([path,] callback [, callback...])

Referenced from:- http://expressjs.com/en/api.html#app.use

The middleware function is executed when the base of the requested path matches path.

Simple API in Node js

The app.use method takes 2 parameters; the first parameter is path and the second parameter is function or middleware function. 

If the path we request “/api/products” is matched, then it will call Productcontroller function and return a response in Json format.

Products Table

Simple API in Node js

Save entire application and run it.

Now to access API open Postman or any other Rest Client and enter Url: - http://localhost:1337/api/products and set HTTP method request to Get and click on Send request.

The response of Get API

Simple API in Node js

After completing  get request next we are going create POST request API.

Creating POST API

Simple API in Node js

In post API we are first going to create Stored Procedure for inserting records in the products table.

Simple API in Node js

Next, as we have written route in get API in the same way we are going to write POST API route.

But something is new in this code snippet because we are using stored produce along with transaction and in this request, we are going to get values from the post request body which we are going to insert in the product table.

Simple API in Node js

Code snippet of POST API

  1. router.route('/')  
  2.         .post(function (req, res) {  
  3.             conn.connect().then(function () {  
  4.                 var transaction = new sql.Transaction(conn);  
  5.                 transaction.begin().then(function () {  
  6.                     var request = new sql.Request(transaction);  
  7.                     request.input("ProductName", sql.VarChar(50), req.body.ProductName)  
  8.                     request.input("ProductPrice", sql.Decimal(18, 0), req.body.ProductPrice)  
  9.                     request.execute("Usp_InsertProduct").then(function () {  
  10.                         transaction.commit().then(function (recordSet) {  
  11.                             conn.close();  
  12.                             res.status(200).send(req.body);  
  13.                         }).catch(function (err) {  
  14.                             conn.close();  
  15.                             res.status(400).send("Error while inserting data");  
  16.                         });  
  17.                     }).catch(function (err) {  
  18.                         conn.close();  
  19.                         res.status(400).send("Error while inserting data");  
  20.                     });  
  21.                 }).catch(function (err) {  
  22.                     conn.close();  
  23.                     res.status(400).send("Error while inserting data");  
  24.                 });  
  25.             }).catch(function (err) {  
  26.                 conn.close();  
  27.                 res.status(400).send("Error while inserting data");  
  28.             });  
  29.         });  

Now we have completed  creating post request; next we are going make little changes in the server.js file. 

We are going to use body-parser package for parsing the incoming request, we have already installed body-parser package.

What is body-parser?

Node.js body parsing middleware. It parses incoming request bodies in a middleware before your handlers, available under the req.body property.

Referenced from:-  https://www.npmjs.com/package/body-parser

Simplified definition

body-parser extracts the entire body portion of an incoming request stream and exposes it on req. body.

Loading body-parser module.

Code snippet for loading body-parser module

  1. var bodyParser = require('body-parser');  
  2. // create application/x-www-form-urlencoded parser  
  3. app.use(bodyParser.urlencoded({ extended: true }));  
  4. // create application/json parser  
  5. app.use(bodyParser.json());  
Simple API in Node js

Save entire application and run.

Now to access POST API open Postman or any other Rest Client and enter.

Url

http://localhost:1337/api/products and set HTTP method request to POST and in request body add the below Request Json and the next step is to add header "Content-Type" to "application/json" and finally click on Send request.

Request Json

  1. {  
  2.   "ProductName""WebCam",  
  3.   "ProductPrice""5000"  
  4. }  

Note

Do not forget to set add header "Content-Type" to "application/json".

The response of POST API

Simple API in Node js
 
Simple API in Node js

After successfully posting data let’s see if it is present in the products table.

Products table view after inserting data.

Simple API in Node js

Wow, we have successfully inserted product in the products table.

Note

Creating PUT API

Simple API in Node js

In PUT API we are first going to create Stored Procedure for updating records of products table.

Simple API in Node js

Updating a Resource

For updating product, we are going to send id of product from Uri and the request body that contains data which we want to update.

After setting Uri and request body, next we are going add HTTP Put method in Product controller file, and the route for this method will be different because we are going to accept “id” as a parameter and also request body.

For reading route parameters value we use request.params.

Simple API in Node js  

Code snippet of PUT API

  1. router.route('/:id')  
  2.  .put(function (req, res)  
  3.   {  
  4.      var _productID = req.params.id;  
  5.      conn.connect().then(function () {  
  6.          var transaction = new sql.Transaction(conn);  
  7.          transaction.begin().then(function () {  
  8.              var request = new sql.Request(transaction);  
  9.              request.input("ProductID", sql.Int, _productID)  
  10.              request.input("ProductPrice", sql.Decimal(18, 0), req.body.ProductPrice)  
  11.              request.execute("Usp_UpdateProduct").then(function () {  
  12.                  transaction.commit().then(function (recordSet) {  
  13.                      conn.close();  
  14.                      res.status(200).send(req.body);  
  15.                  }).catch(function (err) {  
  16.                      conn.close();  
  17.                      res.status(400).send("Error while updating data");});  
  18.              }).catch(function (err) {  
  19.                  conn.close();  
  20.                  res.status(400).send("Error while updating data");});  
  21.          }).catch(function (err) {  
  22.              conn.close();  
  23.              res.status(400).send("Error while updating data");});  
  24.      }).catch(function (err) {  
  25.              conn.close();  
  26.              res.status(400).send("Error while updating data");});  
  27.  });  

After completing adding the put method, next save the entire application and run it.

Now to access PUT API open Postman or any other Rest Client and enter.

Url

http://localhost:1337/api/products/7 and set HTTP method request to PUT and in the request, body add below Request Json and the next step is to add header "Content-Type" to "application/json" and finally click on Send request.

Request Json

  1. {  
  2.   "ProductPrice""5000"  
  3. }  

Note
Do not forget to set add header "Content-Type" to "application/json".

Simple API in Node js

If an updated request succeeds, it can return status 200 (OK); along with it we are going to get request body in response.

Now we have completed creating PUT request; next we are going to add Delete HTTP method to delete a product.

Creating Delete API

Simple API in Node js

In Delete API we are first going to create Stored Procedure for Deleting a record of a product from products table. The Delete HTTP method route is similar to PUT API route which takes product ID from Uri and on the basis of it, it will delete product records.

Simple API in Node js

Deleting a Resource.

In this part, we are going to delete a product; for doing that we are going to send id of product from Uri as you can see in the below snapshot.

For reading route parameters value we use request.params.

Simple API in Node js

Code snippet of Delete API

  1. router.route('/:id')  
  2.         .delete(function (req, res) {  
  3.             var _productID = req.params.id;  
  4.             conn.connect().then(function () {  
  5.                 var transaction = new sql.Transaction(conn);  
  6.                 transaction.begin().then(function () {  
  7.                     var request = new sql.Request(transaction);  
  8.                     request.input("ProductID", sql.Int, _productID)  
  9.                     request.execute("Usp_DeleteProduct").then(function () {  
  10.                         transaction.commit().then(function (recordSet) {  
  11.                             conn.close();  
  12.                             res.status(200).json("ProductID:" + _productID);  
  13.                         }).catch(function (err) {  
  14.                             conn.close();  
  15.                             res.status(400).send("Error while Deleting data");  
  16.                         });  
  17.                     }).catch(function (err) {  
  18.                         conn.close();  
  19.                         res.status(400).send("Error while Deleting data");  
  20.                     });  
  21.                 }).catch(function (err) {  
  22.                     conn.close();  
  23.                     res.status(400).send("Error while Deleting data");  
  24.                 });  
  25.             })  
  26.         });  

After completing with adding Delete method next save the entire application and run it.

Now to access Delete API open Postman or any other Rest Client and enter.

Url: - http://localhost:1337/api/products/7 and set HTTP method request to Delete and add header "Content-Type" to "application/json" and finally click on Send request.

Note
Do not forget to set add header "Content-Type" to "application/json".

Simple API in Node js

Products table view after deleting Product

Simple API in Node js

Complete Project Structure

Simple API in Node js

Conclusion

So far we have learned how to create Node js API in a simple step, we have started with creating node js applications, after that we have created a simple GET API in server.js file, next we have created product controller; in that we have created route and moved entire logic of API in to this controller, and also in Sql server we have created stored procedure for inserting, updating and deleting data, finally we have used POSTMAN for testing our API which we have created.

I hope you have liked my article to kick start Node js. In the next article you will learn how to validate API Request.