REST API With NodeJS Using SQLite3 and Express
Tags: javascript, beginners
Published: 2020-06-12 17:55:20

<link rel="canonical" href="https://medium.com/@codesprintpro/rest-api-using-sqlite3-nodejs-and-expressjs-f8c0c0847fe5" />

In this blog, we will discuss how to create REST API which provides CRUD functionality to an entity using NodeJS.

### Topics Covered

-   What is REST
-   What is CRUD
-   REST & CRUD Together
-   REST Standards and Response Code Recommendation
-   Setting Up Database and Initial NodeJs File
-   GET API With NodeJS
-   POST API With NodeJS
-   PUT API With NodeJS
-   DELETE API With NodeJS

If you already know the concept you can jump onto the _Setting Up Database and Initial NodeJS File_ section.

### What is REST

REST stands for Representation State Transfer, we can think it as an architectural style standard which simplifies the communication between computer systems. The computer system which is REST compliant also called REStful systems.

RESTful systems are stateless and separate the concern of client and server in an HTTP communication protocol.

**Separation of Client & Server**

The development of the client and server can take place independently. We  
don’t have to bother while changing how each side work.

Until and unless both sides know which format of the message to send, which is understood by the other side, they can be developed independently.

**Statelessness**

Client and Server don’t have to know the state of the other side while communicating. They can understand each other messages without knowing anything about the previous message.

From [https://restfulapi.net/statelessness/](https://restfulapi.net/statelessness/)

> Each request from the client to server must contain all of the information necessary to understand the request, and cannot take advantage of any stored context on the server. Session state is therefore kept entirely on the client. client is responsible for storing and handling all application state related information on client side.

  

The statelessness behavior gives the power to scale, be a reliable and quick performer. [For more on REST.](https://restfulapi.net/)

### What is CRUD

When we build API, we need it to provide four basic functionalities namely Create, Read, Update, and Delete a resource. The CRUD paradigm is common in constructing web applications because it provides a memorable framework for reminding developers of how to construct full, usable models.

### REST & CRUD Together

In the REST API, each verb in CRUD relates to a specific HTTP method.

-   Create — POST
-   Read — GET
-   Update — PUT
-   Delete — Delete

The above four are the basic building blocks for a persistent backend system.

### REST Standards and Response Code Recommendation

Let us take an entity type for an example and explain the recommendation based on that.

We will take an Employee entity here. DB Diagram for the _employee_ table.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/uir4atbn4zkofpws5os9.png)

Before writing code let us understand the different REST API which we will write for employee resources.

#### **Create REST API Request**

To create a resource in employees entity we use an HTTP POST request. POST creates a new resource of the specified resource type.

Before we start discussing let’s fix the message format we will communicate to JSON.

Let’s imagine employee resource, we want to add a new employee to the existing lists.

**Request Format**

Request format will have always the resource name in plural format. Like here we are working on _employee_ resource the API should point to _employees_.

http://localhost:8080/employees/

**Request Body**

The body will be the new employee object detail in JSON format. In the case of a POST request, we will not pass the primary key as that will be automatically generated by the system.

    {  
      "last_name": "Sarawgi",  
      "first_name": "Sachin",  
      "title": "Software Developer",  
      "address": "India",  
      "country_code": "IN"  
    }

**HTTP Method Type:** POST

**Response Body**

The response body of a POST request should contain the newly created resource with the primary key.

    {  
      "employee_id": 1,  
      "last_name": "Sarawgi",  
      "first_name": "Sachin",  
      "title": "Software Developer",  
      "address": "India",  
      "country_code": "IN"  
    }

**Response Code:** When we try to create a new resource and it’s successful then the server should return 201 (CREATED) response code.

----------

#### GET REST API Request

To read a particular item/row in an employee entity we use an HTTP GET request. Reading a resource should never change any information. GET requests can be used to read a particular item in a resource or read the whole list of items.

Let’s imagine employee resource, we want to read a particular employee from the existing lists.

**Request Format**

For reading a particular employee we will pass the primary key of the item.

http://localhost:8080/employees/1

For reading all the employees from the employee resource list.

http://localhost:8080/employees/

**Request Body:** In the case of GET request, we don't send any request body.

**HTTP Method Type:** GET

**Response Body**

The response body of a GET request should contain the employee object we request for.

If we requested for a particular employee it should be like:

    {  
      "employee_id": 1,  
      "last_name": "Sarawgi",  
      "first_name": "Sachin",  
      "title": "Software Developer",  
      "address": "India",  
      "country_code": "IN"  
    }

If we requested for all employees, the response should be an array of employee

    [  
      {  
        "employee_id": 1,  
        "last_name": "Sarawgi",  
        "first_name": "Sachin",  
        "title": "Software Developer",  
        "address": "India",  
        "country_code": "IN"  
      },  
      {  
        "employee_id": 2,  
        "last_name": "Chandan",  
        "first_name": "Praveen",  
        "title": "Senior Software Developer",  
        "address": "India",  
        "country_code": "IN"  
      }  
    ]

**Response Code:** When we try to read a resource and it’s successful then the server should return 200 (OK) response code.

----------

#### Update REST API Request

To update a resource in employees entity we use an HTTP PUT request. PUT updates an already existing resource of the specified resource type.

Let’s imagine employee resource, we want to update an old employee with the new title.

**Request Format**

Request format will have always the resource name in plural format. Like here we are working on _employee_ resources the API should point to _employees_.

[http://localhost:8080/employees/](http://localhost:8080/employees/)

**Request Body**

The body will be the old employee object detail in JSON format. In the case of a PUT request, we will pass the primary key as that will be needed to identify the resource.

    {  
      "employee_id": 1,  
      "last_name": "Sarawgi",  
      "first_name": "Sachin",  
      "title": "Senior Software Developer",  
      "address": "India",  
      "country_code": "IN"  
    }

**HTTP Method Type:** PUT

**Response Body:** The response body of a PUT request is not necessary if asked we can return the updated employee object.

**Response Code:** When we try to update an old resource and it’s successful then the server should return 200 (OK) response code.

----------

#### DELETE REST API Request

To delete a particular item/row in an employee entity we use an HTTP DELETE request. It is used to remove a resource from the system.

Let’s imagine employee resource, we want to delete a particular employee from the existing lists.

We should never provide bulk delete functionality.

**Request Format**

For deleting a particular employee we will pass the primary key of the item.

[http://localhost:8080/employees/1](http://localhost:8080/employees/1)

**Request Body:** In the case of DELETE request, we don’t send any request body.

**HTTP Method Type:** DELETE

**Response Body:** The response body of a DELETE request is not necessary.

**Response Code:** When we try to delete an old resource and it’s successful then the server should return 200 (OK) response code.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ed8c5vbaw05plxl3gecq.jpeg)
### Setting Up Database and Initial NodeJs File

Create a workspace in your local machine where you will write all the code. Type `npm init` to set up the project with basic details. Run below command inside the folder.

-   Run`npm install sqlite3 --save` to use the sqlite3 package in our project
-   Run`npm install express --save` to use express package from npm

Create a file with the name `restwithnodejssqlite3.js` and write below code:

    const sqlite3 = require('sqlite3');
    const express = require("express");
    var app = express();
    
    const HTTP_PORT = 8000
    app.listen(HTTP_PORT, () => {
        console.log("Server is listening on port " + HTTP_PORT);
    });
    
    const db = new sqlite3.Database('./emp_database.db', (err) => {
        if (err) {
            console.error("Erro opening database " + err.message);
        } else {
    
            db.run('CREATE TABLE employees( \
                employee_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\
                last_name NVARCHAR(20)  NOT NULL,\
                first_name NVARCHAR(20)  NOT NULL,\
                title NVARCHAR(20),\
                address NVARCHAR(100),\
                country_code INTEGER\
            )', (err) => {
                if (err) {
                    console.log("Table already exists.");
                }
                let insert = 'INSERT INTO employees (last_name, first_name, title, address, country_code) VALUES (?,?,?,?,?)';
                db.run(insert, ["Chandan", "Praveen", "SE", "Address 1", 1]);
                db.run(insert, ["Samanta", "Mohim", "SSE", "Address 2", 1]);
                db.run(insert, ["Gupta", "Pinky", "TL", "Address 3", 1]);
            });
        }
    });

Run the file using `npm restwithnodejs.js` , it will start the server on port 8000. It will also create the employee table and insert some sample records in the database.

### GET API With NodeJS

Now the server is up and running and the table is ready with some sample database.

Next step is to query the table to get a particular employee based on the `employee_id` .

    app.get("/employees/:id", (req, res, next) => {
        var params = [req.params.id]
        db.get(`SELECT * FROM employees where employee_id = ?`, [req.params.id], (err, row) => {
            if (err) {
              res.status(400).json({"error":err.message});
              return;
            }
            res.status(200).json(row);
          });
    });
We can also write API for getting all the employee we can

    app.get("/employees", (req, res, next) => {
        db.all("SELECT * FROM employees", [], (err, rows) => {
            if (err) {
              res.status(400).json({"error":err.message});
              return;
            }
            res.status(200).json({rows});
          });
    });
  
  ### POST API With NodeJS
  After getting an employee by id, we need something by which we can insert an employee

    app.post("/employees/", (req, res, next) => {
        var reqBody = re.body;
        db.run(`INSERT INTO employees (last_name, first_name, title, address, country_code) VALUES (?,?,?,?,?)`,
            [reqBody.last_name, reqBody.first_name, reqBody.title, reqBody.address, reqBody.country_code],
            function (err, result) {
                if (err) {
                    res.status(400).json({ "error": err.message })
                    return;
                }
                res.status(201).json({
                    "employee_id": this.lastID
                })
            });
    });

### PUT API With NodeJS

Now suppose we want to update the existing employee.

    app.patch("/employees/", (req, res, next) => {
        var reqBody = re.body;
        db.run(`UPDATE employees set last_name = ?, first_name = ?, title = ?, address = ?, country_code = ? WHERE employee_id = ?`,
            [reqBody.last_name, reqBody.first_name, reqBody.title, reqBody.address, reqBody.country_code, reqBody.employee_id],
            function (err, result) {
                if (err) {
                    res.status(400).json({ "error": res.message })
                    return;
                }
                res.status(200).json({ updatedID: this.changes });
            });
    });

### DELETE API With NodeJS

Code for deleting a particular employee from the table

    app.delete("/employees/:id", (req, res, next) => {
        db.run(`DELETE FROM user WHERE id = ?`,
            req.params.id,
            function (err, result) {
                if (err) {
                    res.status(400).json({ "error": res.message })
                    return;
                }
                res.status(200).json({ deletedID: this.changes })
            });
    });
This ends our coding part, we can run the file using `node restwithnodejssqlite3.js` , try hitting the API using Postman. I hope this helps to understand the concepts of REST, CRUD, and how to write it in terms of coding using NodeJS. [The complete code for the project can be found here](https://github.com/codesprintpro/restapiusingnodejsandsqlite3).

_If you enjoyed reading this, don’t forget the like. 👏  
Thank you._

_Follow me over Medium for such articles  [@CodeSprintPro](https://medium.com/@codesprintpro)_