Express, Sequelize with PostgreSQL database를 사용하여 Node.js Restful CRUD API 구현하기 step2

Express, Sequelize with PostgreSQL database를 사용하여 Node.js Restful CRUD API 구현하기 step1 에 이어서 작성

1. CRUD function 작성

app/controllers 폴더에 tutorial.controller.js 생성

• 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) => {
  if (!req.body.title) {
    res.status(400).send({
      message: "Content can not be empty!",
    });
    return;
  }

  const tutorial = {
    title: req.body.title,
    description: req.body.description,
    published: req.body.published ? req.body.published : false,
  };

  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 all Tutorials from the database.
exports.findAll = (req, res) => {
  const title = req.query.title;
  var condition = title ? { title: { [Op.iLike]: `%${title}%` } } : null;

  Tutorial.findAll({ where: condition })
    .then((data) => {
      res.send(data);
    })
    .catch((err) => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials.",
      });
    });
};

// Find a single Tutorial with an id
exports.findOne = (req, res) => {
  const id = req.params.id;

  Tutorial.findByPk(id)
    .then((data) => {
      res.send(data);
    })
    .catch((err) => {
      res.status(500).send({
        message: "error retrieving Tutorial with id =" + id,
      });
    });
};

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

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

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

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

// Delete all Tutorials from the database.
exports.deleteAll = (req, res) => {
  Tutorial.destroy({
    where: {},
    truncate: false,
  })
    .then((nums) => {
      res.send({ message: `${nums} Tutorials were deleted successfully!` });
    })
    .catch((err) => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while removingall tutorials.",
      });
    });
};

// Find all published Tutorials
exports.findAllPublished = (req, res) => {
  Tutorial.findAll({ where: { published: true } })
    .then((data) => {
      res.send(data);
    })
    .catch((err) => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials.",
      });
    });
};

---

2. Route

• Client가 HTTP request를 보내오면 서버가 어떻게 대응할지 routes 설정을 해야한다.
  - 사용할 route
  • /api/tutorials : GET, POST, DELETE
  • /api/tutorials/:id : GET, PUT, DELETE
  • /api/tutorials/published : GET

app/routes에 tutorial.routes.js 생성

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 Tutorial
  router.get("/", tutorials.findAll);

  // Retrieve all published Tutorial
  router.get("/published", tutorials.findAllPublished);

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

  // Update a Tutorial with id
  router.post("/:id", tutorials.update);

  // Delete a Tutorial with id
  router.delete("/:id", tutorials.delete);

  // Delete all Tutorial
  router.delete("/", tutorials.deleteAll);

  app.use("/api/tutorials", router);
};

• server.js에 routes require 해줘야 한다. (app.listen(); 직전에)
  

---

3. 서버 Test 해보기

• test를 하기 이전에 PostgreSQL에 "my_DB" 데이터베이스를 만들어 놔야한다.
• PostgreSQL을 설치하고 첫 접속이라면 시작 -> sql shell(psql) ->사용자의 암호 : 가 나올때까지 Enter -> 사용자 암호에 설치때 설정했던 암호 입력 -> 접속완료
  

postgres=# CREATE DATABASE "my_DB";

• \list 입력 하여 my_DB 생성된 것 확인.

• 이후 \connect my_DB로 my_DB에 연결
  

node server.js`** 실행

• select * from tutorials; 로 자동으로 생성된 table 체크
  

• DB생성이 완료되었으면 이제 postman으로 test진행.

• 현재 tutorials 테이블에 데이터가 없으므로 POST형식으로 데이터를 입력 진행
  

• Body선택 -> raw -> Json -> data 입력후 send
  

  published: true로 입력하면
  GET http://localhost:8080/api/tutorials/published -> published : true 만 볼 수 있음.

• 결과
  

• DB확인
  

• 이후 post, get, delete 실행시 정상 작동하는 것을 확인가능.
• Postman과 Visual Studio Code 터미널에서 정상적으로 작동됨을 확인가능.
  POST : http://localhost:8080/api/tutorials -> create 실행
  POST : http://localhost:8080/api/tutorials/1 -> update 실행 (id=1)
  GET : http://localhost:8080/api/tutorials -> findAll 실행
  GET : http://localhost:8080/api/tutorials/2 -> findOne 실행 (id=2)
  GET : http://localhost:8080/api/tutorials/published -> findAllPublished 실행
  DELETE : http://localhost:8080/api/tutorials -> deleteAll 실행
  DELETE : http://localhost:8080/api/tutorials/3 -> delete 실행 (id=3)

#참고 :

const db = require("./app/models");
db.sequelize.sync();

db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
});

server.js에서 위처럼 변경시 node server.js 실행시킬때마다 DB 초기화.

---

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