Skip to main content
Version: v6 - stable

方言特定的事物

底层连接器库​

¥Underlying Connector Libraries

MySQL​

Sequelize for MySQL 使用的底层连接器库是 mysql2 npm 包（版本 1.5.2 或更高版本）。

¥The underlying connector library used by Sequelize for MySQL is the mysql2 npm package (version 1.5.2 or higher).

你可以使用 Sequelize 构造函数中的 dialectOptions 为其提供自定义选项：

¥You can provide custom options to it using the dialectOptions in the Sequelize constructor:

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mysql',
  dialectOptions: {
    // Your mysql2 options here
  },
});

dialectOptions 直接传递给 MySQL 连接构造函数。完整的选项列表可以在 MySQL 文档。

¥dialectOptions are passed directly to the MySQL connection constructor. A full list of options can be found in the MySQL docs.

MariaDB​

Sequelize for MariaDB 使用的底层连接器库是 mariadb npm 包。

¥The underlying connector library used by Sequelize for MariaDB is the mariadb npm package.

你可以使用 Sequelize 构造函数中的 dialectOptions 为其提供自定义选项：

¥You can provide custom options to it using the dialectOptions in the Sequelize constructor:

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mariadb',
  dialectOptions: {
    // Your mariadb options here
    // connectTimeout: 1000
  },
});

dialectOptions 直接传递给 MariaDB 连接构造函数。完整的选项列表可以在 MariaDB 文档。

¥dialectOptions are passed directly to the MariaDB connection constructor. A full list of options can be found in the MariaDB docs.

SQLite​

Sequelize for SQLite 使用的底层连接器库是 sqlite3 npm 包（版本 4.0.0 或更高版本）。由于 sqlite3@^4 存在安全漏洞，如果无法更新到 sqlite3@^5.0.3，建议使用 @vscode/sqlite3 分支。

¥The underlying connector library used by Sequelize for SQLite is the sqlite3 npm package (version 4.0.0 or above).\ Due to security vulnerabilities with sqlite3@^4 it is recommended to use the @vscode/sqlite3 fork if updating to sqlite3@^5.0.3 is not possible.

你可以在 Sequelize 构造函数中使用 storage 选项指定存储文件（对于内存中 SQLite 实例使用 :memory:）。

¥You specify the storage file in the Sequelize constructor with the storage option (use :memory: for an in-memory SQLite instance).

你可以使用 Sequelize 构造函数中的 dialectOptions 为其提供自定义选项：

¥You can provide custom options to it using the dialectOptions in the Sequelize constructor:

import { Sequelize } from 'sequelize';
import SQLite from 'sqlite3';

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'sqlite',
  storage: 'path/to/database.sqlite', // or ':memory:'
  dialectOptions: {
    // Your sqlite3 options here
    // for instance, this is how you can configure the database opening mode:
    mode: SQLite.OPEN_READWRITE | SQLite.OPEN_CREATE | SQLite.OPEN_FULLMUTEX,
  },
});

以下字段可以传递给 SQLite dialectOptions：

¥The following fields may be passed to SQLite dialectOptions:

  • mode：设置 SQLite 连接的打开方式。潜在值由 sqlite3 包提供，可以包括 SQLite.OPEN_READONLY、SQLite.OPEN_READWRITE 或 SQLite.OPEN_CREATE。有关详细信息，请参阅 sqlite3 的 API 参考 和 SQLite C 接口文档。

    ¥mode: Set the opening mode for the SQLite connection. Potential values are provided by the sqlite3 package, and can include SQLite.OPEN_READONLY, SQLite.OPEN_READWRITE, or SQLite.OPEN_CREATE.\ See sqlite3's API reference and the SQLite C interface documentation for more details.

PostgreSQL​

Sequelize for PostgreSQL 使用的底层连接器库是 pg 包（对于 Node 10 和 12，使用 pg 版本 7.0.0 或更高版本。对于 Node 14 及更高版本，你需要使用 pg 版本 8.2.x 或更高版本，按照 PG 文档） 。模块 pg-hstore 也是必需的。

¥The underlying connector library used by Sequelize for PostgreSQL is the pg package (for Node 10 & 12, use pg version 7.0.0 or above. For Node 14 and above you need to use pg version 8.2.x or above, as per the pg documentation). The module pg-hstore is also necessary.

你可以使用 Sequelize 构造函数中的 dialectOptions 为其提供自定义选项：

¥You can provide custom options to it using the dialectOptions in the Sequelize constructor:

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'postgres',
  dialectOptions: {
    // Your pg options here
  },
});

以下字段可以传递到 Postgres dialectOptions：

¥The following fields may be passed to Postgres dialectOptions:

  • application_name：pg_stat_activity 中的应用名称。详细信息请参见 Postgres 文档。

    ¥application_name: Name of application in pg_stat_activity. See the Postgres docs for details.

  • ssl：SSL 选项。详细信息请参见 pg 文档。

    ¥ssl: SSL options. See the pg docs for details.

  • client_encoding：// Setting 'auto' determines locale based on the client LC_CTYPE environment variable.详细信息请参见 Postgres 文档。

    ¥client_encoding: // Setting 'auto' determines locale based on the client LC_CTYPE environment variable. See the Postgres docs for details.

  • keepAlive：用于启用 TCP KeepAlive 的布尔值。详细信息请参见 pg 变更日志。

    ¥keepAlive: Boolean to enable TCP KeepAlive. See the pg changelog for details.

  • statement_timeout：在设定的时间（以毫秒为单位）后查询超时。在 pg v7.3 中添加。详细信息请参见 Postgres 文档。

    ¥statement_timeout: Times out queries after a set time in milliseconds. Added in pg v7.3. See the Postgres docs for details.

  • idle_in_transaction_session_timeout：终止具有空闲时间超过指定持续时间（以毫秒为单位）的打开事务的任何会话。详细信息请参见 Postgres 文档。

    ¥idle_in_transaction_session_timeout: Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. See the Postgres docs for details.

要通过 unix 域套接字进行连接，请在 host 选项中指定套接字目录的路径。套接字路径必须以 / 开头。

¥To connect over a unix domain socket, specify the path to the socket directory in the host option. The socket path must start with /.

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'postgres',
  host: '/path/to/socket_directory',
});

Sequelize 中默认的 client_min_messages 配置是 WARNING。

¥The default client_min_messages config in sequelize is WARNING.

Redshift​

大多数配置与上面的 PostgreSQL 相同。

¥Most configuration is same as PostgreSQL above.

Redshift 不支持 client_min_messages，需要 'ignore' 才能跳过配置：

¥Redshift doesn't support client_min_messages, 'ignore' is needed to skip the configuration:

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'postgres',
  dialectOptions: {
    // Your pg options here
    // ...
    clientMinMessages: 'ignore', // case insensitive
  },
});

Microsoft SQL Server (MSSQL)​

Sequelize for MSSQL 使用的底层连接器库是 tedious npm 包（版本 6.0.0 或更高版本）。

¥The underlying connector library used by Sequelize for MSSQL is the tedious npm package (version 6.0.0 or above).

你可以在 Sequelize 构造函数中使用 dialectOptions.options 为其提供自定义选项：

¥You can provide custom options to it using dialectOptions.options in the Sequelize constructor:

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mssql',
  dialectOptions: {
    // Observe the need for this nested `options` field for MSSQL
    options: {
      // Your tedious options here
      useUTC: false,
      dateFirst: 1,
    },
  },
});

完整的选项列表可以在 乏味的文档。

¥A full list of options can be found in the tedious docs.

MSSQL 域账户​

¥MSSQL Domain Account

要连接域账户，请使用以下格式。

¥In order to connect with a domain account, use the following format.

const sequelize = new Sequelize('database', null, null, {
  dialect: 'mssql',
  dialectOptions: {
    authentication: {
      type: 'ntlm',
      options: {
        domain: 'yourDomain',
        userName: 'username',
        password: 'password',
      },
    },
    options: {
      instanceName: 'SQLEXPRESS',
    },
  },
});

Snowflake（实验性）​

¥Snowflake (Experimental)

Sequelize for Snowflake 使用的底层连接器库是 snowflake-sdk npm 包。

¥The underlying connector library used by Sequelize for Snowflake is the snowflake-sdk npm package.

要连接账户，请使用以下格式：

¥In order to connect with an account, use the following format:

const sequelize = new Sequelize('database', null, null, {
  dialect: 'snowflake',
  dialectOptions: {
    // put your snowflake account here,
    account: 'myAccount', // my-app.us-east-1

    // below option should be optional
    role: 'myRole',
    warehouse: 'myWarehouse',
    schema: 'mySchema',
  },
  // same as other dialect
  username: 'myUserName',
  password: 'myPassword',
  database: 'myDatabaseName',
});

注意 没有提供测试沙箱，因此雪花集成测试不是管道的一部分。核心团队也很难进行分类和调试。该方言目前需要由雪花用户/社区维护。

¥NOTE There is no test sandbox provided so the snowflake integration test is not part of the pipeline. Also it is difficult for core team to triage and debug. This dialect needs to be maintained by the snowflake user/community for now.

对于运行集成测试：

¥For running integration test:

SEQ_ACCOUNT=myAccount SEQ_USER=myUser SEQ_PW=myPassword SEQ_ROLE=myRole SEQ_DB=myDatabaseName SEQ_SCHEMA=mySchema SEQ_WH=myWareHouse npm run test-integration-snowflake

甲骨文数据库​

¥Oracle Database

Sequelize for Oracle 使用的底层连接器库是 node-oracledb 包。请参阅 发布 以了解支持哪些版本的 Oracle 数据库和 node-oracledb。

¥The underlying connector library used by Sequelize for Oracle is the node-oracledb package.\ See Releases to see which versions of Oracle Database & node-oracledb are supported.

node-oracledb 需要 Oracle 即时客户端 才能工作。你可以使用 node-oracledb 快速开始 链接进行安装。

¥node-oracledb needs Oracle Instant Client to work. You can use the node-oracledb quick start link for installations.

下面是一个带有与 Oracle 数据库相关的参数的 Sequelize 构造函数。

¥Below is a Sequelize constructor with parameters related to Oracle Database.

const sequelize = new Sequelize('servicename', 'username', 'password', {
  dialect: 'oracle',
  host: 'hostname',
  port: 'port number', //optional
});

Oracle 数据库的默认端口号是 1521。

¥The default port number for Oracle database is 1521.

Sequelize 还允许你以 URL 格式传递凭据：

¥Sequelize also lets you pass credentials in URL format:

const sequelize = new Sequelize('oracle://user:pass@hostname:port/servicename');

你可以使用 dialectOptions.connectString 将 Easy Connect 字符串、网络服务名称或连接描述符传递给 Sequelize 构造函数：

¥You can pass an Easy Connect String, a Net Service Name, or a Connect Descriptor to the Sequelize constructor using dialectOptions.connectString:

const sequelize = new Sequelize({
  dialect: 'oracle',
  username: 'user',
  password: 'password',
  dialectOptions: {
    connectString: 'inst1',
  },
});

请注意，database、host 和 port 将被覆盖，并且 connectString 中的值将用于身份验证。

¥Note that the database, host and port will be overriden and the values in connectString will be used for authentication.

有关连接字符串的更多信息，请参阅 连接字符串。

¥Please refer to Connect String for more about connect strings.

数据类型：TIMESTAMP WITHOUT TIME ZONE - 仅限 PostgreSQL​

¥Data type: TIMESTAMP WITHOUT TIME ZONE - PostgreSQL only

如果你使用的是 PostgreSQL TIMESTAMP WITHOUT TIME ZONE 并且需要将其解析到不同的时区，请使用 pg 库自己的解析器：

¥If you are working with the PostgreSQL TIMESTAMP WITHOUT TIME ZONE and you need to parse it to a different timezone, please use the pg library's own parser:

require('pg').types.setTypeParser(1114, stringValue => {
  return new Date(stringValue + '+0000');
  // e.g., UTC offset. Use any offset that you would like.
});

数据类型：ARRAY(ENUM) - 仅限 PostgreSQL​

¥Data type: ARRAY(ENUM) - PostgreSQL only

Array(Enum)类型需要特殊处理。每当 Sequelize 与数据库对话时，它都必须使用 ENUM 名称对数组值进行类型转换。

¥Array(Enum) type requireS special treatment. Whenever Sequelize will talk to the database, it has to typecast array values with ENUM name.

所以这个枚举名称必须遵循这种模式 enum_<table_name>_<col_name>。如果你使用 sync，则会自动生成正确的名称。

¥So this enum name must follow this pattern enum_<table_name>_<col_name>. If you are using sync then correct name will automatically be generated.

表格提示 - 仅限 MSSQL​

¥Table Hints - MSSQL only

tableHint 选项可用于定义表提示。提示必须是 TableHints 中的值，并且仅应在绝对必要时使用。当前每个查询仅支持单个表提示。

¥The tableHint option can be used to define a table hint. The hint must be a value from TableHints and should only be used when absolutely necessary. Only a single table hint is currently supported per query.

表提示通过指定某些选项来覆盖 MSSQL 查询优化器的默认行为。它们仅影响该子句中引用的表或视图。

¥Table hints override the default behavior of MSSQL query optimizer by specifying certain options. They only affect the table or view referenced in that clause.

const { TableHints } = require('sequelize');
Project.findAll({
  // adding the table hint NOLOCK
  tableHint: TableHints.NOLOCK,
  // this will generate the SQL 'WITH (NOLOCK)'
});

索引提示 - 仅限 MySQL/MariaDB​

¥Index Hints - MySQL/MariaDB only

indexHints 选项可用于定义索引提示。提示类型必须是 IndexHints 中的值，并且该值应引用现有索引。

¥The indexHints option can be used to define index hints. The hint type must be a value from IndexHints and the values should reference existing indexes.

索引提示 覆盖 MySQL 查询优化器的默认行为。

¥Index hints override the default behavior of the MySQL query optimizer.

const { IndexHints } = require('sequelize');
Project.findAll({
  indexHints: [{ type: IndexHints.USE, values: ['index_project_on_name'] }],
  where: {
    id: {
      [Op.gt]: 623,
    },
    name: {
      [Op.like]: 'Foo %',
    },
  },
});

上面将生成一个如下所示的 MySQL 查询：

¥The above will generate a MySQL query that looks like this:

SELECT * FROM Project USE INDEX (index_project_on_name) WHERE name LIKE 'FOO %' AND id > 623;

Sequelize.IndexHints 包括 USE、FORCE 和 IGNORE。

¥Sequelize.IndexHints includes USE, FORCE, and IGNORE.

原始 API 提案请参阅 问题 #9421。

¥See Issue #9421 for the original API proposal.

引擎 - 仅限 MySQL/MariaDB​

¥Engines - MySQL/MariaDB only

模型的默认引擎是 InnoDB。

¥The default engine for a model is InnoDB.

你可以使用 engine 选项更改模型的引擎（例如，更改为 MyISAM）：

¥You can change the engine for a model with the engine option (e.g., to MyISAM):

const Person = sequelize.define(
  'person',
  {
    /* attributes */
  },
  {
    engine: 'MYISAM',
  },
);

与模型定义的每个选项一样，也可以使用 Sequelize 构造函数的 define 选项全局更改此设置：

¥Like every option for the definition of a model, this setting can also be changed globally with the define option of the Sequelize constructor:

const sequelize = new Sequelize(db, user, pw, {
  define: { engine: 'MYISAM' },
});

表格注释 - 仅限 MySQL/MariaDB/PostgreSQL​

¥Table comments - MySQL/MariaDB/PostgreSQL only

你可以在定义模型时为表指定注释：

¥You can specify a comment for a table when defining the model:

class Person extends Model {}
Person.init(
  {
    /* attributes */
  },
  {
    comment: "I'm a table comment!",
    sequelize,
  },
);

该注释将在调用 sync() 时设置。

¥The comment will be set when calling sync().