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

bezkoder Node.js

Express is one of the most popular web frameworks for Node.js that supports routing, middleware, view system… Sequelize is a promise-based Node.js ORM that supports the dialects for PostgreSQL, MySQL, SQL Server… In this tutorial, I will show you step by step to build Node.js Restful CRUD API using Express, Sequelize with PostgreSQL database.

You should install PostgreSQL in your machine first. The installation instructions can be found at Official PostgreSQL installation manual.

Related Post: Node.js Express Pagination with PostgreSQL example
Typescript: Node.js Typescript Rest API with Postgres example

Fullstack:
– Vue.js + Node.js + Express + PostgreSQL example
– Angular 8 + Node.js Express + PostgreSQL example
– Angular 10 + Node.js Express + PostgreSQL example
– Angular 11 + Node.js Express + PostgreSQL example
– Angular 12 + Node.js Express + PostgreSQL example
– Angular 13 + Node.js Express + PostgreSQL example
– Angular 14 + Node.js Express + PostgreSQL example
– Angular 15 + Node.js Express + PostgreSQL example
– Angular 16 + Node.js Express + PostgreSQL example
– React + Node.js + Express + PostgreSQL example

Security: Node.js JWT Authentication & Authorization with PostgreSQL example


Node.js PostgreSQL CRUD Rest 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

Demo Video

This is our Node.js PostgreSQL CRUD example using Express & Sequelize application demo, test Rest Apis with Postman.

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.
Run the command:

npm install express sequelize pg pg-hstore 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": {
    "cors": "^2.8.5",
    "express": "^4.18.2",
    "pg": "^8.11.0",
    "pg-hstore": "^2.3.4",
    "sequelize": "^6.32.0"
  }
}

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(express.json());

// parse requests of content-type - application/x-www-form-urlencoded
app.use(express.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 and cors modules:

  • Express is for building the Rest apis
  • cors provides Express middleware to enable CORS with various options.

– create an Express app, then add body-parser (json, urlencoded) 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:

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

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()
  .then(() => {
    console.log("Synced db.");
  })
  .catch((err) => {
    console.log("Failed to sync db: " + err.message);
  });

...

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

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 => {
      if (data) {
        res.send(data);
      } else {
        res.status(404).send({
          message: `Cannot find Tutorial with id=${id}.`
        });
      }
    })
    .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


    2. node-js-express-sequelize-postgresql-crud-create

    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. node-js-express-sequelize-postgresql-crud-retrieve

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


    4. node-js-express-sequelize-postgresql-crud-get-by-id

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


    5. node-js-express-sequelize-postgresql-crud-update

    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. node-js-express-sequelize-postgresql-crud-find-by-field

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


    7. node-js-express-sequelize-postgresql-crud-find-published

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


    8. node-js-express-sequelize-postgresql-crud-delete

    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


    9. node-js-express-sequelize-postgresql-crud-delete-all

    Now there are no rows in tutorials table:

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

You can use the Simple HTTP Client using Axios to check it.

axios-request-example-get-post-put-delete

Or: Simple HTTP Client using Fetch API

Conclusion

Today, we’ve learned how to create Node.js Rest Apis with an Express web server to interact with PostgreSQL database. We also know way to add configuration for PostgreSQL database & Sequelize, create a Sequelize Model, write a controller and define routes for handling all CRUD operations.

For Server-side Pagination:
Node.js Express Pagination with PostgreSQL example

You can also find how to build a fullstack system with this app as backend server in the posts:
– Vue.js + Node.js + Express + PostgreSQL example
– Angular 8 + Node.js Express + PostgreSQL example
– Angular 10 + Node.js Express + PostgreSQL example
– Angular 11 + Node.js Express + PostgreSQL example
– Angular 12 + Node.js Express + PostgreSQL example
– Angular 13 + Node.js Express + PostgreSQL example
– Angular 14 + Node.js Express + PostgreSQL example
– Angular 15 + Node.js Express + PostgreSQL example
– Angular 16 + Node.js Express + PostgreSQL example
– React + Node.js + Express + PostgreSQL example

Or Security: Node.js JWT Authentication & Authorization with PostgreSQL example

Happy learning! See you again.

Further Reading

Source code

You can find the complete source code for this example on Github.

If you want to add Comments for each Tutorial. It is the One-to-Many Relationship there is a tutorial:
Node.js Sequelize Associations: One-to-Many example
(the tutorial uses MySQL but you can easily change the configuration for PostgreSQL)

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

Typescript: Node.js Typescript Rest API with Postgres example

70 thoughts to “Node.js Express & PostgreSQL: CRUD Rest APIs example with Sequelize”

  1. @Bezkoder
    Hi, very nice Tutorials ! However I have the same issue as Emmanuel John,
    Testing API end point with postman is causing the response tab to load forever without getting a response.
    I couldn’t figure how to send img over this chat box so I made a weTransfer link : https://we.tl/t-Aj2QO5f1P7

    Any help would be greatly appreciated !

    Tks

  5. Hi, very thorough tutorial. I’m having an unusual issue when sending the POST statement in Postman: TypeError: Cannot read property 'title' of undefined… Other than naming my db ‘tutorialdb’ (yours was named ‘testdb’) everything else was as you posted (except for the typo mentioned earlier). I also used psql to try and insert a record into the tutorials table ( INSERT INTO tutorials (“title”, “description”) VALUES (“Node Tutorial #1”, “Tutorial #1 Description”) ) and got back an error that “Node Tutorial #1” is not a column name, which is weird. Its like there’s some kind of issue with the column “title”. Any thoughts? Also the column “title” is defined as VARCHAR(255).

  6. Sir please help
    I am getting this error
    (node:5188) UnhandledPromiseRejectionWarning: SequelizeConnectionError: password authentication failed for user “root”
    at Client._connectionCallback (D:\crud\nodejs-express-sequelize-postgresql\node_modules\sequelize\lib\dialects\postgres\connection-manager.js:184:24)
    at Client._handleErrorWhileConnecting (D:\crud\nodejs-express-sequelize-postgresql\node_modules\pg\lib\client.js:305:19)
    at Client._handleErrorMessage (D:\crud\nodejs-express-sequelize-postgresql\node_modules\pg\lib\client.js:325:19)
    at Connection.emit (events.js:400:28)
    at D:\crud\nodejs-express-sequelize-postgresql\node_modules\pg\lib\connection.js:114:12
    at Parser.parse (D:\crud\nodejs-express-sequelize-postgresql\node_modules\pg-protocol\dist\parser.js:40:17)
    at Socket. (D:\crud\nodejs-express-sequelize-postgresql\node_modules\pg-protocol\dist\index.js:11:42)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:290:12)
    at readableAddChunk (internal/streams/readable.js:265:9)
    (Use `node –trace-warnings …` to show where the warning was created)
    (node:5188) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `–unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
    (node:5188) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

    1. Hi, you need to find how to setup your PostgreSQL database first. Then use the credentials in the project.

  7. Hello. I am having trouble when doing GET, POST, PUT etc. It seems the server is not responding to the requests.

  8. C:\Users\YOUMIR\Desktop\Node-Rest-API\node_modules\sequelize\lib\sequelize.js:281
    throw new Error(‘Dialect needs to be explicitly supplied as of v4.0.0’);
    ^

    Error: Dialect needs to be explicitly supplied as of v4.0.0
    at new Sequelize (C:\Users\YOUMIR\Desktop\Node-Rest-API\node_modules\sequelize\lib\sequelize.js:281:13)
    at Object. (C:\Users\YOUMIR\Desktop\Node-Rest-API\models\building.models.js:3:17)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
    at Module.load (internal/modules/cjs/loader.js:928:32)
    at Function.Module._load (internal/modules/cjs/loader.js:769:14)
    at Module.require (internal/modules/cjs/loader.js:952:19)
    at require (internal/modules/cjs/helpers.js:88:18)
    at Object. (C:\Users\YOUMIR\Desktop\Node-Rest-API\models\index.js:21:16)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)
    [nodemon] app crashed – waiting for file changes before starting…
    Hi I have thi problem can you help me with it?

    1. Hi, you need to indicate dialect in db.config.js:

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

  9. Hello,
    Could you tell us why there are twice “db.Sequelize = Sequelize;
    db.sequelize = sequelize;” in the “Initialize Sequelize” section ?

    Regards,

    1. Hi, you can think it like OOP: Class (Sequelize) and instance of the Class (sequelize).

  12. Hello the tutorial very good.
    Just a detail in the tutorial.controller.js file in line 3:
    const Op = db.Sequelize.Op;
    with capital S

      1. Hi,

        Also from my side. Thank you for your work.

        I also have an issue with creating the tables. I have double checked the db.config.js all seems to be right but no tables are being created. Anything else i can do? Is there a way to test the DB connection?

        Thank you.

      2. I tried adding this to the server.js file:

        db.sequelize
        .authenticate()
        .then(() => {
        console.log(‘Connection has been established successfully.’);
        })
        .catch(err => {
        console.error(‘Unable to connect to the database:’, err);
        });

        but do not get any console output

  15. Can you help me for that issue:

    PS C:\EasyPHP-Devserver-17\eds-www\projectCR> node server.js
    internal/modules/cjs/loader.js:883
    throw err;
    ^

    Error: Cannot find module ‘./app/models’
    Require stack:
    – C:\EasyPHP-Devserver-17\eds-www\projectCR\server.js
    at Function.Module._resolveFilename (internal/modules/cjs/loader.js:880:15)
    at Function.Module._load (internal/modules/cjs/loader.js:725:27)
    at Module.require (internal/modules/cjs/loader.js:952:19)
    at require (internal/modules/cjs/helpers.js:88:18)
    at Object. (C:\EasyPHP-Devserver-17\eds-www\projectCR\server.js:19:12)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
    at Module.load (internal/modules/cjs/loader.js:928:32)
    at Function.Module._load (internal/modules/cjs/loader.js:769:14)
    at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:72:12) {
    code: ‘MODULE_NOT_FOUND’,
    requireStack: [ ‘C:\\EasyPHP-Devserver-17\\eds-www\\projectCR\\server.js’ ]
    }

  16. First off, thanks so much!

    I’m having problems checking the api with Postman though…

    I can visit localhost:8080 and get the message, and there are no errors when running the server,

    (> node server.js

    (node:7460) [SEQUELIZE0004] DeprecationWarning: A boolean value was passed to options.operatorsAliases. This is a no-op with v5 and should be removed.
    (Use `node –trace-deprecation …` to show where the warning was created)
    Server is running on port 8080.
    Executing (default): DROP TABLE IF EXISTS “tutorials” CASCADE;
    Executing (default): CREATE TABLE IF NOT EXISTS “tutorials” (“id” SERIAL , “title” VARCHAR(255), “description” VARCHAR(255), “published” BOOLEAN, “createdAt” TIMESTAMP WITH TIME ZONE NOT NULL, “updatedAt” TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (“id”));
    Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = ‘r’ and t.relname = ‘tutorials’ GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
    Executing (default): DROP TABLE IF EXISTS “tutorials” CASCADE;
    Executing (default): CREATE TABLE IF NOT EXISTS “tutorials” (“id” SERIAL , “title” VARCHAR(255), “description” VARCHAR(255), “published” BOOLEAN, “createdAt” TIMESTAMP WITH TIME ZONE NOT NULL, “updatedAt” TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (“id”));
    Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = ‘r’ and t.relname = ‘tutorials’ GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
    Drop and re-sync db.
    )

    but when I try to Post via Postman it just keeps trying and trying, but nothing sends.

    I added Content-Type: application/json to the header and copied the body…

    Is there anything else I can check?

    https://imgur.com/HJr8Xci

  19. Can you please also make a brief descriptive document like above on sign up and login with Node, express and PostgreSQL with sequelize ORM.

  20. How to provide the Postgres SQL Schema name here in this tutorial. I am totally new in this area. So please excuse me if it’s a silly question.

  23. Thanks for this tutorial, and the others in your series! They are a great way to learn about Node and related technologies.

    When first running this with Postman, I tried to retrieve a row with an invalid primary key (GET http://localhost:8080/api/tutorials/99), expecting a 404 response that is typical of REST APIs when there is no data. Unfortunately, the Tutorial.findByPk(id) call returns null data instead of an error, which causes a 200 response with no body. I fixed this by changing the text in the “then(data …” block like this:

    Tutorial.findByPk(id)
    .then(data => {
    if (data == null) {
    res.status(404).send({
    message: “No such Tutorial with id=” + id
    });
    } else {
    res.send(data);
    }
    }) …

    Looks like the delete and update should also be modified to return a 404 status if nothing was deleted or updated, respectively.

  24. HIe, super tutorial help me alot but everything is okay the spread operation is giving me syntaxtical err
    …
    require(“./app/routes/turorial.routes”)(app);
    // set port, listen for requests
    const PORT = …;
    app.listen(…);

    err:
    F:\Partices\nodejs-express-sequelize-postgresql\server.js:5
    const app = express();
    ^^^^^

    SyntaxError: Unexpected token ‘const’
    at wrapSafe (internal/modules/cjs/loader.js:1047:16)
    at Module._compile (internal/modules/cjs/loader.js:1097:27)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1153:10)
    at Module.load (internal/modules/cjs/loader.js:977:32)
    at Function.Module._load (internal/modules/cjs/loader.js:877:14)
    at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:74:12)
    at internal/main/run_main_module.js:18:47
    [nodemon] app crashed – waiting for file changes before starting…

    1. Hi, please read the github source code. There is no spread operator in server.js.
      ... is just for making code lean.

  25. Hello!

    Did anyone had issues with the sync? For some reason sequelize is not creating the tables

  26. Ah, as with most novice errors, there was a simple fix that had already been outlined. Adding { force: true } to db sync() snippet (as outlined in the tutorial) in server.js fixed the problem!

  27. I’m running into the same problem as Emmanuel John. Server.js will start successfully but includes the following warning text:

    Executing (default): CREATE TABLE IF NOT EXISTS “tutorials” (“id” SERIAL , “title” VARCHAR(255), “description” VARCHAR(255), “published” BOOLEAN, “createdAt” TIMESTAMP WITH TIME ZONE NOT NULL, “updatedAt” TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (“id”));
    Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = ‘r’ and t.relname = ‘tutorials’ GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
    Executing (default): SELECT “id”, “title”, “description”, “published”, “createdAt”, “updatedAt” FROM “tutorials” AS “tutorial”;

    Using the POST method in Postman returns 400 error:
    {
    “message”: “Content can not be empty!”
    }

    This happens on my Tutorial. I downloaded the Bezkoder version from GIT to ensure I didn’t have a syntax error but the problem (and Postman result) are identical in both builds!

    I’ll keep looking but any help would be appreciated.

    1. Hi, you should send POST request with 'Content-type': 'application/json' in the Header.

  28. Thanks so much for this awesome tutorial! 🙂 When playing it through i’ve noticed some small “fixes”.

    1. there is a typo: “turorial” => “tutorial”
    2. for me i couldn’t post to the routes with a raw body in postman (as suggested in the screenshots). I had to use the “x-www-form-urlencoded” option. (which makes sense i guess because we set “app.use(bodyParser.urlencoded({ extended: true })) in server.js”)

    the rest worked like a charm! really a great tutorial!

  29. Thank you so much for these tutorials, bezkoder! I am a newbie in fullstack, but I followed this guide for back-end and this (https://github.com/bezkoder/react-crud-web-api) for front-end. Everything is working great on localhost.

    However, when I try to access the site from local area network or internet, the back-end connection does not seem to work due to CORS. My web browser console prints this error:

    Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at http://localhost:8080/api/tutorials. (Reason: CORS request did not succeed).

    I have looked through hours of different tutorials, but I still seem to get the same error. Could you please help me? 🙂

      1. Hi, thank you but it does not seem to work… To replicate the problem, I used your repositories:

        $ git clone https://github.com/bezkoder/react-crud-web-api.git
        $ git clone https://github.com/bezkoder/node-express-sequelize-postgresql.git

        I disabled nginx just to be sure.

        Then I
        1) set up the PORT=8081 as in the github readme
        2) did $ npm install in both the cloned repository directories
        3) changed PostgreSQL credentials to match my own in db.config.js
        4) commented the line you designated in the post above in server.js
        5) started the backend using $ node server.js (database connection is working)
        6) started the frontend using $ npm start (this is now running in :8081)
        7) navigated to 178.xx.xx.xx:8081 with Firefox

        I can see the frontend, but the backend connection does not seem to fetch/add/delete any data. The Firefox console output reads:

        Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at http://localhost:8080/api/tutorials. (Reason: CORS request did not succeed).

        Error: “Network Error”
        createError createError.js:16
        handleError xhr.js:83

  30. @bezcoder after setting up all the requirement for the source code, I got the log below in my linux cmd prompt.
    Testing API end point with postman is causing the response tab to load forever without getting a response.

    Server is running on port 8080.
    Executing (default): CREATE TABLE IF NOT EXISTS “tutorials” (“id” SERIAL , “title” VARCHAR(255), “description” VARCHAR(255), “published” BOOLEAN, “createdAt” TIMESTAMP WITH TIME ZONE NOT NULL, “updatedAt” TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (“id”));
    Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = ‘r’ and t.relname = ‘tutorials’ GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;

      1. Hi, just run the front-end application, it will automatically call back-end api 🙂

  32. Thank for this Node.js & Postgres tutorial. You always make many helpful things for people. 🙂

  33. Hi, I appreciate your valuable post and sharing your knowledge and experiences.
    But I am having a problem at “node server.js”, which give me below error message..
    Do you know how to handle this? I have run postgresql by “brew services start postgresql”

    “`
    (node:72681) [SEQUELIZE0004] DeprecationWarning: A boolean value was passed to options.operatorsAliases. This is a no-op with v5 and should be removed.
    Server is running on port 8080.
    Unhandled rejection SequelizeConnectionError: password authentication failed for user “postgres”
    at /Users/xeedlab/nodeJsProjects/nodejs-express-sequelize-postgresql/node_modules/sequelize/lib/dialects/postgres/connection-manager.js:182:24
    at Connection.connectingErrorHandler (/Users/xeedlab/nodeJsProjects/nodejs-express-sequelize-postgresql/node_modules/pg/lib/client.js:194:14)
    at Connection.emit (events.js:311:20)
    at Socket. (/Users/xeedlab/nodeJsProjects/nodejs-express-sequelize-postgresql/node_modules/pg/lib/connection.js:134:12)
    at Socket.emit (events.js:311:20)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)
    “`

    plus, do I have to create DB referring db.config.js file?
    I have not created any db or user,,

    I have just started my career here,, so there are many thing i don’t know.
    Your help will be greatly appreciated

    Thank you!

    1. Yes, you should create db first, the table will be generated automatically by Sequelize 🙂 .

      1. Hi, I made a user ‘postgres’ and database ‘testdb’ by myself, and now the both your front-end, back-end projects work fine! Thank you for your reply and your post again:)

      2. Would it be possible to learn how to do that using sequelize `sequelize db:create` and migrating `sequelize db:migrate`
        I think that would be really helpful to add to this tutorial.
        Great job by the way. Thank you for sharing your knowledge!

    2. To fix the deprecation warning, change “operatorsAliases: false” inside models/index.js to “operatorsAliases: 0”

    3. Hi there.
      You must change operatorsAliases: 0, (false to zero). That will fix that problem

  34. Hi bezkoder you have done a very good job!
    Thanks for the great tutorials.

    Don’t stop.

  36. Thanks very much for your reply. It’s working now after correctly installing postgres on my PC.

  37. Just following up with more details, the Terminal is showing the following messages;

    Server is running on port 8080.
Unhandled rejection SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:5432
    at C:\SuperActive\api\node_modules\sequelize\lib\dialects\postgres\connection-manager.js:170:24
    at Connection.connectingErrorHandler (C:\SuperActive\api\node_modules\pg\lib\client.js:194:14)
    at Connection.emit (events.js:223:5)
    at Socket.reportStreamError (C:\SuperActive\api\node_modules\pg\lib\connection.js:73:10)
    at Socket.emit (events.js:223:5)
    at emitErrorNT (internal/streams/destroy.js:92:8)
    at emitErrorAndCloseNT (internal/streams/destroy.js:60:3)
    at processTicksAndRejections (internal/process/task_queues.js:81:21)

    The above is related to one section of the server.js file below, when the below section is removed, there is no error shown above.

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

    Also, the below section was not included in the server.js file;

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

    because I wasn’t sure if, it was meant to be in there from the instructions.

    Thanks
    });

    1. Hi, for “connect ECONNREFUSED”, please make sure that you’ve run PostgreSQL on your PC first.

      We must run db.sequelize.sync(...) to make Sequelize work with the database. When you remove it, there is no error shown because there was no connection established with PostgreSQL that wasn’t run.

      1. Hi Bezkoder, thanks for the awesome detailed tutorial. I am facing the same issue. I have ran PostgreSQL on my Mac already. I was able to create tables for other projects. But keep getting this error. Any advice?

    2. Hi Terry,

      Pls check your connection details for PG DB, maybe the password for PG admin/superuser is not same as the one you provided in the config JS file.

  38. Thanks for sharing the tutorials.

    Can you help with an error message I am getting when POST-ing an API request –
    {
    message”: “connect ECONNREFUSED 127.0.0.1:5432”
    }

    Thank advance for your help.

Comments are closed to reduce spam. If you have any question, please send me an email.