原始查询

由于经常存在执行原始/已准备好的 SQL 查询更容易的用例，因此你可以使用 sequelize.query 方法。

¥As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can use the sequelize.query method.

默认情况下该函数将返回两个参数 - 结果数组和包含元数据的对象（例如受影响的行数等）。请注意，由于这是原始查询，因此元数据是特定于方言的。某些方言返回元数据 "within" 结果对象（作为数组的属性）。然而，总是会返回两个参数，但对于 MSSQL 和 MySQL 来说，它将是对同一对象的两个引用。

¥By default the function will return two arguments - a results array, and an object containing metadata (such as amount of affected rows, etc). Note that since this is a raw query, the metadata are dialect specific. Some dialects return the metadata "within" the results object (as properties on an array). However, two arguments will always be returned, but for MSSQL and MySQL it will be two references to the same object.

const [results, metadata] = await sequelize.query('UPDATE users SET y = 42 WHERE x = 12');
// Results will be an empty array and metadata will contain the number of affected rows.

如果你不需要访问元数据，你可以传入查询类型来告诉 sequelize 如何格式化结果。例如，对于简单的选择查询，你可以执行以下操作：

¥In cases where you don't need to access the metadata you can pass in a query type to tell sequelize how to format the results. For example, for a simple select query you could do:

const { QueryTypes } = require('sequelize');
const users = await sequelize.query('SELECT * FROM `users`', {
  type: QueryTypes.SELECT,
});
// We didn't need to destructure the result here - the results were returned directly

还有其他几种查询类型可用。查看源代码以获取详细信息。

¥Several other query types are available. Peek into the source for details.

第二个选项是模型。如果你传递模型，则返回的数据将是该模型的实例。

¥A second option is the model. If you pass a model the returned data will be instances of that model.

// Callee is the model definition. This allows you to easily map a query to a predefined model
const projects = await sequelize.query('SELECT * FROM projects', {
  model: Projects,
  mapToModel: true, // pass true here if you have any mapped fields
});
// Each element of `projects` is now an instance of Project

在 查询 API 参考 中查看更多选项。一些例子：

¥See more options in the query API reference. Some examples:

const { QueryTypes } = require('sequelize');
await sequelize.query('SELECT 1', {
  // A function (or false) for logging your queries
  // Will get called for every SQL query that gets sent
  // to the server.
  logging: console.log,

  // If plain is true, then sequelize will only return the first
  // record of the result set. In case of false it will return all records.
  plain: false,

  // Set this to true if you don't have a model definition for your query.
  raw: false,

  // The type of query you are executing. The query type affects how results are formatted before they are passed back.
  type: QueryTypes.SELECT,
});

// Note the second argument being null!
// Even if we declared a callee here, the raw: true would
// supersede and return a raw object.
console.log(await sequelize.query('SELECT * FROM projects', { raw: true }));

"点状" 属性和 nest 选项

¥"Dotted" attributes and the nest option

如果表的属性名称包含点，则通过设置 nest: true 选项，生成的对象可以成为嵌套对象。这是通过 dottie.js 在后台实现的。见下文：

¥If an attribute name of the table contains dots, the resulting objects can become nested objects by setting the nest: true option. This is achieved with dottie.js under the hood. See below:

• 没有 nest: true：

  ¥Without nest: true:

  const { QueryTypes } = require('sequelize');
  const records = await sequelize.query('select 1 as `foo.bar.baz`', {
    type: QueryTypes.SELECT,
  });
  console.log(JSON.stringify(records[0], null, 2));

  {
    "foo.bar.baz": 1
  }

• 对于 nest: true：

  ¥With nest: true:

  const { QueryTypes } = require('sequelize');
  const records = await sequelize.query('select 1 as `foo.bar.baz`', {
    nest: true,
    type: QueryTypes.SELECT,
  });
  console.log(JSON.stringify(records[0], null, 2));

  {
    "foo": {
      "bar": {
        "baz": 1
      }
    }
  }

替换

¥Replacements

查询中的替换可以通过两种不同的方式完成，或者使用命名参数（以 : 开头），或者使用未命名参数（由 ? 表示）。替换在选项对象中传递。

¥Replacements in a query can be done in two different ways, either using named parameters (starting with :), or unnamed, represented by a ?. Replacements are passed in the options object.

• 如果传递一个数组，? 将按照它们在数组中出现的顺序被替换

  ¥If an array is passed, ? will be replaced in the order that they appear in the array

• 如果传递一个对象，:key 将替换为该对象中的键。如果对象包含查询中未找到的键，反之亦然，则会引发异常。

  ¥If an object is passed, :key will be replaced with the keys from that object. If the object contains keys not found in the query or vice versa, an exception will be thrown.

const { QueryTypes } = require('sequelize');

await sequelize.query('SELECT * FROM projects WHERE status = ?', {
  replacements: ['active'],
  type: QueryTypes.SELECT,
});

await sequelize.query('SELECT * FROM projects WHERE status = :status', {
  replacements: { status: 'active' },
  type: QueryTypes.SELECT,
});

将自动处理数组替换，以下查询搜索状态与值数组匹配的项目。

¥Array replacements will automatically be handled, the following query searches for projects where the status matches an array of values.

const { QueryTypes } = require('sequelize');

await sequelize.query('SELECT * FROM projects WHERE status IN(:status)', {
  replacements: { status: ['active', 'inactive'] },
  type: QueryTypes.SELECT,
});

要使用通配符 %，请将其附加到你的替换项中。以下查询匹配名称以 'ben' 开头的用户。

¥To use the wildcard operator %, append it to your replacement. The following query matches users with names that start with 'ben'.

const { QueryTypes } = require('sequelize');

await sequelize.query('SELECT * FROM users WHERE name LIKE :search_name', {
  replacements: { search_name: 'ben%' },
  type: QueryTypes.SELECT,
});

绑定参数

¥Bind Parameter

绑定参数就像替换。除了在查询发送到数据库之前替换被转义并通过 sequelize 插入到查询中之外，而绑定参数在 SQL 查询文本之外发送到数据库。查询可以具有绑定参数或替换参数。绑定参数由 $1、$2、... 引用（数字）或 $key（字母数字）。这与方言无关。

¥Bind parameters are like replacements. Except replacements are escaped and inserted into the query by sequelize before the query is sent to the database, while bind parameters are sent to the database outside the SQL query text. A query can have either bind parameters or replacements. Bind parameters are referred to by either $1, $2, ... (numeric) or $key (alpha-numeric). This is independent of the dialect.

• 如果传递数组，则 $1 绑定到数组中的第一个元素 (bind[0])

  ¥If an array is passed, $1 is bound to the 1st element in the array (bind[0])

• 如果传递一个对象，则 $key 绑定到 object['key']。每个键必须以非数字字符开头。即使 object['1'] 存在，$1 也不是有效密钥。

  ¥If an object is passed, $key is bound to object['key']. Each key must begin with a non-numeric char. $1 is not a valid key, even if object['1'] exists.

• 在任何一种情况下，$$ 都可以用来转义字面量 $ 符号。

  ¥In either case $$ can be used to escape a literal $ sign.

数组或对象必须包含所有绑定值，否则 Sequelize 将引发异常。这甚至适用于数据库可能忽略绑定参数的情况。

¥The array or object must contain all bound values or Sequelize will throw an exception. This applies even to cases in which the database may ignore the bound parameter.

数据库可能会对此添加进一步的限制。绑定参数不能是 SQL 关键字，也不能是表名或列名。它们在引用的文本或数据中也会被忽略。在 PostgreSQL 中，如果无法从上下文 $1::varchar 推断出类型，则可能还需要对它们进行类型转换。

¥The database may add further restrictions to this. Bind parameters cannot be SQL keywords, nor table or column names. They are also ignored in quoted text or data. In PostgreSQL it may also be needed to typecast them, if the type cannot be inferred from the context $1::varchar.

const { QueryTypes } = require('sequelize');

await sequelize.query(
  'SELECT *, "text with literal $$1 and literal $$status" as t FROM projects WHERE status = $1',
  {
    bind: ['active'],
    type: QueryTypes.SELECT,
  },
);

await sequelize.query(
  'SELECT *, "text with literal $$1 and literal $$status" as t FROM projects WHERE status = $status',
  {
    bind: { status: 'active' },
    type: QueryTypes.SELECT,
  },
);