Node.js Express & PostgreSQL: CRUD Rest APIs example with Sequelize
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:
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.
In the root folder, let’s create a new server.js file:
const express = require("express");
const bodyParser = require("body-parser");
const cors = require("cors");
const app = express();
var corsOptions = {
origin: "http://localhost:8081"
};
app.use(cors(corsOptions));
// parse requests of content-type - application/json
app.use(bodyParser.json());
// parse requests of content-type - application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: true }));
// simple route
app.get("/", (req, res) => {
res.json({ message: "Welcome to bezkoder application." });
});
// set port, listen for requests
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
console.log(`Server is running on port ${PORT}.`);
});
What we do are:
– import express, body-parser and cors modules:
Express is for building the Rest apis
body-parser helps to parse the request and create the req.body object
cors provides Express middleware to enable CORS with various options.
– create an Express app, then add body-parser and cors middlewares using app.use() method. Notice that we set origin: http://localhost:8081.
– define a GET route which is simple for test.
– listen on port 8080 for incoming requests.
Now let’s run the app with command: node server.js.
Open your browser with url http://localhost:8080/, you will see:
Yeah, the first step is done. We’re gonna work with Sequelize in the next section.
Configure PostgreSQL database & Sequelize
In the app folder, we create a separate config folder for configuration with db.config.js file like this:
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:
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:
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
After creating some new Tutorials, you can check PostgreSQL table:
testdb=# select * from tutorials;
id | title | description | published | createdAt | updatedAt
----+-------+-------------+-----------+-----------+-----------
