学会Sequelize，让你操作数据更丝滑

见贤思齐

Sequelize是一个基于promise的Node.jsORM,目前支持Postgres,MySQL,MariaDB,SQLite以及MicrosoftSQLServer.它具有强大的事务支持,关联关系,预读和延迟加载,读取复制等功能。一、前言本文希望通过下面的知识讲解及一些实战经验分享，给予即将入门或正在学习的同学一些帮助。在之前刚接触Sequelize的时候，遇到挺多问题，比如数据的聚合统计应该怎么做？复杂的排序规则应该怎么写？索引这块应该如何定义？性能如何衡量？等等这些问题，后来经过自己的琢磨及与后台人员交流探讨，这些问题都得予解决。我们直接进入主题，学习目录结构如下：一、前言二、入门1.配置2.创建模型3.增删改查4.查询条件三、进阶1.联表查询2.分组3.聚合函数4索引5.事务四、实战演练五、问题思考二、入门1.配置module.exports = () => { const config = (exports = {}) config.sequelize = {  //支持的数据库类型  dialect: 'mysql',  //连接数据库的主机  host: 'localhost',  //连接数据库的端口  port: 3306,  //数据库名称  database: 'db_test',  //数据库用户名  username: 'root',  //数据库密码  password: 'xxxxxx',  //设置标准时区  timezone: '+08:00',  //配置  dialectOptions: {    // 时间格式化，返回字符串    dateStrings: true,    typeCast(field, next) {      if (field.type === 'DATETIME') {        return field.string()      }      return next()    }  } } return config}注：如果没有加dialectOptions配置上的typeCast属性值为true的话，返回的时间是ISO标准时间日期字符。（如：'2022-04-16T15:02:08.017Z'）2.创建模型2.1常用数据类型类型说明INTEGER整数类型STRING字符串TEXT文本类型BOOLEAN布尔类型DATE时间类型像数字、字符类型默认都有一些长度的限定，有时候因为长度问题导致接口出错，所以需要根据情况而定。还有其他类型就不一一列了，需要用到的可以参照文档看看。Sequelize数据类型[1]2.2定义模型module.exports = (app) => {  const { STRING, INTEGER, DATE } = app.Sequelize  const model = app.model.define(    'student',    {           id: {        //类型        type: INTEGER(11),        //是否允许为空        allowNull: false,        //是否为主键        primaryKey: true,        //自动自增        autoIncrement: true,        //备注        comment: '学生id',      },      name: {        type: STRING(50),        allowNull: false,        validate: {          notEmpty: true,        },        comment: '学生姓名',      },      class_id: {        type: INTEGER(11),        allowNull: false,        validate: {          notEmpty: true,        },        comment: '班级',      },      cid: {        type: STRING(50),        allowNull: false,        unique: 'cid',        validate: {          notEmpty: true,        },        comment: '身份证',      }    },    {      // 启动时间，设置为ture会自动生成创建和更新时间，默认字段名称为createAt、updateAt。      timestamps: true,      //对应的表名将与model名相同      freezeTableName: true,      //表备注      comment: '表1',      //创建时间字段别名或不展示      createdAt: 'createTime',      //更新时间字段别名或不展示      updatedAt: 'updateTime'    }  )  return model}使用model.sync(options)可自动执行SQL语句建表，但这个不建议用，第一这么做容易出现问题，第二我们的规范也不允许这么做。2.3表字段规范这里讲的是我们公司的SQL规范，先来看一个案例：CREATE TABLE `student` (  `id` int NOT NULL AUTO_INCREMENT COMMENT '学生id',  `name` varchar(50) NOT NULL COMMENT '学生姓名',  `cid` varchar(50) NOT NULL COMMENT '身份证',  `class_id` int NOT NULL COMMENT '班级',  `create_time` datetime NOT NULL COMMENT '',  `update_time` datetime NOT NULL COMMENT '更新时间',  `mobile` varchar(50) DEFAULT NULL COMMENT '手机号',  RIMARY KEY (`id`),  UNIQUE KEY `cid_unique` (`cid`),  KEY `class_id` (`class_id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '学生表';审核结果：审核结果下面讲一下基本的设计规范：1)禁用关键字在设计的时候注意不要用到关键字，比如name、type、status等字段。常见关键字：关键字2)字段禁止默认值为null从上面的案例中mobile字段使用了允许为null值，审核平台这块没有强限制，但不建议用null为默认值，很容易出现问题。3)需要添加字段注释和表注释CREATE TABLE `student` (  `id` int NOT NULL AUTO_INCREMENT COMMENT '学生id',  `username` varchar(50) NOT NULL COMMENT '学生姓名',  `cid` varchar(50) NOT NULL COMMENT '身份证',  `class_id` int NOT NULL COMMENT '班级',  `create_time` datetime NOT NULL COMMENT '创建时间',  `update_time` datetime NOT NULL COMMENT '更新时间',  `mobile` varchar(50) DEFAULT NULL COMMENT '手机号',  RIMARY KEY (`id`),  UNIQUE KEY `cid` (`cid`),  KEY `iclass_id` (`class_id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '学生表';4）创建时间和更新时间字段该表中必须含有create_time和update_time字段，如果没有该2个字段的话审核会不通过。5)索引命名唯一索引：必须以uniq_前缀命名。普通索引：必须以idx_前缀命名。索引命名6)其它以上是常用的基本规范，更多根据自己公司情况而定。3.增删改查3.1create创建数据const values = { username: '张三', age: 18}const result = await this.model.create(values)console.log(result)3.2findAll查询数据并返回数组const filter = { username: '张三', age: 18}const result = await this.model.findAll({ where: filter})console.log(result)3.3findAndCountAll查询数据并返回总数和数组列表const filter = { username: '张三', age: 18}const result = await this.model.findAndCountAll({ where: filter})console.log(result)3.4findOne查询某一条数据返回对象const filter = { id: 20}const result = await this.model.findOne({ where: filter})console.log(result)3.5count返回总条数const total = await this.model.count()console.log(total)3.6update更新数据const value = { id: 2, username: '张三', age: 20}const result = await this.model.update(value)console.log(result)3.7destroy删除数据const filter = { id: 1}const result = await this.model.destroy({ where: filter})console.log(result)3.8其它还有一些其他的方法，具体可以看看Sequelize方法文档[2]。4.查询条件这个具体的查询用法就不一一说了，根据自己需要的查询条件看下对应的查询功能即可。this.Op = this.app.Sequelize.Opthis.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)    someAttribute: {      // 基本      [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)      // 使用方言特定的列标识符 (以下示例中使用 G):      [Op.col]: 'user.organization_id',        // = "user"."organization_id"      // 数字比较      [Op.gt]: 6,                              // > 6      [Op.gte]: 6,                             // >= 6      [Op.lt]: 10,                             //  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' (不区分大小写) (仅 G)      [Op.notILike]: '%hat',                   // NOT ILIKE '%hat'  (仅 G)      [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]' (仅 G)      [Op.notIRegexp]: '^[h|a|t]',             // !~* '^[h|a|t]' (仅 G)      [Op.any]: [2, 3],                        // ANY ARRAY[2, 3]::INTEGER (仅 G)      [Op.match]: Sequelize.fn('to_tsquery', 'fat & rat') // 匹配文本搜索字符串 'fat' 和 'rat' (仅 G)      // 在 ostgres 中, Op.like/Op.iLike/Op.notLike 可以结合 Op.any 使用:      [Op.like]: { [Op.any]: ['cat', 'hat'] }  // LIKE ANY ARRAY['cat', 'hat']    }  }});三、进阶1.联表查询1.1一对一比如学生表和身份证表，一名学生只会有一个身份证号码，一张身份证只会对应一名学生。这种就是一对一的关系，如图：一对一假如学生表作为主表，身份证表作为副表，那边这张副表的外键就是学生表的id。在使用Sequelize库中，我们想通过学生表关联到相应的身份证表信息，可通过hasOne方法做关联：student.hasOne(card, {foreignKey : 'id'});如果我们想通过身份证表反向关联到学生表，这是可以用belongsTo方法做关联：card.belongsTo(student, {foreignKey : 'cid'});总结一下hasOne和belongsTo的区别：方法说明hasOne正向关联，可以理解为一名学生拥有一张身份证。belongsTo反向关联，可以理解为一张身份证属于一名学生。1.2一对多比如学生表和班级表，一名学生只有一个班级，一个班级由多名学生组成，这种是一对多（多对一）的关系，如图：一对多在使用Sequelize库中，可通过hasMany方法做关联：class.hasMany(student, {foreignKey : 'class_id'});1.3多对多比如学生表和课程表，一名学生可以有多门课程，一门课程可以由多名学生参与，这种是多对多的关系，如图：多对多在使用Sequelize库中，可通过belongsToMany方法做关联：const Student = sequelize.define('Student', { name: DataTypes.STRING });const Course = sequelize.define('Course', { name: DataTypes.STRING });const StudentCourse = sequelize.define('StudentCourse', {  studentId: {    type: DataTypes.INTEGER,    references: {      model: Student,      key: 'id'    }  },  courseId: {    type: DataTypes.INTEGER,    references: {      model: Course,      key: 'id'    }  }});Student.belongsToMany(Course, { through: StudentCourse });Course.belongsToMany(Student, { through: StudentCourse });1.4联表查询优劣优点：联表查询用起来很方便，不需要做太多了处理，尤其在B端场景会用的比较多。缺点：对于C端这种流量较大场景，使用联表查询效率会很低下，可能会导致服务器崩溃，所以现在大多数在C端都不采用SQL自带的联表查询功能，一般会通过代码逻辑来处理，大大减少查询效率。有兴趣的可以了解下mysql联表查询的步骤是怎样的，这样对比起来会更加直观一点。2.分组举个例子，你想对订单各个状态的数量进行一个统计，你应该会怎么做？在之前不知道分组这个功能时，我是这么做的：const obj = {}obj.problemAmoumt = await this.model.count()obj.resolvedAmoumt = await this.model.count({  where: {    problemStatus:2  }})return ctx.returnStatus.SUCCESS(obj)这似乎看起来很粗暴。后来发现可通过SQL语句中的group进行一个分组，group是指定要进行分组的字段，示例如下：this.model.count({ attributes: ['order_status'], group: 'order_status'})最终转换成SQL语句如下：SELECT `order_status`, count(*) AS `count` FROM `order_list` AS `order_list` GROUP BY `order_status`;返回结果[ {     "order_status": 1,     "count": 4 }, {     "order_status": 2,     "count": 2 }, {     "order_status": 3,     "count": 2 }]思考：1.分组在应用时会不会有坑？自己在使用时可以看看。2.如果想对order_status进行一个别名bbb字段返回，这时的查询语句应该如何调整？3.聚合函数3.1count统计总数，比如统计各个订单的数量：this.model.findAll({  attributes: [    'orderStatus',    [sequelize.fn('count', sequelize.col('order_status')), 'total']  ],  group: 'orderStatus'})3.2sum求和，比如统计全部同学的成绩总数：this.model.findAll({  attributes: [    [sequelize.fn('sum', sequelize.col('score')), 'score']  ]})3.3max查询最大值，比如找到这个分数最高的同学：this.model.findAll({  attributes: [    [sequelize.fn('max', sequelize.col('score')), 'score']  ]})3.4min查询最小值，比如找到这个分数最低的同学：this.model.findAll({  attributes: [    [sequelize.fn('min', sequelize.col('score')), 'score']  ]})当然这些方法也可以通过this.model.方法(字段名称)来实现，参数为指定的字段。4索引索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够提高数据查询的效率，从而提升服务器的性能。4.1主键索引一个表只能由一个主键索引，且不为空。可通过primaryKey属性来设置主键，通常会把id设为该表的主键，当然你也可以指定其它字段来作为主键，该字段值必须是唯一的。 const student = app.model.define(    'student',    {      id: {        type: INTEGER(11),        allowNull: false,        primaryKey: true,        autoIncrement: true,        default: 10000,        comment: '学生id',      },      name: {        type: STRING(50),        allowNull: false,        validate: {          notEmpty: true,        },        comment: '学生姓名',      }    } ) return student      4.2唯一索引唯一索引的列的值必须唯一，但是允许出现空值。可通过unique属性设置为唯一索引，值为true的话，索引名称会自动生成，也可以设置为自定义索引名称。 const student = app.model.define(    'student',    {      id: {        type: INTEGER(11),        allowNull: false,        primaryKey: true,        autoIncrement: true,        default: 10000,        comment: '学生id',      },      name: {        type: STRING(50),        allowNull: false,        validate: {          notEmpty: true,        },        comment: '学生姓名',      },      cid: {        type: STRING(50),        allowNull: false,        unique: 'cid_unique',        validate: {          notEmpty: true,        },        comment: '身份证',      }    } ) return student      4.3组合索引用多个列组合构建的索引，这多个列中的值不允许有空值。const student = app.model.define(    'student',    {      id: {        type: INTEGER(11),        allowNull: false,        primaryKey: true,        autoIncrement: true,        default: 10000,        comment: '学生id',      },      name: {        type: STRING(50),        allowNull: false,        validate: {          notEmpty: true,        },        comment: '学生姓名',      },      class_id: {        type: INTEGER(11),        allowNull: false,        validate: {          notEmpty: true,        },        comment: '班级',      },      cid: {        type: STRING(50),        allowNull: false,        unique: 'cid',        validate: {          notEmpty: true,        },        comment: '身份证',      }    },    {      indexes: [{        name: 'name',        fields: ['name']      }, {        name: 'cid',        fields: ['cid']      }]    }  )  return student4.4普通索引用表中的普通列构建的索引，没有任何限制。用法只要在indexed属性里不设置unique字段即可。const student = app.model.define(    'student',    {      id: {        type: INTEGER(11),        allowNull: false,        primaryKey: true,        autoIncrement: true,        default: 10000,        comment: '学生id',      },      name: {        type: STRING(50),        allowNull: false,        validate: {          notEmpty: true,        },        comment: '学生姓名',      },      class_id: {        type: INTEGER(11),        allowNull: false,        validate: {          notEmpty: true,        },        comment: '班级',      },      cid: {        type: STRING(50),        allowNull: false,        unique: 'cid',        validate: {          notEmpty: true,        },        comment: '身份证',      }    },    {      indexes: [{     unique: true,        name: 'name',        fields: ['name']      }, {     unique: true,        name: 'class_id',        fields: ['class_id']      }]    }  )  return student4.5加与不加索引的区别？优点：增加索引会提供查询效率缺点：增加内存空间我们来看一条查询语句：explain select * from `experience_problem_list` where `experience_problem_list`.`page_type` = 2;先看看没加索引的执行结果：idtypepossible_keyskeykey_lenrefrowsfilteredExtra1ALL(NULL)(NULL)(NULL)(NULL)1641610(NULL)表中总共有16416条数据，扫描行数也是16416条。再看下加了索引的执行结果：idtypepossible_keyskeykey_lenrefrowsfilteredExtra1refpage_typepage_type4const4100Usingwhere我们来看下rows字段的对比，加了索引的扫描行数只有4条，由此可以看到，加索引的查询效率大大高于普通查询。5.事务可以理解为一个事务对应的是一组完整的业务，并且在这个事务中所作的一切操作要么全部成功，要么全部失败，只要有一个操作没成功，整个事务都将回滚到事务开始前。这里简单放一个官方使用案例吧：// 首先,我们开始一个事务并将其保存到变量中const t = await sequelize.transaction();try {  // 然后,我们进行一些调用以将此事务作为参数传递:  const user = await User.create({    firstName: 'Bart',    lastName: 'Simpson'  }, { transaction: t });  await user.addSibling({    firstName: 'Lisa',    lastName: 'Simpson'  }, { transaction: t });  // 如果执行到此行,且没有引发任何错误.  // 我们提交事务.  await t.commit();} catch (error) {  // 如果执行到达此行,则抛出错误.  // 我们回滚事务.  await t.rollback();}四、实战演练1.查询数据不想返回内置的数据结构，只想单纯返回纯数据结构怎么处理？来看下默认返回查询的数据结果：{  count: 8,  rows: [    student {      dataValues: [Object],      _previousDataValues: [Object],      uniqno: 1,      _changed: Set(0) {},      _options: [Object],      isNewRecord: false    }  ]}这里rows里面对象返回了很多层，在业务处理时可能取值会不太方便，我们可以在调用方法的参数里加上raw等于true，就返回正常的数据格式。const result = await this.model.findAll({ where: filter, raw: true})返回：{  count: 8,  rows: [    {      id: 8,      name: '张三',      cid: 'xxx',      createTime: '2022-04-08 18:06:52',      updateTime: '2022-04-08 18:06:52'    }  ]}2.如果列表筛选涉及到主副表应该怎么查询合适？有几种方案：1）直接用mysql语句做表关联即可，但性能比较低下。2）用代码逻辑处理，但写起来可能有点绕，但效率比直接用mysql表关联效率高。3）如果主表字段固定且不多的话，可以直接冗余副表中，但如果后续主表加字段的话，副表更新是个问题。4）采用ES，将这2张表的数据合并同步到ES的一张表里，但ES使用场景一般都是量很大的，加上nodejs新增其它数据库交互，有额外的开销成本。综合以上考虑，如果是内部项目，量也不是很多的话，查询比较复杂的话可以直接采用第一种方法，如果逻辑相对比较简单，也可以采用第二种方法。3.分组group遇到的问题。group只能填写已有的查询字段。比如你表中有这个字段aaa，但查询返回的字段aaa被过滤了，这时不能以aaa进行分组。4.如何获取今日、最近7天、一个月的数据？可通过字符串函数DATE_FORMAT实现：1）首先通过时间选择器获取到今日、最近7天、一个月的开始时间和结束时间。2）然后使用对改模型使用count进行查询，这时获取到的知识该时期的总条数。3）对改数据进行分组，使用group属性，字段为创建的时间，但我们创建的时间是包含时分秒的，如果这么聚合的话，生成的数据并不是我们想到的，我们需要的是针对年月日，所以需要对这个时间进行一个处理。[    {        "create_time": "2022-04-12 17:02:36",        "count": 1    },    {        "create_time": "2022-04-14 17:02:38",        "count": 1    },    {        "create_time": "2022-04-14 17:03:38",        "count": 1    },    {        "create_time": "2022-04-14 17:03:58",        "count": 1    }]这个显然不是我们想要的一个结果。4）然后函数DATE_FORMAT，将时间格式化为年月日。总体实现如下：const countArr = await this.model.count({  where: filter,  attributes: [    [sequelize.fn('DATE_FORMAT', sequelize.col('create_time'), '%Y-%m-%d'), 'createTime'],  ], group: 'createTime',})假设我想获取4月10日-4月14日的数据，正常返回结构如下：[    {        "createTime": "2022-04-12",        "count": 1    },    {        "createTime": "2022-04-14",        "count": 3    }]5）因为表中只有12号和14号的数据，10、11、13号没有，这块需要自己对代码进行一个处理，把其它日期没有的数据为0即可。最终希望达到的效果：[ {     "time": "2022-04-10",     "count": 0 }, {     "time": "2022-04-11",     "count": 0 }, {     "time": "2022-04-12",     "count": 1 }, {     "time": "2022-04-13",     "count": 0 }, {     "time": "2022-04-14",     "count": 3 }]五、问题思考如果想要获取一年中每个月的订单量，应该怎么查询？答案：关注 "大转转FE"公众号，回复"统计"、"一年"其中一个即可如何根据查询条件的顺序，返回相应的数据顺序？答案：关注 "大转转FE"公众号，，回复"filed"、"排序"其中一个即可如何进行动态分表？答案：关注 "大转转FE"公众号，回复"分表"即可。注：如果您还有其它问题想交流，欢迎在底部留言。参考资料[1]Sequelize数据类型:https://www.sequelize.com.cn/core-concepts/model-basics[2]Sequelize方法文档:https://www.sequelize.com.cn/core-concepts/model-querying-finders