sequelize在koa中的使用

这篇文章主要是记录在项目过程中使用sequelize对数据库进行操作的感受和经验

---

一、sequelize

Sequelize 是一个基于 promise 的 Node.js ORM 工具, 目前支持 Postgres, MySQL, MariaDB, SQLite 以及 Microsoft SQL Server, Amazon Redshift 和 Snowflake’s Data Cloud. 它具有强大的事务支持, 关联关系, 预读和延迟加载,读取复制等功能。
sequelize将sql查询语句分装成一个个方法调用，使用起来非常便捷。

二、sequelize连接Mysql

安装

npm install sequelize
yarn add sequelize

实例化,seq连接MySQL数据库

const Seq = require('sequelize')

// 实例化seq对象
// 参数依次为：要导入的数据库名，账号，密码
const seq = new Seq(
  SQLConfig.database.DATABASE, // 数据库名称
  SQLConfig.database.USERNAME, // 账号
  SQLConfig.database.PASSWORD, // 密码
  {
    host: SQLConfig.database.HOST,
    dialect: 'mysql',
    define: { // 模型定义的默认选项
      timestamps: false,
    },
    pool: { // 连接池属性
      max: 5,
      min: 0,
      idle: 30000
    }
  }
)

三、sequelize的使用

sequelize在使用的基本步骤
1.安装sequelize包
2. 构建需要的数据库
3. 创建连接数据库的seq实例
4. 按照seq构建模型的方式，将数据库的表都映射成一个个seq模型
5. 将模型导出，使用模型实例的方法来对数据进行增删查改的操作。

模型构建

// 引入构建好的seq实例，将模型挂载在上面
import seq from "../config/sequelizeBase" 
const Sequelize = require('sequelize')

//使用define的方法定义模型
const bookingModel = seq.define(
  'booking', // 模型名称,seq会在别名的基础上识别真实表名，一般是添加上‘s’,即此处seq会识别此模型对于的表名为'bookings'
  { // 分别描述表里每一列的数据特性
    id: {
      type: Sequelize.INTEGER(10), // 数据类型
      primaryKey: true, // 是否为主键
      allowNull: false, // 是否允许为null,默认为true
      autoIncrement: true // 字段是否自增
    },
    Uid: {
      type: Sequelize.STRING(11),
      allowNull: false
    },
    Hid: {
      type: Sequelize.INTEGER(11),
      allowNull: false
    },
    viewDate: {
      type: Sequelize.TEXT
    },
    deleted: {
      type: Sequelize.INTEGER
    },
    createTime: {
      type: Sequelize.BIGINT
    }
  }
)
export default bookingModel

注意事项

1. 默认情况下, 构建模型时只提供模型名称,Sequelize 会自动将模型名复数并将其用作表名. 想要强制表名称和模型名称相等则需要设置freezeTableName: true 参数停止 Sequelize 执行自动复数化.

sequelize.define('booking', {
  // ... (属性)
}, {
  freezeTableName: true
});

也可以直接提供表名

sequelize.define('booking', {
  // ... (属性)
}, {
  tableName: 'Employees'
});

2. 字段类型需要和数据库中列的类型保持一致。
3. seq没有设置外键的选项，表与表之间的联系需要通过表关联相关方法进行设置

sequelize的数据类型

DataTypes.STRING             // VARCHAR(255)
DataTypes.STRING(1234)       // VARCHAR(1234)
DataTypes.STRING.BINARY      // VARCHAR BINARY
DataTypes.TEXT               // TEXT
DataTypes.TEXT('tiny')       // TINYTEXT
DataTypes.INTEGER            // INTEGER

DataTypes.BOOLEAN            // TINYINT(1)
DataTypes.INTEGER            // INT
DataTypes.BIGINT             // BIGINT
DataTypes.BIGINT(11)         // BIGINT(11)

DataTypes.FLOAT              // FLOAT
DataTypes.FLOAT(11)          // FLOAT(11)
DataTypes.FLOAT(11, 10)      // FLOAT(11,10)

DataTypes.DATE       // DATETIME 适用于 mysql / sqlite, 带时区的TIMESTAMP 适用于 postgres
DataTypes.DATE(6)    // DATETIME(6) 适用于 mysql 5.6.4+. 支持6位精度的小数秒
DataTypes.DATEONLY   // 不带时间的 DATE

如果数据库中表与模型不一致，想要以模型为主可以可以通过调用一个异步函数(返回一个Promise)model.sync(options)来实现模型同步
这个过程只改变数据库端的表

插入、更新、删除

const result = await bookingModel.create({...request}) // 向bookings表中插入一条数据
await bookingModel.update({ viewDate: "2022-06-01" }, {
  where: {
    Uid:'159'
  }
}); // 更新bookings表里Uid为159行viewDate列的数据
await bookingModel.destroy({
  where: {
    Uid:'159'
  }
}); // 删除bookings表里Uid为159行数据记录

// 使用模型实例来对数据进行删除或者更新
result.viewDate = "2022-06-01" // result为上文插入数据的返回实例，只指向一行数据
await result.save() // 便可更新result实例的viewDate字段
await result.destroy() // 删除result对应的行数据

查询

// 查询表booking中一个匹配项，有则获得一个数据实例无则返回null
const result = await bookingModel.findOne({
          where: {// 查询条件
            id: request.id
          }，
          attributes: ['id', 'Uid', 'blockName', 'doorNumber'] // 指定需要获取的数据列，不设置则为全字段获取
        })
// 查询表booking中所以匹配项，有则获得一个数据实例数组无则返回[]      
const result = await bookingModel.findAll({
            where: {
              viewDate: {
                [Op.like]: `${request.viewDate}%`
              },
              Uid,
              deleted:0
            },
            attributes: ['viewDate', 
            // 使用sequelize.fn进行数据聚合
            [sequelize.fn('COUNT', sequelize.col('viewDate')), 'count']
            ],
            group: 'viewDate' // 以字段‘viewDate’进行聚合查询
          })

Op

Op是Sequelize 提供的数据查询运算符

 	[Op.and]: [{ a: 5 }, { b: 6 }],            // (a = 5) AND (b = 6)
    [Op.or]: [{ a: 5 }, { b: 6 }],             // (a = 5) OR (b = 6)
      // 基本
      [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)
      // 数字比较
      [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.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'

高级查询

where条件动态组合

下面将通过两个例子来展示如何使用变量动态生成where查询条件
场景1：多条件搜索查询，根据用户传来的查询条件参数不同设置不同的查询条件

知识点：findAndCountAll方法返回的数据结构为

• count : 一个整数 - 与查询匹配的记录总数
• rows : 一个数组对象 - 获得的记录

 // 将查询条件转存
      let where = {}
 // 根据不同的查询场景，动态生成查询条件
      if (request.keyword) {
        request.keyword && (searchController.keyword = request.keyword)
 // 将查询条件看做对象进行赋值设置
        where[Op.or] = [
          {
            address: {
              [Op.like]: `%${searchController.keyword}%`
            }
          },
          {
            blockName: {
              [Op.like]: `%${searchController.keyword}%`
            }
          }
        ]
      }
      if (request.district) {
        where["district"] = request.district
      }
      if (request.price) {
        const price = request.price.split('-')
        where["price"] = {
          [Op.between]: price
        }
      }
      if (request.houseType) {
        where['houseType'] = {
          [Op.like]: `${request.houseType}%`
        }
      }
      if (request.rentalType) {
        where['rentalType'] = request.rentalType
      }
      where['state'] = 0
      where['deleted'] = 0
  // 分页查询
     const result = await houseModel.findAndCountAll({
          where,
          limit: request.limit || 10,
          offset: request.offset,
        })

场景2：查询房东所有房源的预约信息列表
解决思路：先获取房东拥有的房源id集，将其作为作为某一查询条件的数据集进行查询获取

// 查询房东拥有的房源数据
  const house = await houseModel.findAll({
      where: {
        Uid
      }
    })
    let list: Object[] = []
    if (house.length) {
      // 将查询结果转换成普通对象数组，便于作为查询条件
      house.map((val: any) => {
        let array = { Hid: 0 }
        array.Hid = val.id
        list.push(array)
      }, list)

    }
    // 不可以直接将查询结果作为查询条件列表，必须是普通对象数组
          if (list.length) {
            // 根据房源id获取相应由预约的日期
            const bookingRecord: DataList<any> = await bookingModel.findAll({
              where: {
                [Op.or]: list,
                deleted: 0,
                bookingWay: 1,
                viewDate: {
                  [Op.like]: `${request.viewDate}%`
                },
              }
            })
          }

order

根据某一字段对查询数据进行排序，使用如下:

order: seq.literal('updateTime DESC')

表关联

Sequelize 提供了 四种 关联类型,并将它们组合起来以创建关联：

• HasOne 一对一 ，关联关系(外键)存在于目标模型中。
• BelongsTo 一对一，关联关系(外键)存在于源模型中。
• HasMany 一对多，关联关系(外键)存在于目标模型中。
• BelongsToMany 多对多
  下面以两个例子来说明关联的建立

// bookingModle为源模型，houseModel为目标模型
// 一个源模型外键对应于一个目标模型中的数据
bookingModel.belongsTo(houseModel, {
  as: 'houseBooking', // 联合表名
  foreignKey: 'Hid', // 源模型的外键字段
  targetKey: 'id', // 对应于目标模型中的字段
})
// 一个用户数据可以有多条评论数据
userModel.hasMany(commentModel, {
  as: "user",
  foreignKey: 'Uid', // 目标模型中的外键
  targetKey: 'UId', // 对应于源模型中的字段
})

联合查询

以获取预定信息时，同时获取预定房源的相关信息的联合查询为例，展示sequelize联合查询的使用。

// 用户查看预约事宜
          const bookingRecord: DataList<any> = await bookingModel.findAndCountAll({
            where: {
              Uid,
              deleted: 0,
              bookingWay: 1,
              viewDate: request.viewDate
            },
            attributes: ['viewTime', 'Hid', 'Uid', 'id'],
            offset: request.offset,
            limit: request.limit || 10,
            // 联合查询
            include: [
              {
              	// 关系别名，具有唯一性
                association: 'houseBooking',
                // 获取目标模型的那些字段
                attributes: ['blockName', 'Uid']
              }]
          })

注意：设置了关联外键的表的数据列不可以随意删除，会产生错误。

事务查询

sequelize的事务概念与数据库的事务管理一致，主要是为了保证操作过程中数据修改的一致性，避免在数据更改中应错误而导致数据不一致问题。在一个事务中，只用当所有的数据库操作都正常执行成功后，才会结束。如果中途出现错误，则会撤销前面的操作回滚至初识状态。具体使用如下：

// 托管事务会自动处理提交或回滚事务
await seq.transaction(async () => {
          const UId = verify(ctx)
          const result = await bookingModel.findOne({
            where: {
              id: request.id
            }
          })
          const now = new Date().getTime()
          // 在约定的时间一天前 取消预约扣除信誉分2
          if (now - result.updateTime < 24 * 60 * 1000) {
            const update = await userModel.findOne({
              where: {
                UId
              }
            })
          // 给房东发送消息
          await bookingController.setMessage(result, 0)
          result.deleted = 1
          result.save()
        })