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
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
– React + Node.js + Express + PostgreSQL example
Security: Node.js JWT Authentication & Authorization with PostgreSQL example
Contents
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:
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.17.1",
"pg": "^8.7.3",
"pg-hstore": "^2.3.4",
"sequelize": "^6.21.1"
}
}
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:
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 poolmin
: minimum number of connection in poolidle
: maximum time, in milliseconds, that a connection can be idle before being releasedacquire
: 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.
- Create a new Tutorial using
POST /tutorials
Api - Retrieve all Tutorials using
GET /tutorials
Api - Retrieve a single Tutorial by id using
GET /tutorials/:id
Api - Update a Tutorial using
PUT /tutorials/:id
Api - Find all Tutorials which title contains ‘js’:
GET /tutorials?title=js
- Find all published Tutorials using
GET /tutorials/published
Api - Delete a Tutorial using
DELETE /tutorials/:id
Api - Delete all Tutorials using
DELETE /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
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
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
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.
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
– React + Node.js + Express + PostgreSQL example
Or Security: Node.js JWT Authentication & Authorization with PostgreSQL example
Happy learning! See you again.
Further Reading
- Express.js Routing
- https://www.npmjs.com/package/express
- https://www.npmjs.com/package/body-parser
- https://www.npmjs.com/package/pg
- Tutorials and Guides for Sequelize
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
@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
really useful tutorial.
Thank you for this…. could you also show how to implement seed file here?
arigatou
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).
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.
Hi, you need to find how to setup your PostgreSQL database first. Then use the credentials in the project.
Hello. I am having trouble when doing GET, POST, PUT etc. It seems the server is not responding to the requests.
Hi, you need to show more details about the issue.
Start the application in vscode… “nodemon server” or node server
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?
Hi, you need to indicate dialect in db.config.js:
Hello,
Could you tell us why there are twice “db.Sequelize = Sequelize;
db.sequelize = sequelize;” in the “Initialize Sequelize” section ?
Regards,
Hi, you can think it like OOP: Class (
Sequelize
) and instance of the Class (sequelize
).This is the best site to learn Node.js and backend…
Very good tutorial. Thank you bezkoder.
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
Hi,
For People having connection issues with the database, there seems to be a Sequelize/NodeJS Issue with NodeJS versions 14 and higher.
I have downgraded to NodeJs v.12 and now it works.
https://github.com/sequelize/sequelize/issues/12274
I have to say great job. I have a question if I can because I learn your code. I downloaded the project https://github.com/bezkoder/node-express-sequelize-postgresql and I managed to run it, but unfortunately, despite the fact that I have such a database, I do not create tables, do you know why this is happening?
Hi, please check your database configuration in db.config.js 🙂
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.
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
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’ ]
}
Hi, please check where you put server.js file along with app folder at the same place.
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
Many thanks!
Great tutorial , i have just found the initial know how to develop my first NodeJS App .
Can you please also make a brief descriptive document like above on sign up and login with Node, express and PostgreSQL with sequelize ORM.
Hi, you can find instruction in the post:
Node.js Token Based Authentication & Authorization example
You only need to change database configurarion in db.config.js.
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.
Very Thank you
Thank You so MUCH !!
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.
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…
Hi, please read the github source code. There is no spread operator in server.js.
...
is just for making code lean.Hello!
Did anyone had issues with the sync? For some reason sequelize is not creating the tables
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!
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.
Hi, you should send POST request with
'Content-type': 'application/json'
in the Header.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!
I needed to use “x-www-form-urlencoded” option in Postman also! Thanks for mentioning this – others might encounter the same issue while getting the API server to work.
Also, this tutorial is missing how to install Postgres for those who needed. Some extra steps if you’re on Mac and using Homebrew like me. See here https://stackoverflow.com/questions/7975556 and also here https://stackoverflow.com/questions/15301826.
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? 🙂
Hi, just comment (disable) this line:
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
You can change your browser, Chrome for example instead of Firefox.
More details: https://bugzilla.mozilla.org/show_bug.cgi?id=700837
@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;
Hi, please capture what you sent to the server using Postman.
Hi, Im newbie in Angular and in this kinds of stuff. Just wondering what would be the final folder structure of the project?
for this tutuorial
https://bezkoder.com/node-express-sequelize-postgresql/
to be used as the backend for this tutorial
https://bezkoder.com/angular-crud-app/
I am confused. sorry.
how to i connect the front-end to the back-end.
thanks.
Hi, just run the front-end application, it will automatically call back-end api 🙂
Yes, they are. You can also find the overview of this fullstack in the post:
Angular 8 + Node.js Express + PostgreSQL example: Build CRUD Application
Thank for this Node.js & Postgres tutorial. You always make many helpful things for people. 🙂
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!
Yes, you should create db first, the table will be generated automatically by Sequelize 🙂 .
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:)
Yeah, I’m so happy to hear that 🙂
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!
To fix the deprecation warning, change “operatorsAliases: false” inside models/index.js to “operatorsAliases: 0”
Hi there.
You must change operatorsAliases: 0, (false to zero). That will fix that problem
Hi bezkoder you have done a very good job!
Thanks for the great tutorials.
Don’t stop.
Great tutorials
Thank you
Thanks very much for your reply. It’s working now after correctly installing postgres on my PC.
I’m so happy to hear that 🙂
Just following up with more details, the Terminal is showing the following messages;
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.
Also, the below section was not included in the server.js file;
because I wasn’t sure if, it was meant to be in there from the instructions.
Thanks
});
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.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?
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.
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.
you cannot connect to ur db