Click here to Skip to main content
15,867,453 members
Articles / Web Development / Node.js

Creating Simple API in Node.js

Rate me:
Please Sign up or sign in to vote.
4.98/5 (23 votes)
12 Apr 2018CPOL13 min read 103.2K   4.1K   26   30
How to create a simple API in Node.js and use MS SQL as a data source 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 operation.

This article is designed so that a beginner and professional can easily understand it.

Image 1

Before starting with creating API, let's understand the 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
  • Builds twice as fast with fewer people
  • 33% fewer lines of code
  • 40% fewer files
  • 2x request/sec
  • 35% faster response

Large ecosystem of the open source library.

JavaScript everywhere (front + back)

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

Prerequisites

  1. Node.js 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 a Products table.

Image 2

Products Table

Image 3

Creating Node.js Application

After creating a database and tables, next we are going to create a Node.js application.

Creating Simple Directory

Image 4

After creating Directory next, we are going to test Node for testing if our installed node is working properly. We are going to enter the command "node." After entering the command, it should enter into language shell mode as shown below:

Image 5

After creating a directory and how to use the "node" command, now let's create package.json.

What is a package.Json?

The file that contains the name of the application, the version and lists the packages that your project depends on. Allows you to specify the versions of a package that your project can use, using semantic versioning rules. 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 command "npm init".

Image 6

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

The first question it will ask for is application name. Here, I am going to enter "demoproductapi," next, it will ask for version. It will by default be 1.0.0, after that it will ask description. I going to enter "product API", next, it is going to ask entry point. Here, I am going to enter server.js "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 the package.json file.

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

Image 7

Next, it has generated your package.json file.

Opening the 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.

Image 8

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

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

Installing Modules

  1. Express

    Fast, unopinionated, minimalist web framework for node.

    More details can be found at https://www.npmjs.com/package/express.

  2. body-parser

    Node.js body parsing middleware.

    Parse incoming request bodies in a middleware before your handlers, available under the req.body property.

  3. Mssql

    Microsoft SQL Server client for Node.js

  4. 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 + ‘~'].

Image 9

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

Image 10

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.

Image 11

Next, we are going to add a JS file with the 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 requests.

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

Image 12

Until now, we installed all modules. To use that module in a Node.js application, we need to use the 'require' keyword.

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

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

Command: - "node server.js".

Image 13

After you enter the command, you can see the log which we have written. This indicates that 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 Simple Service to Understand

For creating 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 get a request, we used the Http Get method, next we provided a path for API "/product" after that, we wrote a callback function which has two parameters, request and response. We are going to use response parameter to send a response in JSON format.

Image 14

Code Snippet for the First API

JavaScript
var express = require('express');
var app = express();
var port = process.env.port || 1337;
 
app.get("/product",function(request,response)
{
    response.json({"Message":"Welcome to Node js"});
});
 
app.listen(port, function () {
    var datetime = new Date();
    var message = "Server running on Port:- " + port + "Started at :- " + datetime;
    console.log(message);
});

After completing creating an API, next, to test 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 that, I am setting Request Method to Get type. Further, I entered localhost URL along with port no and API path http://localhost:1337/product after setting request URL, the final step is set Content-Type to application/json and click on Send button to send the request.

Image 15

After sending request, we get a response which we have set in 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 a 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.

Image 16

Creating GET API

Image 17

Connect.js for Creating Database Connection for Using SQL Server

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

After creating 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.

Image 18

Code Snippet

JavaScript
var sql = require("mssql");
var connect = function()
{
    var conn = new sql.ConnectionPool({
        user: 'sa',
        password: 'Pass@123',
        server: 'SAI-PC',
        database: 'TESTDB'
    });
 
    return conn;
};

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 product in one router.

For example, all "/product" routes can be defined in one router. In the 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 route at one place.

Image 19

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

Loading Required Modules

Image 20

Code Explanation

First we are going to load external module which we require.

JavaScript
var express = require('express');
var router = express.Router();
var sql = require("mssql");
var conn = require("../connection/connect")();
  • Express: express which is a web framework for creating API
  • Router: router to create a route
  • SQL: Microsoft SQL Server client for Node.js
  • Conn: we are importing SQL connection from connect.js Class

After importing module, we have defined Anonymous functions and stored in "routes" variable.

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

After defining route next, we have declared HTTP Method "Get" and wrote a callback.

JavaScript
.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, we are going to use Connect function for creating a new connection pool.

JavaScript
conn.connect().then(function ()
            {

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

JavaScript
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 a 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.

JavaScript
var req = new sql.Request(conn);
                req.query(sqlQuery).then(function (recordset)
                {
                    res.json(recordset.recordset);
                    conn.close();
                })

The response which you will get from MS SQL driver will be in JSON format, and the Get API will return JSON of all products.

Finally, we have written a catch method for catching expectation.

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

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.

JavaScript
var express = require('express');
var app = express();
var port = process.env.port || 1337;
 
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.

Image 21

The app.use method takes two parameters. The first parameter is path and 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

Image 22

Save entire application and run.

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

Image 23

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

Creating POST API

Image 24

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

Image 25

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 procedure along with transaction and in this request, we are going to get values from post request body, which we are going to insert in the product table.

Image 26

Code Snippet of POST API

JavaScript
router.route('/')
        .post(function (req, res) {
            conn.connect().then(function () {
                var transaction = new sql.Transaction(conn);
                transaction.begin().then(function () {
                    var request = new sql.Request(transaction);
                    request.input("ProductName", sql.VarChar(50), req.body.ProductName)
                    request.input("ProductPrice", sql.Decimal(18, 0), req.body.ProductPrice)
                    request.execute("Usp_InsertProduct").then(function () {
                        transaction.commit().then(function (recordSet) {
                            conn.close();
                            res.status(200).send(req.body);
                        }).catch(function (err) {
                            conn.close();
                            res.status(400).send("Error while inserting data");
                        });
                    }).catch(function (err) {
                        conn.close();
                        res.status(400).send("Error while inserting data");
                    });
                }).catch(function (err) {
                    conn.close();
                    res.status(400).send("Error while inserting data");
                });
            }).catch(function (err) {
                conn.close();
                res.status(400).send("Error while inserting data");
            });
        });

Now we have completed with creating a post request. Next, we are going make few 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.

Note:

What is body-parser?

Node.js body parsing middleware. Parse 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

JavaScript
var bodyParser = require('body-parser');
// create application/x-www-form-urlencoded parser
app.use(bodyParser.urlencoded({ extended: true }));
// create application/json parser
app.use(bodyParser.json());

Image 27

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 below Request JSON and the next step is to add header "Content-Type" to "application/json" and finally, click on Send request.

Request Json

JavaScript
{
  "ProductName": "WebCam",
  "ProductPrice": "5000"
}

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

The Response of POST API

Image 28

Image 29

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

Products Table View After Inserting Data

Image 30

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

Note:

  • req.query: directly access the parsed query string parameters
  • req.params: directly access the parsed route parameters from the path

Referenced from: http://stackabuse.com/get-query-strings-and-parameters-in-express-js/

Creating PUT API

Image 31

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

Image 32

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.

Image 33

Code Snippet of PUT API

JavaScript
router.route('/:id')
.put(function (req, res)
 {
    var _productID = req.params.id;
    conn.connect().then(function () {
        var transaction = new sql.Transaction(conn);
        transaction.begin().then(function () {
            var request = new sql.Request(transaction);
            request.input("ProductID", sql.Int, _productID)
            request.input("ProductPrice", sql.Decimal(18, 0), req.body.ProductPrice)
            request.execute("Usp_UpdateProduct").then(function () {
                transaction.commit().then(function (recordSet) {
                    conn.close();
                    res.status(200).send(req.body);
                }).catch(function (err) {
                    conn.close();
                    res.status(400).send("Error while updating data");});
            }).catch(function (err) {
                conn.close();
                res.status(400).send("Error while updating data");});
        }).catch(function (err) {
            conn.close();
            res.status(400).send("Error while updating data");});
    }).catch(function (err) {
            conn.close();
            res.status(400).send("Error while updating data");});
});

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

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

JavaScript
{
  "ProductPrice": "5000"
}

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

Image 34

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 with creating PUT request, next we are going add Delete HTTP method to delete a product.

Creating Delete API

Image 35

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

Image 36

Deleting a Resource

In this part, we are going to Delete 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.

Image 37

Code Snippet of Delete API

JavaScript
router.route('/:id')
        .delete(function (req, res) {
            var _productID = req.params.id;
            conn.connect().then(function () {
                var transaction = new sql.Transaction(conn);
                transaction.begin().then(function () {
                    var request = new sql.Request(transaction);
                    request.input("ProductID", sql.Int, _productID)
                    request.execute("Usp_DeleteProduct").then(function () {
                        transaction.commit().then(function (recordSet) {
                            conn.close();
                            res.status(200).json("ProductID:" + _productID);
                        }).catch(function (err) {
                            conn.close();
                            res.status(400).send("Error while Deleting data");
                        });
                    }).catch(function (err) {
                        conn.close();
                        res.status(400).send("Error while Deleting data");
                    });
                }).catch(function (err) {
                    conn.close();
                    res.status(400).send("Error while Deleting data");
                });
            })
        });

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

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".

Image 38

Products Table View After Deleting Product

Image 39

Complete Project Structure

Image 40

Conclusion

Until now, we have learned how to create Node.js API in a simple step, we have started with creating Node.js application, after that, we have to create a simple GET API in server.js file. Next, we have created a product controller in that we have created route and move 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 liked my article to kickstart Node.js. In the next article, you will learn how to validate API Request.

History

  • 12th April, 2018: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India
Microsoft Most Valuable Professional
Code Project Most Valuable Author
C# Corner Most Valuable Professional

I am Senior Technical lead Working on.Net Web Technology
ASP.NET MVC,.Net Core,ASP.NET CORE, C#, SQL Server, MYSQL, MongoDB, Windows

Comments and Discussions

 
QuestionHow to connect Reactjs(Visual Studio Code) with Nodejs(Visual studio 2017) Pin
Member 1485981010-Jun-20 20:42
Member 1485981010-Jun-20 20:42 
QuestionExcellent work for someone to start. Pin
Jnyanendra3-Nov-19 21:36
professionalJnyanendra3-Nov-19 21:36 
Questionenter procedure that looks for a single result Pin
Member 1452116617-Jul-19 3:43
Member 1452116617-Jul-19 3:43 
QuestionMake multiple POST requests Pin
Member 144816037-Jun-19 1:34
Member 144816037-Jun-19 1:34 
Questioncomo puedo usar la auntenticacion de windows para conectarme a la base de datos Pin
Member 1432874426-Apr-19 5:58
Member 1432874426-Apr-19 5:58 
QuestionCan not get POST to work Pin
humphrysr10-Dec-18 8:45
humphrysr10-Dec-18 8:45 
Questionhow to connect windows authentication msqsl database Pin
Member 1293466917-Sep-18 21:22
Member 1293466917-Sep-18 21:22 
how to connect windows authentication msqsl database in it

PraiseExcellent explanation Pin
Hvishal889-Sep-18 20:16
Hvishal889-Sep-18 20:16 
QuestionI receive error when I run server.js Pin
Member 1391390117-Jul-18 3:38
Member 1391390117-Jul-18 3:38 
AnswerRe: I receive error when I run server.js Pin
Madhavi-Madhu2-May-22 2:35
Madhavi-Madhu2-May-22 2:35 
QuestionHow to connect (LocalDB)\MSSQLLocalDB Pin
Mostafa Asaduzzaman27-Jun-18 12:20
Mostafa Asaduzzaman27-Jun-18 12:20 
AnswerRe: How to connect (LocalDB)\MSSQLLocalDB Pin
Member 1208703019-Sep-18 23:06
Member 1208703019-Sep-18 23:06 
QuestionGreat Work ! Pin
Member 1177416219-Jun-18 12:16
Member 1177416219-Jun-18 12:16 
QuestionExcellent article Pin
Member 1092658726-Apr-18 2:45
Member 1092658726-Apr-18 2:45 
AnswerRe: Excellent article Pin
Saineshwar Bageri3-May-18 2:45
Saineshwar Bageri3-May-18 2:45 
Questionproblem connecting to database Pin
Sergej_S23-Apr-18 23:13
Sergej_S23-Apr-18 23:13 
AnswerRe: problem connecting to database Pin
Saineshwar Bageri3-May-18 2:48
Saineshwar Bageri3-May-18 2:48 
GeneralRe: problem connecting to database Pin
Sugeshan Govindsamy17-Oct-19 9:15
Sugeshan Govindsamy17-Oct-19 9:15 
GeneralRe: problem connecting to database Pin
Sugeshan Govindsamy17-Oct-19 22:00
Sugeshan Govindsamy17-Oct-19 22:00 
QuestionHelpful Pin
Member 1155185318-Apr-18 2:00
Member 1155185318-Apr-18 2:00 
AnswerRe: Helpful Pin
Saineshwar Bageri3-May-18 2:46
Saineshwar Bageri3-May-18 2:46 
QuestionImage with wrong code. Pin
Member 1377875118-Apr-18 1:31
Member 1377875118-Apr-18 1:31 
AnswerRe: Image with wrong code. Pin
Saineshwar Bageri19-Apr-18 20:57
Saineshwar Bageri19-Apr-18 20:57 
GeneralMy vote of 5 Pin
Mou_kol12-Apr-18 21:50
Mou_kol12-Apr-18 21:50 
GeneralRe: My vote of 5 Pin
Saineshwar Bageri3-May-18 2:46
Saineshwar Bageri3-May-18 2:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.