📖
Maissen's Grimoire
  • Maissen's Grimoire
  • Html and css grimoire
    • HTML5 Periodical Table
    • HTML Cheat Sheet
    • CSS Cheatsheets
  • Javascript Grimoire
    • JavaScript Cheat Sheet
      • Javascript Array in depth
      • Tagged Template Literals
      • Guard Clauses - The Best Way To Write Complex Conditional Logic
      • JavaScript Optional Chaining
      • JavaScript Null Coalesce
      • What Are Magic Numbers And Why Are They Bad
      • ES6/ECMAScript2015 Cheatsheet
      • First-class and Higher Order Functions: Effective Functional JavaScript
    • Useful JavaScript Tips, Tricks and Best Practices
    • Bits of code
    • Useful JavaScript libraries
      • Components
      • Animation
      • Maps
      • Helpers
      • Presentations
      • Charts
      • Games
      • Audio
      • Images
      • Video
    • Js the right way
  • Angular Grimoire
    • Angular doc
    • Getting Started
    • Angular clean architecture
    • Angular Cheat Sheet
    • TypeScript Cheat Sheet
    • My Favorite Tips and Tricks in Angular
    • NgRx: tips & tricks
    • Bits of code
      • Execute Multiple HTTP Requests in Angular
      • Authentification
        • Angular 8 JWT Authentication with HttpInterceptor and Router
      • Integrations
        • Spring Boot
          • Rest Example
            • Angular,Spring Boot,Spring Data and Rest Example(CRUD)
          • Authentification
            • Angular, Spring Boot: JWT Authentication with Spring Security example
            • Angular Spring Boot Security Oauth2
              • Spring Boot OAUTH2 Role-Based Authorization
              • Spring Boot Security Google Oauth
              • Spring Security OAuth2 User Registration
    • Most used dependency
  • Node Grimoire
    • Express.js 4 Cheatsheet
    • Useful Dependencies
    • How To Use And Write Express Middleware
    • Node.js with SQL databases
      • Node.js Token Based Authentication & Authorization example
      • Node.js Rest APIs example with Express, Sequelize & MySQL
      • Node.js Express & PostgreSQL: CRUD Rest APIs example with Sequelize
      • Sequelize
        • Sequelize Many-to-Many Association example – Node.js & MySQL
        • Sequelize One-to-Many Association example with Node.js & MySQL
    • Node.js with NOSQL databases
      • Node.js + MongoDB: User Authentication & Authorization with JWT
      • Node.js, Express & MongoDb: Build a CRUD Rest Api example
      • MongoDB One-to-One relationship tutorial with Mongoose example
      • MongoDB One-to-Many Relationship tutorial with Mongoose examples
      • MongoDB Many-to-Many Relationship with Mongoose examples
  • Upload files
    • How to upload multiple files in Node.js
    • Upload & resize multiple images in Node.js using Express, Multer, Sharp
    • Upload/store images in MySQL using Node.js, Express & Multer
    • How to upload/store images in MongoDB using Node.js, Express & Multer
  • React Grimoire
    • React Doc
    • React Grimoire
    • React Cheat Sheet
  • spring boot Grimoire
    • Getting started
    • Spring Boot, Spring Data JPA – Rest CRUD API example
    • Spring Boot Token based Authentication with Spring Security & JWT
  • Mongo Grimoire
    • MongoDb-Mongoose Cheat Sheet
  • Development tools
    • Design Patterns
  • maissen_grimoire
Powered by GitBook
On this page
  • Node.js Rest CRUD API overview
  • Create Node.js App
  • Setup Express web server
  • Configure PostgreSQL database & Sequelize
  • Initialize Sequelize
  • Define the Sequelize Model
  • Create the Controller
  • Define Routes
  • Test the APIs

Was this helpful?

  1. Node Grimoire
  2. Node.js with SQL databases

Node.js Express & PostgreSQL: CRUD Rest APIs example with Sequelize

PreviousNode.js Rest APIs example with Express, Sequelize & MySQLNextSequelize

Last updated 1 year ago

Was this helpful?

Node.js Rest CRUD API overview

We will build Rest Apis that can create, retrieve, update, delete and find Tutorials by title.

First, we start with an Express web server. Next, we add configuration for PostgreSQL database, create Tutorial model with Sequelize, write the controller. Then we define routes for handling all CRUD operations (including custom finder).

The following table shows overview of the Rest APIs that will be exported:

Methods

Urls

Actions

GET

api/tutorials

get all Tutorials

GET

api/tutorials/:id

get Tutorial by id

POST

api/tutorials

add new Tutorial

PUT

api/tutorials/:id

update Tutorial by id

DELETE

api/tutorials/:id

remove Tutorial by id

DELETE

api/tutorials

remove all Tutorials

GET

api/tutorials/published

find all published Tutorials

GET

api/tutorials?title=[kw]

find all Tutorials which title contains 'kw'

Finally, we’re gonna test the Rest Apis using Postman.

This is our project structure:

node-js-express-sequelize-postgresql-project-structure

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.

The package.json file should look like this:

{
  "name": "nodejs-express-sequelize-postgresql",
  "version": "1.0.0",
  "description": "Node.js Rest Apis with Express, Sequelize & M",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "nodejs",
    "express",
    "sequelize",
    "rest",
    "api",
    "postgresql"
  ],
  "author": "bezkoder",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "pg": "^7.17.1",
    "pg-hstore": "^2.3.3",
    "sequelize": "^5.21.3"
  }
}

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

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

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

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:

  • remove all Tutorials: destroy(where: {})

  • find all Tutorials by title: findAll({ where: { title: ... } })

These functions will be used in our Controller.

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) => {
  
};

Let’s implement these functions.

Create a new object

Create and Save a new Tutorial:

exports.create = (req, res) => {
  // Validate request
  if (!req.body.title) {
    res.status(400).send({
      message: "Content can not be empty!"
    });
    return;
  }

  // Create a Tutorial
  const tutorial = {
    title: req.body.title,
    description: req.body.description,
    published: req.body.published ? req.body.published : false
  };

  // Save Tutorial in the database
  Tutorial.create(tutorial)
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while creating the Tutorial."
      });
    });
};

Retrieve objects (with condition)

Retrieve all Tutorials/ find by title from the database:

exports.findAll = (req, res) => {
  const title = req.query.title;
  var condition = title ? { title: { [Op.iLike]: `%${title}%` } } : null;

  Tutorial.findAll({ where: condition })
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials."
      });
    });
};

We use req.query.title to get query string from the Request and consider it as condition for findAll() method.

Retrieve a single object

Find a single Tutorial with an id:

exports.findOne = (req, res) => {
  const id = req.params.id;

  Tutorial.findByPk(id)
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message: "Error retrieving Tutorial with id=" + id
      });
    });
};

Update an object

Update a Tutorial identified by the id in the request:

exports.update = (req, res) => {
  const id = req.params.id;

  Tutorial.update(req.body, {
    where: { id: id }
  })
    .then(num => {
      if (num == 1) {
        res.send({
          message: "Tutorial was updated successfully."
        });
      } else {
        res.send({
          message: `Cannot update Tutorial with id=${id}. Maybe Tutorial was not found or req.body is empty!`
        });
      }
    })
    .catch(err => {
      res.status(500).send({
        message: "Error updating Tutorial with id=" + id
      });
    });
};

Delete an object

Delete a Tutorial with the specified id:

exports.delete = (req, res) => {
  const id = req.params.id;

  Tutorial.destroy({
    where: { id: id }
  })
    .then(num => {
      if (num == 1) {
        res.send({
          message: "Tutorial was deleted successfully!"
        });
      } else {
        res.send({
          message: `Cannot delete Tutorial with id=${id}. Maybe Tutorial was not found!`
        });
      }
    })
    .catch(err => {
      res.status(500).send({
        message: "Could not delete Tutorial with id=" + id
      });
    });
};

Delete all objects

Delete all Tutorials from the database:

exports.deleteAll = (req, res) => {
  Tutorial.destroy({
    where: {},
    truncate: false
  })
    .then(nums => {
      res.send({ message: `${nums} Tutorials were deleted successfully!` });
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while removing all tutorials."
      });
    });
};

Find all objects by condition

Find all Tutorials with published = true:

exports.findAllPublished = (req, res) => {
  Tutorial.findAll({ where: { published: true } })
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials."
      });
    });
};

Define Routes

When a client sends request for an endpoint using HTTP request (GET, POST, PUT, DELETE), we need to determine how the server will reponse by setting up the routes.

These are our routes:

  • /api/tutorials: GET, POST, DELETE

  • /api/tutorials/:id: GET, PUT, DELETE

  • /api/tutorials/published: GET

Create a turorial.routes.js inside app/routes folder with content like this:

module.exports = app => {
  const tutorials = require("../controllers/tutorial.controller.js");

  var router = require("express").Router();

  // Create a new Tutorial
  router.post("/", tutorials.create);

  // Retrieve all Tutorials
  router.get("/", tutorials.findAll);

  // Retrieve all published Tutorials
  router.get("/published", tutorials.findAllPublished);

  // Retrieve a single Tutorial with id
  router.get("/:id", tutorials.findOne);

  // Update a Tutorial with id
  router.put("/:id", tutorials.update);

  // Delete a Tutorial with id
  router.delete("/:id", tutorials.delete);

  // Create a new Tutorial
  router.delete("/", tutorials.deleteAll);

  app.use('/api/tutorials', router);
};

You can see that we use a controller from /controllers/tutorial.controller.js.

We also need to include routes in server.js (right before app.listen()):

...

require("./app/routes/turorial.routes")(app);

// set port, listen for requests
const PORT = ...;
app.listen(...);

Test the APIs

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

Using Postman, we’re gonna test all the Apis above.

  1. Create a new Tutorial using POST /tutorials Api

    After creating some new Tutorials, you can check PostgreSQL table:

    testdb=# select * from tutorials;
     id |    title    |    description    | published |         createdAt          |         updatedAt
    ----+-------------+-------------------+-----------+----------------------------+----------------------------
      1 | Node Tut #1 | Tut#1 Description | f         | 2020-01-29 10:42:57.121+07 | 2020-01-29 10:42:57.121+07
      2 | Node Tut #2 | Tut#2 Description | f         | 2020-01-29 10:43:05.131+07 | 2020-01-29 10:43:05.131+07
      3 | Node Tut #3 | Tut#3 Description | f         | 2020-01-29 10:43:48.028+07 | 2020-01-29 10:43:48.028+07
      4 | Js Tut #4   | Tut#4 Desc        | f         | 2020-01-29 10:45:40.016+07 | 2020-01-29 10:45:40.016+07
      5 | Js Tut #5   | Tut#5 Desc        | f         | 2020-01-29 10:45:44.289+07 | 2020-01-29 10:45:44.289+07
  2. Retrieve all Tutorials using GET /tutorials Api

  3. Retrieve a single Tutorial by id using GET /tutorials/:id Api

  4. Update a Tutorial using PUT /tutorials/:id Api

    Check tutorials table after some rows were updated:

    testdb=# select * from tutorials;
     id |     title      |    description    | published |         createdAt          |         updatedAt
    ----+----------------+-------------------+-----------+----------------------------+----------------------------
      1 | Node Tut #1    | Tut#1 Description | f         | 2020-01-29 10:42:57.121+07 | 2020-01-29 10:42:57.121+07
      3 | Node Tut #3    | Tut#3 Description | f         | 2020-01-29 10:43:48.028+07 | 2020-01-29 10:43:48.028+07
      2 | Node Js Tut #2 | Tut#2 Description | t         | 2020-01-29 10:43:05.131+07 | 2020-01-29 10:51:55.235+07
      4 | Js Tut #4      | Tut#4 Desc        | t         | 2020-01-29 10:45:40.016+07 | 2020-01-29 10:54:17.468+07
      5 | Js Tut #5      | Tut#5 Desc        | t         | 2020-01-29 10:45:44.289+07 | 2020-01-29 10:54:20.544+07
  5. Find all Tutorials which title contains ‘js’: GET /tutorials?title=js

  6. Find all published Tutorials using GET /tutorials/published Api

  7. Delete a Tutorial using DELETE /tutorials/:id Api

    Tutorial with id=4 was removed from tutorials table:

    testdb=# select * from tutorials;
     id |     title      |    description    | published |         createdAt          |         updatedAt
    ----+----------------+-------------------+-----------+----------------------------+----------------------------
      1 | Node Tut #1    | Tut#1 Description | f         | 2020-01-29 10:42:57.121+07 | 2020-01-29 10:42:57.121+07
      3 | Node Tut #3    | Tut#3 Description | f         | 2020-01-29 10:43:48.028+07 | 2020-01-29 10:43:48.028+07
      2 | Node Js Tut #2 | Tut#2 Description | t         | 2020-01-29 10:43:05.131+07 | 2020-01-29 10:51:55.235+07
      5 | Js Tut #5      | Tut#5 Desc        | t         | 2020-01-29 10:45:44.289+07 | 2020-01-29 10:54:20.544+07
  8. Delete all Tutorials using DELETE /tutorials Api

    Now there are no rows in tutorials table:

    testdb=# select * from tutorials;
     id | title | description | published | createdAt | updatedAt
    ----+-------+-------------+-----------+-----------+-----------

helps to parse the request and create the req.body object

provides Express middleware to enable CORS with various options.

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

node-js-express-sequelize-postgresql-example-setup-server

For more details, please visit .

create a new Tutorial: (object)

find a Tutorial by id: (id)

get all Tutorials: ()

update a Tutorial by id: (data, where: { id: id })

remove a Tutorial: (where: { id: id })

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:

Or you can add Tags for each Tutorial and add Tutorials to Tag (Many-to-Many Relationship):

body-parser
cors
http://localhost:8080/
API Reference for the Sequelize constructor
create
findByPk
findAll
update
destroy
Node.js Sequelize Associations: One-to-Many example
Node.js Sequelize Associations: Many-to-Many example
node-js-express-sequelize-postgresql-crud-get-by-id
node-js-express-sequelize-postgresql-crud-update
node-js-express-sequelize-postgresql-crud-find-by-field
node-js-express-sequelize-postgresql-crud-find-published
node-js-express-sequelize-postgresql-crud-create
node-js-express-sequelize-postgresql-crud-retrieve
node-js-express-sequelize-postgresql-crud-delete
node-js-express-sequelize-postgresql-crud-delete-all