NodeJs CRUD

We will be building REST APIS using node express and sequelize.

1. create folder: mkdir workshop && cd workshop
2. initialize the project: npm init
3. Install required dependencies: npm install express sequelize pg pg-hstore cors --save
4. Setup express web server: in the root folder create app.js file and add:

const express = require("express");
const bodyParser = require("body-parser");
const cors = require("cors");
const app = express();
var corsOptions = {
  origin: "*"
};
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: "Hello World!." });
});
// set port, listen for requests
const PORT = process.env.PORT || 5000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}.`);
});

where:
Express: this module is used for building the Rest apis.
cors: provides Express middleware to enable CORS with various options.
body-parser: parse the request body

1. connect database: create config folder and add db.config.js file and add:

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

where 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

1. Initialize Sequelize create database/models and add index.js with:

const dbConfig = require("../config/db.config.js");
const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  port: dbConfig.PORT,
  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.plans = require("./plan.model.js")(sequelize, Sequelize);
module.exports = db;

and update app.js with (put is just below the const app = express();)

const db = require("./database/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.");
});

1. Define the Sequelize Model In models folder, create plan.model.js file like this:

module.exports = (sequelize, Sequelize) => {
  const Plan = sequelize.define("plan", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING
    },
    completed: {
      type: Sequelize.BOOLEAN
    }
  });
  return Plan;
};

This Sequelize Model represents paln table in PostgreSQL database. These columns will be generated automatically: id, title, description, completed, createdAt, updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them:

create a new plan: create(object)
find a Plan by id: findByPk(id)
get all Plans: findAll()
update a Plan by id: update(data, where: { id: id })
remove a Plan: destroy(where: { id: id })
remove all Plans: destroy(where: {})
find all Plans by title: findAll({ where: { title: ... } })
These functions will be used in our Controller.

1. Add controllers create controllers folder and add plan.controller.js file with following functions:

create
findAll
findOne
update
delete
deleteAll
findAllCompleted
i.e.

const db = require("../database/models");
const Plans = db.plans;
const Op = db.Sequelize.Op;
// Create and Save a new Plan
exports.create = (req, res) => {

};
// Retrieve all P from the database.
exports.findAll = (req, res) => {

};
// Find a single Plan with an id
exports.findOne = (req, res) => {

};
// Update a Plan by the id in the request
exports.update = (req, res) => {

};
// Delete a Plan with the specified id in the request
exports.delete = (req, res) => {

};
// Delete all Plans from the database.
exports.deleteAll = (req, res) => {

};
// Find all completed Plans
exports.findAllCompleted = (req, res) => {

};

Implementations:

1. create:

exports.create = (req, res) => {
    // Validate request
    if (!req.body.title) {
        res.status(400).send({
            message: "Title can not be empty!"
        });
        return;
    }
    // Create a Plan
    const plan = {
        title: req.body.title,
        description: req.body.description,
        completed: req.body.completed ? req.body.completed : false
    };
    // Save plan in the database
    Plans.create(plan)
        .then(data => {
            res.send(data);
        })
        .catch(err => {
            res.status(500).send({
                message:
                    err.message || "Some error occurred while creating the plan."
            });
        });
};

1. retrive

exports.findAll = (req, res) => {
    const title = req.query?.title;
    var condition = title ? { title: { [Op.iLike]: `%${title}%` } } : null;
    Plans.findAll({ where: condition })
        .then(data => {
            res.send(data);
        }).catch(err => {
            res.status(500).send({
                message:
                    err.message || "Some error occurred while retrieving plans."
            });
        });
};

1. Retrieve a single object

exports.findOne = (req, res) => {
    const id = req.params.id;
    Plans.findByPk(id)
        .then(data => {
            if (data) {
                res.send(data);
            } else {
                res.status(404).send({
                    message: `Cannot find Plan with id = ${id}.`
                });
            }
        }).catch(err => {
            res.status(500).send({
                message: "Error retrieving Plan with id=" + id
            });
        });
};

1. Update an object

exports.update = (req, res) => {
    const id = req.params.id;
    Plans.update(req.body, {
        where: { id: id }
    })
        .then(num => {
            if (num == 1) {
                res.send({
                    message: "Plan was updated successfully."
                });
            } else {
                res.send({
                    message: `Cannot update Plan with id=${id}. Maybe Plan was not found or req.body is empty!`
                });
            }
        }).catch(err => {
            res.status(500).send({
                message: "Error updating Plan with id=" + id
            });
        });
};

1. Delete an object

exports.delete = (req, res) => {
    const id = req.params.id;
    Plans.destroy({
        where: { id: id }
    })
        .then(num => {
            if (num == 1) {
                res.send({
                    message: "Plan was deleted successfully!"
                });
            } else {
                res.send({
                    message: `Cannot delete Plan with id=${id}. Maybe Plan was not found!`
                });
            }
        }).catch(err => {
            res.status(500).send({
                message: "Could not delete Plan with id=" + id
            });
        });
};

1. Delete all objects

exports.deleteAll = (req, res) => {
    Plans.destroy({
        where: {},
        truncate: false
    })
        .then(nums => {
            res.send({
                message: `${nums} Plans were deleted successfully!})`
            }).catch(err => {
                res.status(500).send({
                    message:
                        err.message || "Some error occurred while removing all plans."
                });
            });
        });
};

1. Find all objects by condition

exports.findAllCompleted = (req, res) => {
    Plans.findAll({ where: { completed: true } })
        .then(data => {
            res.send(data);
        }).catch(err => {
            res.status(500).send({
                message:
                    err.message || "Some error occurred while retrieving Plans."
            });
        });
};

*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 response by setting up the routes.

These are our routes:

/api/plans: GET, POST, DELETE
/api/plans/🆔 GET, PUT, DELETE
/api/plans/completed: GET

Create a plan.routes.js inside routes folder with content like this:

module.exports = app => {
    const plans = require("../controllers/plan.controller.js");
    var router = require("express").Router();

    // Create a new Plan
    router.post("/", plans.create);

    // Retrieve all Plans
    router.get("/", plans.findAll);

    // Retrieve all completed Plans
    router.get("/completed", plans.findAllCompleted);

    // Retrieve a single Plan with id
    router.get("/:id", plans.findOne);

    // Update a Plan with id
    router.put("/:id", plans.update);

    // Delete a Plan with id
    router.delete("/:id", plans.delete);

    // Create a new Plan
    router.delete("/", plans.deleteAll);

    app.use('/api/plans', router);
};

You can see that we use a controller from /controllers/plan.controller.js.

We also need to include routes in app.js (right before app.listen()):

require("./routes/plan.route")(app);

*Test the APIs
*
Run our NodeJs application with command: node app.js.

Using Postman, we’re gonna test all the APIs above

References:
1) https://www.bezkoder.com/node-express-sequelize-postgresql/

Code repository: https://github.com/mondyfy/nodejs-workshop-kist