React + Node.js + Express + PostgreSQL example: Build a CRUD App

In this tutorial, I will show you how to build full-stack React + Node.js + Express + PostgreSQL example with a CRUD Application. The back-end server uses Node.js + Express for REST APIs, front-end side is a React.js client with React Router, Axios & Bootstrap.

Related Posts:
– React + Node.js Express: User Authentication with JWT example
– React File Upload with Axios and Progress Bar to Rest API

Run both projects in one place:
How to integrate React with Node.js Express on same Server/Port

React + Node.js + Express + PostgreSQL example Overview

We will build a full-stack Tutorial Application in that:

• Tutorial has id, title, description, published status.
• User can create, retrieve, update, delete Tutorials.
• There is a search box for finding Tutorials by title.

Here are screenshots of the example.

– Add a Tutorial:

– Show all objects:

– Click on Edit button to access an object:

On this Page, you can:

• change status to Published/Pending using Publish/UnPublished button
• remove the object from PostgreSQL Database using Delete button
• update this object’s details on Database with Update button

– Search objects by field ‘title’:

– Check PostgreSQL database:

testdb=# select * from tutorials;
 id |               title                |          description          | published |         createdAt          |         updatedAt
----+------------------------------------+-------------------------------+-----------+----------------------------+----------------------------
  5 | PostgreSQL Tut#1                   | Tut#1 Description             | f         | 2020-03-11 09:14:01.773+07 | 2020-03-11 09:14:01.773+07
  6 | React.js Tut#2                     | Tut#2 Description             | f         | 2020-03-11 09:15:05.629+07 | 2020-03-11 09:15:05.629+07
  8 | React Hooks Tut#4                  | Tut#4 Description             | f         | 2020-03-11 09:24:33.295+07 | 2020-03-11 09:24:33.295+07
  9 | React Express PostgreSQL Tut#5     | Tut#5 Description             | f         | 2020-03-11 09:24:53.463+07 | 2020-03-11 09:24:53.463+07
  7 | Node. Express PostgreSQL (updated) | This is Description for Tut#3 | t         | 2020-03-11 09:22:53.635+07 | 2020-03-11 09:28:45.568+07
(5 rows)

React, Node.js Express, PostgreSQL Architecture

This is architecture of our system:

– Node.js Express exports REST APIs & interacts with PostgreSQL Database using Sequelize ORM.
– React Client sends HTTP Requests and retrieves HTTP Responses using Axios, consume data on the components. React Router is used for navigating to pages.

Video

This is our React Node.js PostgreSQL CRUD application with Express & Sequelize demo:

Node.js Express Back-end

Overview

These are APIs that Node.js Express App will export:

Project Structure

– db.config.js exports configuring parameters for PostgreSQL connection & Sequelize.
– Express web server in server.js where we configure CORS, initialize & run Express REST APIs.
– Next, we add configuration for PostgreSQL database in models/index.js, create Sequelize data model in models/tutorial.model.js.
– Tutorial controller in controllers.
– Routes for handling all CRUD operations (including custom finder) in tutorial.routes.js.

Implementation

Create Node.js App

First, we create a folder:

$ mkdir nodejs-express-sequelize-postgresql
$ cd nodejs-express-sequelize-postgresql

Next, we initialize the Node.js App with a package.json file:

npm init

name: (nodejs-express-sequelize-postgresql) 
version: (1.0.0) 
description: Node.js Rest Apis with Express, Sequelize & PostgreSQL.
entry point: (index.js) server.js
test command: 
git repository: 
keywords: nodejs, express, sequelize, postgresql, rest, api
author: bezkoder
license: (ISC)

Is this ok? (yes) yes

We need to install necessary modules: express, sequelize, pg, pg-hstore and body-parser.
Run the command:

npm install express sequelize pg pg-hstore body-parser cors --save

*pg for PostgreSQL and pg-hstore for converting data into the PostgreSQL hstore format.

Setup Express web server

In the root folder, let’s create a new server.js file:

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

const app = express();

var corsOptions = {
  origin: "http://localhost:8081"
};

app.use(cors(corsOptions));

// parse requests of content-type - application/json
app.use(bodyParser.json());

// parse requests of content-type - application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: true }));

// simple route
app.get("/", (req, res) => {
  res.json({ message: "Welcome to bezkoder application." });
});

// set port, listen for requests
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}.`);
});

What we do are:
– import express, body-parser and cors modules:

• Express is for building the Rest apis
• body-parser helps to parse the request and create the req.body object
• cors provides Express middleware to enable CORS with various options.

– create an Express app, then add body-parser and cors middlewares using app.use() method. Notice that we set origin: http://localhost:8081.
– define a GET route which is simple for test.
– listen on port 8080 for incoming requests.

Now let’s run the app with command: node server.js.
Open your browser with url http://localhost:8080/, you will see:

Yeah, the first step is done. We’re gonna work with Sequelize in the next section.

Configure PostgreSQL database & Sequelize

In the app folder, we create a separate config folder for configuration with db.config.js file like this:

module.exports = {
  HOST: "localhost",
  USER: "postgres",
  PASSWORD: "123",
  DB: "testdb",
  dialect: "postgres",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

First five parameters are for PostgreSQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:

• max: maximum number of connection in pool
• min: minimum number of connection in pool
• idle: maximum time, in milliseconds, that a connection can be idle before being released
• acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Initialize Sequelize

We’re gonna initialize Sequelize in app/models folder that will contain model in the next step.

Now create app/models/index.js with the following code:

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);

module.exports = db;

Don’t forget to call sync() method in server.js:

...
const app = express();
app.use(...);

const db = require("./app/models");
db.sequelize.sync();

...

In development, you may need to drop existing tables and re-sync database. Just use force: true as following code:

db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
});

Define the Sequelize Model

In models folder, create tutorial.model.js file like this:

module.exports = (sequelize, Sequelize) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING
    },
    published: {
      type: Sequelize.BOOLEAN
    }
  });

  return Tutorial;
};

This Sequelize Model represents tutorials table in PostgreSQL database. These columns will be generated automatically: id, title, description, published, createdAt, updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them:

• create a new Tutorial: create(object)
• find a Tutorial by id: findByPk(id)
• get all Tutorials: findAll()
• update a Tutorial by id: update(data, where: { id: id })
• remove a Tutorial: destroy(where: { id: id })
• remove all Tutorials: destroy(where: {})
• find all Tutorials by title: findAll({ where: { title: ... } })

These functions will be used in our Controller.

We can improve the example by adding Comments for each Tutorial. It is the One-to-Many Relationship and I write a tutorial for this at:
Node.js Sequelize Associations: One-to-Many example

Or you can add Tags for each Tutorial and add Tutorials to Tag (Many-to-Many Relationship):
Node.js Sequelize Associations: Many-to-Many example

Create the Controller

Inside app/controllers folder, let’s create tutorial.controller.js with these CRUD functions:

• create
• findAll
• findOne
• update
• delete
• deleteAll
• findAllPublished

const db = require("../models");
const Tutorial = db.tutorials;
const Op = db.Sequelize.Op;

// Create and Save a new Tutorial
exports.create = (req, res) => {
  
};

// Retrieve all Tutorials from the database.
exports.findAll = (req, res) => {
  
};

// Find a single Tutorial with an id
exports.findOne = (req, res) => {
  
};

// Update a Tutorial by the id in the request
exports.update = (req, res) => {
  
};

// Delete a Tutorial with the specified id in the request
exports.delete = (req, res) => {
  
};

// Delete all Tutorials from the database.
exports.deleteAll = (req, res) => {
  
};

// Find all published Tutorials
exports.findAllPublished = (req, res) => {
  
};

You can continue with step by step to implement this Node.js Express App in the post:
Node.js Express & PostgreSQL: CRUD Rest APIs example with Sequelize

Run the Node.js Express Server

Run our Node.js application with command: node server.js.

React.js Front-end

Overview

– The App component is a container with React Router. It has navbar that links to routes paths.

– TutorialsList component gets and displays Tutorials.
– Tutorial component has form for editing Tutorial’s details based on :id.
– AddTutorial component has form for submission new Tutorial.

– These Components call TutorialDataService methods which use axios to make HTTP requests and receive responses.

Or you can use React with Redux:

More details at: React Redux CRUD App example with Rest API

Technology

• React 18/17
• react-router-dom 6
• axios 0.27.2
• bootstrap 4

Project Structure

– package.json contains 4 main modules: react, react-router-dom, axios & bootstrap.
– App is the container that has Router & navbar.
– There are 3 components: TutorialsList, Tutorial, AddTutorial.
– http-common.js initializes axios with HTTP base Url and headers.
– TutorialDataService has methods for sending HTTP requests to the Apis.
– .env configures port for this React CRUD App.

For Typescript version:

Please visit:
React Typescript CRUD example with Web API

Implementation

Setup React.js Project

Open cmd at the folder you want to save Project folder, run command:
npx create-react-app react-crud

After the process is done. We create additional folders and files like the following tree:

---

public

src

components

add-tutorial.component.js

tutorial.component.js

tutorials-list.component.js

services

tutorial.service.js

App.css

App.js

index.js

package.json

---

Import Bootstrap to React CRUD App

Run command: npm install bootstrap.

Open src/App.js and modify the code inside it as following-

import React, { Component } from "react";
import "bootstrap/dist/css/bootstrap.min.css";

class App extends Component {
  render() {
    // ...
  }
}

export default App;

Add React Router to React CRUD App

– Run the command: npm install react-router-dom.
– Open src/index.js and wrap App component by BrowserRouter object.

import React from "react";
import { createRoot } from "react-dom/client";
import { BrowserRouter } from "react-router-dom";

import App from "./App";

const container = document.getElementById("root");
const root = createRoot(container);

root.render(
  <BrowserRouter>
    <App />
  </BrowserRouter>
);

Add Navbar to React CRUD App

Open src/App.js, this App component is the root container for our application, it will contain a navbar, and also, a Routes object with several Route. Each Route points to a React Component.

import React, { Component } from "react";
...

class App extends Component {
  render() {
    return (
      <div>
        <nav className="navbar navbar-expand navbar-dark bg-dark">
          <a href="/tutorials" className="navbar-brand">
            bezKoder
          </a>
          <div className="navbar-nav mr-auto">
            <li className="nav-item">
              <Link to={"/tutorials"} className="nav-link">
                Tutorials
              </Link>
            </li>
            <li className="nav-item">
              <Link to={"/add"} className="nav-link">
                Add
              </Link>
            </li>
          </div>
        </nav>

        <div className="container mt-3">
          <Routes>
            <Route path="/" element={<TutorialsList/>} />
            <Route path="/tutorials" element={<TutorialsList/>} />
            <Route path="/add" element={<AddTutorial/>} />
            <Route path="/tutorials/:id" element={<Tutorial/>} />
          </Routes>
        </div>
      </div>
    );
  }
}

export default App;

Initialize Axios for React CRUD HTTP Client

Let’s install axios with command: npm install axios.
Under src folder, we create http-common.js file with following code:

import axios from "axios";

export default axios.create({
  baseURL: "http://localhost:8080/api",
  headers: {
    "Content-type": "application/json"
  }
});

You can change the baseURL that depends on REST APIs url that your Server configures.

Create Data Service

In this step, we’re gonna create a service that uses axios object above to send HTTP requests.

services/tutorial.service.js

import http from "../http-common";

class TutorialDataService {
  getAll() {
    return http.get("/tutorials");
  }

  get(id) {
    return http.get(`/tutorials/${id}`);
  }

  create(data) {
    return http.post("/tutorials", data);
  }

  update(id, data) {
    return http.put(`/tutorials/${id}`, data);
  }

  delete(id) {
    return http.delete(`/tutorials/${id}`);
  }

  deleteAll() {
    return http.delete(`/tutorials`);
  }

  findByTitle(title) {
    return http.get(`/tutorials?title=${title}`);
  }
}

export default new TutorialDataService();

We call axios get, post, put, delete method corresponding to HTTP Requests: GET, POST, PUT, DELETE to make CRUD Operations.

Create React Components/Pages

Now we’re gonna build 3 components corresponding to 3 Routes defined before.

• Add new Item
• List of items
• Item details

You can continue with step by step to implement this React App in the post:
– React.js CRUD example to consume Web API
– or React Hooks CRUD example to consume Web API

Using React with Redux:
– React Redux CRUD example with Rest API
– React Hooks + Redux: CRUD example with Rest API

For Typescript version:
React Typescript CRUD example to consume Web API

Run React CRUD App

You can run our App with command: npm start.
If the process is successful, open Browser with Url: http://localhost:8081/ and check it.

Source Code

You can find Github source code for this tutorial at: React + Node App Github

Conclusion

Today we have an overview of React.js + Node.js Express + PostgreSQL example when building a full-stack CRUD App.

We also take a look at client-server architecture for REST API using Express & Sequelize ORM, as well as React.js project structure for building a front-end app to make HTTP requests and consume responses.

Next tutorials show you more details about how to implement the system (including source code):
– Back-end
– Front-end:

• Using React Components
• Using React Typescript Components
• Using React Redux
• Using React Hooks
• Using React Hooks + Redux
• Using React with Material UI

You will want to know how to run both projects in one place:
How to integrate React with Node.js Express on same Server/Port

With Pagination:
React Pagination with API using Material-UI

Or Serverless with Firebase:
– React Firebase CRUD with Realtime Database
– React Firestore CRUD App example | Firebase Cloud Firestore

Happy learning, see you again!

Further Reading

• https://www.npmjs.com/package/express
• https://github.com/sequelize/sequelize
• https://www.npmjs.com/package/pg
• React Component