查询接口

Sequelize 的实例使用称为查询接口的东西以与方言无关的方式与数据库进行通信。你在本手册中学到的大多数方法都是借助查询接口中的几种方法来实现的。

¥An instance of Sequelize uses something called Query Interface to communicate to the database in a dialect-agnostic way. Most of the methods you've learned in this manual are implemented with the help of several methods from the query interface.

因此，查询接口中的方法是更底层的方法；仅当你找不到其他方法来使用 Sequelize 的更高级别 API 时才应使用它们。当然，它们仍然比直接运行原始查询（即手动编写 SQL）更高级别。

¥The methods from the query interface are therefore lower-level methods; you should use them only if you do not find another way to do it with higher-level APIs from Sequelize. They are, of course, still higher-level than running raw queries directly (i.e., writing SQL by hand).

本指南显示了一些示例，但有关其功能的完整列表以及每种方法的详细用法，请查看 QueryInterface API。

¥This guide shows a few examples, but for the full list of what it can do, and for detailed usage of each method, check the QueryInterface API.

获取查询接口

¥Obtaining the query interface

从现在开始，我们将 queryInterface 称为 QueryInterface 类的单例实例，该实例在你的 Sequelize 实例上可用：

¥From now on, we will call queryInterface the singleton instance of the QueryInterface class, which is available on your Sequelize instance:

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize(/* ... */);
const queryInterface = sequelize.getQueryInterface();

创建表

¥Creating a table

queryInterface.createTable('Person', {
  name: DataTypes.STRING,
  isBetaMember: {
    type: DataTypes.BOOLEAN,
    defaultValue: false,
    allowNull: false,
  },
});

生成的 SQL（使用 SQLite）：

¥Generated SQL (using SQLite):

CREATE TABLE IF NOT EXISTS `Person` (
  `name` VARCHAR(255),
  `isBetaMember` TINYINT(1) NOT NULL DEFAULT 0
);

注意：考虑改为定义一个模型并调用 YourModel.sync()，这是一种更高级别的方法。

¥Note: Consider defining a Model instead and calling YourModel.sync() instead, which is a higher-level approach.

向表中添加一列

¥Adding a column to a table

queryInterface.addColumn('Person', 'petName', { type: DataTypes.STRING });

生成的 SQL（使用 SQLite）：

¥Generated SQL (using SQLite):

ALTER TABLE `Person` ADD `petName` VARCHAR(255);

更改列的数据类型

¥Changing the datatype of a column

queryInterface.changeColumn('Person', 'foo', {
  type: DataTypes.FLOAT,
  defaultValue: 3.14,
  allowNull: false,
});

生成的 SQL（使用 MySQL）：

¥Generated SQL (using MySQL):

ALTER TABLE `Person` CHANGE `foo` `foo` FLOAT NOT NULL DEFAULT 3.14;

删除列

¥Removing a column

queryInterface.removeColumn('Person', 'petName', {
  /* query options */
});

生成的 SQL（使用 PostgreSQL）：

¥Generated SQL (using PostgreSQL):

ALTER TABLE "public"."Person" DROP COLUMN "petName";

更改和删除 SQLite 中的列

¥Changing and removing columns in SQLite

SQLite 不支持直接更改和删除列。然而，受到 这些说明 的启发，Sequelize 将尝试通过在备份表的帮助下重新创建整个表来解决此问题。

¥SQLite does not support directly altering and removing columns. However, Sequelize will try to work around this by recreating the whole table with the help of a backup table, inspired by these instructions.

例如：

¥For example:

// Assuming we have a table in SQLite created as follows:
queryInterface.createTable('Person', {
  name: DataTypes.STRING,
  isBetaMember: {
    type: DataTypes.BOOLEAN,
    defaultValue: false,
    allowNull: false,
  },
  petName: DataTypes.STRING,
  foo: DataTypes.INTEGER,
});

// And we change a column:
queryInterface.changeColumn('Person', 'foo', {
  type: DataTypes.FLOAT,
  defaultValue: 3.14,
  allowNull: false,
});

为 SQLite 生成以下 SQL 调用：

¥The following SQL calls are generated for SQLite:

PRAGMA TABLE_INFO(`Person`);

CREATE TABLE IF NOT EXISTS `Person_backup` (
  `name` VARCHAR(255),
  `isBetaMember` TINYINT(1) NOT NULL DEFAULT 0,
  `foo` FLOAT NOT NULL DEFAULT '3.14',
  `petName` VARCHAR(255)
);

INSERT INTO `Person_backup`
  SELECT
    `name`,
    `isBetaMember`,
    `foo`,
    `petName`
  FROM `Person`;

DROP TABLE `Person`;

CREATE TABLE IF NOT EXISTS `Person` (
  `name` VARCHAR(255),
  `isBetaMember` TINYINT(1) NOT NULL DEFAULT 0,
  `foo` FLOAT NOT NULL DEFAULT '3.14',
  `petName` VARCHAR(255)
);

INSERT INTO `Person`
  SELECT
    `name`,
    `isBetaMember`,
    `foo`,
    `petName`
  FROM `Person_backup`;

DROP TABLE `Person_backup`;

其他

¥Other

正如本指南开头提到的，Sequelize 中的查询接口还有很多可用功能！检查 QueryInterface API 以获取可以执行的操作的完整列表。

¥As mentioned in the beginning of this guide, there is a lot more to the Query Interface available in Sequelize! Check the QueryInterface API for a full list of what can be done.