publicclass| source

Sequelize

This is the main class, the entry point to sequelize.

Static Method Summary

| Static Public Methods | | publicstatic |

and(args: ...string | Object): and

An AND query

| since v2.0.0-dev3 | | publicstatic |

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

Creates an object representing a call to the cast function.

| since v2.0.0-dev3 | | publicstatic |

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 | | publicstatic |

fn(fn: string, args: any): fn

Creates an object representing a database function.

| since v2.0.0-dev3 | | publicstatic |

json(conditionsOrPath: string | Object, value: string | number | boolean): json

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

| | | publicstatic |

literal(val: any): literal

Creates an object representing a literal, i.e.

| since v2.0.0-dev3 | | publicstatic |

or(args: ...string | Object): or

An OR query

| since v2.0.0-dev3 | | publicstatic |

useCLS(ns: Object): Object

Use CLS with Sequelize.

| | | publicstatic |

where(attr: Object, comparator: Symbol, 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: Object): 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 database.

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

getDatabaseName(): string

Returns the database name.

| | | public |

getDialect(): string

Returns the specified dialect.

| | | public |

getQueryInterface(): QueryInterface

Returns an instance of QueryInterface.

| | | public |

import(importPath: 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, optionally bypassing 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

publicstaticand(args: ...string | Object): and since v2.0.0-dev3 source

An AND query

Params:

Each argument will be joined by AND

Return:

| and |

See:

Model.findAll

publicstaticcast(val: any, type: string): cast since v2.0.0-dev3 source

Creates an object representing a call to the cast function.

Params:

The value to cast

The type to cast it to

Return:

| cast |

publicstaticcol(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:

The name of the column

Return:

| col |

See:

Sequelize#fn

publicstaticfn(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.

Params:

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.update({
  username: sequelize.fn('upper', sequelize.col('username'))
});

See:

publicstaticjson(conditionsOrPath: string | Object, value: string | number | boolean): json source

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

Params:

A hash containing strings/numbers or other nested hash, a string using dot notation or a string using postgres/sqlite/mysql json syntax.

optional

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

Return:

| json |

See:

Model.findAll

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

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

Params:

literal value

Return:

| literal |

publicstaticor(args: ...string | Object): or since v2.0.0-dev3 source

An OR query

Params:

Each argument will be joined by OR

Return:

| or |

See:

Model.findAll

publicstaticuseCLS(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:

CLS namespace

Return:

| Object |

Sequelize constructor

publicstaticwhere(attr: Object, comparator: Symbol, 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:

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 | Symbol |

optional
default: 'Op.eq'

operator

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

Return:

| * |

See:

Model.findAll

Public Constructors

publicconstructor(database: string, username: string, password: string, options: Object) source

Instantiate sequelize with name of database, username and password.

Params:

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 | number |

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.dialectModule | string |

optional
default: null

If specified, use this dialect library. For example, if you want to use pg.js instead of pg when connecting to a pg database, you should specify 'require("pg.js")' here

| | 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 '/path/to/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 Model.init.

| | options.query | Object |

optional
default: {}

Default options for sequelize.query

| | options.schema | string |

optional
default: null

A schema to use

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

optional
default: 'warning'

The PostgreSQL client_min_messages session parameter. Set to false to not override the database's default.

| | 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 | number |

optional
default: 5

Maximum number of connection in pool

| | options.pool.min | number |

optional
default: 0

Minimum number of connection in pool

| | options.pool.idle | number |

optional
default: 10000

The maximum time, in milliseconds, that a connection can be idle before being released.

| | options.pool.acquire | number |

optional
default: 60000

The maximum time, in milliseconds, that pool will try to get connection before throwing error

| | options.pool.evict | number |

optional
default: 1000

The time interval, in milliseconds, after which sequelize-pool will remove idle connections.

| | 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 | number |

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, and select with where clause, e.g. validate that arguments passed to integer fields are integer-like.

| | options.operatorsAliases | Object |

optional

String based operator alias. Pass object to limit set of aliased operators.

| | options.hooks | Object |

optional

An object of global hook functions that are called before and after certain lifecycle events. Global hooks will run after any model-specific hooks defined for the same event (See Sequelize.Model.init() for a list). Additionally, beforeConnect(), afterConnect(), beforeDisconnect(), and afterDisconnect() hooks may be defined here.

| | options.minifyAliases | boolean |

optional
default: false

A flag that defines if aliases should be minified (mostly useful to avoid Postgres alias character limit of 64)

| | options.logQueryParameters | boolean |

optional
default: false

A flag that defines if show bind patameters in log.

Example:

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

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

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

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

// option examples
const sequelize = new Sequelize('database', 'username', 'password', {
  // the sql dialect of the database
  // currently supported: 'mysql', 'sqlite', 'postgres', 'mssql'
  dialect: 'mysql',

  // custom host; default: localhost
  host: 'my.server.tld',
  // for postgres, you can also specify an absolute path to a directory
  // containing a UNIX socket to connect over
  // host: '/sockets/psql_sockets'.

  // custom port; default: dialect default
  port: 12345,

  // custom protocol; default: 'tcp'
  // postgres only, useful for Heroku
  protocol: null,

  // disable logging or provide a custom logging function; default: console.log
  logging: false,

  // you can also pass any dialect options to the underlying dialect library
  // - default is empty
  // - currently supported: 'mysql', 'postgres', 'mssql'
  dialectOptions: {
    socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock',
    supportBigNumbers: true,
    bigNumberStrings: true
  },

  // the storage engine for sqlite
  // - default ':memory:'
  storage: 'path/to/database.sqlite',

  // disable inserting undefined values as NULL
  // - default: false
  omitNull: true,

  // a flag for using a native library or not.
  // in the case of 'pg' -- set this to true will allow SSL support
  // - default: false
  native: true,

  // Specify options, which are used when sequelize.define is called.
  // The following example:
  // define: { timestamps: false }
  // is basically the same as:
  // Model.init(attributes, { timestamps: false });
  // sequelize.define(name, attributes, { timestamps: false });
  // so defining the timestamps for each model will be not necessary
  define: {
    underscored: false,
    freezeTableName: false,
    charset: 'utf8',
    dialectOptions: {
      collate: 'utf8_general_ci'
    },
    timestamps: true
  },

  // similar for sync: you can define this to always force sync for models
  sync: { force: true },

  // pool configuration used to pool database connections
  pool: {
    max: 5,
    idle: 30000,
    acquire: 60000,
  },

  // isolation level of each transaction
  // defaults to dialect default
  isolationLevel: Transaction.ISOLATION_LEVELS.REPEATABLE_READ
})

Public Members

publicmodels: {} source

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

Public Methods

publicauthenticate(options: Object): Promise source

Test the connection by trying to authenticate. It runs SELECT 1+1 AS result query.

Params:

optional
default: {}

query options

Return:

| Promise |

publicclose(): 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 |

publiccreateSchema(schema: string, options: Object): Promise source

Create a new database schema.

Note: 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:

Name of the schema

| | options | Object |

optional
default: {}

query options

optional

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

Return:

| Promise |

See:

Model.schema

publicdefine(modelName: string, attributes: Object, options: Object): Model source

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

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

Params:

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

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 |

Newly defined 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'
  },
  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:

Model.init for a more comprehensive specification of the options and attributes objects.
Model definition Manual related to model definition
DataTypes For a list of possible data types

publicdrop(options: Object): Promise source

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

Params:

optional

The options passed to each call to Model.drop

optional

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

Return:

| Promise |

See:

Model.drop for options

publicdropAllSchemas(options: Object): Promise source

Drop all schemas.

Note: 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:

optional
default: {}

query options

optional

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

Return:

| Promise |

publicdropSchema(schema: string, options: Object): Promise source

Drop a single schema

Note: 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:

Name of the schema

| | options | Object |

optional
default: {}

query options

optional

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

Return:

| Promise |

publicescape(value: string): string source

Escape value.

Params:

string value to escape

Return:

| string |

publicgetDatabaseName(): string source

Returns the database name.

Return:

| string |

The database name.

publicgetDialect(): string source

Returns the specified dialect.

Return:

| string |

The specified dialect.

publicgetQueryInterface(): QueryInterface source

Returns an instance of QueryInterface.

Return:

| QueryInterface |

An instance (singleton) of QueryInterface.

publicimport(importPath: 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.

Params:

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 |

Imported model, returned from cache if was already imported

publicisDefined(modelName: string): boolean source

Checks whether a model with the given name is defined

Params:

The name of a model defined with Sequelize.define

Return:

| boolean |

Returns true if model is already defined, otherwise false

publicmodel(modelName: string): Model source

Fetch a Model which is already defined

Params:

The name of a model defined with Sequelize.define

Return:

| Model |

Specified model

Throw:

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

publicquery(sql: string, options: Object): Promise source

Execute a query on the DB, optionally bypassing 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.

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...').then(([results, metadata]) => {
  // Raw query - use then plus array spread
});

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

Params:

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

optional

Either an object of named parameter replacements in the format :param or an array of unnamed replacements to replace ? in your SQL.

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.

publicrandom(): fn source

Get the fn for random based on the dialect

Return:

| fn |

publicset(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:

Object with multiple variables.

| | options | Object |

optional

query options.

| | options.transaction | Transaction |

optional

The transaction that the query should be executed under

Return:

| Promise |

publicshowAllSchemas(options: Object): Promise source

Show all defined schemas

Note: 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:

optional
default: {}

query options

optional

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

Return:

| Promise |

publicsync(options: Object): Promise source

Sync all defined models to the DB.

Params:

optional
default: {}

sync options

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

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 overridden 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 |

publictransaction(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 @see Transaction

If you have CLS enabled, the transaction will automatically be passed to any query that runs within the callback

Params:

optional

Transaction options

| | 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.deferrable | string |

optional

Sets the constraints to be deferred or immediately checked. See Sequelize.Deferrable. PostgreSQL Only

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

Example:

sequelize.transaction().then(transaction => {
  return User.findOne(..., {transaction})
    .then(user => user.update(..., {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.update(..., {transaction}))
}).then(() => {
  // Committed
}).catch(err => {
  // Rolled back
  console.error(err);
});

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

publictruncate(options: Object): Promise source

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

Params:

optional

The options passed to Model.destroy in addition to truncate

optional

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

Return:

| Promise |

See:

Model.truncate for more information