模型查询 - 基础
Sequelize 提供了各种方法来协助查询数据库中的数据。
¥Sequelize provides various methods to assist querying your database for data.
重要的提醒:要使用 Sequelize 执行生产就绪查询,请确保你也已阅读 事务指南。事务对于确保数据完整性和提供其他好处非常重要。
¥Important notice: to perform production-ready queries with Sequelize, make sure you have read the Transactions guide as well. Transactions are important to ensure data integrity and to provide other benefits.
本指南将展示如何进行标准 CRUD 查询。
¥This guide will show how to make the standard CRUD queries.
简单的 INSERT 查询
¥Simple INSERT queries
首先,一个简单的例子:
¥First, a simple example:
// Create a new user
const jane = await User.create({ firstName: 'Jane', lastName: 'Doe' });
console.log("Jane's auto-generated ID:", jane.id);
Model.create()
方法是使用 Model.build()
构建未保存实例并使用 instance.save()
保存实例的简写。
¥The Model.create()
method is a shorthand for building an unsaved instance with Model.build()
and saving the instance with instance.save()
.
还可以定义可以在 create
方法中设置哪些属性。如果你基于可由用户填写的表单创建数据库条目,这会特别有用。例如,使用它可以让你限制 User
模型仅设置用户名而不设置管理员标志(即 isAdmin
):
¥It is also possible to define which attributes can be set in the create
method. This can be especially useful if you create database entries based on a form which can be filled by a user. Using that would, for example, allow you to restrict the User
model to set only a username but not an admin flag (i.e., isAdmin
):
const user = await User.create(
{
username: 'alice123',
isAdmin: true,
},
{ fields: ['username'] },
);
// let's assume the default of isAdmin is false
console.log(user.username); // 'alice123'
console.log(user.isAdmin); // false
简单的 SELECT 查询
¥Simple SELECT queries
你可以使用 findAll
方法从数据库读取整个表:
¥You can read the whole table from the database with the findAll
method:
// Find all users
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 查询的属性
¥Specifying attributes for SELECT queries
要仅选择某些属性,可以使用 attributes
选项:
¥To select only some attributes, you can use the attributes
option:
Model.findAll({
attributes: ['foo', 'bar'],
});
SELECT foo, bar FROM ...
可以使用嵌套数组重命名属性:
¥Attributes can be renamed using a nested array:
Model.findAll({
attributes: ['foo', ['bar', 'baz'], 'qux'],
});
SELECT foo, bar AS baz, qux FROM ...
你可以使用 sequelize.fn
进行聚合:
¥You can use sequelize.fn
to do aggregations:
Model.findAll({
attributes: ['foo', [sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats'], 'bar'],
});
SELECT foo, COUNT(hats) AS n_hats, bar FROM ...
使用聚合函数时,必须为其指定别名才能从模型访问它。在上面的示例中,你可以通过 instance.n_hats
获取帽子的数量。
¥When using aggregation function, you must give it an alias to be able to access it from the model. In the example above you can get the number of hats with instance.n_hats
.
有时,如果你只想添加聚合,则列出模型的所有属性可能会很烦人:
¥Sometimes it may be tiresome to list all the attributes of the model if you only want to add an aggregation:
// This is a tiresome way of getting the number of hats (along with every column)
Model.findAll({
attributes: [
'id',
'foo',
'bar',
'baz',
'qux',
'hats', // We had to list all attributes...
[sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats'], // To add the aggregation...
],
});
// This is shorter, and less error prone because it still works if you add / remove attributes from your model later
Model.findAll({
attributes: {
include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats']],
},
});
SELECT id, foo, bar, baz, qux, hats, COUNT(hats) AS n_hats FROM ...
同样,也可以删除选定的几个属性:
¥Similarly, it's also possible to remove a selected few attributes:
Model.findAll({
attributes: { exclude: ['baz'] },
});
-- Assuming all columns are 'id', 'foo', 'bar', 'baz' and 'qux'
SELECT id, foo, bar, qux FROM ...
应用 WHERE 子句
¥Applying WHERE clauses
where
选项用于过滤查询。有许多运算符可用于 where
子句,可作为 Op
中的符号使用。
¥The where
option is used to filter the query. There are lots of operators to use for the where
clause, available as Symbols from Op
.
基础
¥The basics
Post.findAll({
where: {
authorId: 2,
},
});
// SELECT * FROM post WHERE authorId = 2;
请注意,没有显式传递任何运算符(来自 Op
),因此 Sequelize 默认情况下采用相等比较。上面的代码相当于:
¥Observe that no operator (from Op
) was explicitly passed, so Sequelize assumed an equality comparison by default. The above code is equivalent to:
const { Op } = require('sequelize');
Post.findAll({
where: {
authorId: {
[Op.eq]: 2,
},
},
});
// SELECT * FROM post WHERE authorId = 2;
可以通过多项检查:
¥Multiple checks can be passed:
Post.findAll({
where: {
authorId: 12,
status: 'active',
},
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';
就像 Sequelize 在第一个示例中推断出 Op.eq
运算符一样,这里 Sequelize 推断出调用者想要 AND
来进行这两项检查。上面的代码相当于:
¥Just like Sequelize inferred the Op.eq
operator in the first example, here Sequelize inferred that the caller wanted an AND
for the two checks. The code above is equivalent to:
const { Op } = require('sequelize');
Post.findAll({
where: {
[Op.and]: [{ authorId: 12 }, { status: 'active' }],
},
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';
OR
可以通过类似的方式轻松执行:
¥An OR
can be easily performed in a similar way:
const { Op } = require('sequelize');
Post.findAll({
where: {
[Op.or]: [{ authorId: 12 }, { authorId: 13 }],
},
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
由于上面是涉及相同字段的 OR