Home Reference Source RepositoryJoin us on Slack
public class | source

Sequelize

This is the main class, the entry point to sequelize. To use it, you just need to import sequelize:

const Sequelize = require('sequelize');

In addition to sequelize, the connection library for the dialect you want to use should also be installed in your project. You don't need to import it however, as sequelize will take care of that.

Static Method Summary

Static Public Methods
public static

and(args: String | Object): and

An AND query

 since v2.0.0-dev3
public static

cast(val: any, type: String): cast

Creates an object representing a call to the cast function.

 since v2.0.0-dev3
public static

col(col: String): col

Creates an object which represents a column in the DB, this allows referencing another column in your query.

 since v2.0.0-dev3
public static

fn(fn: String, args: any): fn

Creates an object representing a database function.

 since v2.0.0-dev3
public static

json(conditions: String | Object, value: String | Number | Boolean): json

Creates an object representing nested where conditions for postgres/sqlite/mysql json data-type.
public static

literal(val: any): literal

Creates an object representing a literal, i.e.

 since v2.0.0-dev3
public static

or(args: String | Object): or

An OR query

 since v2.0.0-dev3
public static

useCLS(ns: Object): Object

Use CLS with Sequelize.
public static

where(attr: Object, comparator: String | Op, logic: String | Object): *

A way of specifying attr = condition.

 since v2.0.0-dev3

Constructor Summary

Public Constructor
public

constructor(database: String, username: String, password: String, options: Object)

Instantiate sequelize with name of database, username and password

Member Summary

Public Members
public

models: {}

Models are stored here under the name given to sequelize.define

Method Summary

Public Methods
public

authenticate(options: *): Promise

Test the connection by trying to authenticate
public

close(): Promise

Close all connections used by this sequelize instance, and free all references so the instance can be garbage collected.
public

createSchema(schema: String, options: Object): Promise

Create a new database schema.
public

define(modelName: String, attributes: Object, options: Object): Model

Define a new model, representing a table in the DB.
public

drop(options: object): Promise

Drop all tables defined through this sequelize instance.
public

dropAllSchemas(options: Object): Promise

Drop all schemas
public

dropSchema(schema: String, options: Object): Promise

Drop a single schema
public

escape(value: String): String

Escape value.
public

getDialect(): String

Returns the specified dialect.
public

Returns an instance of QueryInterface.
public

import(path: String): Model

Imports a model defined in another file
public

isDefined(modelName: String): Boolean

Checks whether a model with the given name is defined
public

model(modelName: String): Model

Fetch a Model which is already defined
public

query(sql: String, options: Object): Promise

Execute a query on the DB, with the possibility to bypass all the sequelize goodness.
public

random(): fn

Get the fn for random based on the dialect
public

set(variables: Object, options: Object): Promise

Execute a query which would set an environment or user variable.
public

showAllSchemas(options: Object): Promise

Show all defined schemas
public

sync(options: Object): Promise

Sync all defined models to the DB.
public

transaction(options: Object, autoCallback: Function): Promise

Start a transaction.
public

truncate(options: object): Promise

Truncate all tables defined through the sequelize models.

Static Public Methods

public static and(args: String | Object): and since v2.0.0-dev3 source

An AND query

Params:

NameTypeAttributeDescription
args String | Object

Each argument will be joined by AND

Return:

and

See:

public static cast(val: any, type: String): cast since v2.0.0-dev3 source

Creates an object representing a call to the cast function.

Params:

NameTypeAttributeDescription
val any

The value to cast
type String

The type to cast it to

Return:

cast

public static col(col: String): col since v2.0.0-dev3 source

Creates an object which represents a column in the DB, this allows referencing another column in your query. This is often useful in conjunction with sequelize.fn, since raw string arguments to fn will be escaped.

Params:

NameTypeAttributeDescription
col String

The name of the column

Return:

col

See:

  • Sequelize#fn

public static fn(fn: String, args: any): fn since v2.0.0-dev3 source

Creates an object representing a database function. This can be used in search queries, both in where and order parts, and as default values in column definitions. If you want to refer to columns in your function, you should use sequelize.col, so that the columns are properly interpreted as columns and not a strings.

Convert a user's username to upper case

instance.updateAttributes({
  username: self.sequelize.fn('upper', self.sequelize.col('username'))
})

Params:

NameTypeAttributeDescription
fn String

The function you want to call
args any

All further arguments will be passed as arguments to the function

Return:

fn

Example:

Convert a user's username to upper case
instance.updateAttributes({
  username: self.sequelize.fn('upper', self.sequelize.col('username'))
})

See:

public static json(conditions: String | Object, value: String | Number | Boolean): json source

Creates an object representing nested where conditions for postgres/sqlite/mysql json data-type.

Params:

NameTypeAttributeDescription
conditions String | Object

A hash containing strings/numbers or other nested hash, a string using dot notation or a string using postgres/sqlite/mysql json syntax.
value String | Number | Boolean
  • optional

An optional value to compare against. Produces a string of the form "<json path> = '<value>'".

Return:

json

See:

  • Model#findAll

public static literal(val: any): literal since v2.0.0-dev3 source

Creates an object representing a literal, i.e. something that will not be escaped.

Params:

NameTypeAttributeDescription
val any

Return:

literal

public static or(args: String | Object): or since v2.0.0-dev3 source

An OR query

Params:

NameTypeAttributeDescription
args String | Object

Each argument will be joined by OR

Return:

or

See:

public static useCLS(ns: Object): Object source

Use CLS with Sequelize. CLS namespace provided is stored as Sequelize._cls and bluebird Promise is patched to use the namespace, using cls-bluebird module.

Params:

NameTypeAttributeDescription
ns Object

CLS namespace

Return:

Object

Sequelize constructor

public static where(attr: Object, comparator: String | Op, logic: String | Object): * since v2.0.0-dev3 source

A way of specifying attr = condition.

The attr can either be an object taken from Model.rawAttributes (for example Model.rawAttributes.id or Model.rawAttributes.name). The attribute should be defined in your model definition. The attribute can also be an object from one of the sequelize utility functions (sequelize.fn, sequelize.col etc.)

For string attributes, use the regular { where: { attr: something }} syntax. If you don't want your string to be escaped, use sequelize.literal.

Params:

NameTypeAttributeDescription
attr Object

The attribute, which can be either an attribute object from Model.rawAttributes or a sequelize object, for example an instance of sequelize.fn. For simple string attributes, use the POJO syntax
comparator String | Op
  • optional
  • default: '='
logic String | Object

The condition. Can be both a simply type, or a further condition (or, and, .literal etc.)

Return:

*

See:

Public Constructors

public constructor(database: String, username: String, password: String, options: Object) source

Instantiate sequelize with name of database, username and password

Example usage

// without password and options
const sequelize = new Sequelize('database', 'username')

// without options
const sequelize = new Sequelize('database', 'username', 'password')

// without password / with blank password
const sequelize = new Sequelize('database', 'username', null, {})

// with password and options
const sequelize = new Sequelize('my_database', 'john', 'doe', {})

// with database, username, and password in the options object
const sequelize = new Sequelize({ database, username, password });

// with uri
const sequelize = new Sequelize('mysql://localhost:3306/database', {})

Params:

NameTypeAttributeDescription
database String
  • optional

The name of the database
username String
  • optional
  • default: null

The username which is used to authenticate against the database.
password String
  • optional
  • default: null

The password which is used to authenticate against the database. Supports SQLCipher encryption for SQLite.
options Object
  • optional
  • default: {}

An object with options.
options.host String
  • optional
  • default: 'localhost'

The host of the relational database.
options.port Integer
  • optional
  • default:

The port of the relational database.
options.username String
  • optional
  • default: null

The username which is used to authenticate against the database.
options.password String
  • optional
  • default: null

The password which is used to authenticate against the database.
options.database String
  • optional
  • default: null

The name of the database
options.dialect String
  • optional

The dialect of the database you are connecting to. One of mysql, postgres, sqlite and mssql.
options.dialectModulePath String
  • optional
  • default: null

If specified, load the dialect library from this path. For example, if you want to use pg.js instead of pg when connecting to a pg database, you should specify 'pg.js' here
options.dialectOptions Object
  • optional

An object of additional options, which are passed directly to the connection library
options.storage String
  • optional

Only used by sqlite. Defaults to ':memory:'
options.protocol String
  • optional
  • default: 'tcp'

The protocol of the relational database.
options.define Object
  • optional
  • default: {}

Default options for model definitions. See sequelize.define for options
options.query Object
  • optional
  • default: {}

Default options for sequelize.query
options.set Object
  • optional
  • default: {}

Default options for sequelize.set
options.sync Object
  • optional
  • default: {}

Default options for sequelize.sync
options.timezone string
  • optional
  • default: '+00:00'

The timezone used when converting a date from the database into a JavaScript date. The timezone is also used to SET TIMEZONE when connecting to the server, to ensure that the result of NOW, CURRENT_TIMESTAMP and other time related functions have in the right timezone. For best cross platform performance use the format +/-HH:MM. Will also accept string versions of timezones used by moment.js (e.g. 'America/Los_Angeles'); this is useful to capture daylight savings time changes.
options.standardConformingStrings boolean
  • optional
  • default: true

The PostgreSQL standard_conforming_strings session parameter. Set to false to not set the option. WARNING: Setting this to false may expose vulnerabilities and is not recommended!
options.logging Function
  • optional
  • default: console.log

A function that gets executed every time Sequelize would log something.
options.benchmark Boolean
  • optional
  • default: false

Pass query execution time in milliseconds as second argument to logging function (options.logging).
options.omitNull Boolean
  • optional
  • default: false

A flag that defines if null values should be passed to SQL queries or not.
options.native Boolean
  • optional
  • default: false

A flag that defines if native library shall be used or not. Currently only has an effect for postgres
options.replication Boolean
  • optional
  • default: false

Use read / write replication. To enable replication, pass an object, with two properties, read and write. Write should be an object (a single server for handling writes), and read an array of object (several servers to handle reads). Each read/write server can have the following properties: host, port, username, password, database
options.pool Object
  • optional

sequelize connection pool configuration
options.pool.max Integer
  • optional
  • default: 5

Maximum number of connection in pool
options.pool.min Integer
  • optional
  • default: 0

Minimum number of connection in pool
options.pool.idle Integer
  • optional
  • default: 10000

The maximum time, in milliseconds, that a connection can be idle before being released. Use with combination of evict for proper working, for more details read https://github.com/coopernurse/node-pool/issues/178#issuecomment-327110870
options.pool.acquire Integer
  • optional
  • default: 10000

The maximum time, in milliseconds, that pool will try to get connection before throwing error
options.pool.evict Integer
  • optional
  • default: 10000

The time interval, in milliseconds, for evicting stale connections. Set it to 0 to disable this feature.
options.pool.handleDisconnects Boolean
  • optional
  • default: true

Controls if pool should handle connection disconnect automatically without throwing errors
options.pool.validate Function
  • optional

A function that validates a connection. Called with client. The default function checks that client is an object, and that its state is not disconnected
options.quoteIdentifiers Boolean
  • optional
  • default: true

Set to false to make table names and attributes case-insensitive on Postgres and skip double quoting of them. WARNING: Setting this to false may expose vulnerabilities and is not recommended!
options.transactionType String
  • optional
  • default: 'DEFERRED'

Set the default transaction type. See Sequelize.Transaction.TYPES for possible options. Sqlite only.
options.isolationLevel String
  • optional

Set the default transaction isolation level. See Sequelize.Transaction.ISOLATION_LEVELS for possible options.
options.retry Object
  • optional

Set of flags that control when a query is automatically retried.
options.retry.match Array
  • optional

Only retry a query if the error matches one of these strings.
options.retry.max Integer
  • optional

How many times a failing query is automatically retried. Set to 0 to disable retrying on SQL_BUSY error.
options.typeValidation Boolean
  • optional
  • default: false

Run built in type validators on insert and update, e.g. validate that arguments passed to integer fields are integer-like.
options.operatorsAliases Object | Boolean
  • optional
  • default: true

String based operator alias, default value is true which will enable all operators alias. Pass object to limit set of aliased operators or false to disable completely.

Public Members

public models: {} source

Models are stored here under the name given to sequelize.define

Public Methods

public authenticate(options: *): Promise source

Test the connection by trying to authenticate

Params:

NameTypeAttributeDescription
options *

Return:

Promise

public close(): Promise source

Close all connections used by this sequelize instance, and free all references so the instance can be garbage collected.

Normally this is done on process exit, so you only need to call this method if you are creating multiple instances, and want to garbage collect some of them.

Return:

Promise

public createSchema(schema: String, options: Object): Promise source

Create a new database schema.

Note, that this is a schema in the postgres sense of the word, not a database table. In mysql and sqlite, this command will do nothing.

Params:

NameTypeAttributeDescription
schema String

Name of the schema
options Object
  • default: {}
options.logging Boolean | function

A function that logs sql queries, or false for no logging

Return:

Promise

See:

public define(modelName: String, attributes: Object, options: Object): Model source

Define a new model, representing a table in the DB.

The table columns are defined by the object that is given as the second argument. Each key of the object represents a column

Params:

NameTypeAttributeDescription
modelName String

The name of the model. The model will be stored in sequelize.models under this name
attributes Object

An object, where each attribute is a column of the table. See Model.init
options Object
  • optional

These options are merged with the default define options provided to the Sequelize constructor and passed to Model.init()

Return:

Model

Example:

sequelize.define('modelName', {
    columnA: {
        type: Sequelize.BOOLEAN,
        validate: {
          is: ["[a-z]",'i'],        // will only allow letters
          max: 23,                  // only allow values <= 23
          isIn: {
            args: [['en', 'zh']],
            msg: "Must be English or Chinese"
          }
        },
        field: 'column_a'
        // Other attributes here
    },
    columnB: Sequelize.STRING,
    columnC: 'MY VERY OWN COLUMN TYPE'
})

sequelize.models.modelName // The model will now be available in models under the name given to define

See:

public drop(options: object): Promise source

Drop all tables defined through this sequelize instance. This is done by calling Model.drop on each model

Params:

NameTypeAttributeDescription
options object

The options passed to each call to Model.drop
options.logging Boolean | function

A function that logs sql queries, or false for no logging

Return:

Promise

See:

public dropAllSchemas(options: Object): Promise source

Drop all schemas

Note,that this is a schema in the postgres sense of the word, not a database table. In mysql and sqlite, this is the equivalent of drop all tables.

Params:

NameTypeAttributeDescription
options Object
  • default: {}
options.logging Boolean | function

A function that logs sql queries, or false for no logging

Return:

Promise

public dropSchema(schema: String, options: Object): Promise source

Drop a single schema

Note, that this is a schema in the postgres sense of the word, not a database table. In mysql and sqlite, this drop a table matching the schema name

Params:

NameTypeAttributeDescription
schema String

Name of the schema
options Object
  • default: {}
options.logging Boolean | function

A function that logs sql queries, or false for no logging

Return:

Promise

public escape(value: String): String source

Escape value.

Params:

NameTypeAttributeDescription
value String

Return:

String

public getDialect(): String source

Returns the specified dialect.

Return:

String

The specified dialect.

public getQueryInterface(): QueryInterface source

Returns an instance of QueryInterface.

Return:

QueryInterface

An instance (singleton) of QueryInterface.

public import(path: String): Model source

Imports a model defined in another file

Imported models are cached, so multiple calls to import with the same path will not load the file multiple times

See https://github.com/sequelize/express-example for a short example of how to define your models in separate files so that they can be imported by sequelize.import

Params:

NameTypeAttributeDescription
path String

The path to the file that holds the model you want to import. If the part is relative, it will be resolved relatively to the calling file

Return:

Model

public isDefined(modelName: String): Boolean source

Checks whether a model with the given name is defined

Params:

NameTypeAttributeDescription
modelName String

The name of a model defined with Sequelize.define

Return:

Boolean

public model(modelName: String): Model source

Fetch a Model which is already defined

Params:

NameTypeAttributeDescription
modelName String

The name of a model defined with Sequelize.define

Return:

Model

Throw:

*

Will throw an error if the model is not defined (that is, if sequelize#isDefined returns false)

public query(sql: String, options: Object): Promise source

Execute a query on the DB, with the possibility to bypass all the sequelize goodness.

By default, the function will return two arguments: an array of results, and a metadata object, containing number of affected rows etc. Use .spread to access the results.

If you are running a type of query where you don't need the metadata, for example a SELECT query, you can pass in a query type to make sequelize format the results:

sequelize.query('SELECT...').spread((results, metadata) => {
  // Raw query - use spread
});

sequelize.query('SELECT...', { type: sequelize.QueryTypes.SELECT }).then(results => {
  // SELECT query - use then
})

Params:

NameTypeAttributeDescription
sql String
options Object
  • optional
  • default: {}

Query options.
options.raw Boolean
  • optional

If true, sequelize will not try to format the results of the query, or build an instance of a model from the result
options.transaction Transaction
  • optional
  • default: null

The transaction that the query should be executed under
options.type QueryTypes
  • optional
  • default: 'RAW'

The type of query you are executing. The query type affects how results are formatted before they are passed back. The type is a string, but Sequelize.QueryTypes is provided as convenience shortcuts.
options.nest Boolean
  • optional
  • default: false

If true, transforms objects with . separated property names into nested objects using dottie.js. For example { 'user.username': 'john' } becomes { user: { username: 'john' }}. When nest is true, the query type is assumed to be 'SELECT', unless otherwise specified
options.plain Boolean
  • optional
  • default: false

Sets the query type to SELECT and return a single row
options.replacements Object | Array
  • optional

Either an object of named parameter replacements in the format :param or an array of unnamed replacements to replace ? in your SQL.
options.bind Object | Array
  • optional

Either an object of named bind parameter in the format _param or an array of unnamed bind parameter to replace $1, $2, ... in your SQL.
options.useMaster Boolean
  • optional
  • default: false

Force the query to use the write pool, regardless of the query type.
options.logging Function
  • optional
  • default: false

A function that gets executed while running the query to log the sql.
options.instance new Model()
  • optional

A sequelize instance used to build the return instance
options.model Model
  • optional

A sequelize model used to build the returned model instances (used to be called callee)
options.retry Object
  • optional

Set of flags that control when a query is automatically retried.
options.retry.match Array
  • optional

Only retry a query if the error matches one of these strings.
options.retry.max Integer
  • optional

How many times a failing query is automatically retried.
options.searchPath String
  • optional
  • default: DEFAULT

An optional parameter to specify the schema search_path (Postgres only)
options.supportsSearchPath Boolean
  • optional

If false do not prepend the query with the search_path (Postgres only)
options.mapToModel Boolean
  • optional
  • default: false

Map returned fields to model's fields if options.model or options.instance is present. Mapping will occur before building the model instance.
options.fieldMap Object
  • optional

Map returned fields to arbitrary names for SELECT query type.

Return:

Promise

See:

  • Model.build for more information about instance option.

public random(): fn source

Get the fn for random based on the dialect

Return:

fn

public set(variables: Object, options: Object): Promise source

Execute a query which would set an environment or user variable. The variables are set per connection, so this function needs a transaction. Only works for MySQL.

Params:

NameTypeAttributeDescription
variables Object

Object with multiple variables.
options Object

Query options.
options.transaction Transaction

The transaction that the query should be executed under

Return:

Promise

public showAllSchemas(options: Object): Promise source

Show all defined schemas

Note, that this is a schema in the postgres sense of the word, not a database table. In mysql and sqlite, this will show all tables.

Params:

NameTypeAttributeDescription
options Object
  • default: {}
options.logging Boolean | function

A function that logs sql queries, or false for no logging

Return:

Promise

public sync(options: Object): Promise source

Sync all defined models to the DB.

Params:

NameTypeAttributeDescription
options Object
  • optional
  • default: {}
options.force Boolean
  • optional
  • default: false

If force is true, each Model will run DROP TABLE IF EXISTS, before it tries to create its own table
options.match RegExp
  • optional

Match a regex against the database name before syncing, a safety check for cases where force: true is used in tests but not live code
options.logging Boolean | function
  • optional
  • default: console.log

A function that logs sql queries, or false for no logging
options.schema String
  • optional
  • default: 'public'

The schema that the tables should be created in. This can be overriden for each table in sequelize.define
options.searchPath String
  • optional
  • default: DEFAULT

An optional parameter to specify the schema search_path (Postgres only)
options.hooks Boolean
  • optional
  • default: true

If hooks is true then beforeSync, afterSync, beforeBulkSync, afterBulkSync hooks will be called
options.alter Boolean
  • optional
  • default: false

Alters tables to fit models. Not recommended for production use. Deletes data in columns that were removed or had their type changed in the model.

Return:

Promise

public transaction(options: Object, autoCallback: Function): Promise source

Start a transaction. When using transactions, you should pass the transaction in the options argument in order for the query to happen under that transaction

sequelize.transaction().then(transaction => {
  return User.findOne(..., {transaction})
    .then(user => user.updateAttributes(..., {transaction}))
    .then(() => transaction.commit())
    .catch(() => transaction.rollback());
})

A syntax for automatically committing or rolling back based on the promise chain resolution is also supported:

sequelize.transaction(transaction => { // Note that we use a callback rather than a promise.then()
  return User.findOne(..., {transaction})
    .then(user => user.updateAttributes(..., {transaction}))
}).then(() => {
  // Committed
}).catch(err => {
  // Rolled back
  console.error(err);
});

If you have CLS enabled, the transaction will automatically be passed to any query that runs within the callback. To enable CLS, add it do your project, create a namespace and set it on the sequelize constructor:

const cls = require('continuation-local-storage');
const ns = cls.createNamespace('....');
const Sequelize = require('sequelize');
Sequelize.useCLS(ns);

Note, that CLS is enabled for all sequelize instances, and all instances will share the same namespace

Params:

NameTypeAttributeDescription
options Object
  • optional
  • default: {}
options.autocommit Boolean
  • optional
options.type String
  • optional
  • default: 'DEFERRED'

See Sequelize.Transaction.TYPES for possible options. Sqlite only.
options.isolationLevel String
  • optional

See Sequelize.Transaction.ISOLATION_LEVELS for possible options
options.logging Function
  • optional
  • default: false

A function that gets executed while running the query to log the sql.
autoCallback Function
  • optional

The callback is called with the transaction object, and should return a promise. If the promise is resolved, the transaction commits; if the promise rejects, the transaction rolls back

Return:

Promise

See:

public truncate(options: object): Promise source

Truncate all tables defined through the sequelize models. This is done by calling Model.truncate() on each model.

Params:

NameTypeAttributeDescription
options object
  • optional

The options passed to Model.destroy in addition to truncate
options.transaction Boolean | function
  • optional
options.logging Boolean | function
  • optional

A function that logs sql queries, or false for no logging

Return:

Promise

See: