Node.js Meets PostgreSQL: Building a Scalable CRUD REST APIs

PostgreSQL and Node.js are very famous and powerful technologies for building application backends.

As per the Stack Overflow Developer Survey, Node.js is used by over 50% of developers worldwide. Similarly, PostgreSQL has been the fastest-growing database in the world for the past three years.

This article provides simple steps to write a Restful API for Create, Update, Retrieve(List), and Delete operations on Postgres using Node.js / Express.js

Prerequisites

1. npm and node.js are installed on your system
2. Postgres is installed on your system.
3. You know the basics of Node.js, Postgres, git, and npm
4. You know the basics of REST API and you have Postman or a similar client installed on your system.

Getting Started

Create Table

Here in this article, I will create CRUD APIs for eCommerce Categories. I was assuming that you have already created the Postgres database.

Open psql tool. (It comes with Postgres)

Create a new table called categories with the following DDL command on PostgreSQL.

You can also use UI tools like DBeaver to manage the database.

Initiate the Node.js project

We will use the Express.js framework.

If you use express-generator it creates a lot of extra code which we don’t need for this exercise.

Check this commit for the first version of the Express.js setup

Connect Postgres from Node.js using pg module

Install the pg module. It is a non-blocking PostgreSQL client for Node.js.

With node-postgres , we can use connection pooling to connect to the database and save resources.

The following code creates and exports the Postgres Connection pool.

This Pool object can be used by the other parts of the code to interact with the database.

Creating the Controller for CRUD operations

The controller will handle the requests coming from the Express Router. It will have various functions which will get called on different REST Endpoints.

For simplicity, I will add business logic to the controller file itself. You can add an additional abstraction layer in the form of Service classes between the Controller and the database.

I have created category.controller.js under the controllers directory under the root of the project.

We need to import the database pool here in this file because we are going to talk to the database using pool.

const db = require('../db');

//controller functions go here        

Endpoints

We will create CRUD for categories, so here are the endpoints that we will create.

1. Create Category - POST /api/category
2. Get All Categories - GET /api/category
3. Get Single Category - GET /api/category/${categoryid}
4. Update Category - PUT /api/category/${categoryid}
5. Delete Category - DELETE /api/category/${categoryid}

I had created a few entries from the table. So here is the current data in the table.

Setup the router

I will create a separate route file category.route.js under routes folder to keep the code clean.

Here we will create an Express Router and export it.

We will also import category.controller what we created above. Category Controller will have handler functions for the requests.

We need to register this router in index.js file using app.use function

Now /api/controller is the root path for all the category routes.

Create Record - POST API

This API will accept 2 strings: category title and description. As per our table definition, the title is required but the description field is optional.

Controller Function in category.controller.js

Here we need to read the title and description fields from the request body (req.body)

We will call the SQL statements using db.query which will have SQL statements with placeholders like $1, $2, etc. Values that will go inside the placeholders will be passed as an array in the second argument. See the code below.

We will use this controller function in the category.route.js file like this.

router.post('/', CategoryController.create);        

After this, we can test this endpoint using Postman or any similar tool.

You can do a quick SELECT statement to verify if it worked.

Get All Records - GET API

Now let's create an API that gets all the records from the table.

Again, we create a controller function in category.controller.js.

We can register this in the route file like this.

Test it using Postman

Get Single Record - GET API

We will create this endpoint with a path /api/category/:${categoryid}

ID would be passed as a “param” in the URL. Suppose we want to get the category with ID 5, the URL would look like api/category/5

The controller function will return the category object. If the category is not found in the database, we return HTTP status code 404 here.

Route registration looks like this.

router.get('/:id', CategoryController.getSingle);        

Now we can test with Postman like this

Let’s test once for an invalid ID.

Update Record - PUT API

Endpoint would look like /api/category/${categoryid}

It will also accept the new category title and description in the request body.

Notice the sequence of params in db.query.

Route registration is simple.

router.put('/:id', CategoryController.updateSingle);        

If you run it with Postman, you can see the result like this.

Delete a Record - DELETE API

We will keep the endpoint URL as DELETE /api/category/${category}

Here is the code to delete from the PostgresDB using Node.js.

Let’s register this route as well

router.delete('/:id', CategoryController.deleteSingle);        

Soft Deletes

Some developers use soft delete (they mark it deleted in the database but the data is there in the database). This technique is used when you want user to be able to restore the data. But it has its drawbacks, which we will discuss in some other post

Complete Code

The complete Code is available on my GitHub repository at https://github.com/mohitsehgal/nodejs-postgres-starter

Conclusion

We have kept the code very simple and to the point for understanding. We can add more features like pagination and search to the list-all record API.

We can also add input validation to avoid data corruption and appropriate error handling.

Feel free to share your feedback or questions in the comments section if you need any help with PostgreSQL or Node.js, Inbox me on LinkedIn.