Model Querying - Basics - 模型查询(基础)
Sequelize 提供了多种方法来协助查询数据库中的数据.
重要说明：要使用 Sequelize 执行生产级别的查询,请确保你还阅读了事务指南. 事务对于确保数据完整性和提供其它好处很重要.
本指南将说明如何进行标准的 增删改查(CRUD) 查询.
简单 INSERT 查询
首先,一个简单的例子：
// 创建一个新用户
const jane = await User.create({ firstName: "Jane", lastName: "Doe" });
console.log("Jane's auto-generated ID:", jane.id);
Model.create() 方法是使用 Model.build() 构建未保存实例并使用 instance.save() 保存实例的简写形式.
也可以定义在 create 方法中的属性. 如果你基于用户填写的表单创建数据库条目,这将特别有用. 例如,使用它可以允许你将 User 模型限制为仅设置用户名和地址,而不设置管理员标志 (例如, isAdmin)：
const user = await User.create({
username: 'alice123',
isAdmin: true
}, { fields: ['username'] });
// 假设 isAdmin 的默认值为 false
console.log(user.username); // 'alice123'
console.log(user.isAdmin); // false
简单 SELECT 查询
你可以使用 findAll 方法从数据库中读取整个表：
// 查询所有用户
const users = await User.findAll();
console.log(users.every(user => user instanceof User)); // true
console.log("All users:", JSON.stringify(users, null, 2));
SELECT * FROM ...
SELECT 查询特定属性
选择某些特定属性,可以使用 attributes 参数：
Model.findAll({
attributes: ['foo', 'bar']
});
SELECT foo, bar FROM ...
可以使用嵌套数组来重命名属性：
Model.findAll({
attributes: ['foo', ['bar', 'baz'], 'qux']
});
SELECT foo, bar AS baz, qux FROM ...
你可以使用 fn 进行聚合：
import { fn, col } from '@sequelize/core';
Model.findAll({
attributes: [
'foo',
[fn('COUNT', col('hats')), 'n_hats'],
'bar'
]
});
SELECT foo, COUNT(hats) AS n_hats, bar FROM ...
使用聚合函数时,必须为它提供一个别名,以便能够从模型中访问它. 在上面的示例中,你可以通过 instance.n_hats 获取帽子数量.
有时,如果只想添加聚合,那么列出模型的所有属性可能会很麻烦：
import { fn, col } from '@sequelize/core';
// 这是获取帽子数量的烦人方法(每列都有)
Model.findAll({
attributes: [
'id', 'foo', 'bar', 'baz', 'qux', 'hats', // 我们必须列出所有属性...
[fn('COUNT', col('hats')), 'n_hats'] // 添加聚合...
]
});
// 这个更短,并且更不易出错. 如果以后在模型中添加/删除属性,它仍然可以正常工作
Model.findAll({
attributes: {
include: [
[fn('COUNT', col('hats')), 'n_hats']
]
}
});
SELECT id, foo, bar, baz, qux, hats, COUNT(hats) AS n_hats FROM ...
同样,也可以排除某些属性：
Model.findAll({
attributes: { exclude: ['baz'] }
});
-- Assuming all columns are 'id', 'foo', 'bar', 'baz' and 'qux'
SELECT id, foo, bar, qux FROM ...
应用 WHERE 子句
where 参数用于过滤查询.where 子句有很多运算符,可以从 Op 中以 Symbols 的形式使用.
基础
Post.findAll({
where: {
authorId: 2
}
});
// SELECT * FROM post WHERE authorId = 2;
可以看到没有显式传递任何运算符(来自Op),因为默认情况下 Sequelize 假定进行相等比较. 上面的代码等效于：
const { Op } = require('@sequelize/core');
Post.findAll({
where: {
authorId: {
[Op.eq]: 2
}
}
});
// SELECT * FROM post WHERE authorId = 2;
可以传递多个校验:
Post.findAll({
where: {
authorId: 12,
status: 'active'
}
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';
就像在第一个示例中 Sequelize 推断出 Op.eq 运算符一样,在这里 Sequelize 推断出调用者希望对两个检查使用 AND. 上面的代码等效于：
const { Op } = require('@sequelize/core');
Post.findAll({
where: {
[Op.and]: [
{ authorId: 12 },
{ status: 'active' }
]
}
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';
OR 可以通过类似的方式轻松执行：
const { Op } = require('@sequelize/core');
Post.findAll({
where: {
[Op.or]: [
{ authorId: 12 },
{ authorId: 13 }
]
}
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
由于以上的 OR 涉及相同字段 ,因此 Sequelize 允许你使用稍有不同的结构,该结构更易读并且作用相同：
const { Op } = require('@sequelize/core');
Post.destroy({
where: {
authorId: {
[Op.or]: [12, 13]
}
}
});
// DELETE FROM post WHERE authorId = 12 OR authorId = 13;
操作符
Sequelize 提供了多种运算符.
const { Op, literal, fn } = require('@sequelize/core');
Post.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)
// 使用方言特定的列标识符 (以下示例中使用 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]: 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)
[Op.match]: fn('to_tsquery', 'fat & rat') // 匹配文本搜索字符串 'fat' 和 'rat' (仅 PG)
// 在 Postgres 中, Op.like/Op.iLike/Op.notLike 可以结合 Op.any 使用:
[Op.like]: { [Op.any]: ['cat', 'hat'] } // LIKE ANY (ARRAY['cat', 'hat'])
// 还有更多的仅限 postgres 的范围运算符,请参见下文
}
}
});
Op.in 的简写语法
直接将数组参数传递给 where 将隐式使用 IN 运算符：
Post.findAll({
where: {
id: [1,2,3] // 等同使用 `id: { [Op.in]: [1,2,3] }`
}
});
// SELECT ... FROM "posts" AS "post" WHERE "post"."id" IN (1, 2, 3);
运算符的逻辑组合
运算符 Op.and, Op.or 和 Op.not 可用于创建任意复杂的嵌套逻辑比较.
使用 Op.and 和 Op.or 示例
const { Op } = require('@sequelize/core');
Foo.findAll({
where: {
rank: {
[Op.or]: {
[Op.lt]: 1000,
[Op.eq]: null
}
},
// rank < 1000 OR rank IS NULL
{
createdAt: {
[Op.lt]: new Date(),
[Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
}
},
// createdAt < [timestamp] AND createdAt > [timestamp]
{
[Op.or]: [
{
title: {
[Op.like]: 'Boat%'
}
},
{
description: {
[Op.like]: '%boat%'
}
}
]
}
// title LIKE 'Boat%' OR description LIKE '%boat%'
}
});
使用 Op.not 示例
Project.findAll({
where: {
name: 'Some Project',
[Op.not]: [
{ id: [1,2,3] },
{
description: {
[Op.like]: 'Hello%'
}
}
]
}
});
上面将生成：
SELECT *
FROM `Projects`
WHERE (
`Projects`.`name` = 'Some Project'
AND NOT (
`Projects`.`id` IN (1,2,3)
AND
`Projects`.`description` LIKE 'Hello%'
)
)
高级查询(不仅限于列)
如果你想得到类似 WHERE char_length("content") = 7 的结果怎么办？
import { where, fn, col } from '@sequelize/core';
Post.findAll({
where: where(fn('char_length', col('content')), 7)
});
// SELECT ... FROM "posts" AS "post" WHERE char_length("content") = 7
请注意方法 fn 和 col 的用法,应分别用于指定 SQL 函数调用和列. 应该使用这些方法,而不是传递纯字符串(例如 char_length(content)),因为 Sequelize 需要以不同的方式对待这种情况(例如,使用其他符号转义方法).
如果你需要更复杂的东西怎么办？
import { where, fn, col, Op } from '@sequelize/core';
Post.findAll({
where: {
[Op.or]: [
where(fn('char_length', col('content')), 7),
{
content: {
[Op.like]: 'Hello%'
}
},
{
[Op.and]: [
{ status: 'draft' },
where(fn('char_length', col('content')), {
[Op.gt]: 10
})
]
}
]
}
});
上面生成了以下SQL：
SELECT
...
FROM "posts" AS "post"
WHERE (
char_length("content") = 7
OR
"post"."content" LIKE 'Hello%'
OR (
"post"."status" = 'draft'
AND
char_length("content") > 10
)
)
查询 JSON
可以通过三种不同的方式查询 JSON:
// 嵌套对象
await Foo.findOne({
where: {
meta: {
video: {
url: {
[Op.ne]: null
}
}
}
}
});
// 嵌套键
await Foo.findOne({
where: {
"meta.audio.length": {
[Op.gt]: 20
}
}
});
// 内容物
await Foo.findOne({
where: {
meta: {
[Op.contains]: {
site: {
url: 'https://google.com'
}
}
}
}
});
MSSQL
MSSQL 没有 JSON 数据类型, 但是自 SQL Server 2016 以来, 它确实通过某些函数为存储为字符串的 JSON 提供了一些支持. 使用这些函数, 你将能够查询存储在字符串中的 JSON, 但任何返回值都将 需要单独解析.
import { where, fn, col } from '@sequelize/core';
// ISJSON - 测试字符串是否包含有效的 JSON
await User.findAll({
where: where(fn('ISJSON', col('userDetails')), 1)
});
// JSON_VALUE - 从 JSON 字符串中提取标量值
await User.findAll({
attributes: [[ fn('JSON_VALUE', col('userDetails'), '$.address.Line1'), 'address line 1']]
});
// JSON_VALUE - 从 JSON 字符串查询标量值
await User.findAll({
where: where(fn('JSON_VALUE', col('userDetails'), '$.address.Line1'), '14, Foo Street')
});
// JSON_QUERY - 提取一个对象或数组
await User.findAll({
attributes: [[ fn('JSON_QUERY', col('userDetails'), '$.address'), 'full address']]
});
仅限 Postgres 的范围运算符
可以使用所有支持的运算符查询范围类型.
请记住,提供的范围值也可以定义绑定的 包含/排除.
[Op.contains]: 2, // @> '2'::integer (PG range 包含元素运算符)
[Op.contains]: [1, 2], // @> [1, 2) (PG range 包含范围运算符)
[Op.contained]: [1, 2], // <@ [1, 2) (PG range 包含于运算符)
[Op.overlap]: [1, 2], // && [1, 2) (PG range 重叠(有共同点)运算符)
[Op.adjacent]: [1, 2], // -|- [1, 2) (PG range 相邻运算符)
[Op.strictLeft]: [1, 2], // << [1, 2) (PG range 左严格运算符)
[Op.strictRight]: [1, 2], // >> [1, 2) (PG range 右严格运算符)
[Op.noExtendRight]: [1, 2], // &< [1, 2) (PG range 未延伸到右侧运算符)
[Op.noExtendLeft]: [1, 2], // &> [1, 2) (PG range 未延伸到左侧运算符)
不推荐使用: 操作符别名
在 Sequelize v4 中,可以指定字符串来引用运算符,而不是使用 Symbols. 现在不建议使用此方法,很可能在下一个主要版本中将其删除. 如果确实需要,可以在 Sequelize 构造函数中传递 operatorAliases 参数.
例如:
const { Sequelize, Op } = require('@sequelize/core');
const sequelize = new Sequelize('sqlite::memory:', {
operatorsAliases: {
$gt: Op.gt
}
});
// 现在我们可以在 where 子句中使用 `$gt` 代替 `[Op.gt]`：
Foo.findAll({
where: {
$gt: 6 // 就像使用 [Op.gt]
}
});
简单 UPDATE 查询
Update 查询也接受 where 参数,就像上面的读取查询一样.
// 将所有没有姓氏的人更改为 "Doe"
await User.update({ lastName: "Doe" }, {
where: {
lastName: null
}
});
简单 DELETE 查询
Delete 查询也接受 where 参数,就像上面的读取查询一样.
// 删除所有名为 "Jane" 的人
await User.destroy({
where: {
firstName: "Jane"
}
});
要销毁所有内容,可以使用 TRUNCATE SQL：
// 截断表格
await User.destroy({
truncate: true
});
批量创建
Sequelize 提供了 Model.bulkCreate 方法,以允许仅一次查询即可一次创建多个记录.
通过接收数组对象而不是单个对象,Model.bulkCreate 的用法与 Model.create 非常相似.
const captains = await Captain.bulkCreate([
{ name: 'Jack Sparrow' },
{ name: 'Davy Jones' }
]);
console.log(captains.length); // 2
console.log(captains[0] instanceof Captain); // true
console.log(captains[0].name); // 'Jack Sparrow'
console.log(captains[0].id); // 1 // (或另一个自动生成的值)
但是,默认情况下,bulkCreate 不会在要创建的每个对象上运行验证(而 create 可以做到). 为了使 bulkCreate 也运行这些验证,必须通过validate: true 参数. 但这会降低性能. 用法示例：
const Foo = sequelize.define('foo', {
bar: {
type: DataTypes.TEXT,
validate: {
len: [4, 6]
}
}
});
// 这不会引发错误,两个实例都将被创建
await Foo.bulkCreate([
{ name: 'abc123' },
{ name: 'name too long' }
]);
// 这将引发错误,不会创建任何内容
await Foo.bulkCreate([
{ name: 'abc123' },
{ name: 'name too long' }
], { validate: true });
如果你直接从用户获取值,那么限制实际插入的列可能会有所帮助. 为了做到这一点,bulkCreate() 接受一个 fields 参数,该参数须为你要定义字段的数组(其余字段将被忽略).
await User.bulkCreate([
{ username: 'foo' },
{ username: 'bar', admin: true }
], { fields: ['username'] });
// foo 和 bar 都不会是管理员.
排序和分组
Sequelize 提供了 order and group 参数,来与 ORDER BY 和 GROUP BY 一起使用.
排序
order 参数采用一系列 项 来让 sequelize 方法对查询进行排序. 这些 项 本身是 [column, direction] 形式的数组. 该列将被正确转义,并且将在有效方向列表中进行验证(例如 ASC, DESC, NULLS FIRST 等).
import { where, fn, col, literal } from '@sequelize/core';
Subtask.findAll({
order: [
// 将转义 title 并针对有效方向列表进行降序排列
['title', 'DESC'],
// 将按最大年龄进行升序排序
fn('max', col('age')),
// 将按最大年龄进行降序排序
[fn('max', col('age')), 'DESC'],
// 将按 otherfunction(`col1`, 12, 'lalala') 进行降序排序
[fn('otherfunction', col('col1'), 12, 'lalala'), 'DESC'],
// 将使用模型名称作为关联名称按关联模型的 createdAt 排序.
[Task, 'createdAt', 'DESC'],
// 将使用模型名称作为关联名称通过关联模型的 createdAt 排序.
[Task, Project, 'createdAt', 'DESC'],
// 将使用关联名称按关联模型的 createdAt 排序.
['Task', 'createdAt', 'DESC'],
// 将使用关联的名称按嵌套的关联模型的 createdAt 排序.
['Task', 'Project', 'createdAt', 'DESC'],
// 将使用关联对象按关联模型的 createdAt 排序. (首选方法)
[Subtask.associations.Task, 'createdAt', 'DESC'],
// 将使用关联对象按嵌套关联模型的 createdAt 排序. (首选方法)
[Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],
// 将使用简单的关联对象按关联模型的 createdAt 排序.
[{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
// 将由嵌套关联模型的 createdAt 简单关联对象排序.
[{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
],
// 将按最大年龄降序排列
order: literal('max(age) DESC'),
// 如果忽略方向,则默认升序,将按最大年龄升序排序
order: fn('max', col('age')),
// 如果省略方向,则默认升序, 将按年龄升序排列
order: col('age'),
// 将根据方言随机排序(但不是 fn('RAND') 或 fn('RANDOM'))
order: sequelize.random()
});
Foo.findOne({
order: [
// 将返回 `name`
['name'],
// 将返回 `username` DESC
['username', 'DESC'],
// 将返回 max(`age`)
fn('max', col('age')),
// 将返回 max(`age`) DESC
[fn('max', col('age')), 'DESC'],
// 将返回 otherfunction(`col1`, 12, 'lalala') DESC
[fn('otherfunction', col('col1'), 12, 'lalala'), 'DESC'],
// 将返回 otherfunction(awesomefunction(`col`)) DESC, 这种嵌套可能是无限的!
[fn('otherfunction', fn('awesomefunction', col('col'))), 'DESC']
]
});
回顾一下,order 数组的元素可以如下：
- 一个字符串 (它将被自动引用)
- 一个数组, 其第一个元素将被引用,第二个将被逐字追加
- 一个具有
raw字段的对象:raw内容将不加引用地逐字添加- 其他所有内容都将被忽略,如果未设置
raw,查询将失败
- 调用
fn()(这将在 SQL 中生成一个函数调用) - 调用
col()(这将引用列名)
分组
分组和排序的语法相同,只是分组不接受方向作为数组的最后一个参数(不存在 ASC, DESC, NULLS FIRST 等).
你还可以将字符串直接传递给 group,该字符串将直接(普通)包含在生成的 SQL 中. 请谨慎使用,请勿与用户生成的内容一起使用.
Project.findAll({ group: 'name' });
// 生成 'GROUP BY name'
限制和分页
使用 limit 和 offset 参数可以进行 限制/分页：
// 提取10个实例/行
Project.findAll({ limit: 10 });
// 跳过8个实例/行
Project.findAll({ offset: 8 });
// 跳过5个实例,然后获取5个实例
Project.findAll({ offset: 5, limit: 5 });
通常这些与 order 参数一起使用.
实用方法
Sequelize 还提供了一些实用方法.
count
count 方法仅计算数据库中元素出现的次数.
console.log(`这有 ${await Project.count()} 个项目`);
const amount = await Project.count({
where: {
id: {
[Op.gt]: 25
}
}
});
console.log(`这有 ${amount} 个项目 id 大于 25`);
max, min 和 sum
Sequelize 还提供了 max,min 和 sum 便捷方法.
假设我们有三个用户,分别是10、5和40岁.
await User.max('age'); // 40
await User.max('age', { where: { age: { [Op.lt]: 20 } } }); // 10
await User.min('age'); // 5
await User.min('age', { where: { age: { [Op.gt]: 5 } } }); // 10
await User.sum('age'); // 55
await User.sum('age', { where: { age: { [Op.gt]: 5 } } }); // 50
increment, decrement
Sequelize 还提供了 increment 简便方法.
假设我们有一个用户, 他的年龄是 10 岁.
await User.increment({age: 5}, { where: { id: 1 } }) // 将年龄增加到15岁
await User.increment({age: -5}, { where: { id: 1 } }) // 将年龄降至5岁
literal (原生 SQL)
Sequelize 并不总是能够以干净的方式支持每个 SQL 特性. 有时自己编写 SQL 查询会更好.
这可以通过两种方式完成:
import { literal } from '@sequelize/core';
User.findAll({
where: literal('id = $id'),
bind: {
id: 5,
},
});