Node.js Rest CRUD API with Postgres

#node #restapi #postgres

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