Model Querying - Basics - 模型查询(基础)

Sequelize 提供了多种方法来协助查询数据库中的数据.

重要说明:要使用 Sequelize 执行生产级别的查询,请确保你还阅读了事务指南. 事务对于确保数据完整性和提供其它好处很重要.

本指南将说明如何进行标准的 增删改查(CRUD) 查询.

简单 INSERT 查询

首先,一个简单的例子:

  1. // 创建一个新用户
  2. const jane = await User.create({ firstName: "Jane", lastName: "Doe" });
  3. console.log("Jane's auto-generated ID:", jane.id);

Model.create() 方法是使用 Model.build() 构建未保存实例并使用 instance.save() 保存实例的简写形式.

也可以定义在 create 方法中的属性. 如果你基于用户填写的表单创建数据库条目,这将特别有用. 例如,使用它可以允许你将 User 模型限制为仅设置用户名和地址,而不设置管理员标志:

  1. const user = await User.create({
  2. username: 'alice123',
  3. isAdmin: true
  4. }, { fields: ['username'] });
  5. // 假设 isAdmin 的默认值为 false
  6. console.log(user.username); // 'alice123'
  7. console.log(user.isAdmin); // false

简单 SELECT 查询

你可以使用 findAll 方法从数据库中读取整个表:

  1. // 查询所有用户
  2. const users = await User.findAll();
  3. console.log(users.every(user => user instanceof User)); // true
  4. console.log("All users:", JSON.stringify(users, null, 2));
  1. SELECT * FROM ...

SELECT 查询特定属性

选择某些特定属性,可以使用 attributes 参数:

  1. Model.findAll({
  2. attributes: ['foo', 'bar']
  3. });
  1. SELECT foo, bar FROM ...

可以使用嵌套数组来重命名属性:

  1. Model.findAll({
  2. attributes: ['foo', ['bar', 'baz'], 'qux']
  3. });
  1. SELECT foo, bar AS baz, qux FROM ...

你可以使用 sequelize.fn 进行聚合:

  1. Model.findAll({
  2. attributes: [
  3. 'foo',
  4. [sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats']
  5. 'bar'
  6. ]
  7. });
  1. SELECT foo, COUNT(hats) AS n_hats, bar FROM ...

使用聚合函数时,必须为它提供一个别名,以便能够从模型中访问它. 在上面的示例中,你可以通过 instance.n_hats 获取帽子数量.

有时,如果只想添加聚合,那么列出模型的所有属性可能会很麻烦:

  1. // 这是获取帽子数量的烦人方法(每列都有)
  2. Model.findAll({
  3. attributes: [
  4. 'id', 'foo', 'bar', 'baz', 'qux', 'hats', // 我们必须列出所有属性...
  5. [sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats'] // 添加聚合...
  6. ]
  7. });
  8. // 这个更短,并且更不易出错. 如果以后在模型中添加/删除属性,它仍然可以正常工作
  9. Model.findAll({
  10. attributes: {
  11. include: [
  12. [sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats']
  13. ]
  14. }
  15. });
  1. SELECT id, foo, bar, baz, qux, hats, COUNT(hats) AS n_hats FROM ...

同样,也可以排除某些属性:

  1. Model.findAll({
  2. attributes: { exclude: ['baz'] }
  3. });
  1. -- Assuming all columns are 'id', 'foo', 'bar', 'baz' and 'qux'
  2. SELECT id, foo, bar, qux FROM ...

应用 WHERE 子句

where 参数用于过滤查询.where 子句有很多运算符,可以从 Op 中以 Symbols 的形式使用.

基础

  1. Post.findAll({
  2. where: {
  3. authorId: 2
  4. }
  5. });
  6. // SELECT * FROM post WHERE authorId = 2

可以看到没有显式传递任何运算符(来自Op),因为默认情况下 Sequelize 假定进行相等比较. 上面的代码等效于:

  1. const { Op } = require("sequelize");
  2. Post.findAll({
  3. where: {
  4. authorId: {
  5. [Op.eq]: 2
  6. }
  7. }
  8. });
  9. // SELECT * FROM post WHERE authorId = 2

可以传递多个校验:

  1. Post.findAll({
  2. where: {
  3. authorId: 12
  4. status: 'active'
  5. }
  6. });
  7. // SELECT * FROM post WHERE authorId = 12 AND status = 'active';

就像在第一个示例中 Sequelize 推断出 Op.eq 运算符一样,在这里 Sequelize 推断出调用者希望对两个检查使用 AND. 上面的代码等效于:

  1. const { Op } = require("sequelize");
  2. Post.findAll({
  3. where: {
  4. [Op.and]: [
  5. { authorId: 12 },
  6. { status: 'active' }
  7. ]
  8. }
  9. });
  10. // SELECT * FROM post WHERE authorId = 12 AND status = 'active';

OR 可以通过类似的方式轻松执行:

  1. const { Op } = require("sequelize");
  2. Post.findAll({
  3. where: {
  4. [Op.or]: [
  5. { authorId: 12 },
  6. { authorId: 13 }
  7. ]
  8. }
  9. });
  10. // SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

由于以上的 OR 涉及相同字段 ,因此 Sequelize 允许你使用稍有不同的结构,该结构更易读并且作用相同:

  1. const { Op } = require("sequelize");
  2. Post.destroy({
  3. where: {
  4. authorId: {
  5. [Op.or]: [12, 13]
  6. }
  7. }
  8. });
  9. // DELETE FROM post WHERE authorId = 12 OR authorId = 13;

操作符

Sequelize 提供了多种运算符.

  1. const { Op } = require("sequelize");
  2. Post.findAll({
  3. where: {
  4. [Op.and]: [{ a: 5 }, { b: 6 }], // (a = 5) AND (b = 6)
  5. [Op.or]: [{ a: 5 }, { b: 6 }], // (a = 5) OR (b = 6)
  6. someAttribute: {
  7. // 基本
  8. [Op.eq]: 3, // = 3
  9. [Op.ne]: 20, // != 20
  10. [Op.is]: null, // IS NULL
  11. [Op.not]: true, // IS NOT TRUE
  12. [Op.or]: [5, 6], // (someAttribute = 5) OR (someAttribute = 6)
  13. // 使用方言特定的列标识符 (以下示例中使用 PG):
  14. [Op.col]: 'user.organization_id', // = "user"."organization_id"
  15. // 数字比较
  16. [Op.gt]: 6, // > 6
  17. [Op.gte]: 6, // >= 6
  18. [Op.lt]: 10, // < 10
  19. [Op.lte]: 10, // <= 10
  20. [Op.between]: [6, 10], // BETWEEN 6 AND 10
  21. [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
  22. // 其它操作符
  23. [Op.all]: sequelize.literal('SELECT 1'), // > ALL (SELECT 1)
  24. [Op.in]: [1, 2], // IN [1, 2]
  25. [Op.notIn]: [1, 2], // NOT IN [1, 2]
  26. [Op.like]: '%hat', // LIKE '%hat'
  27. [Op.notLike]: '%hat', // NOT LIKE '%hat'
  28. [Op.startsWith]: 'hat', // LIKE 'hat%'
  29. [Op.endsWith]: 'hat', // LIKE '%hat'
  30. [Op.substring]: 'hat', // LIKE '%hat%'
  31. [Op.iLike]: '%hat', // ILIKE '%hat' (不区分大小写) (仅 PG)
  32. [Op.notILike]: '%hat', // NOT ILIKE '%hat' (仅 PG)
  33. [Op.regexp]: '^[h|a|t]', // REGEXP/~ '^[h|a|t]' (仅 MySQL/PG)
  34. [Op.notRegexp]: '^[h|a|t]', // NOT REGEXP/!~ '^[h|a|t]' (仅 MySQL/PG)
  35. [Op.iRegexp]: '^[h|a|t]', // ~* '^[h|a|t]' (仅 PG)
  36. [Op.notIRegexp]: '^[h|a|t]', // !~* '^[h|a|t]' (仅 PG)
  37. [Op.any]: [2, 3], // ANY ARRAY[2, 3]::INTEGER (仅 PG)
  38. // 在 Postgres 中, Op.like/Op.iLike/Op.notLike 可以结合 Op.any 使用:
  39. [Op.like]: { [Op.any]: ['cat', 'hat'] } // LIKE ANY ARRAY['cat', 'hat']
  40. // 还有更多的仅限 postgres 的范围运算符,请参见下文
  41. }
  42. }
  43. });

Op.in 的简写语法

直接将数组参数传递给 where 将隐式使用 IN 运算符:

  1. Post.findAll({
  2. where: {
  3. id: [1,2,3] // 等同使用 `id: { [Op.in]: [1,2,3] }`
  4. }
  5. });
  6. // SELECT ... FROM "posts" AS "post" WHERE "post"."id" IN (1, 2, 3);

运算符的逻辑组合

运算符 Op.and, Op.orOp.not 可用于创建任意复杂的嵌套逻辑比较.

使用 Op.andOp.or 示例

  1. const { Op } = require("sequelize");
  2. Foo.findAll({
  3. where: {
  4. rank: {
  5. [Op.or]: {
  6. [Op.lt]: 1000,
  7. [Op.eq]: null
  8. }
  9. },
  10. // rank < 1000 OR rank IS NULL
  11. {
  12. createdAt: {
  13. [Op.lt]: new Date(),
  14. [Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
  15. }
  16. },
  17. // createdAt < [timestamp] AND createdAt > [timestamp]
  18. {
  19. [Op.or]: [
  20. {
  21. title: {
  22. [Op.like]: 'Boat%'
  23. }
  24. },
  25. {
  26. description: {
  27. [Op.like]: '%boat%'
  28. }
  29. }
  30. ]
  31. }
  32. // title LIKE 'Boat%' OR description LIKE '%boat%'
  33. }
  34. });

使用 Op.not 示例

  1. Project.findAll({
  2. where: {
  3. name: 'Some Project',
  4. [Op.not]: [
  5. { id: [1,2,3] },
  6. {
  7. description: {
  8. [Op.like]: 'Hello%'
  9. }
  10. }
  11. ]
  12. }
  13. });

上面将生成:

  1. SELECT *
  2. FROM `Projects`
  3. WHERE (
  4. `Projects`.`name` = 'a project'
  5. AND NOT (
  6. `Projects`.`id` IN (1,2,3)
  7. OR
  8. `Projects`.`description` LIKE 'Hello%'
  9. )
  10. )

高级查询(不仅限于列)

如果你想得到类似 WHERE char_length("content") = 7 的结果怎么办?

  1. Post.findAll({
  2. where: sequelize.where(sequelize.fn('char_length', sequelize.col('content')), 7)
  3. });
  4. // SELECT ... FROM "posts" AS "post" WHERE char_length("content") = 7

请注意方法 sequelize.fnsequelize.col 的用法,应分别用于指定 SQL 函数调用和列. 应该使用这些方法,而不是传递纯字符串(例如 char_length(content)),因为 Sequelize 需要以不同的方式对待这种情况(例如,使用其他符号转义方法).

如果你需要更复杂的东西怎么办?

  1. Post.findAll({
  2. where: {
  3. [Op.or]: [
  4. sequelize.where(sequelize.fn('char_length', sequelize.col('content')), 7),
  5. {
  6. content: {
  7. [Op.like]: 'Hello%'
  8. }
  9. },
  10. {
  11. [Op.and]: [
  12. { status: 'draft' },
  13. sequelize.where(sequelize.fn('char_length', sequelize.col('content')), {
  14. [Op.gt]: 10
  15. })
  16. ]
  17. }
  18. ]
  19. }
  20. });

上面生成了以下SQL:

  1. SELECT
  2. ...
  3. FROM "posts" AS "post"
  4. WHERE (
  5. char_length("content") = 7
  6. OR
  7. "post"."content" LIKE 'Hello%'
  8. OR (
  9. "post"."status" = 'draft'
  10. AND
  11. char_length("content") > 10
  12. )
  13. )

仅限 Postgres 的范围运算符

可以使用所有支持的运算符查询范围类型.

请记住,提供的范围值也可以定义绑定的 包含/排除.

  1. [Op.contains]: 2, // @> '2'::integer (PG range 包含元素运算符)
  2. [Op.contains]: [1, 2], // @> [1, 2) (PG range 包含范围运算符)
  3. [Op.contained]: [1, 2], // <@ [1, 2) (PG range 包含于运算符)
  4. [Op.overlap]: [1, 2], // && [1, 2) (PG range 重叠(有共同点)运算符)
  5. [Op.adjacent]: [1, 2], // -|- [1, 2) (PG range 相邻运算符)
  6. [Op.strictLeft]: [1, 2], // << [1, 2) (PG range 左严格运算符)
  7. [Op.strictRight]: [1, 2], // >> [1, 2) (PG range 右严格运算符)
  8. [Op.noExtendRight]: [1, 2], // &< [1, 2) (PG range 未延伸到右侧运算符)
  9. [Op.noExtendLeft]: [1, 2], // &> [1, 2) (PG range 未延伸到左侧运算符)

不推荐使用: 操作符别名

在 Sequelize v4 中,可以指定字符串来引用运算符,而不是使用 Symbols. 现在不建议使用此方法,很可能在下一个主要版本中将其删除. 如果确实需要,可以在 Sequelize 构造函数中传递 operatorAliases 参数.

例如:

  1. const { Sequelize, Op } = require("sequelize");
  2. const sequelize = new Sequelize('sqlite::memory:', {
  3. operatorsAliases: {
  4. $gt: Op.gt
  5. }
  6. });
  7. // 现在我们可以在 where 子句中使用 `$gt` 代替 `[Op.gt]`:
  8. Foo.findAll({
  9. where: {
  10. $gt: 6 // 就像使用 [Op.gt]
  11. }
  12. });

简单 UPDATE 查询

Update 查询也接受 where 参数,就像上面的读取查询一样.

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

简单 DELETE 查询

Delete 查询也接受 where 参数,就像上面的读取查询一样.

  1. // 删除所有名为 "Jane" 的人
  2. await User.destroy({
  3. where: {
  4. firstName: "Jane"
  5. }
  6. });

要销毁所有内容,可以使用 TRUNCATE SQL:

  1. // 截断表格
  2. await User.destroy({
  3. truncate: true
  4. });

批量创建

Sequelize 提供了 Model.bulkCreate 方法,以允许仅一次查询即可一次创建多个记录.

通过接收数组对象而不是单个对象,Model.bulkCreate 的用法与 Model.create 非常相似.

  1. const captains = await Captain.bulkCreate([
  2. { name: 'Jack Sparrow' },
  3. { name: 'Davy Jones' }
  4. ]);
  5. console.log(captains.length); // 2
  6. console.log(captains[0] instanceof Captain); // true
  7. console.log(captains[0].name); // 'Jack Sparrow'
  8. console.log(captains[0].id); // 1 // (或另一个自动生成的值)

但是,默认情况下,bulkCreate 不会在要创建的每个对象上运行验证(而 create 可以做到). 为了使 bulkCreate 也运行这些验证,必须通过validate: true 参数. 但这会降低性能. 用法示例:

  1. const Foo = sequelize.define('foo', {
  2. bar: {
  3. type: DataTypes.TEXT,
  4. validate: {
  5. len: [4, 6]
  6. }
  7. }
  8. });
  9. // 这不会引发错误,两个实例都将被创建
  10. await Foo.bulkCreate([
  11. { name: 'abc123' },
  12. { name: 'name too long' }
  13. ]);
  14. // 这将引发错误,不会创建任何内容
  15. await Foo.bulkCreate([
  16. { name: 'abc123' },
  17. { name: 'name too long' }
  18. ], { validate: true });

如果你直接从用户获取值,那么限制实际插入的列可能会有所帮助. 为了做到这一点,bulkCreate() 接受一个 fields 参数,该参数须为你要定义字段的数组(其余字段将被忽略).

  1. await User.bulkCreate([
  2. { username: 'foo' },
  3. { username: 'bar', admin: true }
  4. ], { fields: ['username'] });
  5. // foo 和 bar 都不会是管理员.

排序和分组

Sequelize 提供了 order and group 参数,来与 ORDER BYGROUP BY 一起使用.

排序

order 参数采用一系列 来让 sequelize 方法对查询进行排序. 这些 本身是 [column, direction] 形式的数组. 该列将被正确转义,并且将在有效方向列表中进行验证(例如 ASC, DESC, NULLS FIRST 等).

  1. Subtask.findAll({
  2. order: [
  3. // 将转义 title 并针对有效方向列表进行降序排列
  4. ['title', 'DESC'],
  5. // 将按最大年龄进行升序排序
  6. sequelize.fn('max', sequelize.col('age')),
  7. // 将按最大年龄进行降序排序
  8. [sequelize.fn('max', sequelize.col('age')), 'DESC'],
  9. // 将按 otherfunction(`col1`, 12, 'lalala') 进行降序排序
  10. [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
  11. // 将使用模型名称作为关联名称按关联模型的 createdAt 排序.
  12. [Task, 'createdAt', 'DESC'],
  13. // 将使用模型名称作为关联名称通过关联模型的 createdAt 排序.
  14. [Task, Project, 'createdAt', 'DESC'],
  15. // 将使用关联名称按关联模型的 createdAt 排序.
  16. ['Task', 'createdAt', 'DESC'],
  17. // 将使用关联的名称按嵌套的关联模型的 createdAt 排序.
  18. ['Task', 'Project', 'createdAt', 'DESC'],
  19. // 将使用关联对象按关联模型的 createdAt 排序. (首选方法)
  20. [Subtask.associations.Task, 'createdAt', 'DESC'],
  21. // 将使用关联对象按嵌套关联模型的 createdAt 排序. (首选方法)
  22. [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],
  23. // 将使用简单的关联对象按关联模型的 createdAt 排序.
  24. [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
  25. // 将由嵌套关联模型的 createdAt 简单关联对象排序.
  26. [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
  27. ],
  28. // 将按最大年龄降序排列
  29. order: sequelize.literal('max(age) DESC'),
  30. // 如果忽略方向,则默认升序,将按最大年龄升序排序
  31. order: sequelize.fn('max', sequelize.col('age')),
  32. // 如果省略方向,则默认升序, 将按年龄升序排列
  33. order: sequelize.col('age'),
  34. // 将根据方言随机排序(但不是 fn('RAND') 或 fn('RANDOM'))
  35. order: sequelize.random()
  36. });
  37. Foo.findOne({
  38. order: [
  39. // 将返回 `name`
  40. ['name'],
  41. // 将返回 `username` DESC
  42. ['username', 'DESC'],
  43. // 将返回 max(`age`)
  44. sequelize.fn('max', sequelize.col('age')),
  45. // 将返回 max(`age`) DESC
  46. [sequelize.fn('max', sequelize.col('age')), 'DESC'],
  47. // 将返回 otherfunction(`col1`, 12, 'lalala') DESC
  48. [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
  49. // 将返回 otherfunction(awesomefunction(`col`)) DESC, 这种嵌套可能是无限的!
  50. [sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']
  51. ]
  52. });

回顾一下,order 数组的元素可以如下:

  • 一个字符串 (它将被自动引用)
  • 一个数组, 其第一个元素将被引用,第二个将被逐字追加
  • 一个具有 raw 字段的对象:
    • raw 内容将不加引用地逐字添加
    • 其他所有内容都将被忽略,如果未设置 raw,查询将失败
  • 调用 Sequelize.fn (这将在 SQL 中生成一个函数调用)
  • 调用 Sequelize.col (这将引用列名)

分组

分组和排序的语法相同,只是分组不接受方向作为数组的最后一个参数(不存在 ASC, DESC, NULLS FIRST 等).

你还可以将字符串直接传递给 group,该字符串将直接(普通)包含在生成的 SQL 中. 请谨慎使用,请勿与用户生成的内容一起使用.

  1. Project.findAll({ group: 'name' });
  2. // 生成 'GROUP BY name'

限制和分页

使用 limitoffset 参数可以进行 限制/分页:

  1. // 提取10个实例/行
  2. Project.findAll({ limit: 10 });
  3. // 跳过8个实例/行
  4. Project.findAll({ offset: 8 });
  5. // 跳过5个实例,然后获取5个实例
  6. Project.findAll({ offset: 5, limit: 5 });

通常这些与 order 参数一起使用.

实用方法

Sequelize 还提供了一些实用方法.

count

count 方法仅计算数据库中元素出现的次数.

  1. console.log(`这有 ${await Project.count()} 个项目`);
  2. const amount = await Project.count({
  3. where: {
  4. id: {
  5. [Op.gt]: 25
  6. }
  7. }
  8. });
  9. console.log(`这有 ${amount} 个项目 id 大于 25`);

max, minsum

Sequelize 还提供了 max,min 和 sum 便捷方法.

假设我们有三个用户,分别是10、5和40岁.

  1. await User.max('age'); // 40
  2. await User.max('age', { where: { age: { [Op.lt]: 20 } } }); // 10
  3. await User.min('age'); // 5
  4. await User.min('age', { where: { age: { [Op.gt]: 5 } } }); // 10
  5. await User.sum('age'); // 55
  6. await User.sum('age', { where: { age: { [Op.gt]: 5 } } }); // 50