dialect-specific-things

MySQL

The underlying connector library used by Sequelize for MySQL is the mysql2 package.
See Releases to see which versions of MySQL & mysql2 are supported.

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 are passed directly to the MySQL connection constructor. A full list of options can be found in the MySQL docs.

SQLite

The underlying connector library used by Sequelize for SQLite is the sqlite3 npm package.
See Releases to see which versions of sqlite3 are supported.

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

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

import { Sequelize } from '@sequelize/core';
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,
  },
});

The following fields may be passed to SQLite dialectOptions:

• 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.

Db2

The underlying connector library used by Sequelize for Db2 is the ibm_db npm package.
See Releases to see which versions of DB2 and ibm_db are supported.

Snowflake

caution

While this dialect is included in Sequelize, the implementation is not tested, and is not guaranteed to work.

The underlying connector library used by Sequelize for Snowflake is the snowflake-sdk package.
See Releases to see which versions of Snowflake and snowflake-sdk are supported.

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:

# using npm
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
# using yarn
SEQ_ACCOUNT=myAccount SEQ_USER=myUser SEQ_PW=myPassword SEQ_ROLE=myRole SEQ_DB=myDatabaseName SEQ_SCHEMA=mySchema SEQ_WH=myWareHouse yarn test-integration-snowflake

Db2 for IBM i

caution

While this dialect is included in Sequelize, the implementation is not tested, and is not guaranteed to work.

The underlying connector library used by Sequelize for Db2 for IBM i is the odbc npm package.
See Releases to see which versions of IBMi and odbc are supported.

To learn more about using ODBC with IBM i, consult the IBM i and ODBC documentation.

When passing options to the constructor, the concept of database is mapped to the ODBC DSN. You can provide additional connection string options to Sequelize using the dialectOptions.odbcConnectionString. This connection string is then appended with the values found in the database, username, and password parameters:

const sequelize = new Sequelize('MY_DSN', 'username', 'password', {
  dialect: 'ibmi',
  dialectOptions: {
    odbcConnectionString: 'CMT=1;NAM=0;...'
  },
});

The final connection string generated by the above configuration would look like CMT=1;NAMING=0;...;DSN=MY_DSN;UID=username;PWD=password;. Additionally, the host option will map the the SYSTEM= connection string key.

Data type: ARRAY(ENUM) - PostgreSQL only

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

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.

Table Hints - MSSQL only

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.

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.

import { TableHints } from '@sequelize/core';

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

Index Hints - MySQL/MariaDB only

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.

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

import { IndexHints } from '@sequelize/core';
Project.findAll({
  indexHints: [
    { type: IndexHints.USE, values: ['index_project_on_name'] },
  ],
  where: {
    id: {
      [Op.gt]: 623,
    },
    name: {
      [Op.like]: 'Foo %',
    },
  },
});

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;

IndexHints includes USE, FORCE, and IGNORE.

See Issue #9421 for the original API proposal.

Engines - MySQL/MariaDB only

The default engine for a model is InnoDB.

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

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

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' },
});

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,
});

The comment will be set when calling sync().