PostgreSQL is a powerful, open source object-relational database system that has earned it a strong reputation for reliability, feature robustness, and performance. In this article, We will build Rest Apis that can create, retrieve, update and delete Notes.
First we will start setting up express server and routes with express router. Next we add configuration for PostgreSQL database and create Note Model with sequelize. Our File structure at the end will be
To create express server and install required dependencies, in your terminal type
mkdir postgres-crud
cd postgres-crud
npm init -y
touch server.js
mkdir app
npm i express cors body-parser pg pg-hstore sequelize@5.21.13
This will create a folder called postgres-crud, initialize a node project and install the required dependencies. Now, we need to set-up our express server code and configure the route. To set up the server, Edit the server.js file as
// Bring in required Modules
const express = require("express");
const bodyParser = require("body-parser");
const cors = require("cors");
const app = express();
// Bring in the route
const routes = require("./app/routes");
var corsOptions = {
origin: "http://localhost:8081",
};
app.use(cors(corsOptions));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
const db = require("./app/models");
db.sequelize.sync();
app.use("/api/notes", routes);
// Define PORT
const PORT = process.env.PORT || 8080;
// Listen to the defined PORT
app.listen(PORT, () => {
console.log(`Server is running on port ${PORT}`);
});
Now, to set up the routes, we will create a folder called routes in our app folder and under routes folder, we will create a file called index.js. You can do these all through the editor or type the following command in your terminal
mkdir app/routes
touch app/routes/index.js
Now, edit the app/routes/index.js file as
// Bring in the express server
const express = require("express");
// Bring in the Express Router
const router = express.Router();
// Import the Controller
const controller = require("../controllers");
// Create a new Note
router.post("/", controller.create);
// Get all Notes
router.get("/", controller.findAll);
// Get Note by Id
router.get("/:id", controller.findOne);
// Modify existing Note
router.put("/:id", controller.update);
// Delete Note by Id
router.delete("/:id", controller.delete);
module.exports = router;
Now, the next step is to configure the Database. In order to do so, we create config folder inside our app folder then create a file db.config.js file under the config folder. To do these through command line
mkdir app/config
touch app/config/db.config.js
Now, edit the db.config.js file as below. You have to replace the HOST, USER, PASSWORD values with your own db values.
module.exports = {
HOST: "localhost", // Replace it with your own host address
USER: "user123", // Replace with your own username
PASSWORD: "12345", // Replace with your own password
DB: "testdb",
dialect: "postgres",
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000,
},
};
Now, the setting up db configuration part is done. Next is to define the db model. To do so, create a folder called models inside app folder and initialize two files namely index.js and notes.model.js. Now, edit the index.js file as
const dbConfig = require("../config/db.config");
const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
host: dbConfig.HOST,
dialect: dbConfig.dialect,
operatorAliases: 0,
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.notes = require("./notes.model.js")(sequelize, Sequelize);
module.exports = db;
Also, edit the notes.model.js file as
const { sequelize, Sequelize } = require(".");
module.exports = (sequelize, Sequelize) => {
const Note = sequelize.define("note", {
title: {
type: Sequelize.STRING,
},
description: {
type: Sequelize.STRING,
},
published: {
type: Sequelize.BOOLEAN,
},
});
return Note;
};
Now, the model is all set. Last thing remaining to do is defining controller. To do so, create a folder called controllers inside app folder and initialize a file namely index.js inside controllers folder. Edit the index.js file as
const db = require("../models");
const Notes = db.notes;
const Op = db.Sequelize.Op;
exports.create = (req, res) => {
if (!req.body.title) {
res.status(400).send({
message: "Content can not be empty !",
});
return;
}
const note = {
title: req.body.title,
description: req.body.description,
published: req.body.published ? req.body.published : false,
};
Notes.create(note)
.then((data) => {
res.send(data);
})
.catch((err) => {
res.status(500).send({
message: err.message || "Some error occurred while create the Notes",
});
});
};
exports.findAll = (req, res) => {
const title = req.query.title;
Notes.findAll()
.then((data) => {
res.send(data);
})
.catch((err) => {
res.status(500).send({
message: err.message || "Some error occured while retrieving Notes",
});
});
};
exports.findOne = (req, res) => {
const id = req.params.id;
Notes.findByPk(id)
.then((data) => {
res.send(data);
})
.catch((err) => {
res.status(500).send({
message: "Error retrieving Notes with id=" + id,
});
});
};
exports.update = (req, res) => {
const id = req.params.id;
Notes.update(req.body, {
where: { id: id },
}).then((data) => {
if (data) {
res.send({
message: "Note was updated successfully",
});
} else {
res.send({
message: `Cannot update Note with id=${id}`,
});
}
});
};
exports.delete = (req, res) => {
const id = req.params.id;
Notes.destroy({
where: { id: id },
}).then((data) => {
if (data) {
res.send({
message: "Note was delete successfully!",
});
} else {
res.send({
message: `Cannot delete Note with id=${id}`,
});
}
});
};
Now, we can finally run the application. To do so run
node server.js
If you've provided valid credentials for db and followed the steps correctly, you'll see the message Server is running on port 8080 and we'll be able to test the endpoints on Postman. The test result will be similar to mine for create and getAll methods
Thanks for reading. You can find the code for the tutorial on Github
Top comments (2)
Good tutorial, I personally recently love the new postgres module, but with Sequelize I also already build a few successful projects.
Thanks and haven't looked into postgres module yet but definitely going to, might be interesting !!