Skip to main content
Version: v6 - stable

Dialect-Specific Things

Underlying Connector Libraries

MySQL

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

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.

MariaDB

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

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

SQLite

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 [email protected]^4 it is recommended to use the @vscode/sqlite3 fork if updating to [email protected]^5.0.3 is not possible.

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';
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.

PostgreSQL

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.

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

The following fields may be passed to Postgres dialectOptions:

  • application_name: Name of application in pg_stat_activity. See the Postgres docs for details.
  • ssl: SSL options. See the pg docs for details.
  • client_encoding: // Setting 'auto' determines locale based on the client LC_CTYPE environment variable. See the Postgres docs for details.
  • keepAlive: Boolean to enable TCP KeepAlive. See the pg changelog for details.
  • 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: 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.

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

The default client_min_messages config in sequelize is WARNING.

Redshift

Most configuration is same as PostgreSQL above.

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

MSSQL

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

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 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 (Experiment)

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

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 needs Oracle Instant Client to work. You can use the node-oracledb quick start link for installations.

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

The default port number for Oracle database is 1521.

Sequelize also lets you pass credentials in URL format:

const sequelize = new Sequelize('oracle://user:[email protected]:port/servicename');

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

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.

Data type: TIMESTAMP WITHOUT TIME ZONE - PostgreSQL only

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

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.

const { TableHints } = require('sequelize');
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.

const { IndexHints } = require("sequelize");
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;

Sequelize.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().