[BE201] Express & Sequelize part 6

ORM (Object Relational Mapping)

將資料庫裡面的物件和某個程式語言裡面的物件建立對應關係，所以只要操作程式裡面的物件，資料庫也會被改動

Sequelize

前置作業

• 安裝 library：npm install --save sequelize
• 替資料庫安裝驅動程式：npm install --save mysql

Connecting to database & modeling table

const { Sequelize } = require('sequelize');

const sequelize = new Sequelize('database', 'user', 'password', {
  host: '127.0.0.1',
  dialect: 'mysql'
});

const User = sequelize.define('User', {
  // Model attributes are defined here
  firstName: {
    type: Sequelize.STRING,
    allowNull: false // 可不可以是空的
  },
  lastName: {
    type: Sequelize.STRING
    // allowNull defaults to true
  }
}, {
  // Other model options go here
});

sequelize.sync().then(() => { // 會去資料庫將 data 給建立出來，會回傳一個 promise
  User.create({
    firstName: 'Nick',
    lastName: 'Mary'
  }). then(() => {
    console.log("created!")
  })
})

Quering 練習

findAll()

sequelize.sync().then(() => { // 會去資料庫將 data 給建立出來，會回傳一個 promise
  User.findAll({
    where: {
      id: '2'
    }
  }).then((users) => {
    console.log(users[0].id, users[0].firstName)
  })
})

資料庫關聯

hasMany()

When a Sequelize association is defined between two models, only the source model knows about it. So, for example, when using Foo.hasOne(Bar) (so Foo is the source model and Bar is the target model), only Foo knows about the existence of this association.

const User = sequelize.define('Users', {
  firstName: {
    type: Sequelize.STRING,
    allowNull: false // 可不可以是空的
  },
  lastName: {
    type: Sequelize.STRING
  }
});

const Comment = sequelize.define('comment', {
  content: {
    type: Sequelize.STRING,
  }
});

User.hasMany(Comment)

會新增一個 comments 的 table，然後裡面有 UserId

• 新增一筆 UserId 是 2 的 comment：

equelize.sync().then(() => {
  Comment.create({
    UserId: 2,
    content: 'heyyyyy'
  }).then(() => {
    console.log("done")
  })
})

• 以下程式碼會印出 UserId 是 2 的 comments：

sequelize.sync().then(() => {
  User.findOne({
    where: {
      firstName: 'Anna'
    },
    include: Comment // 把 Anna 發過的評論撈出來
  }).then(user => {
    console.log(JSON.stringify(user, null, 4))
  })
})

• 用 Comment 找到 User 的資料

Comment.belongsTo(User) // 創造雙向關係

sequelize.sync().then(() => {
  Comment.findOne({
    where: {
      content: '妳好妳好妳好'
    },
    include: User
  }).then(comment => {
    console.log(JSON.stringify(comment, null, 4))
  })
})

Sequelize CLI

前置作業

• 安裝 npm install --save-dev sequelize-cli
• 初始化：npx sequelize-cli init
  • config.json：放連到資料庫的設定，有放帳號密碼，通常不會提供給別人，分為不同的階段 development、test、development

creating the first model

npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string

程式碼執行完成後，會建立兩個檔案：

• Create a model file user in models folder.
• Create a migration file with name like XXXXXXXXXXXXXX-create-user.js in migrations folder.

npx sequelize-cli db:migrate

• 執行這段程式碼，才會在 database 裡面創建新的 table
• 會新增一個叫 SequelizeMeta 的 table，裡面用來記錄當前這個 database 曾經 run 過什麼 migrations

model file

Sequelize 用

migration file

操縱 database，用來紀錄 database 的變動 (keep track of the changes to the database)，裡面有 up & down，up 放要前進一個版本要做的事情，down 裡面放的是要 revert 回去一個版本要做的事情。

npx sequelize-cli migration:create --name change-name 可以新增一個 migration

Running Migrations

npx sequelize-cli db:migrate 執行後才會把資料寫進資料庫裡面

用 ORM 實作留言板

index.js

• 把跟 db 有關的內容刪掉

const express = require('express')
const bodyParser = require('body-parser')
const session = require('express-session')
const flash = require('connect-flash')
const app = express()
const port = 5001

const userController = require('./controllers/user')
const commentController = require('./controllers/comment')

app.set('view engine', 'ejs')

app.use(bodyParser.urlencoded({ extended: false })) 
app.use(bodyParser.json()) 
app.use(flash())

app.use(session({
  secret: 'keyboard cat',
  resave: false,
  saveUninitialized: true,
}))

app.use((req, res, next) => {
  res.locals.username = req.session.username
  res.locals.errorMessage = req.flash('errorMessage')
  next()
})

function redirectBack(req, res) { // 自己寫的 Middleware
  res.redirect('back')
}

app.get('/', commentController.index)

app.get('/login', (req, res) => {
  res.render('login')
})

app.get('/login', userController.login)
app.post('/login', userController.handleLogin, redirectBack)
app.get('/logout', userController.logout)
app.get('/register', userController.register)
app.post('/register', userController.handleRegister, redirectBack)

app.post('/comment', commentController.add)
app.get('/delete_comment/:id', commentController.delete) // 冒號 id 代表它是一個動態參數
app.get('/update_comment/:id', commentController.update)
app.post('/update_comment/:id', commentController.handleUpdate)

app.post('/login', (req, res) => {
  if (req.body.password === "abc") {
    req.session.isLogin = true 
    res.redirect('/') 
  } else {
    req.flash('errorMessage', 'Please input the correct password')
    res.redirect('/login')
  }
})

app.get('/logout', (req, res) => {
  req.session.isLogin = false
  res.redirect('/')
})

app.listen(port, () => {
  console.log(`hello world, listening on port ${port}`)
})

controller

comment.js

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

const commentController = {
  add: (req, res) => {
    const {userId} = req.session
    const {content} = req.body
    if (!userId || !content) {
      return res.redirect('/')
    }

    Comment.create({
      content,
      UserId: userId
    }).then(() => {
      res.redirect('/')
    })

  },

  index: (req, res) => {
    Comment.findAll({
      include: User // 把 User 引入進來才有使用者的資料
    }).then((comments) => {
      res.render('user/index', {
        comments
      })
    })


  },

  delete: (req, res) => {
    Comment.findOne({
      where: {
        id: req.params.id,
        UserId: req.session.userId
      }
    }).then((comment) => {
      return comment.destroy()
    }).then(() => {
      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

user.js

const bcrypt = require('bcrypt')
const saltRounds = 10; // 可以調整密碼的複雜程度
const db = require('../models')
const User = db.User

const userController = {

  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
      }
    }).then((user) => {
      if (!user) {
        req.flash('errorMessage', '使用者不存在') // 帳號密碼錯誤代表找不到 user
        return next()
      }

      bcrypt.compare(password, user.password, (err, isSuccessful) => {
        if (err || !isSuccessful) {
          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('/')
  },

  register: (req, res) => {
    res.render('user/register')
  },

  handleRegister: (req, res, next) => {
    // ES6 解構語法
    const {username, nickname, password} = req.body
    if (!username || !nickname || !password) {
      req.flash('errorMessage', '缺少必要欄位') // 這邊用 return 的話，下面的程式碼就不會執行到，用 if else 會有很多層結構
      return next()
    }

    bcrypt.hash(password, saltRounds, (err, hash) => { // 為非同步
      if (err) {
        req.flash('errorMessage', err.toString())
        return next()
      }

      User.create({
        username,
        nickname,
        password: hash
      }).then((user) => {
        req.session.username = username // username 這個 key 如果有值代表登入成功了
        req.session.userId = user.id
        res.redirect('/')
      }).catch((err) => {
        req.flash('errorMessage', err.toString())
        return next()
      })
    })
  }

}

module.exports = userController

models

• 讓 User 和 Comment 產生關聯

user.js

static associate(models) {
      User.hasMany(models.Comment) // 這邊要大寫是因為在 generate model 的時候用的名字是大寫
    }

comment.js

static associate(models) {
      Comment.belongsTo(models.User)
    }

view

index.ejs

<!doctype html>
<head>
  <%- include('../template/head') %>
</head>
<html>

  <%- include('../template/navbar') %>

  <div class="container">
    <% if (username) { %>
      <form method="POST" action="/comment" >
        <div class="form-group">
          <label>留言內容</label>
          <textarea name="content" class="form-control" ></textarea>
        </div>
        <button type="submit" class="btn btn-primary">送出留言</button>
      </form>
    <% } %>

    <% comments.forEach(function(comment) { %>
      <div class="card" style="width: 18rem; margin-top: 10px;">
        <div class="card-body">
          <h5 class="card-title"><%= comment.User.nickname  %></h5>
          <p class="card-text"><%=  comment.content %></p>
          <% if (username === comment.User.username) { %>
            <a class="card-link" href="/update_comment/<%= comment.id %>">編輯</a>
            <a class="card-link" href="/delete_comment/<%= comment.id %>">刪除</a>
          <% } %>
        </div>
      </div>
    <% }) %>
  </div>
</html>