Sequelize 的使用

一. Sequelize 是什么？

Sequelize 是一个基于 promise 的 Node.js ORM, 目前支持 Postgres, MySQL, MariaDB, SQLite 以及 Microsoft SQL Server. 它具有强大的事务支持, 关联关系, 预读和延迟加载,读取复制等功能。

ORM 是什么?

ORM 简称对象关系映射，它通过使用描述对象和数据库之间映射的元数据，将程序中的对象与关系数据库相互映射，用人话就是用代码来定义一个个数据的模型，每个模型在数据库中都有一张对应的表，表里的字段等信息就是你通过模型文件定义的，模型实例化后对应的都是表内的一条数据

Sequelize 中文文档地址：https://www.sequelize.com.cn/

二. sequelize 的使用

以下内容均是针对 MySql 数据库，若是其他数据库请查看Sequelize 官网教程。

步骤简介：

1、创建项目，用 navicat 创建数据库
2、下载安装数据库驱动 mysql2，和 sequelize
3、项目内配置数据库链接的 host，port，数据库名称等信息
4、创建 sequelize 数据库模型（字段，字段类型，字段约束，表关联等信息）
5、用 sequelize 提供的方法对数据库进行 CRUD

（一）下载

bash

npm install --save sequelize mysql2

（二）修改连接配置

1、在项目更目录创建 `db.js` 文件

express-test 数据库名称 root 数据库的 username 123456 数据库 password

const { Sequelize } = require("sequelize");
// 创建实例对象并连接数据库
const db = new Sequelize("数据库名", "用户名", "密码", {
  dialect: "mysql", // 数据库类型，支持: 'mysql', 'sqlite', 'postgres', 'mssql'
  host: "localhost", // 自定义连接地址,可以是ip或者域名,默认为本机:localhost
  port: "3306", // 端口号,默认为3306
  logging: true, // 是否开启日志(建议开启,有利于我们查看进行的操作)
  pool: {
    // 连接池
    min: 0,
    max: 5,
    idle: 30000, //空闲最长连接时间
    acquire: 60000, //建立连接最长时间
  },
  define: {
    // 统一定义表内的一些属性
    timestamps: false, // 取消默认字段 createAt, updateAt
    freezeTableName: true, // 允许给表设置别名
    underscored: false, // 字段以下划线（_）来分割（默认是驼峰命名风格）
  },
  dialectOptions: {
    dateStrings: true, // 正确显示时间 否则查出来的时间格式为 2019-08-27T12:02:05.000Z
    typeCast: true,
  },
  timezone: "+08:00", // 改为标准时区
});
module.exports = db;

2、在入口文件内添加数据库测试连接接配置

当项目启动时，就会检测数据库链接是否可用：

核心代码：

async function tectConnectDB() {
  try {
    //  用于测试数据库链接情况
    await sequelize.authenticate();
    console.log("数据库链接成功");
  } catch (error) {
    console.error("数据库链接错误: ", error);
  }
}

（三）创建模型，同步模型，书写业务代码

sequelize 里面每一个模型在数据库内都有一张对应的表，模型内的字段对应表里面的字段，但是定义模型的时候有一些字段是不需要定义的，比如 createdAt，updatedAt，只需要配置一下 timestamps: true 即可

1、定义模型

定义模型的方法有两种，

调用 sequelize.define(modelName, attributes, options)
扩展 Model 并调用 init(attributes, options)

以上两种方案都是等效的是不过第二种是以 ES6 的 class 的形式来定义模型的，这里就以 define 的方式演示，扩展 Model 可以参照官方文档

在根目录下创建 model 目录，分别创建 user.js 和 role.js 文件

user.js 和 role.js 文件代表两个模型，分别表示数据库的两张表，代码如下

/* user.js */
const { DataTypes } = require("sequelize");
const sequelize = require("../db");
const crypto = require("crypto");
const Role = require("./role");

const User = sequelize.define(
  "user",
  {
    // 在这里定义模型属性(字段)
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true, // 主键
      allowNull: false, // 不允许为空
      autoIncrement: true,
      unique: true, // 唯一键
      comment: "用户Id", // 列注释
    },
    username: {
      type: DataTypes.STRING,
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false,
      comment: "密码",
      set(value) {
        // 设置存入数据库的值格式化,(密码加密)
        this.setDataValue("password", MD5(value));
      },
    },
    gender: {
      type: DataTypes.INTEGER,
      defaultValue: 2,
      comment: "用户名 0-女 1-男 2-未知",
      get() {
        // 设置取出来的字段的值格式化的函数
        switch (this.getDataValue("gender")) {
          case 0:
            return "女";
          case 1:
            return "男";
          default:
            return "未知";
        }
      },
    },
  },
  {
    // 这是其他模型参数
    freezeTableName: true, // 冻结表名 user 不会变成users
    timetamps: true, // 自定添加创建时间修改时间字段
    underscored: false, // 防止驼峰式字段被默认转为下划线 userId不会变成user_id
  }
);

// 同步模型
// User.sync();

// 定义评论关联
User.associate = function () {
  // 用户角色多对多
  User.belongsToMany(Role, { through: "user_role" });
};

module.exports = User;

function MD5(str) {
  const MD5KEY = "加密的KEY";
  const hash = crypto.createHash("sha256", MD5KEY).update(str).digest("hex");
  return hash;
}

/* role.js */
const { Sequelize, DataTypes } = require("sequelize");
const sequelize = require("../db");

const User = require("./user");

const Role = sequelize.define(
  "role",
  {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      allowNull: false, // 允许为空
      unique: true, // 唯一键
      defaultValue: DataTypes.UUIDV4,
      comment: "角色Id", // 列注释
    },
    rolename: {
      type: DataTypes.STRING(30),
      allowNull: false, // 允许为空
      unique: true, // 唯一键
      comment: "角色名称", // 列注释
    },
    roleFlag: {
      type: DataTypes.STRING(30),
      allowNull: false,
      unique: true, // 唯一键
      comment: "角色标识",
    },
  },
  {
    freezeTableName: true, // 冻结表名
    timetamps: true, // 自定添加创建时间修改时间字段
    underscored: false, // 防止驼峰式字段被默认转为下划线
  }
);

Role.sync();

// 关联
Role.associate = function () {
  // 用户角色多对多
  Role.belongsToMany(User, { through: "user_role" });
};

module.exports = Role;

2、同步模型（根据模型文件，自动建表）

同步模型的方式是 Model.sync()

方法 1： 定义表的时候加上 Model.sync()，看创建模型代码 方法 2： 一次同步所有模型

第 1 步、在入口文件创建同步模型的方法

async function syncAllModel() {
  try {
    /* 
        User.sync()  - 如果表不存在,则创建该表(如果已经存在,则不执行任何操作)
        User.sync({ force: true }) - 将创建表,如果表已经存在,则将其首先删除---生产环境慎用
        User.sync({ alter: true }) - 这将检查数据库中表的当前状态(它具有哪些列,它们的数据类型等),然后在表中进行必要的更改以使其与模型匹配.
      */
    await sequelize.sync();
    console.log("所有模型均已成功同步.");
  } catch (error) {
    console.error("模型同步失败:", error);
  }
}

第 2 步：在测试数据库连接成功后调用同步模型方法

async function tectConnectDB() {
  try {
    //  用于测试数据库链接情况
    await sequelize.authenticate();
    console.log("数据库链接成功");
    syncAllModel(); // 同步所有模型
  } catch (error) {
    console.error("数据库链接错误: ", error);
  }
}

到此，可以通过 navicat 看到表已经自动创建

3、操纵数据库

3.1、在根目录创建 service 目录，用来放置所有操纵数据库的方法

// service/user.js代码

const User = require("../model/user"); // 引入创建的用户模型

// 创建用户
exports.createUser = async function (user = {}) {
  // 假数据,只为测试
  const { password = "123345", gender = 0, username = "zhangsan" } = user;
  try {
    // 调用插入数据的方法(create方法由sequelice提供,详单与sql语句insert into ...)
    let result = await User.create({ password, gender, username });
    return result;
  } catch (error) {
    throw error;
  }
};

3.2、在对应路由书写业务代码

特别注意：sequelize 返回的是 promise，

// router/user.js代码

const express = require("express");
const router = express.Router();
// 引入service的方法
const { createUser } = require("../service/user");

// 创建用户
// 注意sequelize返回的是promise
router.get("/user", async (req, res) => {
  let result = await createUser();
  res.json(result);
});
module.exports = router;

3.3、访问接口，查看数据库

三. 定义模型的参数详解

可参考官方模型定义字段数据类型

const { Sequelize, DataTypes } = require("sequelize");
const sequelize = require("../db");

const User = sequelize.define(
  "table",
  {
    // 在这里定义模型属性(字段)
    firstName: {
      type: DataTypes.INTEGER, // 数据类型
      primaryKey: true, // 主键
      allowNull: false, // 不允许为空 设置列的 allowNull为 false 将会为该列增加 非空 属性
      autoIncrement: true, // 自增值
      /* 
      unique: 唯一键 创建两个拥有相同属性的值会抛出一个错误
      The unique property can be either a boolean, or a string.
      If you provide the same string for multiple columns, they will form a
      composite unique key. */
      unique: true, // unique: 'compositeIndex', unique: true
      comment: "用户Id", // 列注释 只有 MySQL 和 PG 可以使用
      defaultValue: Sequelize.NOW, // 默认值 日期默认值 => 当前时间
      // 插入数据或修改数据时候触发
      set(value) {
        // 设置存入数据库的值格式化,(密码加密)
        this.setDataValue("password", MD5(value));
      },
      // 取值时候触发
      get() {
        let val = this.getDataValue("firstName");
        return val + "123";
      },
      validate: {
        // 字段的验证规则
        is: ["^[a-z]+$", "i"], // 只允许字母
      },
    },
    // 在模型中的名字是小驼峰,在表中的列名可以用 field 属性来指定
    fieldWithUnderscores: {
      type: Sequelize.STRING,
      field: "field_with_underscores",
    },
    // 创建外码
    bar_id: {
      type: Sequelize.INTEGER,

      references: {
        // This is a reference to another model
        model: Bar,
        //被引用模型的  列名  (是列名,即 field 名)
        key: "id",
        // 检查外码约束,只支持 PostgreSQL .
        deferrable: Sequelize.Deferrable.INITIALLY_IMMEDIATE,
      },
    },
  },
  {
    // 这是其他模型参数
    freezeTableName: true, // 冻结表名 user 不会变成复数 users
    timetamps: true, // 自定添加创建时间修改时间字段createdAt 和 updatedAt
    underscored: false, // 防止驼峰式字段被默认转为下划线 userId不会变成user_id
    createdAt: false, //不想要 createdAt
    updatedAt: "updateTimestamp", // 想要 updatedAt 但是希望名称叫做 updateTimestamp
  }
);

// 同步模型
// User.sync();

// 定义表之间的关联
User.associate = function () {};

module.exports = User;

四. 模型 CRUD

（一）新增

const jane = await User.create({ firstName: "Jane", lastName: "Doe" });

（二）删除

// 删除所有名为 "Jane" 的人
await User.destroy({
  where: {
    firstName: "Jane",
  },
});

（三）修改

// 将所有没有姓氏的人更改为 "Doe"
await User.update(
  { lastName: "Doe" },
  {
    where: {
      lastName: null,
    },
  }
);

（四）查找

默认情况下,所有 finder 方法的结果都是模型类的实例(与普通的 JavaScript 对象相反). 这意味着在数据库返回结果之后,Sequelize 会自动将所有内容包装在适当的实例对象中. 在少数情况下,当结果太多时,这种包装可能会效率低下. 要禁用此包装并收到简单的响应,请将 { raw: true } 作为参数传递给 finder 方法.

1、查一条

const project = await Project.findOne({ where: { title: "My Title" } });

2、查主键

const project = await Project.findByPk(123);

3、查所有

const { Op } = require("sequelize");
Model.findAll({
  where: {
    [Op.and]: [{ a: 5 }, { b: 6 }], // (a = 5) AND (b = 6)
    [Op.or]: [{ a: 5 }, { b: 6 }], // (a = 5) OR (b = 6)
    username: {
      // 基本
      [Op.eq]: 3, // = 3
      [Op.ne]: 20, // != 20
      [Op.is]: null, // IS NULL
      [Op.not]: true, // IS NOT TRUE
      [Op.or]: [5, 6], // (someAttribute = 5) OR (someAttribute = 6)

      // 使用方言特定的列标识符 (以下示例中使用 PG):
      [Op.col]: "user.organization_id", // = "user"."organization_id"

      // 数字比较
      [Op.gt]: 6, // > 6
      [Op.gte]: 6, // >= 6
      [Op.lt]: 10, // < 10
      [Op.lte]: 10, // <= 10
      [Op.between]: [6, 10], // BETWEEN 6 AND 10
      [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15

      // 其它操作符

      [Op.all]: sequelize.literal("SELECT 1"), // > ALL (SELECT 1)

      [Op.in]: [1, 2], // IN [1, 2]
      [Op.notIn]: [1, 2], // NOT IN [1, 2]

      [Op.like]: "%hat", // LIKE '%hat'
      [Op.notLike]: "%hat", // NOT LIKE '%hat'
      [Op.startsWith]: "hat", // LIKE 'hat%'
      [Op.endsWith]: "hat", // LIKE '%hat'
      [Op.substring]: "hat", // LIKE '%hat%'
      [Op.iLike]: "%hat", // ILIKE '%hat' (不区分大小写) (仅 PG)
      [Op.notILike]: "%hat", // NOT ILIKE '%hat'  (仅 PG)
      [Op.regexp]: "^[h|a|t]", // REGEXP/~ '^[h|a|t]' (仅 MySQL/PG)
      [Op.notRegexp]: "^[h|a|t]", // NOT REGEXP/!~ '^[h|a|t]' (仅 MySQL/PG)
      [Op.iRegexp]: "^[h|a|t]", // ~* '^[h|a|t]' (仅 PG)
      [Op.notIRegexp]: "^[h|a|t]", // !~* '^[h|a|t]' (仅 PG)

      [Op.any]: [2, 3], // ANY ARRAY[2, 3]::INTEGER (仅 PG)

      // 在 Postgres 中, Op.like/Op.iLike/Op.notLike 可以结合 Op.any 使用:
      [Op.like]: { [Op.any]: ["cat", "hat"] }, // LIKE ANY ARRAY['cat', 'hat']
    },
  },
  attributes: ["id", "title"], // 要包含哪些字段
  attributes: { exclude: ["userId", "password"] }, // 除了这些字段外都要显示
  offset: (page - 1) * size, // 偏移量
  limit: size, // 每页多少条
  include: Role, // 要将关联表Role的所有字段都一起查出来
  include: [
    // 详细配置关联表
    {
      // 关联表一
      model: Role,
      where: {}, // 同上面的where,只有当主表where和此处where同时满足的数据才会被查出
      include: [], // 同上面的include
      attributes: [], // 同上面attributes,关联表要显示的字段名
    },
  ],
  order: [["updatedAt", "DESC"]], // 按照updatedAt字段倒序排列
  distinct: true, // 去重
  raw: true, // 不加这个属性 查出来的数据外面包了一个dataValues,特别注意: 若需要用查出来的数据来进行关联表操作, 不能加raw属性, 否则会报错
  group: "gender", // 按照gender字段分组
});

4、查分页并计算总条数

更多参数参考 findAll 方法，注意，findAll 同样也可用于分页查询，只不过数据总数需要自己计算，可用 Model.count()

const { count, rows } = await Project.findAndCountAll({
  where: {
    title: {
      [Op.like]: "foo%",
    },
  },
  offset: 10,
  limit: 2,
});
console.log(count);
// -一个整数 - 符合查询条件的记录总数;
console.log(rows);
// -一个数组对象 - 获得的记录;

5、查找或新增

findOrCreate 方法，若查到就返回数据，若没查到，先创建一条数据，再返回数据

const [user, created] = await User.findOrCreate({
  where: { username: "sdepold" },
  defaults: {
    // 使用 defaults 参数来定义必须创建的内容. 如果 defaults 不包含每一列的值,则 Sequelize 将采用 where 的值(如果存在).
    job: "Technical Lead JavaScript",
  },
});
console.log(user.username); // 'sdepold'
console.log(user.job); // 这可能是也可能不是 'Technical Lead JavaScript'
console.log(created); // 指示此实例是否刚刚创建的布尔值
if (created) {
  console.log(user.job); // 这里肯定是 'Technical Lead JavaScript'
}

(五)批量修改或批量新增（bulkCreate 的 updateOnDuplicate）

bulkCreate(records, [options]) ---> Promise.<Array.<Instance>>说明：如果 id 存在，则 update，否则 insert updateOnDuplicate : true 若不加 {updateOnDuplicate:true} 则是只有批量创建，更多说明参照批量创建

let obj1 = {
  id: 0,
  name: "园区问卷调查",
};

let obj2 = {
  id: 1,
  name: "园区问卷调查ddddddddddddddddddddd",
};

let obj_list = [obj1, obj2];

Model.bulkCreate(obj_list, { updateOnDuplicate: true })
  .then((result) => {
    result.forEach((item) => {
      console.log("item:", item);
    });
  })
  .catch((err) => {
    console.log("err:", err);
  });

（六）一些常用的函数

await Model.count({where:{}}) // 计算满足条件的数据出现次数
await Model.max('age') // 计算某个字段最大值
await Model.min('age') // 计算某个字段最小值
await Model.sum('age', { where: { age: { [Op.gt]: 5 } } })// 计算满足条件的数据中，字段 age 的总和

更多使用方法移步官方文档：https://www.sequelize.com.cn/core-concepts/raw-queries

Sequelize 的使用 ​

一. Sequelize 是什么？ ​

二. sequelize 的使用 ​

（一）下载 ​

（二）修改连接配置 ​

1、在项目更目录创建 db.js 文件 ​

2、在入口文件内添加数据库测试连接接配置 ​

（三）创建模型，同步模型，书写业务代码 ​

1、定义模型 ​

2、同步模型（根据模型文件，自动建表） ​

3、操纵数据库 ​

三. 定义模型的参数详解 ​

四. 模型 CRUD ​

（一）新增 ​

（二）删除 ​

（三）修改 ​

（四）查找 ​

1、查一条 ​

2、查主键 ​

3、查所有 ​

4、查分页并计算总条数 ​

5、查找或新增 ​

(五)批量修改或批量新增 （bulkCreate 的 updateOnDuplicate） ​

（六）一些常用的函数 ​