[note] sequelize 筆記

CLI

$ npm install --save sequelize-cli

$ npx sequelize init

$ npx sequelize db:create
$ npx sequelize db:migrate
$ npx sequelize db:seed:all
$ npx sequelize db:seed:undo:all
$ npx sequelize db:drop

# 針對測試環境，在最前面加上 NODE_ENV=test
$ NODE_ENV=test npx sequelize db:create
$ NODE_ENV=test npx sequelize db:migrate

# 建立 User Model 及其對應欄位
$ npx sequelize model:generate --name User --attributes name:string,email:string,password:string
$ npx sequelize model:generate --name Restaurant --attributes name:string,tel:string,address:string,opening_hours:string,description:text

# 新增 migration 檔案，名稱為 add-isAdmin-to-users
$ npx sequelize migration:generate --name add-isAdmin-to-users

# 新增 seed 檔案
$ npx sequelize seed:generate --name my-seed-file

• migration
• Sequelize + Express Starter Guide

Controller 常用方法

Query

const db = require('../models');
const { User } = db;

// findAll
User.findAll({
  raw: true,
  order: [
    createdAt:
  ]
}).then(users);

// findByPK
User.findByPk(userId).then(user)

Create

const db = require('../models');
const { User } = db;

User.create({
  name,
  tel,
  address,
});

Update

const db = require('../models');
const { User } = db;

const user = await User.findByPk(userId);
await user.update({
  tel,
});

Delete

const db = require('../models');
const { User } = db;

const user = await User.findByPk(userId);
user.destroy();

Model 常用方法

hasMany

module.exports = (sequelize, DataTypes) => {
  const Category = sequelize.define(
    'Category',
    {
      name: DataTypes.STRING,
    },
    {},
  );
  Category.associate = function (models) {
    // 將可以使用 category.Restaurants
    Category.hasMany(models.Restaurant);
  };
  return Category;
};

belongsTo

module.exports = (sequelize, DataTypes) => {
  const Restaurant = sequelize.define(
    'Restaurant',
    {
      // ...
    },
    {},
  );
  Restaurant.associate = function (models) {
    // 將可以使用 restaurant.Category
    Restaurant.belongsTo(models.Category);
  };
  return Restaurant;
};

取得 dataValues 的資料

keywords: plain, raw, toJSON

// 使用 { raw: true }
// （？）hasMany 的 model 沒辦法使用 raw: true
Model.findAll({
  raw: true,
});

// 針對取出來的資料，使用 instance.toJSON()
Model.findById(1).then((data) => {
  console.log(data.toJSON());
});

// 針對取出來的資料，使用 instance.get({ plain: true })
// const users = await db.users.findAll({}).map(el => el.get({ plain: true }))
Model.findById(1).then((data) => {
  console.log(data.get({ plain: true }));
});

// 使用 dataValues
Model.findAll().then((results) => {
  results = results.map((result) => ({
    ...result.dataValues,
    text: result.text,
  }));
});

• Get only dataValues from Sequelize ORM @ StackOverflow

錯誤處理

SequelizeConnectionError

SequelizeConnectionError: Client does not support authentication protocol requested by server; consider upgrading MySQL client

執行下面的 SQL

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Example / Sample

Express

sequelize/express-sample @ Github

Migration Samples

新增 table

module.exports = {
  up: (queryInterface, Sequelize) =>
    queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      name: {
        type: Sequelize.STRING,
      },
      email: {
        type: Sequelize.STRING,
      },
      password: {
        type: Sequelize.STRING,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    }),
  down: (queryInterface, Sequelize) => queryInterface.dropTable('Users'),
};

新增欄位

// 對 Users Model 新增 isAdmin 的欄位
module.exports = {
  up: (queryInterface, Sequelize) =>
    queryInterface.addColumn('Users', 'isAdmin', {
      type: Sequelize.BOOLEAN,
      defaultValue: false,
    }),
  down: (queryInterface, Sequelize) => {
    queryInterface.removeColumn('Users', 'isAdmin');
  },
};

新增外鍵（foreign key）

// 對 Restaurants Model 新增一個名為 CategoryId 的 foreign key 欄位
module.exports = {
  up: (queryInterface, Sequelize) =>
    queryInterface.addColumn('Restaurants', 'CategoryId', {
      type: Sequelize.INTEGER,
      allowNull: false,
      reference: {
        model: 'Categories',
        key: 'id',
      },
    }),

  down: (queryInterface, Sequelize) => queryInterface.removeColumn('Restaurants', 'CategoryId'),
};

在 Sequelize，預設的外鍵欄位名稱是「Model 名稱 + Id」，而 Model 名稱一般都是以大寫命名，因此這裡會用 CategoryId 而非 categoryId。

Seeder Samples

// ./seeders/my-seed-file.js
const bcrypt = require('bcryptjs');

module.exports = {
  up: (queryInterface, Sequelize) =>
    queryInterface.bulkInsert('Users', [
      {
        email: 'root@example.com',
        password: bcrypt.hashSync('12345678', bcrypt.genSaltSync(10), null),
        isAdmin: true,
        name: 'root',
        createdAt: new Date(),
        updatedAt: new Date(),
      },
      {
        email: 'user1@example.com',
        password: bcrypt.hashSync('12345678', bcrypt.genSaltSync(10), null),
        isAdmin: false,
        name: 'user1',
        createdAt: new Date(),
        updatedAt: new Date(),
      },
      {
        email: 'user2@example.com',
        password: bcrypt.hashSync('12345678', bcrypt.genSaltSync(10), null),
        isAdmin: false,
        name: 'user2',
        createdAt: new Date(),
        updatedAt: new Date(),
      },
    ]),

  // 把 Users Table 中的資料清空，並且寫入 null
  down: (queryInterface, Sequelize) => queryInterface.bulkDelete('Users', null, {}),
};