CRUD REST API with Node.js, Express.js, and PostgreSQL
Tags: javascript, webdev, node, postgres
Published: 2023-06-06 12:28:04

API or Application Program Interface serves as a communication facility between services or software. In web programming, APIs are used to access available data and services.

#REST API (RESTful API)
Representational State Transfer (REST) contains standards for a web service. REST API is an API architectural style that uses the HTTP protocol to access and use data. The following are some data types that REST API has:

- `GET` — Get data
- `PUT` — Modify the state or value of data or resource
- `POST` — Create data or resource
- `DELETE` — Delete data or resource 

Which can be used to create a CRUD (Create, Read, Update, Delete) service/application.

#CRUD
When creating a service, we want some basic functionalities to manipulate the resource. These functionalities are for creating, reading, updating, and deleting data commonly referred to as CRUD.

CRUD stands for **Create**, **Read**, **Update**, and **Delete**, which is actually an application of the data types in REST API. The details are as follows:

- `CREATE` — To create data (POST)
- `READ` — To retrieve/read existing data (GET)
- `UPDATE` — To modify/update data (PUT)
- `DELETE` — To delete data (DELETE)

#Things Required

Before following this article, there are several things that need to be installed first:

- [Node.js](https://nodejs.org/en/)
- [PostgreSQL](https://www.postgresql.org/)
- [Postman](https://www.postman.com/downloads/) application (to hit the REST API Endpoint)

Installation procedures can be found in the respective websites' documentation.

#Setting up the PostgreSQL Database

Firstly, we will create a database in PostgreSQL that will be used by the service. We use `psql` program. Here are the steps.

Login using the command:

```
psql -U postgres
```
By using the above command, we have logged in to PostgreSQL as the postgres user.

```
postgres=#
```

##Creating a Database
To create a database, we use the command:

```
postgres=# CREATE DATABASE tutorial
```

The above command is used to create a database with the name tutorial. To use/connect to the tutorial database, use the command:

```
postgres=# \c tutorial
```


The prompt will change to the following:

```
tutorial=#
```
indicates that we have successfully connected to the tutorial database.

##Creating a Table

Next, create a table in the `tutorial` database. We will create a table named students in the `public` schema. Use the following command:

```
tutorial=# CREATE TABLE public.students (
    id serial NOT NULL,
    firstname varchar(40) NULL,
    lastname varchar(40) NULL,
    origin varchar(50) NULL,
    CONSTRAINT students_pkey PRIMARY KEY (id)
);
```

Use the command `\dt` to check if the table has been successfully created.

```
tutorial=# \dt
            List of relations
Schema |   Name   |  Type | Owner
-------+----------+-------+---------
public | students | table | postgres
(1 row)

```

To check the details of the table, use the command `\d` students.

```
tutorial=# \d students
Table "public. students”
Column    | Type                  | Collation | Nullable | Default
----------+-----------------------+-----------+----------+-----------------------------------------
id        | integer               |           | not null | nextval(’students_id_seq’::regclass)
firstname | character varying(40) |           |          |
lastname  | character varying(40) |           |          |
origin    | character varying(56) |           |          |

Indexes:
      "students_pkey" PRIMARY KEY, btree (id)
```

#Setting up Express.js Server

## 1. Project Structure

In the end of this article, the structure of the project will be like this :
```
tutorial
├───node_modules
│	└─── ....
├───mode
│   └───response.js
├───index.js
├───queries.js
├───package-lock.json
└───package.json
```
You can use this structure to avoid confusion.

## 2. Project Setup

**First step**, create a project directory with name `tutorial`, then initialize **Node.js** using this command:

```bash
mkdir tutorial
cd tutorial
npm init -y
```

The `npm init -y` command will generate `package.json` file with the following contents:

```json
{
  "name": "tutorial",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}
```

**Next**, install dependencies (for Express.js and PostgreSQL) using this command :

```bash
npm install express dan pg
```

After the execution finished, the dependency files will be stored inside `node_modules` folder and list of dependencies will be listed in `package.json` file like below :

```json
{
  "name": "tutorial",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "pg": "^8.7.1"
  }
}
```

Next, create the index.js file in the root folder. This file is the main file that needs to be executed to run the Node.js server. Inside the index.js file, we add the following script :

```javascript
const express = require("express");
const bodyParser = require("body-parser");

const app = express();
const port = 3000;

app.use(bodyParser.json());
app.use(
    bodyParser.urlencoded({
        extended: true
    })
)
```
The `require` function is used to all dependency to our project.

Next, we create a root URL **(/)** that returns JSON.
```javascript
app.get("/", (request, response) => {
    response.json({
        info: 'Hello world!'
    });
})
``` 

Save the file. To run the server, use this command :
```bash
node index.js
```

If the server running successfully, you will get this on your terminal :
```bash
$ node index.js
Server is running on 3000
```

If you access `http://localhost:3000` from your client app (Postman or web browser) you will get a JSON response like below :

```json
{
  "info": "Hello world!"
}
```

## 3. Connect Express with PostgreSQL

The next important step is to connect Node.js with PostgreSQL. We create a file named **queries.js** (the name doesn't have to be exactly the same as in the article, but the usage in the program should be consistent) which will be used to set up the database connection and the queries used to access and manipulate data.

The first thing we need to do in this section is to set up a Pool connection to the database that will be used. This can be done using the following script:

```javascript
const Pool = require("pg").Pool;
const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'nama_db_kamu',
    password: 'password_kamu',
    port: 5432
});
```

## 4. Create Response Class (Optional)

This is an optional step, which is my personal preference. The purpose of creating the Response class is to standardize the response or output of the API we create. The Response class that I commonly use is as follows:

```javascript
class Response {
    constructor(status = false, code = 400, message = "", data = null) {
        this.status = status;
        this.code = code;
        this.message = message;
        this.data = data;
    }
}

module.exports = Response;
```

The Response class will be used in the `queries.js` file. In this article, we will always use this Response class for JSON formatting in API responses.

```javascript
const Pool = require("pg").Pool;
const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'testing_db',
    password: 'development',
    port: 5432
});
const ResponseClass = require("./model/response") // opsional
```

# Creating CRUD Operation Functions

In this section, we will create 5 CRUD functions:
- `getStudents()` — to retrieve all student data
- `getStudentById()` — to retrieve data of a student based on ID
- `createStudent()` — to create a student's data
- `updateStudent()` — to update a student's data based on ID
- `deleteStudent()` — to delete a student's data based on ID

We will declare these five functions in the `queries.js` file, which will be accessed by `index.js`.

## GET | `getStundents()`

```javascript
const getStudents = (request, response) => {
    var responseReturn = new ResponseClass();
    pool.query('SELECT * FROM students ORDER BY id ASC', (error, results) => {
        if (error) {
            throw error
        }

        responseReturn.status = true;
        responseReturn.code = 200;
        responseReturn.message = "Success";
        responseReturn.data = results.rows;

        response.status(200).json(responseReturn);
    })
}
```

## GET | `getStudentById()`

```javascript
const getStudentById = (request, response) => {
    var responseReturn = new ResponseClass();
    const id = parseInt(request.params.id)
    pool.query('SELECT * FROM students WHERE id = $1', [id], (error, results) => {
        if (error) {
            throw error
        }
        if (results.rowCount == 0) {
            responseReturn.status = true;
            responseReturn.code = 404;
            responseReturn.message = "User not found";
            responseReturn.data = null;
        } else {
            responseReturn.status = true;
            responseReturn.code = 200;
            responseReturn.message = "Success";
            responseReturn.data = results.rows[0];
        }
        response.status(200).json(responseReturn);
    })
}
```

## POST | `createStudent()`

```javascript
const createStudent = (request, response) => {
    const { firstname, lastname, origin } = request.body;
    pool.query('INSERT INTO students (firstname, lastname, origin) VALUES ($1, $2, $3)', [firstname, lastname, origin], (error, results) => {
        if (error) {
            throw error
        }
        response.status(201).send("Student added");
    })
}
```

## PUT | `updateStudent()`

```javascript
const updateStudent = (request, response) => {
    const id = parseInt(request.params.id);
    var responseReturn = ResponseClass();
    try {
        const { firstname, lastname, origin } = request.body;
        pool.query('UPDATE students SET firstname = $1, lastname = $2, origin = $3 WHERE id = $4', [firstname, lastname, origin, id], (error, results) => {
            if (error) {
                throw error
            }

            responseReturn.status = true;
            responseReturn.code = 200;
            responseReturn.message = "User modification successed";
            responseReturn.data = null;
            response.status(200).send(responseReturn);
        })
    } catch (error) {
        responseReturn.status = false;
        responseReturn.code = 500;
        responseReturn.message = error.message;
        responseReturn.data = null
        response.status(500).json(responseReturn);
    }
}
```

The complete code for `queries.js` is as follows:

```javascript
const Pool = require("pg").Pool;
const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'testing_db',
    password: 'development',
    port: 5432
});
const ResponseClass = require("./model/response") // opsional

const getStudents = (request, response) => {
    var responseReturn = new ResponseClass();
    pool.query('SELECT * FROM students ORDER BY id ASC', (error, results) => {
        if (error) {
            throw error
        }

        responseReturn.status = true;
        responseReturn.code = 200;
        responseReturn.message = "Success";
        responseReturn.data = results.rows;

        response.status(200).json(responseReturn);
    })
}

const getStudentById = (request, response) => {
    var responseReturn = new ResponseClass();
    const id = parseInt(request.params.id)
    pool.query('SELECT * FROM students WHERE id = $1', [id], (error, results) => {
        if (error) {
            throw error
        }
        if (results.rowCount == 0) {
            responseReturn.status = true;
            responseReturn.code = 404;
            responseReturn.message = "User not found";
            responseReturn.data = null;
        } else {
            responseReturn.status = true;
            responseReturn.code = 200;
            responseReturn.message = "Success";
            responseReturn.data = results.rows[0];
        }
        response.status(200).json(responseReturn);
    })
}

const createStudent = (request, response) => {
    const { firstname, lastname, origin } = request.body;
    pool.query('INSERT INTO students (firstname, lastname, origin) VALUES ($1, $2, $3)', [firstname, lastname, origin], (error, results) => {
        if (error) {
            throw error
        }
        response.status(201).send("Student added");
    })
}

const updateStudent = (request, response) => {
    const id = parseInt(request.params.id);
    var responseReturn = new ResponseClass();
    try {
        const { firstname, lastname, origin } = request.body;
        pool.query('UPDATE students SET firstname = $1, lastname = $2, origin = $3 WHERE id = $4', [firstname, lastname, origin, id], (error, results) => {
            if (error) {
                throw error
            }

            responseReturn.status = true;
            responseReturn.code = 200;
            responseReturn.message = "User modification successed";
            responseReturn.data = null;
            response.status(200).send(responseReturn);
        })
    } catch (error) {
        responseReturn.status = false;
        responseReturn.code = 500;
        responseReturn.message = error.message;
        responseReturn.data = null
        response.status(500).json(responseReturn);
    }
}

const deleteStudent = (request, response) => {
    const id = parseInt(request.params.id)
    pool.query('DELETE FROM students WHERE id = $1', [id], (error, results) => {
        if (error) {
            throw error
        }
        response.status(201).send("Student deleted");
    })
}


module.exports = {
    getStudents,
    getStudentById,
    createStudent,
    updateStudent,
    deleteStudent
}
```

# Creating REST API Endpoints

The next step is to create the endpoints that will be used in the REST API. Endpoints are URLs that can be accessed according to their request methods (`GET`, `POST`, `PUT`, `DELETE`).

The first step is to import the functions from `queries.js` by adding the following code in `index.js`:

```javascript
const db = require('./queries');
```

Next, the code that needs to be added is as follows:

```javascript
const express = require("express");
const bodyParser = require("body-parser");

const app = express();
const port = 3000;
const db = require('./queries');

app.use(bodyParser.json());
app.use(
    bodyParser.urlencoded({
        extended: true
    })
)

app.listen(port, () => {
    console.log("Server is running on " + port);
});

app.get("/", (request, response) => {
    response.json({
        info: 'Hello world!'
    });
})
app.get("/students", db.getStudents);
app.get("/students/:id", db.getStudentById);
app.put("/students/:id", db.updateStudent);
app.post("/students", db.createStudent);
app.delete("/students/:id", db.deleteStudent);
```

Now you can run the server and access the endpoints following by correct HTTP datatype for each.

#Conclusion

Thus, we have successfully created a REST API with basic CRUD functionality. Hopefully, it will be useful 🙂