DEV Community

Sandip Basnet
Sandip Basnet

Posted on • Updated on

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}.`);
});
Enter fullscreen mode Exit fullscreen mode

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
  }
};
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
  });
Enter fullscreen mode Exit fullscreen mode

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;
};
Enter fullscreen mode Exit fullscreen mode

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) => {

};
Enter fullscreen mode Exit fullscreen mode

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."
            });
        });
};
Enter fullscreen mode Exit fullscreen mode
  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."
            });
        });
};
Enter fullscreen mode Exit fullscreen mode
  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
            });
        });
};
Enter fullscreen mode Exit fullscreen mode
  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
            });
        });
};
Enter fullscreen mode Exit fullscreen mode
  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
            });
        });
};
Enter fullscreen mode Exit fullscreen mode
  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."
                });
            });
        });
};
Enter fullscreen mode Exit fullscreen mode
  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."
            });
        });
};
Enter fullscreen mode Exit fullscreen mode

*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);
};

Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)