[BE201] 後端中階：ORM 與 Sequelize 改留言板系統

一、環境設置

1. npm install --save sequelize-cli
2. npm install sequelize
3. npx sequelize-cli init
4. npm install --save mysql2

二、清理，原本用 mysql 寫的東西，那些 db.js 通通拿掉

三、改 config 檔案，調整資料庫連線
四、產生資料結構

npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string
// 建立 model 與 migration 兩個檔案，model 慣例大寫

npx sequelize-cli db:migrate

五、改動 controllers
引入方式改變：

const db = require('../models')
const User = db.User

handleLogin 部分：將 user 撈出來的部分改變了 => User.findOne

  handleLogin: (req, res, next) => {
    const {username, password} = req.body
    if (!username || !password) {
      req.flash('errorMessage', '資料輸入不齊全')
      return next()
    }
    User.findOne({
      where: {
        username: username
      }
    }).then(user => {
      if (!user) {
        req.flash('errorMessage', '用戶資料不存在')
        return next()
      }
      bcrypt.compare(password, user.password, (err, results) => {
        if (err || !results) {
          req.flash('errorMessage', err.toString())
          return next()
        }
        req.session.username = username
        res.redirect('/')
      })
    }).catch(err => {
      req.flash('errorMessage', err.toString())
      return next()
    })
  }

handleRegister 部分： => User.create

  handleRegister: (req, res, next) => {
    const {username, password, nickname} = req.body
    if (!username || !password || !nickname) {
      req.flash('errorMessage', '資料輸入不齊全')
      return next()
    }
    bcrypt.hash(password, saltRounds, (err, hash) => {
      User.create({
        username,
        password: hash,
        nickname
      }).then(user => {
        req.session.username = username
        res.redirect('/')
      }).catch(err => {
        req.flash('errorMessage', err.toString())
        return next()
      })
  }

使用者部分改完，接著換改 comment 的部份。
因為新增評論在資料庫是使用 user id 所以也要一起改。
先看使用者功能有無建立完成，先將東西給刪掉，然後確定註冊、登入功能無誤。

// 刪除後的長相
const commentController = {
  add: (req, res, next) => {
    const {content} = req.body
    const {username} = req.session
    if (!content) {
      req.flash('errorMessage', '沒有輸入留言內容')
      return next()
    }
  },
  index: (req, res, next) => {
    res.render('index', {
      comments: {}
    })
  },
  delete: (req, res) => {
  },
  update: (req, res) => {
    res.render('update', {
      comment: {}
    })
  },
  handleUpdate: (req, res) => {
  }
}

補上 User.id

'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Comments', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      content: {
        type: Sequelize.TEXT
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      UserId: {
        type: Sequelize.INTEGER
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Comments');
  }
};

userController

const bcrypt = require('bcrypt')
const saltRounds = 10
const db = require('../models')
const User = db.User
const userController = {
  register: (req, res) => {
    res.render('register')
  },
  handleRegister: (req, res, next) => {
    const {username, password, nickname} = req.body
    if (!username || !password || !nickname) {
      req.flash('errorMessage', '資料輸入不齊全')
      return next()
    }
    bcrypt.hash(password, saltRounds, (err, hash) => {
      User.create({
        username,
        password: hash,
        nickname
      }).then(user => { // 可以拿到新增完後的 instance
        req.session.username = username
        req.session.userId = user.id
        res.redirect('/')
      }).catch(err => {
        req.flash('errorMessage', err.toString())
        return next()
      })
    })
  },
  logout: (req, res) => {
    req.session.username = null
    res.redirect('/')
  },
  login: (req, res) => {
    res.render('login')
  },
  handleLogin: (req, res, next) => {
    const {username, password} = req.body
    if (!username || !password) {
      req.flash('errorMessage', '資料輸入不齊全')
      return next()
    }
    User.findOne({
      where: {
        username: username
      }
    }).then(user => {
      if (!user) {
        req.flash('errorMessage', '用戶資料不存在')
        return next()
      }
      bcrypt.compare(password, user.password, (err, results) => {
        if (err || !results) {
          req.flash('errorMessage', err.toString())
          return next()
        }
        req.session.username = username
        req.session.userId = user.id
        res.redirect('/')
      })
    }).catch(err => {
      req.flash('errorMessage', err.toString())
      return next()
    })
  },
  logout: (req, res) => {
    req.session.username = null
    res.redirect('/')
  }
}
module.exports = userController

const db = require('../models')
const Comment = db.Comment
const User = db.User
const commentController = {
  add: (req, res, next) => {
    const {content} = req.body
    const {userId} = req.session
    if (!content) {
      req.flash('errorMessage', '沒有輸入留言內容')
      return next()
    }
    if (!userId) {
      req.flash('errorMessage', '登入處理有誤')
      return next()
    }
    Comment.create({
      content,
      UserId: userId,
    }).then(() => {
      res.redirect('/')
    })
  }, // 資料格式有變動，所以 view 也要一起變動
  index: (req, res, next) => {
    Comment.findAll({
      include: User
    }).then(comments => {
      res.render('index', {
        comments
      })
    })
  },
  delete: (req, res) => {
    Comment.findOne({
      where: {
        id: req.params.id,
        UserId: req.session.userId
      }
    }).then(comment => {
      return comment.destroy()
    }).then(() => { // return 後接 .then() 是 promise 的用法
      res.redirect('/')
    }).catch(() => {
      res.redirect('/')
    })
  },
  update: (req, res) => {
    Comment.findOne({
      where: {
        id: req.params.id,
      }
    }).then(comment => {
      res.render('update', {
        comment
      })
    })
  },
  handleUpdate: (req, res) => {
    Comment.findOne({
      where: {
        id: req.params.id,
        userId: req.session.userId
      }
      }).then(comment => { 
        return comment.update({
          content: req.body.content
        })
      }).then(() => {
        res.redirect('/')
      }).catch(() => {
        res.redirect('/')
      })
  }
}
module.exports = commentController