Getting started

Installation

Sequelize is available via NPM and Yarn.

// Using NPM
$ npm install --save sequelize

# And one of the following:
$ npm install --save pg pg-hstore
$ npm install --save mysql2
$ npm install --save sqlite3
$ npm install --save tedious // MSSQL

// Using Yarn
$ yarn add sequelize

# And one of the following:
$ yarn add pg pg-hstore
$ yarn add mysql2
$ yarn add sqlite3
$ yarn add tedious // MSSQL

Setting up a connection

Sequelize will setup a connection pool on initialization so you should ideally only ever create one instance per database if you're connecting to the DB from a single process. If you're connecting to the DB from multiple processes, you'll have to create one instance per process, but each instance should have a maximum connection pool size of "max connection pool size divided by number of instances". So, if you wanted a max connection pool size of 90 and you had 3 worker processes, each process's instance should have a max connection pool size of 30.

const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql'|'sqlite'|'postgres'|'mssql',
  operatorsAliases: false,

  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  },

  // SQLite only
  storage: 'path/to/database.sqlite'
});

// Or you can simply use a connection uri
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');

The Sequelize constructor takes a whole slew of options that are available via the API reference.

Test the connection

You can use the .authenticate() function like this to test the connection.

sequelize
  .authenticate()
  .then(() => {
    console.log('Connection has been established successfully.');
  })
  .catch(err => {
    console.error('Unable to connect to the database:', err);
  });

Your first model

Models are defined with sequelize.define('name', {attributes}, {options}).

const User = sequelize.define('user', {
  firstName: {
    type: Sequelize.STRING
  },
  lastName: {
    type: Sequelize.STRING
  }
});

// force: true will drop the table if it already exists
User.sync({force: true}).then(() => {
  // Table created
  return User.create({
    firstName: 'John',
    lastName: 'Hancock'
  });
});

You can read more about creating models at Model API reference

Your first query

User.findAll().then(users => {
  console.log(users)
})

You can read more about finder functions on models like .findAll() at Data retrieval or how to do specific queries like WHERE and JSONB at Querying.

Application wide model options

The Sequelize constructor takes a define option which will be used as the default options for all defined models.

const sequelize = new Sequelize('connectionUri', {
  define: {
    timestamps: false // true by default
  }
});

const User = sequelize.define('user', {}); // timestamps is false by default
const Post = sequelize.define('post', {}, {
  timestamps: true // timestamps will now be true
});

Promises

Sequelize uses Bluebird promises to control async control-flow.

Note: Sequelize use independent copy of Bluebird instance. You can access it using Sequelize.Promise if you want to set any Bluebird specific options

If you are unfamiliar with how promises work, don't worry, you can read up on them here.

Basically, a promise represents a value which will be present at some point - "I promise you I will give you a result or an error at some point". This means that

// DON'T DO THIS
user = User.findOne()

console.log(user.get('firstName'));

will never work! This is because user is a promise object, not a data row from the DB. The right way to do it is:

User.findOne().then(user => {
  console.log(user.get('firstName'));
});

When your environment or transpiler supports async/await this will work but only in the body of an async function:

user = await User.findOne()

console.log(user.get('firstName'));

Once you've got the hang of what promises are and how they work, use the bluebird API reference as your go-to tool. In particular, you'll probably be using .all a lot.

Basic usage

To get the ball rollin' you first have to create an instance of Sequelize. Use it the following way:

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mysql'
});

This will save the passed database credentials and provide all further methods.

Furthermore you can specify a non-default host/port:

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mysql',
  host: "my.server.tld",
  port: 9821,
})

If you just don't have a password:

const sequelize = new Sequelize({
  database: 'db_name',
  username: 'username',
  password: null,
  dialect: 'mysql'
});

You can also use a connection string:

const sequelize = new Sequelize('mysql://user:pass@example.com:9821/db_name', {
  // Look to the next section for possible options
})

Options

Besides the host and the port, Sequelize comes with a whole bunch of options. Here they are:

• See Sequelize API
• See Model Definition
• See Transactions

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

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

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

  // disable logging; 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:
  //   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
})

Hint: You can also define a custom function for the logging part. Just pass a function. The first parameter will be the string that is logged.

Read replication

Sequelize supports read replication, i.e. having multiple servers that you can connect to when you want to do a SELECT query. When you do read replication, you specify one or more servers to act as read replicas, and one server to act as the write master, which handles all writes and updates and propagates them to the replicas (note that the actual replication process is not handled by Sequelize, but should be set up by database backend).

const sequelize = new Sequelize('database', null, null, {
  dialect: 'mysql',
  port: 3306
  replication: {
    read: [
      { host: '8.8.8.8', username: 'read-username', password: 'some-password' },
      { host: '9.9.9.9', username: 'another-username', password: null }
    ],
    write: { host: '1.1.1.1', username: 'write-username', password: 'any-password' }
  },
  pool: { // If you want to override the options used for the read/write pool you can do so here
    max: 20,
    idle: 30000
  },
})

If you have any general settings that apply to all replicas you do not need to provide them for each instance. In the code above, database name and port is propagated to all replicas. The same will happen for user and password, if you leave them out for any of the replicas. Each replica has the following options:host,port,username,password,database.

Sequelize uses a pool to manage connections to your replicas. Internally Sequelize will maintain two pools created using pool configuration.

If you want to modify these, you can pass pool as an options when instantiating Sequelize, as shown above.

Each write or useMaster: true query will use write pool. For SELECT read pool will be used. Read replica are switched using a basic round robin scheduling.

Dialects

With the release of Sequelize 1.6.0, the library got independent from specific dialects. This means, that you'll have to add the respective connector library to your project yourself.

MySQL

In order to get Sequelize working nicely together with MySQL, you'll need to installmysql2@^1.0.0-rc.10or higher. Once that's done you can use it like this:

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mysql'
})

Note: You can pass options directly to dialect library by setting the dialectOptions parameter. See Options for examples (currently only mysql is supported).

SQLite

For SQLite compatibility you'll needsqlite3@~3.0.0. Configure Sequelize like this:

const sequelize = new Sequelize('database', 'username', 'password', {
  // sqlite! now!
  dialect: 'sqlite',

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

Or you can use a connection string as well with a path:

const sequelize = new Sequelize('sqlite:/home/abs/path/dbname.db')
const sequelize = new Sequelize('sqlite:relativePath/dbname.db')

PostgreSQL

The library for PostgreSQL ispg@^5.0.0 || ^6.0.0 You'll just need to define the dialect:

const sequelize = new Sequelize('database', 'username', 'password', {
  // gimme postgres, please!
  dialect: 'postgres'
})

MSSQL

The library for MSSQL istedious@^1.7.0 You'll just need to define the dialect:

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mssql'
})

Executing raw SQL queries

As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can utilize the function sequelize.query.

• See Sequelize.query API
• See Query Types

Here is how it works:

// Arguments for raw queries
sequelize.query('your query', [, options])

// Quick example
sequelize.query("SELECT * FROM myTable").then(myTableRows => {
  console.log(myTableRows)
})

// If you want to return sequelize instances use the model options.
// This allows you to easily map a query to a predefined model for sequelize e.g:
sequelize
  .query('SELECT * FROM projects', { model: Projects })
  .then(projects => {
    // Each record will now be mapped to the project's model.
    console.log(projects)
  })


// Options is an object with the following keys:
sequelize
  .query('SELECT 1', {
    // A function (or false) for logging your queries
    // Will get called for every SQL query that gets send
    // to the server.
    logging: console.log,

    // If plain is true, then sequelize will only return the first
    // record of the result set. In case of false it will all records.
    plain: false,

    // Set this to true if you don't have a model definition for your query.
    raw: false,

    // The type of query you are executing. The query type affects how results are formatted before they are passed back.
    type: Sequelize.QueryTypes.SELECT
  })

// Note the second argument being null!
// Even if we declared a callee here, the raw: true would
// supersede and return a raw object.
sequelize
  .query('SELECT * FROM projects', { raw: true })
  .then(projects => {
    console.log(projects)
  })

Replacements in a query can be done in two different ways, either using named parameters (starting with :), or unnamed, represented by a ?

The syntax used depends on the replacements option passed to the function:

• If an array is passed, ? will be replaced in the order that they appear in the array
• If an object is passed, :key will be replaced with the keys from that object. If the object contains keys not found in the query or vice versa, an exception will be thrown.

sequelize
  .query(
    'SELECT * FROM projects WHERE status = ?',
    { raw: true, replacements: ['active']
  )
  .then(projects => {
    console.log(projects)
  })

sequelize
  .query(
    'SELECT * FROM projects WHERE status = :status ',
    { raw: true, replacements: { status: 'active' } }
  )
  .then(projects => {
    console.log(projects)
  })

One note: If the attribute names of the table contain dots, the resulting objects will be nested:

sequelize.query('select 1 as `foo.bar.baz`').then(rows => {
  console.log(JSON.stringify(rows))

  /*
    [{
      "foo": {
        "bar": {
          "baz": 1
        }
      }
    }]
  */
})

Model definition

To define mappings between a model and a table, use the define method.

const Project = sequelize.define('project', {
  title: Sequelize.STRING,
  description: Sequelize.TEXT
})

const Task = sequelize.define('task', {
  title: Sequelize.STRING,
  description: Sequelize.TEXT,
  deadline: Sequelize.DATE
})

You can also set some options on each column:

const Foo = sequelize.define('foo', {
 // instantiating will automatically set the flag to true if not set
 flag: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: true },

 // default values for dates => current time
 myDate: { type: Sequelize.DATE, defaultValue: Sequelize.NOW },

 // setting allowNull to false will add NOT NULL to the column, which means an error will be
 // thrown from the DB when the query is executed if the column is null. If you want to check that a value
 // is not null before querying the DB, look at the validations section below.
 title: { type: Sequelize.STRING, allowNull: false },

 // Creating two objects with the same value will throw an error. The unique property can be either a
 // boolean, or a string. If you provide the same string for multiple columns, they will form a
 // composite unique key.
 uniqueOne: { type: Sequelize.STRING,  unique: 'compositeIndex' },
 uniqueTwo: { type: Sequelize.INTEGER, unique: 'compositeIndex' },

 // The unique property is simply a shorthand to create a unique constraint.
 someUnique: { type: Sequelize.STRING, unique: true },

 // It's exactly the same as creating the index in the model's options.
 { someUnique: { type: Sequelize.STRING } },
 { indexes: [ { unique: true, fields: [ 'someUnique' ] } ] },

 // Go on reading for further information about primary keys
 identifier: { type: Sequelize.STRING, primaryKey: true },

 // autoIncrement can be used to create auto_incrementing integer columns
 incrementMe: { type: Sequelize.INTEGER, autoIncrement: true },

 // You can specify a custom field name via the 'field' attribute:
 fieldWithUnderscores: { type: Sequelize.STRING, field: 'field_with_underscores' },

 // It is possible to create foreign keys:
 bar_id: {
   type: Sequelize.INTEGER,

   references: {
     // This is a reference to another model
     model: Bar,

     // This is the column name of the referenced model
     key: 'id',

     // This declares when to check the foreign key constraint. PostgreSQL only.
     deferrable: Sequelize.Deferrable.INITIALLY_IMMEDIATE
   }
 }
})

The comment option can also be used on a table, see model configuration

Timestamps

By default, Sequelize will add the attributes createdAt and updatedAt to your model so you will be able to know when the database entry went into the db and when it was updated last.

Note that if you are using Sequelize migrations you will need to add the createdAt and updatedAt fields to your migration definition:

module.exports = {
  up(queryInterface, Sequelize) {
    return queryInterface.createTable('my-table', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },

      // Timestamps
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE,
    })
  },
  down(queryInterface, Sequelize) {
    return queryInterface.dropTable('my-table');
  },
}

If you do not want timestamps on your models, only want some timestamps, or you are working with an existing database where the columns are named something else, jump straight on to configuration to see how to do that.

Data types

Below are some of the datatypes supported by sequelize. For a full and updated list, see DataTypes.

Sequelize.STRING                      // VARCHAR(255)
Sequelize.STRING(1234)                // VARCHAR(1234)
Sequelize.STRING.BINARY               // VARCHAR BINARY
Sequelize.TEXT                        // TEXT
Sequelize.TEXT('tiny')                // TINYTEXT

Sequelize.INTEGER                     // INTEGER
Sequelize.BIGINT                      // BIGINT
Sequelize.BIGINT(11)                  // BIGINT(11)

Sequelize.FLOAT                       // FLOAT
Sequelize.FLOAT(11)                   // FLOAT(11)
Sequelize.FLOAT(11, 12)               // FLOAT(11,12)

Sequelize.REAL                        // REAL        PostgreSQL only.
Sequelize.REAL(11)                    // REAL(11)    PostgreSQL only.
Sequelize.REAL(11, 12)                // REAL(11,12) PostgreSQL only.

Sequelize.DOUBLE                      // DOUBLE
Sequelize.DOUBLE(11)                  // DOUBLE(11)
Sequelize.DOUBLE(11, 12)              // DOUBLE(11,12)

Sequelize.DECIMAL                     // DECIMAL
Sequelize.DECIMAL(10, 2)              // DECIMAL(10,2)

Sequelize.DATE                        // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres
Sequelize.DATE(6)                     // DATETIME(6) for mysql 5.6.4+. Fractional seconds support with up to 6 digits of precision
Sequelize.DATEONLY                    // DATE without time.
Sequelize.BOOLEAN                     // TINYINT(1)

Sequelize.ENUM('value 1', 'value 2')  // An ENUM with allowed values 'value 1' and 'value 2'
Sequelize.ARRAY(Sequelize.TEXT)       // Defines an array. PostgreSQL only.
Sequelize.ARRAY(Sequelize.ENUM)       // Defines an array of ENUM. PostgreSQL only.

Sequelize.JSON                        // JSON column. PostgreSQL, SQLite and MySQL only.
Sequelize.JSONB                       // JSONB column. PostgreSQL only.

Sequelize.BLOB                        // BLOB (bytea for PostgreSQL)
Sequelize.BLOB('tiny')                // TINYBLOB (bytea for PostgreSQL. Other options are medium and long)

Sequelize.UUID                        // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically)

Sequelize.CIDR                        // CIDR datatype for PostgreSQL
Sequelize.INET                        // INET datatype for PostgreSQL
Sequelize.MACADDR                     // MACADDR datatype for PostgreSQL

Sequelize.RANGE(Sequelize.INTEGER)    // Defines int4range range. PostgreSQL only.
Sequelize.RANGE(Sequelize.BIGINT)     // Defined int8range range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DATE)       // Defines tstzrange range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DATEONLY)   // Defines daterange range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DECIMAL)    // Defines numrange range. PostgreSQL only.

Sequelize.ARRAY(Sequelize.RANGE(Sequelize.DATE)) // Defines array of tstzrange ranges. PostgreSQL only.

Sequelize.GEOMETRY                    // Spatial column.  PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT')           // Spatial column with geometry type. PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT', 4326)     // Spatial column with geometry type and SRID.  PostgreSQL (with PostGIS) or MySQL only.

The BLOB data type allows you to insert data both as strings and as buffers. When you do a find or findAll on a model which has a BLOB column, that data will always be returned as a buffer.

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

In addition to the type mentioned above, integer, bigint, float and double also support unsigned and zerofill properties, which can be combined in any order: Be aware that this does not apply for PostgreSQL!

Sequelize.INTEGER.UNSIGNED              // INTEGER UNSIGNED
Sequelize.INTEGER(11).UNSIGNED          // INTEGER(11) UNSIGNED
Sequelize.INTEGER(11).ZEROFILL          // INTEGER(11) ZEROFILL
Sequelize.INTEGER(11).ZEROFILL.UNSIGNED // INTEGER(11) UNSIGNED ZEROFILL
Sequelize.INTEGER(11).UNSIGNED.ZEROFILL // INTEGER(11) UNSIGNED ZEROFILL

The examples above only show integer, but the same can be done with bigint and float

Usage in object notation:

// for enums:
sequelize.define('model', {
  states: {
    type:   Sequelize.ENUM,
    values: ['active', 'pending', 'deleted']
  }
})

Array(ENUM)

Its only supported with PostgreSQL.

Array(Enum) type require special treatment. Whenever Sequelize will talk to 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.

Range types

Since range types have extra information for their bound inclusion/exclusion it's not very straightforward to just use a tuple to represent them in javascript.

When supplying ranges as values you can choose from the following APIs:

// defaults to '["2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'
// inclusive lower bound, exclusive upper bound
Timeline.create({ range: [new Date(Date.UTC(2016, 0, 1)), new Date(Date.UTC(2016, 1, 1))] });

// control inclusion
const range = [new Date(Date.UTC(2016, 0, 1)), new Date(Date.UTC(2016, 1, 1))];
range.inclusive = false; // '()'
range.inclusive = [false, true]; // '(]'
range.inclusive = true; // '[]'
range.inclusive = [true, false]; // '[)'

// or as a single expression
const range = [
  { value: new Date(Date.UTC(2016, 0, 1)), inclusive: false },
  { value: new Date(Date.UTC(2016, 1, 1)), inclusive: true },
];
// '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"]'

// composite form
const range = [
  { value: new Date(Date.UTC(2016, 0, 1)), inclusive: false },
  new Date(Date.UTC(2016, 1, 1)),
];
// '("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00")'

Timeline.create({ range });

However, please note that whenever you get back a value that is range you will receive:

// stored value: ("2016-01-01 00:00:00+00:00", "2016-02-01 00:00:00+00:00"]
range // [Date, Date]
range.inclusive // [false, true]

Make sure you turn that into a serializable format before serialization since array extra properties will not be serialized.

Special Cases

// empty range:
Timeline.create({ range: [] }); // range = 'empty'

// Unbounded range:
Timeline.create({ range: [null, null] }); // range = '[,)'
// range = '[,"2016-01-01 00:00:00+00:00")'
Timeline.create({ range: [null, new Date(Date.UTC(2016, 0, 1))] });

// Infinite range:
// range = '[-infinity,"2016-01-01 00:00:00+00:00")'
Timeline.create({ range: [-Infinity, new Date(Date.UTC(2016, 0, 1))] });

Deferrable

When you specify a foreign key column it is optionally possible to declare the deferrable type in PostgreSQL. The following options are available:

// Defer all foreign key constraint check to the end of a transaction
Sequelize.Deferrable.INITIALLY_DEFERRED

// Immediately check the foreign key constraints
Sequelize.Deferrable.INITIALLY_IMMEDIATE

// Don't defer the checks at all
Sequelize.Deferrable.NOT

The last option is the default in PostgreSQL and won't allow you to dynamically change the rule in a transaction. See the transaction section for further information.

Getters & setters

It is possible to define 'object-property' getters and setter functions on your models, these can be used both for 'protecting' properties that map to database fields and for defining 'pseudo' properties.

Getters and Setters can be defined in 2 ways (you can mix and match these 2 approaches):

• as part of a single property definition
• as part of a model options

N.B: If a getter or setter is defined in both places then the function found in the relevant property definition will always take precedence.

Defining as part of a property

const Employee = sequelize.define('employee', {
  name: {
    type: Sequelize.STRING,
    allowNull: false,
    get() {
      const title = this.getDataValue('title');
      // 'this' allows you to access attributes of the instance
      return this.getDataValue('name') + ' (' + title + ')';
    },
  },
  title: {
    type: Sequelize.STRING,
    allowNull: false,
    set(val) {
      this.setDataValue('title', val.toUpperCase());
    }
  }
});

Employee
  .create({ name: 'John Doe', title: 'senior engineer' })
  .then(employee => {
    console.log(employee.get('name')); // John Doe (SENIOR ENGINEER)
    console.log(employee.get('title')); // SENIOR ENGINEER
  })

Defining as part of the model options

Below is an example of defining the getters and setters in the model options. The fullName getter, is an example of how you can define pseudo properties on your models - attributes which are not actually part of your database schema. In fact, pseudo properties can be defined in two ways: using model getters, or by using a column with the VIRTUAL datatype. Virtual datatypes can have validations, while getters for virtual attributes cannot.

Note that the this.firstname and this.lastname references in the fullName getter function will trigger a call to the respective getter functions. If you do not want that then use the getDataValue() method to access the raw value (see below).

const Foo = sequelize.define('foo', {
  firstname: Sequelize.STRING,
  lastname: Sequelize.STRING
}, {
  getterMethods: {
    fullName() {
      return this.firstname + ' ' + this.lastname
    }
  },

  setterMethods: {
    fullName(value) {
      const names = value.split(' ');

      this.setDataValue('firstname', names.slice(0, -1).join(' '));
      this.setDataValue('lastname', names.slice(-1).join(' '));
    },
  }
});

Helper functions for use inside getter and setter definitions

• retrieving an underlying property value - always use this.getDataValue()

/* a getter for 'title' property */
get() {
  return this.getDataValue('title')
}

• setting an underlying property value - always use this.setDataValue()

/* a setter for 'title' property */
set(title) {
  this.setDataValue('title', title.toString().toLowerCase());
}

N.B: It is important to stick to using the setDataValue() and getDataValue() functions (as opposed to accessing the underlying "data values" property directly) - doing so protects your custom getters and setters from changes in the underlying model implementations.

Validations

Model validations, allow you to specify format/content/inheritance validations for each attribute of the model.

Validations are automatically run on create, update and save. You can also call validate() to manually validate an instance.

The validations are implemented by validator.js.

const ValidateMe = sequelize.define('foo', {
  foo: {
    type: Sequelize.STRING,
    validate: {
      is: ["^[a-z]+$",'i'],     // will only allow letters
      is: /^[a-z]+$/i,          // same as the previous example using real RegExp
      not: ["[a-z]",'i'],       // will not allow letters
      isEmail: true,            // checks for email format (foo@bar.com)
      isUrl: true,              // checks for url format (http://foo.com)
      isIP: true,               // checks for IPv4 (129.89.23.1) or IPv6 format
      isIPv4: true,             // checks for IPv4 (129.89.23.1)
      isIPv6: true,             // checks for IPv6 format
      isAlpha: true,            // will only allow letters
      isAlphanumeric: true,     // will only allow alphanumeric characters, so "_abc" will fail
      isNumeric: true,          // will only allow numbers
      isInt: true,              // checks for valid integers
      isFloat: true,            // checks for valid floating point numbers
      isDecimal: true,          // checks for any numbers
      isLowercase: true,        // checks for lowercase
      isUppercase: true,        // checks for uppercase
      notNull: true,            // won't allow null
      isNull: true,             // only allows null
      notEmpty: true,           // don't allow empty strings
      equals: 'specific value', // only allow a specific value
      contains: 'foo',          // force specific substrings
      notIn: [['foo', 'bar']],  // check the value is not one of these
      isIn: [['foo', 'bar']],   // check the value is one of these
      notContains: 'bar',       // don't allow specific substrings
      len: [2,10],              // only allow values with length between 2 and 10
      isUUID: 4,                // only allow uuids
      isDate: true,             // only allow date strings
      isAfter: "2011-11-05",    // only allow date strings after a specific date
      isBefore: "2011-11-05",   // only allow date strings before a specific date
      max: 23,                  // only allow values <= 23
      min: 23,                  // only allow values >= 23
      isCreditCard: true,       // check for valid credit card numbers

      // custom validations are also possible:
      isEven(value) {
        if (parseInt(value) % 2 != 0) {
          throw new Error('Only even values are allowed!')
          // we also are in the model's context here, so this.otherField
          // would get the value of otherField if it existed
        }
      }
    }
  }
});

Note that where multiple arguments need to be passed to the built-in validation functions, the arguments to be passed must be in an array. But if a single array argument is to be passed, for instance an array of acceptable strings for isIn, this will be interpreted as multiple string arguments instead of one array argument. To work around this pass a single-length array of arguments, such as [['one', 'two']] as shown above.

To use a custom error message instead of that provided by validator.js, use an object instead of the plain value or array of arguments, for example a validator which needs no argument can be given a custom message with

isInt: {
  msg: "Must be an integer number of pennies"
}

or if arguments need to also be passed add anargsproperty:

isIn: {
  args: [['en', 'zh']],
  msg: "Must be English or Chinese"
}

When using custom validator functions the error message will be whatever message the thrownErrorobject holds.

See the validator.js project for more details on the built in validation methods.

Hint: You can also define a custom function for the logging part. Just pass a function. The first parameter will be the string that is logged.

Validators and allowNull

If a particular field of a model is set to allow null (with allowNull: true) and that value has been set to null , its validators do not run. This means you can, for instance, have a string field which validates its length to be at least 5 characters, but which also allowsnull.

Model validations

Validations can also be defined to check the model after the field-specific validators. Using this you could, for example, ensure either neither of latitude and longitude are set or both, and fail if one but not the other is set.

Model validator methods are called with the model object's context and are deemed to fail if they throw an error, otherwise pass. This is just the same as with custom field-specific validators.

Any error messages collected are put in the validation result object alongside the field validation errors, with keys named after the failed validation method's key in the validate option object. Even though there can only be one error message for each model validation method at any one time, it is presented as a single string error in an array, to maximize consistency with the field errors.

An example:

const Pub = Sequelize.define('pub', {
  name: { type: Sequelize.STRING },
  address: { type: Sequelize.STRING },
  latitude: {
    type: Sequelize.INTEGER,
    allowNull: true,
    defaultValue: null,
    validate: { min: -90, max: 90 }
  },
  longitude: {
    type: Sequelize.INTEGER,
    allowNull: true,
    defaultValue: null,
    validate: { min: -180, max: 180 }
  },
}, {
  validate: {
    bothCoordsOrNone() {
      if ((this.latitude === null) !== (this.longitude === null)) {
        throw new Error('Require either both latitude and longitude or neither')
      }
    }
  }
})

In this simple case an object fails validation if either latitude or longitude is given, but not both. If we try to build one with an out-of-range latitude and no longitude, raging_bullock_arms.validate() might return

{
  'latitude': ['Invalid number: latitude'],
  'bothCoordsOrNone': ['Require either both latitude and longitude or neither']
}

Configuration

You can also influence the way Sequelize handles your column names:

const Bar = sequelize.define('bar', { /* bla */ }, {
  // don't add the timestamp attributes (updatedAt, createdAt)
  timestamps: false,

  // don't delete database entries but set the newly added attribute deletedAt
  // to the current date (when deletion was done). paranoid will only work if
  // timestamps are enabled
  paranoid: true,

  // don't use camelcase for automatically added attributes but underscore style
  // so updatedAt will be updated_at
  underscored: true,

  // disable the modification of table names; By default, sequelize will automatically
  // transform all passed model names (first parameter of define) into plural.
  // if you don't want that, set the following
  freezeTableName: true,

  // define the table's name
  tableName: 'my_very_custom_table_name',

  // Enable optimistic locking.  When enabled, sequelize will add a version count attribute
  // to the model and throw an OptimisticLockingError error when stale instances are saved.
  // Set to true or a string with the attribute name you want to use to enable.
  version: true
})

If you want sequelize to handle timestamps, but only want some of them, or want your timestamps to be called something else, you can override each column individually:

const Foo = sequelize.define('foo',  { /* bla */ }, {
  // don't forget to enable timestamps!
  timestamps: true,

  // I don't want createdAt
  createdAt: false,

  // I want updatedAt to actually be called updateTimestamp
  updatedAt: 'updateTimestamp',

  // And deletedAt to be called destroyTime (remember to enable paranoid for this to work)
  deletedAt: 'destroyTime',
  paranoid: true
})

You can also change the database engine, e.g. to MyISAM. InnoDB is the default.

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

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

Finally you can specify a comment for the table in MySQL and PG

const Person = sequelize.define('person', { /* attributes */ }, {
  comment: "I'm a table comment!"
})

Import

You can also store your model definitions in a single file using the import method. The returned object is exactly the same as defined in the imported file's function. Since v1:5.0 of Sequelize the import is cached, so you won't run into troubles when calling the import of a file twice or more often.

// in your server file - e.g. app.js
const Project = sequelize.import(__dirname + "/path/to/models/project")

// The model definition is done in /path/to/models/project.js
// As you might notice, the DataTypes are the very same as explained above
module.exports = (sequelize, DataTypes) => {
  return sequelize.define("project", {
    name: DataTypes.STRING,
    description: DataTypes.TEXT
  })
}

The import method can also accept a callback as an argument.

sequelize.import('project', (sequelize, DataTypes) => {
  return sequelize.define("project", {
    name: DataTypes.STRING,
    description: DataTypes.TEXT
  })
})

This extra capability is useful when, for example, Error: Cannot find module is thrown even though /path/to/models/project seems to be correct. Some frameworks, such as Meteor, overload require, and spit out "surprise" results like :

Error: Cannot find module '/home/you/meteorApp/.meteor/local/build/programs/server/app/path/to/models/project.js'

This is solved by passing in Meteor's version of require. So, while this probably fails ...

const AuthorModel = db.import('./path/to/models/project');

... this should succeed ...

const AuthorModel = db.import('project', require('./path/to/models/project'));

Optimistic Locking

Sequelize has built-in support for optimistic locking through a model instance version count. Optimistic locking is disabled by default and can be enabled by setting the version property to true in a specific model definition or global model configuration. See model configuration for more details.

Optimistic locking allows concurrent access to model records for edits and prevents conflicts from overwriting data. It does this by checking whether another process has made changes to a record since it was read and throws an OptimisticLockError when a conflict is detected.

Database synchronization

When starting a new project you won't have a database structure and using Sequelize you won't need to. Just specify your model structures and let the library do the rest. Currently supported is the creation and deletion of tables:

// Create the tables:
Project.sync()
Task.sync()

// Force the creation!
Project.sync({force: true}) // this will drop the table first and re-create it afterwards

// drop the tables:
Project.drop()
Task.drop()

// event handling:
Project.[sync|drop]().then(() => {
  // ok ... everything is nice!
}).catch(error => {
  // oooh, did you enter wrong database credentials?
})

Because synchronizing and dropping all of your tables might be a lot of lines to write, you can also let Sequelize do the work for you:

// Sync all models that aren't already in the database
sequelize.sync()

// Force sync all models
sequelize.sync({force: true})

// Drop all tables
sequelize.drop()

// emit handling:
sequelize.[sync|drop]().then(() => {
  // woot woot
}).catch(error => {
  // whooops
})

Because .sync({ force: true }) is destructive operation, you can use match option as an additional safety check. match option tells sequelize to 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.

// This will run .sync() only if database name ends with '_test'
sequelize.sync({ force: true, match: /_test$/ });

Expansion of models

Sequelize Models are ES6 classes. You can very easily add custom instance or class level methods.

const User = sequelize.define('user', { firstname: Sequelize.STRING });

// Adding a class level method
User.classLevelMethod = function() {
  return 'foo';
};

// Adding an instance level method
User.prototype.instanceLevelMethod = function() {
  return 'bar';
};

Of course you can also access the instance's data and generate virtual getters:

const User = sequelize.define('user', { firstname: Sequelize.STRING, lastname: Sequelize.STRING });

User.prototype.getFullname = function() {
  return [this.firstname, this.lastname].join(' ');
};

// Example:
User.build({ firstname: 'foo', lastname: 'bar' }).getFullname() // 'foo bar'

Indexes

Sequelize supports adding indexes to the model definition which will be created during Model.sync() or sequelize.sync.

sequelize.define('user', {}, {
  indexes: [
    // Create a unique index on email
    {
      unique: true,
      fields: ['email']
    },

    // Creates a gin index on data with the jsonb_path_ops operator
    {
      fields: ['data'],
      using: 'gin',
      operator: 'jsonb_path_ops'
    },

    // By default index name will be [table]_[fields]
    // Creates a multi column partial index
    {
      name: 'public_by_author',
      fields: ['author', 'status'],
      where: {
        status: 'public'
      }
    },

    // A BTREE index with a ordered field
    {
      name: 'title_index',
      method: 'BTREE',
      fields: ['author', {attribute: 'title', collate: 'en_US', order: 'DESC', length: 5}]
    }
  ]
})

Model usage

Data retrieval / Finders

Finder methods are intended to query data from the database. They do not return plain objects but instead return model instances. Because finder methods return model instances you can call any model instance member on the result as described in the documentation for instances.

In this document we'll explore what finder methods can do:

find - Search for one specific element in the database

// search for known ids
Project.findById(123).then(project => {
  // project will be an instance of Project and stores the content of the table entry
  // with id 123. if such an entry is not defined you will get null
})

// search for attributes
Project.findOne({ where: {title: 'aProject'} }).then(project => {
  // project will be the first entry of the Projects table with the title 'aProject' || null
})


Project.findOne({
  where: {title: 'aProject'},
  attributes: ['id', ['name', 'title']]
}).then(project => {
  // project will be the first entry of the Projects table with the title 'aProject' || null
  // project.title will contain the name of the project
})

findOrCreate - Search for a specific element or create it if not available

The method findOrCreate can be used to check if a certain element already exists in the database. If that is the case the method will result in a respective instance. If the element does not yet exist, it will be created.

Let's assume we have an empty database with a User model which has a username and a job.

User
  .findOrCreate({where: {username: 'sdepold'}, defaults: {job: 'Technical Lead JavaScript'}})
  .spread((user, created) => {
    console.log(user.get({
      plain: true
    }))
    console.log(created)

    /*
     findOrCreate returns an array containing the object that was found or created and a boolean that will be true if a new object was created and false if not, like so:

    [ {
        username: 'sdepold',
        job: 'Technical Lead JavaScript',
        id: 1,
        createdAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET),
        updatedAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET)
      },
      true ]

 In the example above, the "spread" on line 39 divides the array into its 2 parts and passes them as arguments to the callback function defined beginning at line 39, which treats them as "user" and "created" in this case. (So "user" will be the object from index 0 of the returned array and "created" will equal "true".)
    */
  })

The code created a new instance. So when we already have an instance ...

User.create({ username: 'fnord', job: 'omnomnom' })
  .then(() => User.findOrCreate({where: {username: 'fnord'}, defaults: {job: 'something else'}}))
  .spread((user, created) => {
    console.log(user.get({
      plain: true
    }))
    console.log(created)

    /*
    In this example, findOrCreate returns an array like this:
    [ {
        username: 'fnord',
        job: 'omnomnom',
        id: 2,
        createdAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET),
        updatedAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET)
      },
      false
    ]
    The array returned by findOrCreate gets spread into its 2 parts by the "spread" on line 69, and the parts will be passed as 2 arguments to the callback function beginning on line 69, which will then treat them as "user" and "created" in this case. (So "user" will be the object from index 0 of the returned array and "created" will equal "false".)
    */
  })

... the existing entry will not be changed. See the job of the second user, and the fact that created was false.

findAndCountAll - Search for multiple elements in the database, returns both data and total count

This is a convenience method that combinesfindAll and count (see below) this is useful when dealing with queries related to pagination where you want to retrieve data with a limit and offset but also need to know the total number of records that match the query:

The success handler will always receive an object with two properties:

• count - an integer, total number records matching the where clause and other filters due to associations
• rows - an array of objects, the records matching the where clause and other filters due to associations, within the limit and offset range

Project
  .findAndCountAll({
     where: {
        title: {
          [Op.like]: 'foo%'
        }
     },
     offset: 10,
     limit: 2
  })
  .then(result => {
    console.log(result.count);
    console.log(result.rows);
  });

It support includes. Only the includes that are marked as required will be added to the count part:

Suppose you want to find all users who have a profile attached:

User.findAndCountAll({
  include: [
     { model: Profile, required: true}
  ],
  limit: 3
});

Because the include for Profile has required set it will result in an inner join, and only the users who have a profile will be counted. If we remove required from the include, both users with and without profiles will be counted. Adding a where clause to the include automatically makes it required:

User.findAndCountAll({
  include: [
     { model: Profile, where: { active: true }}
  ],
  limit: 3
});

The query above will only count users who have an active profile, because required is implicitly set to true when you add a where clause to the include.

The options object that you pass to findAndCountAll is the same as for findAll (described below).

findAll - Search for multiple elements in the database

// find multiple entries
Project.findAll().then(projects => {
  // projects will be an array of all Project instances
})

// also possible:
Project.all().then(projects => {
  // projects will be an array of all Project instances
})

// search for specific attributes - hash usage
Project.findAll({ where: { name: 'A Project' } }).then(projects => {
  // projects will be an array of Project instances with the specified name
})

// search within a specific range
Project.findAll({ where: { id: [1,2,3] } }).then(projects => {
  // projects will be an array of Projects having the id 1, 2 or 3
  // this is actually doing an IN query
})

Project.findAll({
  where: {
    id: {
      [Op.and]: {a: 5},           // AND (a = 5)
      [Op.or]: [{a: 5}, {a: 6}],  // (a = 5 OR a = 6)
      [Op.gt]: 6,                // id > 6
      [Op.gte]: 6,               // id >= 6
      [Op.lt]: 10,               // id < 10
      [Op.lte]: 10,              // id <= 10
      [Op.ne]: 20,               // id != 20
      [Op.between]: [6, 10],     // BETWEEN 6 AND 10
      [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
      [Op.in]: [1, 2],           // IN [1, 2]
      [Op.notIn]: [1, 2],        // NOT IN [1, 2]
      [Op.like]: '%hat',         // LIKE '%hat'
      [Op.notLike]: '%hat',       // NOT LIKE '%hat'
      [Op.iLike]: '%hat',         // ILIKE '%hat' (case insensitive)  (PG only)
      [Op.notILike]: '%hat',      // NOT ILIKE '%hat'  (PG only)
      [Op.overlap]: [1, 2],       // && [1, 2] (PG array overlap operator)
      [Op.contains]: [1, 2],      // @> [1, 2] (PG array contains operator)
      [Op.contained]: [1, 2],     // <@ [1, 2] (PG array contained by operator)
      [Op.any]: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)
    },
    status: {
      [Op.not]: false           // status NOT FALSE
    }
  }
})

Complex filtering / OR / NOT queries

It's possible to do complex where queries with multiple levels of nested AND, OR and NOT conditions. In order to do that you can use or, and or not Operators:

Project.findOne({
  where: {
    name: 'a project',
    [Op.or]: [
      { id: [1,2,3] },
      { id: { [Op.gt]: 10 } }
    ]
  }
})

Project.findOne({
  where: {
    name: 'a project',
    id: {
      [Op.or]: [
        [1,2,3],
        { [Op.gt]: 10 }
      ]
    }
  }
})

Both pieces of code will generate the following:

SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name` = 'a project'
   AND (`Projects`.`id` IN (1,2,3) OR `Projects`.`id` > 10)
)
LIMIT 1;

not example:

Project.findOne({
  where: {
    name: 'a project',
    [Op.not]: [
      { id: [1,2,3] },
      { array: { [Op.contains]: [3,4,5] } }
    ]
  }
});

Will generate:

SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name` = 'a project'
   AND NOT (`Projects`.`id` IN (1,2,3) OR `Projects`.`array` @> ARRAY[3,4,5]::INTEGER[])
)
LIMIT 1;

Manipulating the dataset with limit, offset, order and group

To get more relevant data, you can use limit, offset, order and grouping:

// limit the results of the query
Project.findAll({ limit: 10 })

// step over the first 10 elements
Project.findAll({ offset: 10 })

// step over the first 10 elements, and take 2
Project.findAll({ offset: 10, limit: 2 })

The syntax for grouping and ordering are equal, so below it is only explained with a single example for group, and the rest for order. Everything you see below can also be done for group

Project.findAll({order: 'title DESC'})
// yields ORDER BY title DESC

Project.findAll({group: 'name'})
// yields GROUP BY name

Notice how in the two examples above, the string provided is inserted verbatim into the query, i.e. column names are not escaped. When you provide a string to order/group, this will always be the case. If you want to escape column names, you should provide an array of arguments, even though you only want to order/group by a single column

something.findOne({
  order: [
    // will return `name`
    ['name'],
    // will return `username` DESC
    ['username', 'DESC'],
    // will return max(`age`)
    sequelize.fn('max', sequelize.col('age')),
    // will return max(`age`) DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'],
    // will return otherfunction(`col1`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
    // will return otherfunction(awesomefunction(`col`)) DESC, This nesting is potentially infinite!
    [sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']
  ]
})

To recap, the elements of the order/group array can be the following:

• String - will be quoted
• Array - first element will be quoted, second will be appended verbatim
• Object -
  • Raw will be added verbatim without quoting
  • Everything else is ignored, and if raw is not set, the query will fail
• Sequelize.fn and Sequelize.col returns functions and quoted column names

Raw queries

Sometimes you might be expecting a massive dataset that you just want to display, without manipulation. For each row you select, Sequelize creates an instance with functions for update, delete, get associations etc. If you have thousands of rows, this might take some time. If you only need the raw data and don't want to update anything, you can do like this to get the raw data.

// Are you expecting a massive dataset from the DB,
// and don't want to spend the time building DAOs for each entry?
// You can pass an extra query option to get the raw data instead:
Project.findAll({ where: { ... }, raw: true })

count - Count the occurrences of elements in the database

There is also a method for counting database objects:

Project.count().then(c => {
  console.log("There are " + c + " projects!")
})

Project.count({ where: {'id': {[Op.gt]: 25}} }).then(c => {
  console.log("There are " + c + " projects with an id greater than 25.")
})

max - Get the greatest value of a specific attribute within a specific table

And here is a method for getting the max value of an attribute:f

/*
  Let's assume 3 person objects with an attribute age.
  The first one is 10 years old,
  the second one is 5 years old,
  the third one is 40 years old.
*/
Project.max('age').then(max => {
  // this will return 40
})

Project.max('age', { where: { age: { [Op.lt]: 20 } } }).then(max => {
  // will be 10
})

min - Get the least value of a specific attribute within a specific table

And here is a method for getting the min value of an attribute:

/*
  Let's assume 3 person objects with an attribute age.
  The first one is 10 years old,
  the second one is 5 years old,
  the third one is 40 years old.
*/
Project.min('age').then(min => {
  // this will return 5
})

Project.min('age', { where: { age: { [Op.gt]: 5 } } }).then(min => {
  // will be 10
})

sum - Sum the value of specific attributes

In order to calculate the sum over a specific column of a table, you can use the sum method.

/*
  Let's assume 3 person objects with an attribute age.
  The first one is 10 years old,
  the second one is 5 years old,
  the third one is 40 years old.
*/
Project.sum('age').then(sum => {
  // this will return 55
})

Project.sum('age', { where: { age: { [Op.gt]: 5 } } }).then(sum => {
  // will be 50
})

Eager loading

When you are retrieving data from the database there is a fair chance that you also want to get associations with the same query - this is called eager loading. The basic idea behind that, is the use of the attribute include when you are calling find or findAll. Lets assume the following setup:

const User = sequelize.define('user', { name: Sequelize.STRING })
const Task = sequelize.define('task', { name: Sequelize.STRING })
const Tool = sequelize.define('tool', { name: Sequelize.STRING })

Task.belongsTo(User)
User.hasMany(Task)
User.hasMany(Tool, { as: 'Instruments' })

sequelize.sync().then(() => {
  // this is where we continue ...
})

OK. So, first of all, let's load all tasks with their associated user.

Task.findAll({ include: [ User ] }).then(tasks => {
  console.log(JSON.stringify(tasks))

  /*
    [{
      "name": "A Task",
      "id": 1,
      "createdAt": "2013-03-20T20:31:40.000Z",
      "updatedAt": "2013-03-20T20:31:40.000Z",
      "userId": 1,
      "user": {
        "name": "John Doe",
        "id": 1,
        "createdAt": "2013-03-20T20:31:45.000Z",
        "updatedAt": "2013-03-20T20:31:45.000Z"
      }
    }]
  */
})

Notice that the accessor (the User property in the resulting instance) is singular because the association is one-to-something.

Next thing: Loading of data with many-to-something associations!

User.findAll({ include: [ Task ] }).then(users => {
  console.log(JSON.stringify(users))

  /*
    [{
      "name": "John Doe",
      "id": 1,
      "createdAt": "2013-03-20T20:31:45.000Z",
      "updatedAt": "2013-03-20T20:31:45.000Z",
      "tasks": [{
        "name": "A Task",
        "id": 1,
        "createdAt": "2013-03-20T20:31:40.000Z",
        "updatedAt": "2013-03-20T20:31:40.000Z",
        "userId": 1
      }]
    }]
  */
})

Notice that the accessor (the Tasks property in the resulting instance) is plural because the association is many-to-something.

If an association is aliased (using the as option), you must specify this alias when including the model. Notice how the user's Tools are aliased as Instruments above. In order to get that right you have to specify the model you want to load, as well as the alias:

User.findAll({ include: [{ model: Tool, as: 'Instruments' }] }).then(users => {
  console.log(JSON.stringify(users))

  /*
    [{
      "name": "John Doe",
      "id": 1,
      "createdAt": "2013-03-20T20:31:45.000Z",
      "updatedAt": "2013-03-20T20:31:45.000Z",
      "Instruments": [{
        "name": "Toothpick",
        "id": 1,
        "createdAt": null,
        "updatedAt": null,
        "userId": 1
      }]
    }]
  */
})

You can also include by alias name by specifying a string that matches the association alias:

User.findAll({ include: ['Instruments'] }).then(users => {
  console.log(JSON.stringify(users))

  /*
    [{
      "name": "John Doe",
      "id": 1,
      "createdAt": "2013-03-20T20:31:45.000Z",
      "updatedAt": "2013-03-20T20:31:45.000Z",
      "Instruments": [{
        "name": "Toothpick",
        "id": 1,
        "createdAt": null,
        "updatedAt": null,
        "userId": 1
      }]
    }]
  */
})

User.findAll({ include: [{ association: 'Instruments' }] }).then(users => {
  console.log(JSON.stringify(users))

  /*
    [{
      "name": "John Doe",
      "id": 1,
      "createdAt": "2013-03-20T20:31:45.000Z",
      "updatedAt": "2013-03-20T20:31:45.000Z",
      "Instruments": [{
        "name": "Toothpick",
        "id": 1,
        "createdAt": null,
        "updatedAt": null,
        "userId": 1
      }]
    }]
  */
})

When eager loading we can also filter the associated model using where. This will return all Users in which the where clause of Tool model matches rows.

User.findAll({
    include: [{
        model: Tool,
        as: 'Instruments',
        where: { name: { [Op.like]: '%ooth%' } }
    }]
}).then(users => {
    console.log(JSON.stringify(users))

    /*
      [{
        "name": "John Doe",
        "id": 1,
        "createdAt": "2013-03-20T20:31:45.000Z",
        "updatedAt": "2013-03-20T20:31:45.000Z",
        "Instruments": [{
          "name": "Toothpick",
          "id": 1,
          "createdAt": null,
          "updatedAt": null,
          "userId": 1
        }]
      }],

      [{
        "name": "John Smith",
        "id": 2,
        "createdAt": "2013-03-20T20:31:45.000Z",
        "updatedAt": "2013-03-20T20:31:45.000Z",
        "Instruments": [{
          "name": "Toothpick",
          "id": 1,
          "createdAt": null,
          "updatedAt": null,
          "userId": 1
        }]
      }],
    */
  })

When an eager loaded model is filtered using include.where then include.required is implicitly set to true. This means that an inner join is done returning parent models with any matching children.

Top level where with eagerly loaded models

To move the where conditions from an included model from the ON condition to the top level WHERE you can use the '$nested.column$' syntax:

User.findAll({
    where: {
        '$Instruments.name$': { [Op.iLike]: '%ooth%' }
    },
    include: [{
        model: Tool,
        as: 'Instruments'
    }]
}).then(users => {
    console.log(JSON.stringify(users));

    /*
      [{
        "name": "John Doe",
        "id": 1,
        "createdAt": "2013-03-20T20:31:45.000Z",
        "updatedAt": "2013-03-20T20:31:45.000Z",
        "Instruments": [{
          "name": "Toothpick",
          "id": 1,
          "createdAt": null,
          "updatedAt": null,
          "userId": 1
        }]
      }],

      [{
        "name": "John Smith",
        "id": 2,
        "createdAt": "2013-03-20T20:31:45.000Z",
        "updatedAt": "2013-03-20T20:31:45.000Z",
        "Instruments": [{
          "name": "Toothpick",
          "id": 1,
          "createdAt": null,
          "updatedAt": null,
          "userId": 1
        }]
      }],
    */

Including everything

To include all attributes, you can pass a single object with all: true:

User.findAll({ include: [{ all: true }]});

Including soft deleted records

In case you want to eager load soft deleted records you can do that by setting include.paranoid to false

User.findAll({
    include: [{
        model: Tool,
        where: { name: { [Op.like]: '%ooth%' } },
        paranoid: false // query and loads the soft deleted records
    }]
});

Ordering Eager Loaded Associations

In the case of a one-to-many relationship.

Company.findAll({ include: [ Division ], order: [ [ Division, 'name' ] ] });
Company.findAll({ include: [ Division ], order: [ [ Division, 'name', 'DESC' ] ] });
Company.findAll({
  include: [ { model: Division, as: 'Div' } ],
  order: [ [ { model: Division, as: 'Div' }, 'name' ] ]
});
Company.findAll({
  include: [ { model: Division, as: 'Div' } ],
  order: [ [ { model: Division, as: 'Div' }, 'name', 'DESC' ] ]
});
Company.findAll({
  include: [ { model: Division, include: [ Department ] } ],
  order: [ [ Division, Department, 'name' ] ]
});

In the case of many-to-many joins, you are also able to sort by attributes in the through table.

Company.findAll({
  include: [ { model: Division, include: [ Department ] } ],
  order: [ [ Division, DepartmentDivision, 'name' ] ]
});

Nested eager loading

You can use nested eager loading to load all related models of a related model:

User.findAll({
  include: [
    {model: Tool, as: 'Instruments', include: [
      {model: Teacher, include: [ /* etc */]}
    ]}
  ]
}).then(users => {
  console.log(JSON.stringify(users))

  /*
    [{
      "name": "John Doe",
      "id": 1,
      "createdAt": "2013-03-20T20:31:45.000Z",
      "updatedAt": "2013-03-20T20:31:45.000Z",
      "Instruments": [{ // 1:M and N:M association
        "name": "Toothpick",
        "id": 1,
        "createdAt": null,
        "updatedAt": null,
        "userId": 1,
        "Teacher": { // 1:1 association
          "name": "Jimi Hendrix"
        }
      }]
    }]
  */
})

This will produce an outer join. However, a where clause on a related model will create an inner join and return only the instances that have matching sub-models. To return all parent instances, you should add required: false.

User.findAll({
  include: [{
    model: Tool,
    as: 'Instruments',
    include: [{
      model: Teacher,
      where: {
        school: "Woodstock Music School"
      },
      required: false
    }]
  }]
}).then(users => {
  /* ... */
})

The query above will return all users, and all their instruments, but only those teachers associated with Woodstock Music School.

Include all also supports nested loading:

User.findAll({ include: [{ all: true, nested: true }]});

Querying

Attributes

To select only some attributes, you can use the attributes option. Most often, you pass an array:

Model.findAll({
  attributes: ['foo', 'bar']
});

SELECT foo, bar ...

Attributes can be renamed using a nested array:

Model.findAll({
  attributes: ['foo', ['bar', 'baz']]
});

SELECT foo, bar AS baz ...

You can use sequelize.fn to do aggregations:

Model.findAll({
  attributes: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});

SELECT COUNT(hats) AS no_hats ...

When using aggregation function, you must give it an alias to be able to access it from the model. In the example above you can get the number of hats with instance.get('no_hats').

Sometimes it may be tiresome to list all the attributes of the model if you only want to add an aggregation:

// This is a tiresome way of getting the number of hats...
Model.findAll({
  attributes: ['id', 'foo', 'bar', 'baz', 'quz', [sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});

// This is shorter, and less error prone because it still works if you add / remove attributes
Model.findAll({
  attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']] }
});

SELECT id, foo, bar, baz, quz, COUNT(hats) AS no_hats ...

Similarly, it's also possible to remove a selected few attributes:

Model.findAll({
  attributes: { exclude: ['baz'] }
});

SELECT id, foo, bar, quz ...

Where

Whether you are querying with findAll/find or doing bulk updates/destroys you can pass a where object to filter the query.

where generally takes an object from attribute:value pairs, where value can be primitives for equality matches or keyed objects for other operators.

It's also possible to generate complex AND/OR conditions by nesting sets of or and and Operators.

Basics

const Op = Sequelize.Op;

Post.findAll({
  where: {
    authorId: 2
  }
});
// SELECT * FROM post WHERE authorId = 2

Post.findAll({
  where: {
    authorId: 12,
    status: 'active'
  }
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';

Post.findAll({
  where: {
    [Op.or]: [{authorId: 12}, {authorId: 13}]
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.findAll({
  where: {
    authorId: {
      [Op.or]: [12, 13]
    }
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.destroy({
  where: {
    status: 'inactive'
  }
});
// DELETE FROM post WHERE status = 'inactive';

Post.update({
  updatedAt: null,
}, {
  where: {
    deletedAt: {
      [Op.ne]: null
    }
  }
});
// UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;

Post.findAll({
  where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
});
// SELECT * FROM post WHERE char_length(status) = 6;

Operators

Sequelize exposes symbol operators that can be used for to create more complex comparisons -

const Op = Sequelize.Op

[Op.and]: {a: 5}           // AND (a = 5)
[Op.or]: [{a: 5}, {a: 6}]  // (a = 5 OR a = 6)
[Op.gt]: 6,                // > 6
[Op.gte]: 6,               // >= 6
[Op.lt]: 10,               // < 10
[Op.lte]: 10,              // <= 10
[Op.ne]: 20,               // != 20
[Op.eq]: 3,                // = 3
[Op.not]: true,            // IS NOT TRUE
[Op.between]: [6, 10],     // BETWEEN 6 AND 10
[Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
[Op.in]: [1, 2],           // IN [1, 2]
[Op.notIn]: [1, 2],        // NOT IN [1, 2]
[Op.like]: '%hat',         // LIKE '%hat'
[Op.notLike]: '%hat'       // NOT LIKE '%hat'
[Op.iLike]: '%hat'         // ILIKE '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat'      // NOT ILIKE '%hat'  (PG only)
[Op.regexp]: '^[h|a|t]'    // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
[Op.notRegexp]: '^[h|a|t]' // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
[Op.iRegexp]: '^[h|a|t]'    // ~* '^[h|a|t]' (PG only)
[Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (PG only)
[Op.like]: { [Op.any]: ['cat', 'hat']}
                       // LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
[Op.overlap]: [1, 2]       // && [1, 2] (PG array overlap operator)
[Op.contains]: [1, 2]      // @> [1, 2] (PG array contains operator)
[Op.contained]: [1, 2]     // <@ [1, 2] (PG array contained by operator)
[Op.any]: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)

[Op.col]: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example

Range Operators

Range types can be queried with all supported operators.

Keep in mind, the provided range value can define the bound inclusion/exclusion as well.

// All the above equality and inequality operators plus the following:

[Op.contains]: 2           // @> '2'::integer (PG range contains element operator)
[Op.contains]: [1, 2]      // @> [1, 2) (PG range contains range operator)
[Op.contained]: [1, 2]     // <@ [1, 2) (PG range is contained by operator)
[Op.overlap]: [1, 2]       // && [1, 2) (PG range overlap (have points in common) operator)
[Op.adjacent]: [1, 2]      // -|- [1, 2) (PG range is adjacent to operator)
[Op.strictLeft]: [1, 2]    // << [1, 2) (PG range strictly left of operator)
[Op.strictRight]: [1, 2]   // >> [1, 2) (PG range strictly right of operator)
[Op.noExtendRight]: [1, 2] // &< [1, 2) (PG range does not extend to the right of operator)
[Op.noExtendLeft]: [1, 2]  // &> [1, 2) (PG range does not extend to the left of operator)

Combinations

const Op = Sequelize.Op;

{
  rank: {
    [Op.or]: {
      [Op.lt]: 1000,
      [Op.eq]: null
    }
  }
}
// rank < 1000 OR rank IS NULL

{
  createdAt: {
    [Op.lt]: new Date(),
    [Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
  }
}
// createdAt < [timestamp] AND createdAt > [timestamp]

{
  [Op.or]: [
    {
      title: {
        [Op.like]: 'Boat%'
      }
    },
    {
      description: {
        [Op.like]: '%boat%'
      }
    }
  ]
}
// title LIKE 'Boat%' OR description LIKE '%boat%'

Operators Aliases

Sequelize allows setting specific strings as aliases for operators -

const Op = Sequelize.Op;
const operatorsAliases = {
  $gt: Op.gt
}
const connection = new Sequelize(db, user, pass, { operatorsAliases })

[Op.gt]: 6 // > 6
$gt: 6 // same as using Op.gt (> 6)

Operators security

Using Sequelize without any aliases improves security. Some frameworks automatically parse user input into js objects and if you fail to sanitize your input it might be possible to inject an Object with string operators to Sequelize.

Not having any string aliases will make it extremely unlikely that operators could be injected but you should always properly validate and sanitize user input.

For backward compatibility reasons Sequelize sets the following aliases by default - $eq, $ne, $gte, $gt, $lte, $lt, $not, $in, $notIn, $is, $like, $notLike, $iLike, $notILike, $regexp, $notRegexp, $iRegexp, $notIRegexp, $between, $notBetween, $overlap, $contains, $contained, $adjacent, $strictLeft, $strictRight, $noExtendRight, $noExtendLeft, $and, $or, $any, $all, $values, $col

Currently the following legacy aliases are also set but are planned to be fully removed in the near future - ne, not, in, notIn, gte, gt, lte, lt, like, ilike, $ilike, nlike, $notlike, notilike, .., between, !.., notbetween, nbetween, overlap, &&, @>, <@

For better security it is highly advised to use Sequelize.Op and not depend on any string alias at all. You can limit alias your application will need by setting operatorsAliases option, remember to sanitize user input especially when you are directly passing them to Sequelize methods.

const Op = Sequelize.Op;

//use sequelize without any operators aliases
const connection = new Sequelize(db, user, pass, { operatorsAliases: false });

//use sequelize with only alias for $and => Op.and
const connection2 = new Sequelize(db, user, pass, { operatorsAliases: { $and: Op.and } });

Sequelize will warn you if you're using the default aliases and not limiting them if you want to keep using all default aliases (excluding legacy ones) without the warning you can pass the following operatorsAliases option -

const Op = Sequelize.Op;
const operatorsAliases = {
  $eq: Op.eq,
  $ne: Op.ne,
  $gte: Op.gte,
  $gt: Op.gt,
  $lte: Op.lte,
  $lt: Op.lt,
  $not: Op.not,
  $in: Op.in,
  $notIn: Op.notIn,
  $is: Op.is,
  $like: Op.like,
  $notLike: Op.notLike,
  $iLike: Op.iLike,
  $notILike: Op.notILike,
  $regexp: Op.regexp,
  $notRegexp: Op.notRegexp,
  $iRegexp: Op.iRegexp,
  $notIRegexp: Op.notIRegexp,
  $between: Op.between,
  $notBetween: Op.notBetween,
  $overlap: Op.overlap,
  $contains: Op.contains,
  $contained: Op.contained,
  $adjacent: Op.adjacent,
  $strictLeft: Op.strictLeft,
  $strictRight: Op.strictRight,
  $noExtendRight: Op.noExtendRight,
  $noExtendLeft: Op.noExtendLeft,
  $and: Op.and,
  $or: Op.or,
  $any: Op.any,
  $all: Op.all,
  $values: Op.values,
  $col: Op.col
};

const connection = new Sequelize(db, user, pass, { operatorsAliases });

JSON

The JSON data type is supported by the PostgreSQL, SQLite and MySQL dialects only.

PostgreSQL

The JSON data type in PostgreSQL stores the value as plain text, as opposed to binary representation. If you simply want to store and retrieve a JSON representation, using JSON will take less disk space and less time to build from its input representation. However, if you want to do any operations on the JSON value, you should prefer the JSONB data type described below.

MSSQL

MSSQL does not have a JSON data type, however it does provide support for JSON stored as strings through certain functions since SQL Server 2016. Using these functions, you will be able to query the JSON stored in the string, but any returned values will need to be parsed separately.

// ISJSON - to test if a string contains valid JSON
User.findAll({
  where: sequelize.where(sequelize.fn('ISJSON', sequelize.col('userDetails')), 1)
})

// JSON_VALUE - extract a scalar value from a JSON string
User.findAll({
  attributes: [[ sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), 'address line 1']]
})

// JSON_VALUE - query a scalar value from a JSON string
User.findAll({
  where: sequelize.where(sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), '14, Foo Street')
})

// JSON_QUERY - extract an object or array
User.findAll({
  attributes: [[ sequelize.fn('JSON_QUERY', sequelize.col('userDetails'), '$.address'), 'full address']]
})

JSONB

JSONB can be queried in three different ways.

Nested object

{
  meta: {
    video: {
      url: {
        [Op.ne]: null
      }
    }
  }
}

Nested key

{
  "meta.audio.length": {
    [Op.gt]: 20
  }
}

Containment

{
  "meta": {
    [Op.contains]: {
      site: {
        url: 'http://google.com'
      }
    }
  }
}

Relations / Associations

// Find all projects with a least one task where task.state === project.state
Project.findAll({
    include: [{
        model: Task,
        where: { state: Sequelize.col('project.state') }
    }]
})

Pagination / Limiting

// Fetch 10 instances/rows
Project.findAll({ limit: 10 })

// Skip 8 instances/rows
Project.findAll({ offset: 8 })

// Skip 5 instances and fetch the 5 after that
Project.findAll({ offset: 5, limit: 5 })

Ordering

order takes an array of items to order the query by or a sequelize method. Generally you will want to use a tuple/array of either attribute, direction or just direction to ensure proper escaping.

Subtask.findAll({
  order: [
    // Will escape title and validate DESC against a list of valid direction parameters
    ['title', 'DESC'],

    // Will order by max(age)
    sequelize.fn('max', sequelize.col('age')),

    // Will order by max(age) DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'],

    // Will order by  otherfunction(`col1`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],

    // Will order an associated model's created_at using the model name as the association's name.
    [Task, 'createdAt', 'DESC'],

    // Will order through an associated model's created_at using the model names as the associations' names.
    [Task, Project, 'createdAt', 'DESC'],

    // Will order by an associated model's created_at using the name of the association.
    ['Task', 'createdAt', 'DESC'],

    // Will order by a nested associated model's created_at using the names of the associations.
    ['Task', 'Project', 'createdAt', 'DESC'],

    // Will order by an associated model's created_at using an association object. (preferred method)
    [Subtask.associations.Task, 'createdAt', 'DESC'],

    // Will order by a nested associated model's created_at using association objects. (preferred method)
    [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],

    // Will order by an associated model's created_at using a simple association object.
    [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],

    // Will order by a nested associated model's created_at simple association objects.
    [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
  ]

  // Will order by max age descending
  order: sequelize.literal('max(age) DESC')

  // Will order by max age ascending assuming ascending is the default order when direction is omitted
  order: sequelize.fn('max', sequelize.col('age'))

  // Will order by age ascending assuming ascending is the default order when direction is omitted
  order: sequelize.col('age')

  // Will order randomly based on the dialect (instead of fn('RAND') or fn('RANDOM'))
  order: sequelize.random()
})

Table Hint

tableHint can be used to optionally pass a table hint when using mssql. The hint must be a value from Sequelize.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 = Sequelize.TableHints;

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

Instances

Building a non-persistent instance

In order to create instances of defined classes just do as follows. You might recognize the syntax if you coded Ruby in the past. Using the build-method will return an unsaved object, which you explicitly have to save.

const project = Project.build({
  title: 'my awesome project',
  description: 'woot woot. this will make me a rich man'
})

const task = Task.build({
  title: 'specify the project idea',
  description: 'bla',
  deadline: new Date()
})

Built instances will automatically get default values when they were defined:

// first define the model
const Task = sequelize.define('task', {
  title: Sequelize.STRING,
  rating: { type: Sequelize.STRING, defaultValue: 3 }
})

// now instantiate an object
const task = Task.build({title: 'very important task'})

task.title  // ==> 'very important task'
task.rating // ==> 3

To get it stored in the database, use the save-method and catch the events ... if needed:

project.save().then(() => {
  // my nice callback stuff
})

task.save().catch(error => {
  // mhhh, wth!
})

// you can also build, save and access the object with chaining:
Task
  .build({ title: 'foo', description: 'bar', deadline: new Date() })
  .save()
  .then(anotherTask => {
    // you can now access the currently saved task with the variable anotherTask... nice!
  })
  .catch(error => {
    // Ooops, do some error-handling
  })

Creating persistent instances

While an instance created with .build() requires an explicit .save() call to be stored in the database, .create() omits that requirement altogether and automatically stores your instance's data once called.

Task.create({ title: 'foo', description: 'bar', deadline: new Date() }).then(task => {
  // you can now access the newly created task via the variable task
})

It is also possible to define which attributes can be set via the create method. This can be especially very handy if you create database entries based on a form which can be filled by a user. Using that would for example allow you to restrict the User model to set only a username and an address but not an admin flag:

User.create({ username: 'barfooz', isAdmin: true }, { fields: [ 'username' ] }).then(user => {
  // let's assume the default of isAdmin is false:
  console.log(user.get({
    plain: true
  })) // => { username: 'barfooz', isAdmin: false }
})

Updating / Saving / Persisting an instance

Now lets change some values and save changes to the database... There are two ways to do that:

// way 1
task.title = 'a very different title now'
task.save().then(() => {})

// way 2
task.update({
  title: 'a very different title now'
}).then(() => {})

It's also possible to define which attributes should be saved when calling save, by passing an array of column names. This is useful when you set attributes based on a previously defined object. E.g. if you get the values of an object via a form of a web app. Furthermore this is used internally for update. This is how it looks like:

task.title = 'foooo'
task.description = 'baaaaaar'
task.save({fields: ['title']}).then(() => {
 // title will now be 'foooo' but description is the very same as before
})

// The equivalent call using update looks like this:
task.update({ title: 'foooo', description: 'baaaaaar'}, {fields: ['title']}).then(() => {
 // title will now be 'foooo' but description is the very same as before
})

When you call save without changing any attribute, this method will execute nothing;

Destroying / Deleting persistent instances

Once you created an object and got a reference to it, you can delete it from the database. The relevant method is destroy:

Task.create({ title: 'a task' }).then(task => {
  // now you see me...
  return task.destroy();
}).then(() => {
 // now i'm gone :)
})

If the paranoid options is true, the object will not be deleted, instead the deletedAt column will be set to the current timestamp. To force the deletion, you can pass force: true to the destroy call:

task.destroy({ force: true })

Working in bulk (creating, updating and destroying multiple rows at once)

In addition to updating a single instance, you can also create, update, and delete multiple instances at once. The functions you are looking for are called

• Model.bulkCreate
• Model.update
• Model.destroy

Since you are working with multiple models, the callbacks will not return DAO instances. BulkCreate will return an array of model instances/DAOs, they will however, unlike create, not have the resulting values of autoIncrement attributes.update and destroy will return the number of affected rows.

First lets look at bulkCreate

User.bulkCreate([
  { username: 'barfooz', isAdmin: true },
  { username: 'foo', isAdmin: true },
  { username: 'bar', isAdmin: false }
]).then(() => { // Notice: There are no arguments here, as of right now you'll have to...
  return User.findAll();
}).then(users => {
  console.log(users) // ... in order to get the array of user objects
})

To update several rows at once:

Task.bulkCreate([
  {subject: 'programming', status: 'executing'},
  {subject: 'reading', status: 'executing'},
  {subject: 'programming', status: 'finished'}
]).then(() => {
  return Task.update(
    { status: 'inactive' }, /* set attributes' value */
    { where: { subject: 'programming' }} /* where criteria */
  );
}).spread((affectedCount, affectedRows) => {
  // .update returns two values in an array, therefore we use .spread
  // Notice that affectedRows will only be defined in dialects which support returning: true

  // affectedCount will be 2
  return Task.findAll();
}).then(tasks => {
  console.log(tasks) // the 'programming' tasks will both have a status of 'inactive'
})

And delete them:

Task.bulkCreate([
  {subject: 'programming', status: 'executing'},
  {subject: 'reading', status: 'executing'},
  {subject: 'programming', status: 'finished'}
]).then(() => {
  return Task.destroy({
    where: {
      subject: 'programming'
    },
    truncate: true /* this will ignore where and truncate the table instead */
  });
}).then(affectedRows => {
  // affectedRows will be 2
  return Task.findAll();
}).then(tasks => {
  console.log(tasks) // no programming, just reading :(
})

If you are accepting values directly from the user, it might be beneficial to limit the columns that you want to actually insert.bulkCreate()accepts an options object as the second parameter. The object can have a fields parameter, (an array) to let it know which fields you want to build explicitly

User.bulkCreate([
  { username: 'foo' },
  { username: 'bar', admin: true}
], { fields: ['username'] }).then(() => {
  // nope bar, you can't be admin!
})

bulkCreate was originally made to be a mainstream/fast way of inserting records, however, sometimes you want the luxury of being able to insert multiple rows at once without sacrificing model validations even when you explicitly tell Sequelize which columns to sift through. You can do by adding a validate: true property to the options object.

const Tasks = sequelize.define('task', {
  name: {
    type: Sequelize.STRING,
    validate: {
      notNull: { args: true, msg: 'name cannot be null' }
    }
  },
  code: {
    type: Sequelize.STRING,
    validate: {
      len: [3, 10]
    }
  }
})

Tasks.bulkCreate([
  {name: 'foo', code: '123'},
  {code: '1234'},
  {name: 'bar', code: '1'}
], { validate: true }).catch(errors => {
  /* console.log(errors) would look like:
  [
    { record:
    ...
    name: 'SequelizeBulkRecordError',
    message: 'Validation error',
    errors:
      { name: 'SequelizeValidationError',
        message: 'Validation error',
        errors: [Object] } },
    { record:
      ...
      name: 'SequelizeBulkRecordError',
      message: 'Validation error',
      errors:
        { name: 'SequelizeValidationError',
        message: 'Validation error',
        errors: [Object] } }
  ]
  */
})

Values of an instance

If you log an instance you will notice, that there is a lot of additional stuff. In order to hide such stuff and reduce it to the very interesting information, you can use theget-attribute. Calling it with the option plain = true will only return the values of an instance.

Person.create({
  name: 'Rambow',
  firstname: 'John'
}).then(john => {
  console.log(john.get({
    plain: true
  }))
})

// result:

// { name: 'Rambow',
//   firstname: 'John',
//   id: 1,
//   createdAt: Tue, 01 May 2012 19:12:16 GMT,
//   updatedAt: Tue, 01 May 2012 19:12:16 GMT
// }

Hint:You can also transform an instance into JSON by using JSON.stringify(instance). This will basically return the very same as values.

Reloading instances

If you need to get your instance in sync, you can use the methodreload. It will fetch the current data from the database and overwrite the attributes of the model on which the method has been called on.

Person.findOne({ where: { name: 'john' } }).then(person => {
  person.name = 'jane'
  console.log(person.name) // 'jane'

  person.reload().then(() => {
    console.log(person.name) // 'john'
  })
})

Incrementing

In order to increment values of an instance without running into concurrency issues, you may use increment.

First of all you can define a field and the value you want to add to it.

User.findById(1).then(user => {
  return user.increment('my-integer-field', {by: 2})
}).then(user => {
  // Postgres will return the updated user by default (unless disabled by setting { returning: false })
  // In other dialects, you'll want to call user.reload() to get the updated instance...
})

Second, you can define multiple fields and the value you want to add to them.

User.findById(1).then(user => {
  return user.increment([ 'my-integer-field', 'my-very-other-field' ], {by: 2})
}).then(/* ... */)

Third, you can define an object containing fields and its increment values.

User.findById(1).then(user => {
  return user.increment({
    'my-integer-field':    2,
    'my-very-other-field': 3
  })
}).then(/* ... */)

Decrementing

In order to decrement values of an instance without running into concurrency issues, you may use decrement.

First of all you can define a field and the value you want to add to it.

User.findById(1).then(user => {
  return user.decrement('my-integer-field', {by: 2})
}).then(user => {
  // Postgres will return the updated user by default (unless disabled by setting { returning: false })
  // In other dialects, you'll want to call user.reload() to get the updated instance...
})

Second, you can define multiple fields and the value you want to add to them.

User.findById(1).then(user => {
  return user.decrement([ 'my-integer-field', 'my-very-other-field' ], {by: 2})
}).then(/* ... */)

Third, you can define an object containing fields and its decrement values.

User.findById(1).then(user => {
  return user.decrement({
    'my-integer-field':    2,
    'my-very-other-field': 3
  })
}).then(/* ... */)

Associations

This section describes the various association types in sequelize. When calling a method such as User.hasOne(Project), we say that the User model (the model that the function is being invoked on) is the source and the Project model (the model being passed as an argument) is the target.

One-To-One associations

One-To-One associations are associations between exactly two models connected by a single foreign key.

BelongsTo

BelongsTo associations are associations where the foreign key for the one-to-one relation exists on the source model.

A simple example would be a Player being part of a Team with the foreign key on the player.

const Player = this.sequelize.define('player', {/* attributes */});
const Team  = this.sequelize.define('team', {/* attributes */});

Player.belongsTo(Team); // Will add a teamId attribute to Player to hold the primary key value for Team

Foreign keys

By default the foreign key for a belongsTo relation will be generated from the target model name and the target primary key name.

The default casing is camelCase however if the source model is configured with underscored: true the foreignKey will be snake_case.

const User = this.sequelize.define('user', {/* attributes */})
const Company  = this.sequelize.define('company', {/* attributes */});

User.belongsTo(Company); // Will add companyId to user

const User = this.sequelize.define('user', {/* attributes */}, {underscored: true})
const Company  = this.sequelize.define('company', {
  uuid: {
    type: Sequelize.UUID,
    primaryKey: true
  }
});

User.belongsTo(Company); // Will add company_uuid to user

In cases where as has been defined it will be used in place of the target model name.

const User = this.sequelize.define('user', {/* attributes */})
const UserRole  = this.sequelize.define('userRole', {/* attributes */});

User.belongsTo(UserRole, {as: 'role'}); // Adds roleId to user rather than userRoleId

In all cases the default foreign key can be overwritten with the foreignKey option. When the foreign key option is used, Sequelize will use it as-is:

const User = this.sequelize.define('user', {/* attributes */})
const Company  = this.sequelize.define('company', {/* attributes */});

User.belongsTo(Company, {foreignKey: 'fk_company'}); // Adds fk_company to User

Target keys

The target key is the column on the target model that the foreign key column on the source model points to. By default the target key for a belongsTo relation will be the target model's primary key. To define a custom column, use the targetKey option.

const User = this.sequelize.define('user', {/* attributes */})
const Company  = this.sequelize.define('company', {/* attributes */});

User.belongsTo(Company, {foreignKey: 'fk_companyname', targetKey: 'name'}); // Adds fk_companyname to User

HasOne

HasOne associations are associations where the foreign key for the one-to-one relation exists on the target model.

const User = sequelize.define('user', {/* ... */})
const Project = sequelize.define('project', {/* ... */})

// One-way associations
Project.hasOne(User)

/*
  In this example hasOne will add an attribute projectId to the User model!
  Furthermore, Project.prototype will gain the methods getUser and setUser according
  to the first parameter passed to define. If you have underscore style
  enabled, the added attribute will be project_id instead of projectId.

  The foreign key will be placed on the users table.

  You can also define the foreign key, e.g. if you already have an existing
  database and want to work on it:
*/

Project.hasOne(User, { foreignKey: 'initiator_id' })

/*
  Because Sequelize will use the model's name (first parameter of define) for
  the accessor methods, it is also possible to pass a special option to hasOne:
*/

Project.hasOne(User, { as: 'Initiator' })
// Now you will get Project.getInitiator and Project.setInitiator

// Or let's define some self references
const Person = sequelize.define('person', { /* ... */})

Person.hasOne(Person, {as: 'Father'})
// this will add the attribute FatherId to Person

// also possible:
Person.hasOne(Person, {as: 'Father', foreignKey: 'DadId'})
// this will add the attribute DadId to Person

// In both cases you will be able to do:
Person.setFather
Person.getFather

// If you need to join a table twice you can double join the same table
Team.hasOne(Game, {as: 'HomeTeam', foreignKey : 'homeTeamId'});
Team.hasOne(Game, {as: 'AwayTeam', foreignKey : 'awayTeamId'});

Game.belongsTo(Team);

Even though it is called a HasOne association, for most 1:1 relations you usually want the BelongsTo association since BelongsTo will add the foreignKey on the source where hasOne will add on the target.

Difference between HasOne and BelongsTo

In Sequelize 1:1 relationship can be set using HasOne and BelongsTo. They are suitable for different scenarios. Lets study this difference using an example.

Suppose we have two tables to link Player and Team. Lets define their models.

const Player = this.sequelize.define('player', {/* attributes */})
const Team  = this.sequelize.define('team', {/* attributes */});

When we link two models in Sequelize we can refer them as pairs of source and target models. Like this

Having Player as the source and Team as the target

Player.belongsTo(Team);
//Or
Player.hasOne(Team);

Having Team as the source and Player as the target

Team.belongsTo(Player);
//Or
Team.hasOne(Player);

HasOne and BelongsTo insert the association key in different models from each other. HasOne inserts the association key in target model whereas BelongsTo inserts the association key in the source model.

Here is an example demonstrating use cases of BelongsTo and HasOne.

const Player = this.sequelize.define('player', {/* attributes */})
const Coach  = this.sequelize.define('coach', {/* attributes */})
const Team  = this.sequelize.define('team', {/* attributes */});

Suppose our Player model has information about its team as teamId column. Information about each Team's Coach is stored in the Team model as coachId column. These both scenarios requires different kind of 1:1 relation because foreign key relation is present on different models each time.

When information about association is present in source model we can use belongsTo. In this case Player is suitable for belongsTo because it has teamId column.

Player.belongsTo(Team)  // `teamId` will be added on Player / Source model

When information about association is present in target model we can use hasOne. In this case Coach is suitable for hasOne because Team model store information about its Coach as coachId field.

Coach.hasOne(Team)  // `coachId` will be added on Team / Target model

One-To-Many associations (hasMany)

One-To-Many associations are connecting one source with multiple targets. The targets however are again connected to exactly one specific source.

const User = sequelize.define('user', {/* ... */})
const Project = sequelize.define('project', {/* ... */})

// OK. Now things get more complicated (not really visible to the user :)).
// First let's define a hasMany association
Project.hasMany(User, {as: 'Workers'})

This will add the attribute projectId or project_id to User. Instances of Project will get the accessors getWorkers and setWorkers.

Sometimes you may need to associate records on different columns, you may use sourceKey option:

const City = sequelize.define('city', { countryCode: Sequelize.STRING });
const Country = sequelize.define('country', { isoCode: Sequelize.STRING });

// Here we can connect countries and cities base on country code
Country.hasMany(City, {foreignKey: 'countryCode', sourceKey: 'isoCode'});
City.belongsTo(Country, {foreignKey: 'countryCode', targetKey: 'isoCode'});

So far we dealt with a one-way association. But we want more! Let's define it the other way around by creating a many to many association in the next section.

Belongs-To-Many associations

Belongs-To-Many associations are used to connect sources with multiple targets. Furthermore the targets can also have connections to multiple sources.

Project.belongsToMany(User, {through: 'UserProject'});
User.belongsToMany(Project, {through: 'UserProject'});

This will create a new model called UserProject with the equivalent foreign keys projectId and userId. Whether the attributes are camelcase or not depends on the two models joined by the table (in this case User and Project).

Defining through is required. Sequelize would previously attempt to autogenerate names but that would not always lead to the most logical setups.

This will add methods getUsers, setUsers, addUser,addUsers to Project, and getProjects, setProjects, addProject, and addProjects to User.

Sometimes you may want to rename your models when using them in associations. Let's define users as workers and projects as tasks by using the alias (as) option. We will also manually define the foreign keys to use:

User.belongsToMany(Project, { as: 'Tasks', through: 'worker_tasks', foreignKey: 'userId' })
Project.belongsToMany(User, { as: 'Workers', through: 'worker_tasks', foreignKey: 'projectId' })

foreignKey will allow you to set source model key in the through relation. otherKey will allow you to set target model key in the through relation.

User.belongsToMany(Project, { as: 'Tasks', through: 'worker_tasks', foreignKey: 'userId', otherKey: 'projectId'})

Of course you can also define self references with belongsToMany:

Person.belongsToMany(Person, { as: 'Children', through: 'PersonChildren' })
// This will create the table PersonChildren which stores the ids of the objects.

If you want additional attributes in your join table, you can define a model for the join table in sequelize, before you define the association, and then tell sequelize that it should use that model for joining, instead of creating a new one:

const User = sequelize.define('user', {})
const Project = sequelize.define('project', {})
const UserProjects = sequelize.define('userProjects', {
    status: DataTypes.STRING
})

User.belongsToMany(Project, { through: UserProjects })
Project.belongsToMany(User, { through: UserProjects })

To add a new project to a user and set its status, you pass extra options.through to the setter, which contains the attributes for the join table

user.addProject(project, { through: { status: 'started' }})

By default the code above will add projectId and userId to the UserProjects table, and remove any previously defined primary key attribute - the table will be uniquely identified by the combination of the keys of the two tables, and there is no reason to have other PK columns. To enforce a primary key on the UserProjects model you can add it manually.

const UserProjects = sequelize.define('userProjects', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  status: DataTypes.STRING
})

With Belongs-To-Many you can query based on through relation and select specific attributes. For example using findAll with through

User.findAll({
  include: [{
    model: Project,
    through: {
      attributes: ['createdAt', 'startedAt', 'finishedAt'],
      where: {completed: true}
    }
  }]
});

Belongs-To-Many creates a unique key when primary key is not present on through model. This unique key name can be overridden using uniqueKey option.

Project.belongsToMany(User, { through: UserProjects, uniqueKey: 'my_custom_unique' })

Scopes

This section concerns association scopes. For a definition of association scopes vs. scopes on associated models, see Scopes.

Association scopes allow you to place a scope (a set of default attributes for get and create) on the association. Scopes can be placed both on the associated model (the target of the association), and on the through table for n:m relations.

1:m

Assume we have tables Comment, Post, and Image. A comment can be associated to either an image or a post via commentable_id and commentable - we say that Post and Image are Commentable

const Comment = this.sequelize.define('comment', {
  title: Sequelize.STRING,
  commentable: Sequelize.STRING,
  commentable_id: Sequelize.INTEGER
});

Comment.prototype.getItem = function(options) {
  return this['get' + this.get('commentable').substr(0, 1).toUpperCase() + this.get('commentable').substr(1)](options);
};

Post.hasMany(this.Comment, {
  foreignKey: 'commentable_id',
  constraints: false,
  scope: {
    commentable: 'post'
  }
});
Comment.belongsTo(this.Post, {
  foreignKey: 'commentable_id',
  constraints: false,
  as: 'post'
});

Image.hasMany(this.Comment, {
  foreignKey: 'commentable_id',
  constraints: false,
  scope: {
    commentable: 'image'
  }
});
Comment.belongsTo(this.Image, {
  foreignKey: 'commentable_id',
  constraints: false,
  as: 'image'
});

constraints: false, disables references constraints - since the commentable_id column references several tables, we cannot add a REFERENCES constraint to it. Note that the Image -> Comment and Post -> Comment relations define a scope, commentable: 'image' and commentable: 'post' respectively. This scope is automatically applied when using the association functions:

image.getComments()
SELECT * FROM comments WHERE commentable_id = 42 AND commentable = 'image';

image.createComment({
  title: 'Awesome!'
})
INSERT INTO comments (title, commentable_id, commentable) VALUES ('Awesome!', 42, 'image');

image.addComment(comment);
UPDATE comments SET commentable_id = 42, commentable = 'image'

The getItem utility function on Comment completes the picture - it simply converts the commentable string into a call to either getImage or getPost, providing an abstraction over whether a comment belongs to a post or an image. You can pass a normal options object as a parameter to getItem(options) to specify any where conditions or includes.

n:m

Continuing with the idea of a polymorphic model, consider a tag table - an item can have multiple tags, and a tag can be related to several items.

For brevity, the example only shows a Post model, but in reality Tag would be related to several other models.

const ItemTag = sequelize.define('item_tag', {
  id : {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  tag_id: {
    type: DataTypes.INTEGER,
    unique: 'item_tag_taggable'
  },
  taggable: {
    type: DataTypes.STRING,
    unique: 'item_tag_taggable'
  },
  taggable_id: {
    type: DataTypes.INTEGER,
    unique: 'item_tag_taggable',
    references: null
  }
});
const Tag = sequelize.define('tag', {
  name: DataTypes.STRING
});

Post.belongsToMany(Tag, {
  through: {
    model: ItemTag,
    unique: false,
    scope: {
      taggable: 'post'
    }
  },
  foreignKey: 'taggable_id',
  constraints: false
});
Tag.belongsToMany(Post, {
  through: {
    model: ItemTag,
    unique: false
  },
  foreignKey: 'tag_id',
  constraints: false
});

Notice that the scoped column (taggable) is now on the through model (ItemTag).

We could also define a more restrictive association, for example, to get all pending tags for a post by applying a scope of both the through model (ItemTag) and the target model (Tag):

Post.hasMany(Tag, {
  through: {
    model: ItemTag,
    unique: false,
    scope: {
      taggable: 'post'
    }
  },
  scope: {
    status: 'pending'
  },
  as: 'pendingTags',
  foreignKey: 'taggable_id',
  constraints: false
});

Post.getPendingTags();

SELECT `tag`.*  INNER JOIN `item_tags` AS `item_tag`
ON `tag`.`id` = `item_tag`.`tagId`
  AND `item_tag`.`taggable_id` = 42
  AND `item_tag`.`taggable` = 'post'
WHERE (`tag`.`status` = 'pending');

constraints: false disables references constraints on the taggable_id column. Because the column is polymorphic, we cannot say that it REFERENCES a specific table.

Naming strategy

By default sequelize will use the model name (the name passed to sequelize.define) to figure out the name of the model when used in associations. For example, a model named user will add the functions get/set/add User to instances of the associated model, and a property named .user in eager loading, while a model named User will add the same functions, but a property named .User (notice the upper case U) in eager loading.

As we've already seen, you can alias models in associations using as. In single associations (has one and belongs to), the alias should be singular, while for many associations (has many) it should be plural. Sequelize then uses the inflection library to convert the alias to its singular form. However, this might not always work for irregular or non-english words. In this case, you can provide both the plural and the singular form of the alias:

User.belongsToMany(Project, { as: { singular: 'task', plural: 'tasks' }})
// Notice that inflection has no problem singularizing tasks, this is just for illustrative purposes.

If you know that a model will always use the same alias in associations, you can provide it when creating the model

const Project = sequelize.define('project', attributes, {
  name: {
    singular: 'task',
    plural: 'tasks',
  }
})

User.belongsToMany(Project);

This will add the functions add/set/get Tasks to user instances.

Remember, that using as to change the name of the association will also change the name of the foreign key. When using as, it is safest to also specify the foreign key.

Invoice.belongsTo(Subscription)
Subscription.hasMany(Invoice)

Without as, this adds subscriptionId as expected. However, if you were to say Invoice.belongsTo(Subscription, { as: 'TheSubscription' }), you will have both subscriptionId and theSubscriptionId, because sequelize is not smart enough to figure that the calls are two sides of the same relation. 'foreignKey' fixes this problem;

Invoice.belongsTo(Subscription, , { as: 'TheSubscription', foreignKey: 'subscription_id' })
Subscription.hasMany(Invoice, { foreignKey: 'subscription_id' )

Associating objects

Because Sequelize is doing a lot of magic, you have to call Sequelize.sync after setting the associations! Doing so will allow you the following:

Project.hasMany(Task)
Task.belongsTo(Project)

Project.create()...
Task.create()...
Task.create()...

// save them... and then:
project.setTasks([task1, task2]).then(() => {
  // saved!
})

// ok, now they are saved... how do I get them later on?
project.getTasks().then(associatedTasks => {
  // associatedTasks is an array of tasks
})

// You can also pass filters to the getter method.
// They are equal to the options you can pass to a usual finder method.
project.getTasks({ where: 'id > 10' }).then(tasks => {
  // tasks with an id greater than 10 :)
})

// You can also only retrieve certain fields of a associated object.
project.getTasks({attributes: ['title']}).then(tasks => {
  // retrieve tasks with the attributes "title" and "id"
})

To remove created associations you can just call the set method without a specific id:

// remove the association with task1
project.setTasks([task2]).then(associatedTasks => {
  // you will get task2 only
})

// remove 'em all
project.setTasks([]).then(associatedTasks => {
  // you will get an empty array
})

// or remove 'em more directly
project.removeTask(task1).then(() => {
  // it's gone
})

// and add 'em again
project.addTask(task1).then(function() {
  // it's back again
})

You can of course also do it vice versa:

// project is associated with task1 and task2
task2.setProject(null).then(function() {
  // and it's gone
})

For hasOne/belongsTo it's basically the same:

Task.hasOne(User, {as: "Author"})
Task.setAuthor(anAuthor)

Adding associations to a relation with a custom join table can be done in two ways (continuing with the associations defined in the previous chapter):

// Either by adding a property with the name of the join table model to the object, before creating the association
project.UserProjects = {
  status: 'active'
}
u.addProject(project)

// Or by providing a second options.through argument when adding the association, containing the data that should go in the join table
u.addProject(project, { through: { status: 'active' }})


// When associating multiple objects, you can combine the two options above. In this case the second argument
// will be treated as a defaults object, that will be used if no data is provided
project1.UserProjects = {
    status: 'inactive'
}

u.setProjects([project1, project2], { through: { status: 'active' }})
// The code above will record inactive for project one, and active for project two in the join table

When getting data on an association that has a custom join table, the data from the join table will be returned as a DAO instance:

u.getProjects().then(projects => {
  const project = projects[0]

  if (project.UserProjects.status === 'active') {
    // .. do magic

    // since this is a real DAO instance, you can save it directly after you are done doing magic
    return project.UserProjects.save()
  }
})

If you only need some of the attributes from the join table, you can provide an array with the attributes you want:

// This will select only name from the Projects table, and only status from the UserProjects table
user.getProjects({ attributes: ['name'], joinTableAttributes: ['status']})

Check associations

You can also check if an object is already associated with another one (N:M only). Here is how you'd do it:

// check if an object is one of associated ones:
Project.create({ /* */ }).then(project => {
  return User.create({ /* */ }).then(user => {
    return project.hasUser(user).then(result => {
      // result would be false
      return project.addUser(user).then(() => {
        return project.hasUser(user).then(result => {
          // result would be true
        })
      })
    })
  })
})

// check if all associated objects are as expected:
// let's assume we have already a project and two users
project.setUsers([user1, user2]).then(() => {
  return project.hasUsers([user1]);
}).then(result => {
  // result would be true
  return project.hasUsers([user1, user2]);
}).then(result => {
  // result would be true
})

Foreign Keys

When you create associations between your models in sequelize, foreign key references with constraints will automatically be created. The setup below:

const Task = this.sequelize.define('task', { title: Sequelize.STRING })
const User = this.sequelize.define('user', { username: Sequelize.STRING })

User.hasMany(Task)
Task.belongsTo(User)

Will generate the following SQL:

CREATE TABLE IF NOT EXISTS `User` (
  `id` INTEGER PRIMARY KEY,
  `username` VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS `Task` (
  `id` INTEGER PRIMARY KEY,
  `title` VARCHAR(255),
  `user_id` INTEGER REFERENCES `User` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
);

The relation between task and user injects the user_id foreign key on tasks, and marks it as a reference to the User table. By default user_id will be set to NULL if the referenced user is deleted, and updated if the id of the user id updated. These options can be overridden by passing onUpdate and onDelete options to the association calls. The validation options are RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL.

For 1:1 and 1:m associations the default option is SET NULL for deletion, and CASCADE for updates. For n:m, the default for both is CASCADE. This means, that if you delete or update a row from one side of an n:m association, all the rows in the join table referencing that row will also be deleted or updated.

Adding constraints between tables means that tables must be created in the database in a certain order, when using sequelize.sync. If Task has a reference to User, the User table must be created before the Task table can be created. This can sometimes lead to circular references, where sequelize cannot find an order in which to sync. Imagine a scenario of documents and versions. A document can have multiple versions, and for convenience, a document has a reference to its current version.

const Document = this.sequelize.define('document', {
  author: Sequelize.STRING
})
const Version = this.sequelize.define('version', {
  timestamp: Sequelize.DATE
})

Document.hasMany(Version) // This adds document_id to version
Document.belongsTo(Version, { as: 'Current', foreignKey: 'current_version_id'}) // This adds current_version_id to document

However, the code above will result in the following error: Cyclic dependency found. 'Document' is dependent of itself. Dependency Chain: Document -> Version => Document. In order to alleviate that, we can pass constraints: false to one of the associations:

Document.hasMany(Version)
Document.belongsTo(Version, { as: 'Current', foreignKey: 'current_version_id', constraints: false})

Which will allow us to sync the tables correctly:

CREATE TABLE IF NOT EXISTS `Document` (
  `id` INTEGER PRIMARY KEY,
  `author` VARCHAR(255),
  `current_version_id` INTEGER
);
CREATE TABLE IF NOT EXISTS `Version` (
  `id` INTEGER PRIMARY KEY,
  `timestamp` DATETIME,
  `document_id` INTEGER REFERENCES `Document` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
);

Enforcing a foreign key reference without constraints

Sometimes you may want to reference another table, without adding any constraints, or associations. In that case you can manually add the reference attributes to your schema definition, and mark the relations between them.

// Series has a trainer_id=Trainer.id foreign reference key after we call Trainer.hasMany(series)
const Series = sequelize.define('series', {
  title:        DataTypes.STRING,
  sub_title:    DataTypes.STRING,
  description:  DataTypes.TEXT,

  // Set FK relationship (hasMany) with `Trainer`
  trainer_id: {
    type: DataTypes.INTEGER,
    references: {
      model: "trainer",
      key: "id"
    }
  }
})

const Trainer = sequelize.define('trainer', {
  first_name: DataTypes.STRING,
  last_name:  DataTypes.STRING
});

// Video has a series_id=Series.id foreign reference key after we call Series.hasOne(Video)...
const Video = sequelize.define('video', {
  title:        DataTypes.STRING,
  sequence:     DataTypes.INTEGER,
  description:  DataTypes.TEXT,

  // set relationship (hasOne) with `Series`
  series_id: {
    type: DataTypes.INTEGER,
    references: {
      model: Series, // Can be both a string representing the table name, or a reference to the model
      key:   "id"
    }
  }
});

Series.hasOne(Video);
Trainer.hasMany(Series);

Creating with associations

An instance can be created with nested association in one step, provided all elements are new.

Creating elements of a "BelongsTo", "Has Many" or "HasOne" association

Consider the following models:

const Product = this.sequelize.define('product', {
  title: Sequelize.STRING
});
const User = this.sequelize.define('user', {
  first_name: Sequelize.STRING,
  last_name: Sequelize.STRING
});
const Address = this.sequelize.define('address', {
  type: Sequelize.STRING,
  line_1: Sequelize.STRING,
  line_2: Sequelize.STRING,
  city: Sequelize.STRING,
  state: Sequelize.STRING,
  zip: Sequelize.STRING,
});

Product.User = Product.belongsTo(User);
User.Addresses = User.hasMany(Address);
// Also works for `hasOne`

A new Product, User, and one or more Address can be created in one step in the following way:

return Product.create({
  title: 'Chair',
  user: {
    first_name: 'Mick',
    last_name: 'Broadstone',
    addresses: [{
      type: 'home',
      line_1: '100 Main St.',
      city: 'Austin',
      state: 'TX',
      zip: '78704'
    }]
  }
}, {
  include: [{
    association: Product.User,
    include: [ User.Addresses ]
  }]
});

Here, our user model is called user, with a lowercase u - This means that the property in the object should also be user. If the name given to sequelize.define was User, the key in the object should also be User. Likewise for addresses, except it's pluralized being a hasMany association.

Creating elements of a "BelongsTo" association with an alias

The previous example can be extended to support an association alias.

const Creator = Product.belongsTo(User, {as: 'creator'});

return Product.create({
  title: 'Chair',
  creator: {
    first_name: 'Matt',
    last_name: 'Hansen'
  }
}, {
  include: [ Creator ]
});

Creating elements of a "HasMany" or "BelongsToMany" association

Let's introduce the ability to associate a product with many tags. Setting up the models could look like:

const Tag = this.sequelize.define('tag', {
  name: Sequelize.STRING
});

Product.hasMany(Tag);
// Also works for `belongsToMany`.

Now we can create a product with multiple tags in the following way:

Product.create({
  id: 1,
  title: 'Chair',
  tags: [
    { name: 'Alpha'},
    { name: 'Beta'}
  ]
}, {
  include: [ Tag ]
})

And, we can modify this example to support an alias as well:

const Categories = Product.hasMany(Tag, {as: 'categories'});

Product.create({
  id: 1,
  title: 'Chair',
  categories: [
    {id: 1, name: 'Alpha'},
    {id: 2, name: 'Beta'}
  ]
}, {
  include: [{
    model: Categories,
    as: 'categories'
  }]
})

Transactions

Sequelize supports two ways of using transactions:

• One which will automatically commit or rollback the transaction based on the result of a promise chain and, (if enabled) pass the transaction to all calls within the callback
• And one which leaves committing, rolling back and passing the transaction to the user.

The key difference is that the managed transaction uses a callback that expects a promise to be returned to it while the unmanaged transaction returns a promise.

Managed transaction (auto-callback)

Managed transactions handle committing or rolling back the transaction automagically. You start a managed transaction by passing a callback to sequelize.transaction.

Notice how the callback passed to transaction returns a promise chain, and does not explicitly call t.commit() nor t.rollback(). If all promises in the returned chain are resolved successfully the transaction is committed. If one or several of the promises are rejected, the transaction is rolled back.

return sequelize.transaction(function (t) {

  // chain all your queries here. make sure you return them.
  return User.create({
    firstName: 'Abraham',
    lastName: 'Lincoln'
  }, {transaction: t}).then(function (user) {
    return user.setShooter({
      firstName: 'John',
      lastName: 'Boothe'
    }, {transaction: t});
  });

}).then(function (result) {
  // Transaction has been committed
  // result is whatever the result of the promise chain returned to the transaction callback
}).catch(function (err) {
  // Transaction has been rolled back
  // err is whatever rejected the promise chain returned to the transaction callback
});

Throw errors to rollback

When using the managed transaction you should never commit or rollback the transaction manually. If all queries are successful, but you still want to rollback the transaction (for example because of a validation failure) you should throw an error to break and reject the chain:

return sequelize.transaction(function (t) {
  return User.create({
    firstName: 'Abraham',
    lastName: 'Lincoln'
  }, {transaction: t}).then(function (user) {
    // Woops, the query was successful but we still want to roll back!
    throw new Error();
  });
});

Automatically pass transactions to all queries

In the examples above, the transaction is still manually passed, by passing { transaction: t } as the second argument. To automatically pass the transaction to all queries you must install the continuation local storage (CLS) module and instantiate a namespace in your own code:

const cls = require('continuation-local-storage'),
    namespace = cls.createNamespace('my-very-own-namespace');

To enable CLS you must tell sequelize which namespace to use by using a static method of the sequelize constructor:

const Sequelize = require('sequelize');
Sequelize.useCLS(namespace);

new Sequelize(....);

Notice, that the useCLS() method is on the constructor, not on an instance of sequelize. This means that all instances will share the same namespace, and that CLS is all-or-nothing - you cannot enable it only for some instances.

CLS works like a thread-local storage for callbacks. What this means in practice is that different callback chains can access local variables by using the CLS namespace. When CLS is enabled sequelize will set the transaction property on the namespace when a new transaction is created. Since variables set within a callback chain are private to that chain several concurrent transactions can exist at the same time:

sequelize.transaction(function (t1) {
  namespace.get('transaction') === t1; // true
});

sequelize.transaction(function (t2) {
  namespace.get('transaction') === t2; // true
});

In most case you won't need to access namespace.get('transaction') directly, since all queries will automatically look for a transaction on the namespace:

sequelize.transaction(function (t1) {
  // With CLS enabled, the user will be created inside the transaction
  return User.create({ name: 'Alice' });
});

After you've used Sequelize.useCLS() all promises returned from sequelize will be patched to maintain CLS context. CLS is a complicated subject - more details in the docs for cls-bluebird, the patch used to make bluebird promises work with CLS.

Note: _CLS only supports async/await, at the moment, when using cls-hooked package. Although, cls-hooked relies on experimental API async_hooks_

Concurrent/Partial transactions

You can have concurrent transactions within a sequence of queries or have some of them excluded from any transactions. Use the {transaction: } option to control which transaction a query belong to:

Warning: SQLite does not support more than one transaction at the same time.

Without CLS enabled

sequelize.transaction(function (t1) {
  return sequelize.transaction(function (t2) {
    // With CLS enable, queries here will by default use t2
    // Pass in the `transaction` option to define/alter the transaction they belong to.
    return Promise.all([
        User.create({ name: 'Bob' }, { transaction: null }),
        User.create({ name: 'Mallory' }, { transaction: t1 }),
        User.create({ name: 'John' }) // this would default to t2
    ]);
  });
});

Isolation levels

The possible isolations levels to use when starting a transaction:

Sequelize.Transaction.ISOLATION_LEVELS.READ_UNCOMMITTED // "READ UNCOMMITTED"
Sequelize.Transaction.ISOLATION_LEVELS.READ_COMMITTED // "READ COMMITTED"
Sequelize.Transaction.ISOLATION_LEVELS.REPEATABLE_READ  // "REPEATABLE READ"
Sequelize.Transaction.ISOLATION_LEVELS.SERIALIZABLE // "SERIALIZABLE"

By default, sequelize uses the isolation level of the database. If you want to use a different isolation level, pass in the desired level as the first argument:

return sequelize.transaction({
  isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.SERIALIZABLE
  }, function (t) {

  // your transactions

  });

Note: The SET ISOLATION LEVEL queries are not logged in case of MSSQL as the specified isolationLevel is passed directly to tedious

Unmanaged transaction (then-callback)

Unmanaged transactions force you to manually rollback or commit the transaction. If you don't do that, the transaction will hang until it times out. To start an unmanaged transaction, call sequelize.transaction() without a callback (you can still pass an options object) and call then on the returned promise. Notice that commit() and rollback() returns a promise.

return sequelize.transaction().then(function (t) {
  return User.create({
    firstName: 'Bart',
    lastName: 'Simpson'
  }, {transaction: t}).then(function (user) {
    return user.addSibling({
      firstName: 'Lisa',
      lastName: 'Simpson'
    }, {transaction: t});
  }).then(function () {
    return t.commit();
  }).catch(function (err) {
    return t.rollback();
  });
});

Options

The transaction method can be called with an options object as the first argument, that allows the configuration of the transaction.

return sequelize.transaction({ /* options */ });

The following options (with their default values) are available:

{
  autocommit: true,
  isolationLevel: 'REPEATABLE_READ',
  deferrable: 'NOT DEFERRABLE' // implicit default of postgres
}

The isolationLevel can either be set globally when initializing the Sequelize instance or locally for every transaction:

// globally
new Sequelize('db', 'user', 'pw', {
  isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.SERIALIZABLE
});

// locally
sequelize.transaction({
  isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.SERIALIZABLE
});

The deferrable option triggers an additional query after the transaction start that optionally set the constraint checks to be deferred or immediate. Please note that this is only supported in PostgreSQL.

sequelize.transaction({
  // to defer all constraints:
  deferrable: Sequelize.Deferrable.SET_DEFERRED,

  // to defer a specific constraint:
  deferrable: Sequelize.Deferrable.SET_DEFERRED(['some_constraint']),

  // to not defer constraints:
  deferrable: Sequelize.Deferrable.SET_IMMEDIATE
})

Usage with other sequelize methods

The transaction option goes with most other options, which are usually the first argument of a method. For methods that take values, like .create, .update(), .updateAttributes() etc. transaction should be passed to the option in the second argument. If unsure, refer to the API documentation for the method you are using to be sure of the signature.

After commit hook

A transaction object allows tracking if and when it is committed.

An afterCommit hook can be added to both managed and unmanaged transaction objects:

sequelize.transaction(t => {
  t.afterCommit((transaction) => {
    // Your logic
  });
});

sequelize.transaction().then(t => {
  t.afterCommit((transaction) => {
    // Your logic
  });

  return t.commit();
})

The function passed to afterCommit can optionally return a promise that will resolve before the promise chain that created the transaction resolves

afterCommit hooks are not raised if a transaction is rolled back

afterCommit hooks do not modify the return value of the transaction, unlike standard hooks

You can use the afterCommit hook in conjunction with model hooks to know when a instance is saved and available outside of a transaction

```js model.afterSave((instance, options) => { if (options.transaction) { // Save done within a transaction, wait until transaction is committed to // notify listeners the instance has been saved options.transaction.afterCommit(() => / Notify /) return; } // Save done outside a transaction, safe for callers to fetch the updated model // Notify

Scopes

Scoping allows you to define commonly used queries that you can easily use later. Scopes can include all the same attributes as regular finders, where, include, limit etc.

Definition

Scopes are defined in the model definition and can be finder objects, or functions returning finder objects - except for the default scope, which can only be an object:

const Project = sequelize.define('project', {
  // Attributes
}, {
  defaultScope: {
    where: {
      active: true
    }
  },
  scopes: {
    deleted: {
      where: {
        deleted: true
      }
    },
    activeUsers: {
      include: [
        { model: User, where: { active: true }}
      ]
    },
    random: function () {
      return {
        where: {
          someNumber: Math.random()
        }
      }
    },
    accessLevel: function (value) {
      return {
        where: {
          accessLevel: {
            [Op.gte]: value
          }
        }
      }
    }
  }
});

You can also add scopes after a model has been defined by calling addScope. This is especially useful for scopes with includes, where the model in the include might not be defined at the time the other model is being defined.

The default scope is always applied. This means, that with the model definition above, Project.findAll() will create the following query:

SELECT * FROM projects WHERE active = true

The default scope can be removed by calling .unscoped(), .scope(null), or by invoking another scope:

Project.scope('deleted').findAll(); // Removes the default scope

SELECT * FROM projects WHERE deleted = true

It is also possible to include scoped models in a scope definition. This allows you to avoid duplicating include, attributes or where definitions. Using the above example, and invoking the active scope on the included User model (rather than specifying the condition directly in that include object):

activeUsers: {
  include: [
    { model: User.scope('active')}
  ]
}

Usage

Scopes are applied by calling .scope on the model definition, passing the name of one or more scopes. .scope returns a fully functional model instance with all the regular methods: .findAll, .update, .count, .destroy etc. You can save this model instance and reuse it later:

const DeletedProjects = Project.scope('deleted');

DeletedProjects.findAll();
// some time passes

// let's look for deleted projects again!
DeletedProjects.findAll();

Scopes apply to .find, .findAll, .count, .update, .increment and .destroy.

Scopes which are functions can be invoked in two ways. If the scope does not take any arguments it can be invoked as normally. If the scope takes arguments, pass an object:

Project.scope('random', { method: ['accessLevel', 19]}).findAll();

SELECT * FROM projects WHERE someNumber = 42 AND accessLevel >= 19

Merging

Several scopes can be applied simultaneously by passing an array of scopes to .scope, or by passing the scopes as consecutive arguments.

// These two are equivalent
Project.scope('deleted', 'activeUsers').findAll();
Project.scope(['deleted', 'activeUsers']).findAll();

SELECT * FROM projects
INNER JOIN users ON projects.userId = users.id
AND users.active = true

If you want to apply another scope alongside the default scope, pass the key defaultScope to .scope:

Project.scope('defaultScope', 'deleted').findAll();

SELECT * FROM projects WHERE active = true AND deleted = true

When invoking several scopes, keys from subsequent scopes will overwrite previous ones (similar to _.assign). Consider two scopes:

{
  scope1: {
    where: {
      firstName: 'bob',
      age: {
        [Op.gt]: 20
      }
    },
    limit: 2
  },
  scope2: {
    where: {
      age: {
        [Op.gt]: 30
      }
    },
    limit: 10
  }
}

Calling .scope('scope1', 'scope2') will yield the following query

WHERE firstName = 'bob' AND age > 30 LIMIT 10

Note how limit and age are overwritten by scope2, while firstName is preserved. limit, offset, order, paranoid, lock and raw are overwritten, while where and include are shallowly merged. This means that identical keys in the where objects, and subsequent includes of the same model will both overwrite each other.

The same merge logic applies when passing a find object directly to findAll on a scoped model:

Project.scope('deleted').findAll({
  where: {
    firstName: 'john'
  }
})

WHERE deleted = true AND firstName = 'john'

Here the deleted scope is merged with the finder. If we were to pass where: { firstName: 'john', deleted: false } to the finder, the deleted scope would be overwritten.

Associations

Sequelize has two different but related scope concepts in relation to associations. The difference is subtle but important:

• Association scopes Allow you to specify default attributes when getting and setting associations - useful when implementing polymorphic associations. This scope is only invoked on the association between the two models, when using the get, set, add and create associated model functions
• Scopes on associated models Allows you to apply default and other scopes when fetching associations, and allows you to pass a scoped model when creating associations. These scopes both apply to regular finds on the model and to find through the association.

As an example, consider the models Post and Comment. Comment is associated to several other models (Image, Video etc.) and the association between Comment and other models is polymorphic, which means that Comment stores a commentable column, in addition to the foreign key commentable_id.

The polymorphic association can be implemented with an association scope :

this.Post.hasMany(this.Comment, {
  foreignKey: 'commentable_id',
  scope: {
    commentable: 'post'
  }
});

When calling post.getComments(), this will automatically add WHERE commentable = 'post'. Similarly, when adding new comments to a post, commentable will automagically be set to 'post'. The association scope is meant to live in the background without the programmer having to worry about it - it cannot be disabled. For a more complete polymorphic example, see Association scopes

Consider then, that Post has a default scope which only shows active posts: where: { active: true }. This scope lives on the associated model (Post), and not on the association like the commentable scope did. Just like the default scope is applied when calling Post.findAll(), it is also applied when calling User.getPosts() - this will only return the active posts for that user.

To disable the default scope, pass scope: null to the getter: User.getPosts({ scope: null }). Similarly, if you want to apply other scopes, pass an array like you would to .scope:

User.getPosts({ scope: ['scope1', 'scope2']});

If you want to create a shortcut method to a scope on an associated model, you can pass the scoped model to the association. Consider a shortcut to get all deleted posts for a user:

const Post = sequelize.define('post', attributes, {
  defaultScope: {
    where: {
      active: true
    }
  },
  scopes: {
    deleted: {
      where: {
        deleted: true
      }
    }
  }
});

User.hasMany(Post); // regular getPosts association
User.hasMany(Post.scope('deleted'), { as: 'deletedPosts' });

User.getPosts(); // WHERE active = true
User.getDeletedPosts(); // WHERE deleted = true

Hooks

Hooks (also known as lifecycle events), are functions which are called before and after calls in sequelize are executed. For example, if you want to always set a value on a model before saving it, you can add a beforeUpdate hook.

For a full list of hooks, see Hooks file.

Order of Operations

(1)
  beforeBulkCreate(instances, options)
  beforeBulkDestroy(options)
  beforeBulkUpdate(options)
(2)
  beforeValidate(instance, options)
(-)
  validate
(3)
  afterValidate(instance, options)
  - or -
  validationFailed(instance, options, error)
(4)
  beforeCreate(instance, options)
  beforeDestroy(instance, options)
  beforeUpdate(instance, options)
  beforeSave(instance, options)
  beforeUpsert(values, options)
(-)
  create
  destroy
  update
(5)
  afterCreate(instance, options)
  afterDestroy(instance, options)
  afterUpdate(instance, options)
  afterSave(instance, options)
  afterUpsert(created, options)
(6)
  afterBulkCreate(instances, options)
  afterBulkDestroy(options)
  afterBulkUpdate(options)

Declaring Hooks

Arguments to hooks are passed by reference. This means, that you can change the values, and this will be reflected in the insert / update statement. A hook may contain async actions - in this case the hook function should return a promise.

There are currently three ways to programmatically add hooks:

// Method 1 via the .define() method
const User = sequelize.define('user', {
  username: DataTypes.STRING,
  mood: {
    type: DataTypes.ENUM,
    values: ['happy', 'sad', 'neutral']
  }
}, {
  hooks: {
    beforeValidate: (user, options) => {
      user.mood = 'happy';
    },
    afterValidate: (user, options) => {
      user.username = 'Toni';
    }
  }
});

// Method 2 via the .hook() method (or its alias .addHook() method)
User.hook('beforeValidate', (user, options) => {
  user.mood = 'happy';
});

User.addHook('afterValidate', 'someCustomName', (user, options) => {
  return sequelize.Promise.reject(new Error("I'm afraid I can't let you do that!"));
});

// Method 3 via the direct method
User.beforeCreate((user, options) => {
  return hashPassword(user.password).then(hashedPw => {
    user.password = hashedPw;
  });
});

User.afterValidate('myHookAfter', (user, options) => {
  user.username = 'Toni';
});

Removing hooks

Only a hook with name param can be removed.

const Book = sequelize.define('book', {
  title: DataTypes.STRING
});

Book.addHook('afterCreate', 'notifyUsers', (book, options) => {
  // ...
});

Book.removeHook('afterCreate', 'notifyUsers');

You can have many hooks with same name. Calling .removeHook() will remove all of them.

Global / universal hooks

Global hooks are hooks which are run for all models. They can define behaviours that you want for all your models, and are especially useful for plugins. They can be defined in two ways, which have slightly different semantics:

Sequelize.options.define (default hook)

const sequelize = new Sequelize(..., {
    define: {
        hooks: {
            beforeCreate: () => {
                // Do stuff
            }
        }
    }
});

This adds a default hook to all models, which is run if the model does not define its own beforeCreate hook:

const User = sequelize.define('user');
const Project = sequelize.define('project', {}, {
    hooks: {
        beforeCreate: () => {
            // Do other stuff
        }
    }
});

User.create() // Runs the global hook
Project.create() // Runs its own hook (because the global hook is overwritten)

Sequelize.addHook (permanent hook)

sequelize.addHook('beforeCreate', () => {
    // Do stuff
});

This hooks is always run before create, regardless of whether the model specifies its own beforeCreate hook:

const User = sequelize.define('user');
const Project = sequelize.define('project', {}, {
    hooks: {
        beforeCreate: () => {
            // Do other stuff
        }
    }
});

User.create() // Runs the global hook
Project.create() // Runs its own hook, followed by the global hook

Local hooks are always run before global hooks.

Instance hooks

The following hooks will emit whenever you're editing a single object

beforeValidate
afterValidate or validationFailed
beforeCreate / beforeUpdate  / beforeDestroy
afterCreate / afterUpdate / afterDestroy

// ...define ...
User.beforeCreate(user => {
  if (user.accessLevel > 10 && user.username !== "Boss") {
    throw new Error("You can't grant this user an access level above 10!")
  }
})

This example will return an error:

User.create({username: 'Not a Boss', accessLevel: 20}).catch(err => {
  console.log(err); // You can't grant this user an access level above 10!
});

The following example would return successful:

User.create({username: 'Boss', accessLevel: 20}).then(user => {
  console.log(user); // user object with username as Boss and accessLevel of 20
});

Model hooks

Sometimes you'll be editing more than one record at a time by utilizing the bulkCreate, update, destroy methods on the model. The following will emit whenever you're using one of those methods:

beforeBulkCreate(instances, options)
beforeBulkUpdate(options)
beforeBulkDestroy(options)
afterBulkCreate(instances, options)
afterBulkUpdate(options)
afterBulkDestroy(options)

If you want to emit hooks for each individual record, along with the bulk hooks you can pass individualHooks: true to the call.

Model.destroy({ where: {accessLevel: 0}, individualHooks: true});
// Will select all records that are about to be deleted and emit before- + after- Destroy on each instance

Model.update({username: 'Toni'}, { where: {accessLevel: 0}, individualHooks: true});
// Will select all records that are about to be updated and emit before- + after- Update on each instance

The options argument of hook method would be the second argument provided to the corresponding method or its cloned and extended version.

Model.beforeBulkCreate((records, {fields}) => {
  // records = the first argument sent to .bulkCreate
  // fields = one of the second argument fields sent to .bulkCreate
})

Model.bulkCreate([
    {username: 'Toni'}, // part of records argument
    {username: 'Tobi'} // part of records argument
  ], {fields: ['username']} // options parameter
)

Model.beforeBulkUpdate(({attributes, where}) => {
  // where - in one of the fields of the clone of second argument sent to .update
  // attributes - is one of the fields that the clone of second argument of .update would be extended with
})

Model.update({gender: 'Male'} /*attributes argument*/, { where: {username: 'Tom'}} /*where argument*/)

Model.beforeBulkDestroy(({where, individualHooks}) => {
  // individualHooks - default of overridden value of extended clone of second argument sent to Model.destroy
  // where - in one of the fields of the clone of second argument sent to Model.destroy
})

Model.destroy({ where: {username: 'Tom'}} /*where argument*/)

If you use Model.bulkCreate(...) with the updatesOnDuplicate option, changes made in the hook to fields that aren't given in the updatesOnDuplicate array will not be persisted to the database. However it is possible to change the updatesOnDuplicate option inside the hook if this is what you want.

// Bulk updating existing users with updatesOnDuplicate option
Users.bulkCreate([
  { id: 1, isMember: true },
  { id: 2, isMember: false }
], {
  updatesOnDuplicate: ['isMember']
});

User.beforeBulkCreate((users, options) => {
  for (const user of users) {
    if (user.isMember) {
      user.memberSince = new Date();
    }
  }

  // Add memberSince to updatesOnDuplicate otherwise the memberSince date wont be
  // saved to the database
  options.updatesOnDuplicate.push('memberSince');
});

Associations

For the most part hooks will work the same for instances when being associated except a few things

1. When using add/set functions the beforeUpdate/afterUpdate hooks will run.
2. The only way to call beforeDestroy/afterDestroy hooks are on associations with onDelete: 'cascade' and the option hooks: true. For instance:

const Projects = sequelize.define('projects', {
  title: DataTypes.STRING
});

const Tasks = sequelize.define('tasks', {
  title: DataTypes.STRING
});

Projects.hasMany(Tasks, { onDelete: 'cascade', hooks: true });
Tasks.belongsTo(Projects);

This code will run beforeDestroy/afterDestroy on the Tasks table. Sequelize, by default, will try to optimize your queries as much as possible. When calling cascade on delete, Sequelize will simply execute a

DELETE FROM `table` WHERE associatedIdentifier = associatedIdentifier.primaryKey

However, adding hooks: true explicitly tells Sequelize that optimization is not of your concern and will perform a SELECT on the associated objects and destroy each instance one by one in order to be able to call the hooks with the right parameters.

If your association is of type n:m, you may be interested in firing hooks on the through model when using the remove call. Internally, sequelize is using Model.destroy resulting in calling the bulkDestroy instead of the before/afterDestroy hooks on each through instance.

This can be simply solved by passing {individualHooks: true} to the remove call, resulting on each hook to be called on each removed through instance object.

A Note About Transactions

Note that many model operations in Sequelize allow you to specify a transaction in the options parameter of the method. If a transaction is specified in the original call, it will be present in the options parameter passed to the hook function. For example, consider the following snippet:

// Here we use the promise-style of async hooks rather than
// the callback.
User.hook('afterCreate', (user, options) => {
  // 'transaction' will be available in options.transaction

  // This operation will be part of the same transaction as the
  // original User.create call.
  return User.update({
    mood: 'sad'
  }, {
    where: {
      id: user.id
    },
    transaction: options.transaction
  });
});


sequelize.transaction(transaction => {
  User.create({
    username: 'someguy',
    mood: 'happy',
    transaction
  });
});

If we had not included the transaction option in our call to User.update in the preceding code, no change would have occurred, since our newly created user does not exist in the database until the pending transaction has been committed.

Internal Transactions

It is very important to recognize that sequelize may make use of transactions internally for certain operations such as Model.findOrCreate. If your hook functions execute read or write operations that rely on the object's presence in the database, or modify the object's stored values like the example in the preceding section, you should always specify { transaction: options.transaction }.

If the hook has been called in the process of a transacted operation, this makes sure that your dependent read/write is a part of that same transaction. If the hook is not transacted, you have simply specified { transaction: null } and can expect the default behaviour.

Raw queries

As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can utilize the function sequelize.query.

By default the function will return two arguments - a results array, and an object containing metadata (affected rows etc.). Note that since this is a raw query, the metadata (property names etc.) is dialect specific. Some dialects return the metadata "within" the results object (as properties on an array). However, two arguments will always be returned, but for MSSQL and MySQL it will be two references to the same object.

sequelize.query("UPDATE users SET y = 42 WHERE x = 12").spread((results, metadata) => {
  // Results will be an empty array and metadata will contain the number of affected rows.
})

In cases where you don't need to access the metadata you can pass in a query type to tell sequelize how to format the results. For example, for a simple select query you could do:

sequelize.query("SELECT * FROM `users`", { type: sequelize.QueryTypes.SELECT})
  .then(users => {
    // We don't need spread here, since only the results will be returned for select queries
  })

Several other query types are available. Peek into the source for details

A second option is the model. If you pass a model the returned data will be instances of that model.

// Callee is the model definition. This allows you to easily map a query to a predefined model
sequelize
  .query('SELECT * FROM projects', {
    model: Projects,
    mapToModel: true // pass true here if you have any mapped fields
  })
  .then(projects => {
    // Each record will now be an instance of Project
  })

Replacements

Replacements in a query can be done in two different ways, either using named parameters (starting with :), or unnamed, represented by a ?. Replacements are passed in the options object.

• If an array is passed, ? will be replaced in the order that they appear in the array
• If an object is passed, :key will be replaced with the keys from that object. If the object contains keys not found in the query or vice versa, an exception will be thrown.

sequelize.query('SELECT * FROM projects WHERE status = ?',
  { replacements: ['active'], type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

sequelize.query('SELECT * FROM projects WHERE status = :status ',
  { replacements: { status: 'active' }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

Array replacements will automatically be handled, the following query searches for projects where the status matches an array of values.

sequelize.query('SELECT * FROM projects WHERE status IN(:status) ',
  { replacements: { status: ['active', 'inactive'] }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

To use the wildcard operator %, append it to your replacement. The following query matches users with names that start with 'ben'.

sequelize.query('SELECT * FROM users WHERE name LIKE :search_name ',
  { replacements: { search_name: 'ben%'  }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

Bind Parameter

Bind parameters are like replacements. Except replacements are escaped and inserted into the query by sequelize before the query is sent to the database, while bind parameters are sent to the database outside the SQL query text. A query can have either bind parameters or replacements. Bind parameters are referred to by either $1, $2, ... (numeric) or $key (alpha-numeric). This is independent of the dialect.

• If an array is passed, $1 is bound to the 1st element in the array (bind[0])
• If an object is passed, $key is bound to object['key']. Each key must begin with a non-numeric char. $1 is not a valid key, even if object['1'] exists.
• In either case $$ can be used to escape a literal $ sign.

The array or object must contain all bound values or Sequelize will throw an exception. This applies even to cases in which the database may ignore the bound parameter.

The database may add further restrictions to this. Bind parameters cannot be SQL keywords, nor table or column names. They are also ignored in quoted text or data. In PostgreSQL it may also be needed to typecast them, if the type cannot be inferred from the context $1::varchar.

sequelize.query('SELECT *, "text with literal $$1 and literal $$status" as t FROM projects WHERE status = $1',
  { bind: ['active'], type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

sequelize.query('SELECT *, "text with literal $$1 and literal $$status" as t FROM projects WHERE status = $status',
  { bind: { status: 'active' }, type: sequelize.QueryTypes.SELECT }
).then(projects => {
  console.log(projects)
})

Migrations

Just like you use Git / SVN to manage changes in your source code, you can use migrations to keep track of changes to the database. With migrations you can transfer your existing database into another state and vice versa: Those state transitions are saved in migration files, which describe how to get to the new state and how to revert the changes in order to get back to the old state.

You will need Sequelize CLI. The CLI ships support for migrations and project bootstrapping.

The CLI

Installing CLI

Let's start with installing CLI, you can find instructions here. Most preferred way is installing locally like this

$ npm install --save sequelize-cli

Bootstrapping

To create an empty project you will need to execute init command

$ node_modules/.bin/sequelize init

This will create following folders

• config, contains config file, which tells CLI how to connect with database
• models, contains all models for your project
• migrations, contains all migration files
• seeders, contains all seed files

Configuration

Before continuing further we will need to tell CLI how to connect to database. To do that let's open default config file config/config.json. It looks something like this

{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

Now edit this file and set correct database credentials and dialect.

Note: If your database doesn't exists yet, you can just call db:create command. With proper access it will create that database for you.

Creating first Model (and Migration)

Once you have properly configured CLI config file you are ready to create your first migration. It's as simple as executing a simple command.

We will use model:generate command. This command requires two options

• name, Name of the model
• attributes, List of model attributes

Let's create a model named User.

$ node_modules/.bin/sequelize model:generate --name User --attributes firstName:string,lastName:string,email:string

This will do following

• Create a model file user in models folder
• Create a migration file with name like XXXXXXXXXXXXXX-create-user.js in migrations folder

Note: Sequelize will only use Model files, it's the table representation. On the other hand, the migration file is a change in that model or more specifically that table, used by CLI. Treat migrations like a commit or a log for some change in database.

Running Migrations

Until this step, we haven't inserted anything into the database. We have just created required model and migration files for our first model User. Now to actually create that table in database you need to run db:migrate command.

$ node_modules/.bin/sequelize db:migrate

This command will execute these steps:

• Will ensure a table called SequelizeMeta in database. This table is used to record which migrations have run on the current database
• Start looking for any migration files which haven't run yet. This is possible by checking SequelizeMeta table. In this case it will run XXXXXXXXXXXXXX-create-user.js migration, which we created in last step.
• Creates a table called Users with all columns as specified in its migration file.

Undoing Migrations

Now our table has been created and saved in database. With migration you can revert to old state by just running a command.

You can use db:migrate:undo, this command will revert most recent migration.

$ node_modules/.bin/sequelize db:migrate:undo

You can revert back to initial state by undoing all migrations with db:migrate:undo:all command. You can also revert back to a specific migration by passing its name in --to option.

$ node_modules/.bin/sequelize db:migrate:undo:all --to XXXXXXXXXXXXXX-create-posts.js

Creating First Seed

Suppose we want to insert some data into a few tables by default. If we follow up on previous example we can consider creating a demo user for User table.

To manage all data migrations you can use seeders. Seed files are some change in data that can be used to populate database table with sample data or test data.

Let's create a seed file which will add a demo user to our User table.

$ node_modules/.bin/sequelize seed:generate --name demo-user

This command will create a seed file in seeders folder. File name will look something like XXXXXXXXXXXXXX-demo-user.js. It follows the same up / down semantics as the migration files.

Now we should edit this file to insert demo user to User table.

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('Users', [{
        firstName: 'John',
        lastName: 'Doe',
        email: 'demo@demo.com'
      }], {});
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('Users', null, {});
  }
};

Running Seeds

In last step you have create a seed file. It's still not committed to database. To do that we need to run a simple command.

$ node_modules/.bin/sequelize db:seed:all

This will execute that seed file and you will have a demo user inserted into User table.

Note: Seeders execution is not stored anywhere unlike migrations, which use the SequelizeMeta table. If you wish to override this please read Storage section

Undoing Seeds

Seeders can be undone if they are using any storage. There are two commands available for that:

If you wish to undo most recent seed

node_modules/.bin/sequelize db:seed:undo

If you wish to undo all seeds

node_modules/.bin/sequelize db:seed:undo:all

Advance Topics

Migration Skeleton

The following skeleton shows a typical migration file.

module.exports = {
  up: (queryInterface, Sequelize) => {
    // logic for transforming into the new state
  },

  down: (queryInterface, Sequelize) => {
    // logic for reverting the changes
  }
}

The passed queryInterface object can be used to modify the database. The Sequelize object stores the available data types such as STRING or INTEGER. Function up or down should return a Promise. Let's look at an example:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Person', {
        name: Sequelize.STRING,
        isBetaMember: {
          type: Sequelize.BOOLEAN,
          defaultValue: false,
          allowNull: false
        }
      });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Person');
  }
}

The .sequelizerc File

This is a special configuration file. It lets you specify various options that you would usually pass as arguments to CLI. Some scenarios where you can use it.

• You want to override default path to migrations, models, seeders or config folder.
• You want to rename config.json to something else like database.json

And a whole lot more. Let's see how you can use this file for custom configuration.

For starters, let's create an empty file in root directory of your project.

$ touch .sequelizerc

Now let's work with an example config.

const path = require('path');

module.exports = {
  'config': path.resolve('config', 'database.json'),
  'models-path': path.resolve('db', 'models'),
  'seeders-path': path.resolve('db', 'seeders'),
  'migrations-path': path.resolve('db', 'migrations')
}

With this config you are telling CLI to

• Use config/database.json file for config settings
• Use db/models as models folder
• Use db/seeders as seeders folder
• Use db/migrations as migrations folder

Dynamic Configuration

Configuration file is by default a JSON file called config.json. But sometimes you want to execute some code or access environment variables which is not possible in JSON files.

Sequelize CLI can read from both JSON and JS files. This can be setup with .sequelizerc file. Let see how

First you need to create a .sequelizerc file in root folder of your project. This file should override config path to a JS file. Like this

const path = require('path');

module.exports = {
  'config': path.resolve('config', 'config.js')
}

Now Sequelize CLI will load config/config.js for getting configuration options. Since this is a JS file you can have any code executed and export final dynamic configuration file.

An example of config/config.js file

const fs = require('fs');

module.exports = {
  development: {
    username: 'database_dev',
    password: 'database_dev',
    database: 'database_dev',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  test: {
    username: 'database_test',
    password: null,
    database: 'database_test',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  production: {
    username: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    host: process.env.DB_HOSTNAME,
    dialect: 'mysql',
    dialectOptions: {
      ssl: {
        ca: fs.readFileSync(__dirname + '/mysql-ca-master.crt')
      }
    }
  }
};

Using Environment Variables

With CLI you can directly access the environment variables inside the config/config.js. You can use .sequelizerc to tell CLI to use config/config.js for configuration. This is explained in last section.

Then you can just expose file with proper environment variables.

module.exports = {
  development: {
    username: 'database_dev',
    password: 'database_dev',
    database: 'database_dev',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  test: {
    username: process.env.CI_DB_USERNAME,
    password: process.env.CI_DB_PASSWORD,
    database: process.env.CI_DB_NAME,
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  production: {
    username: process.env.PROD_DB_USERNAME,
    password: process.env.PROD_DB_PASSWORD,
    database: process.env.PROD_DB_NAME,
    host: process.env.PROD_DB_HOSTNAME,
    dialect: 'mysql'
  }

Specifying Dialect Options

Sometime you want to specify a dialectOption, if it's a general config you can just add it in config/config.json. Sometime you want to execute some code to get dialectOptions, you should use dynamic config file for those cases.

{
    "production": {
        "dialect":"mysql",
        "dialectOptions": {
            "bigNumberStrings": true
        }
    }
}

Production Usages

Some tips around using CLI and migration setup in production environment.

1) Use environment variables for config settings. This is better achieved with dynamic configuration. A sample production safe configuration may look like.

const fs = require('fs');

module.exports = {
  development: {
    username: 'database_dev',
    password: 'database_dev',
    database: 'database_dev',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  test: {
    username: 'database_test',
    password: null,
    database: 'database_test',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  production: {
    username: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    host: process.env.DB_HOSTNAME,
    dialect: 'mysql',
    dialectOptions: {
      ssl: {
        ca: fs.readFileSync(__dirname + '/mysql-ca-master.crt')
      }
    }
  }
};

Our goal is to use environment variables for various database secrets and not accidentally check them in to source control.

Storage

There are three types of storage that you can use: sequelize, json, and none.

• sequelize : stores migrations and seeds in a table on the sequelize database
• json : stores migrations and seeds on a json file
• none : does not store any migration/seed

Migration Storage

By default the CLI will create a table in your database called SequelizeMeta containing an entry for each executed migration. To change this behavior, there are three options you can add to the configuration file. Using migrationStorage, you can choose the type of storage to be used for migrations. If you choose json, you can specify the path of the file using migrationStoragePath or the CLI will write to the file sequelize-meta.json. If you want to keep the information in the database, using sequelize, but want to use a different table, you can change the table name using migrationStorageTableName.

{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql",

    // Use a different storage type. Default: sequelize
    "migrationStorage": "json",

    // Use a different file name. Default: sequelize-meta.json
    "migrationStoragePath": "sequelizeMeta.json",

    // Use a different table name. Default: SequelizeMeta
    "migrationStorageTableName": "sequelize_meta"
  }
}

Note: The none storage is not recommended as a migration storage. If you decide to use it, be aware of the implications of having no record of what migrations did or didn't run.

Seed Storage

By default the CLI will not save any seed that is executed. If you choose to change this behavior (!), you can use seederStorage in the configuration file to change the storage type. If you choose json, you can specify the path of the file using seederStoragePath or the CLI will write to the file sequelize-data.json. If you want to keep the information in the database, using sequelize, you can specify the table name using seederStorageTableName, or it will default to SequelizeData.

{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql",
    // Use a different storage. Default: none
    "seederStorage": "json",
    // Use a different file name. Default: sequelize-data.json
    "seederStoragePath": "sequelizeData.json",
    // Use a different table name. Default: SequelizeData
    "seederStorageTableName": "sequelize_data"
  }
}

Configuration Connection String

As an alternative to the --config option with configuration files defining your database, you can use the --url option to pass in a connection string. For example:

$ node_modules/.bin/sequelize db:migrate --url 'mysql://root:password@mysql_host.com/database_name'

Connecting over SSL

Ensure ssl is specified in both dialectOptions and in the base config.

{
    "production": {
        "dialect":"postgres",
        "ssl": true,
        "dialectOptions": {
            "ssl": true
        }
    }
}

Programmatic use

Sequelize has a sister library for programmatically handling execution and logging of migration tasks.

Query Interface

Using queryInterface object described before you can change database schema. To see full list of public methods it supports check QueryInterface API

Upgrade to V4

Sequelize v4 is the current release and it introduces some breaking changes. Majority of sequelize codebase has been refactored to use ES2015 features. The following guide lists some of the changes to upgrade from v3 to v4.

Changelog

Full Changelog for v4 release.

Breaking Changes

Node

To use new ES2015 features, Sequelize v4 requires at least Node v4 or above.

General

• Counter Cache plugin and consequently the counterCache option for associations has been removed.
• MariaDB dialect now removed. This was just a thin wrapper around MySQL. You can set dialect: 'mysql' an d Sequelize should be able to work with MariaDB server.
• Model.Instance and instance.Model are removed. To access the Model from an instance, simply use instance.constructor. The Instance class (Model.Instance) is now the Model itself.
• Sequelize now uses an independent copy of bluebird library.
• Promises returned by sequelize are now instances of Sequelize.Promise instead of global bluebird Promise.
• Pooling library was updated to v3, now you will need to call sequelize.close() to shutdown the pool.

Config / Options

• Removed support for old connection pooling configuration keys. Instead of

  Old

    pool: {
      maxIdleTime: 30000,
      minConnections: 20,
      maxConnections: 30
    }
  

  New

    pool: {
      idle: 30000,
      min: 20,
      max: 30
    }
  

• Removed support for pool: false. To use a single connection, set pool.max to 1.
• Removed support for referencesKey, use a references object

    references: {
      key: '',
      model: ''
    }
  

• Removed classMethods and instanceMethods options from sequelize.define. Sequelize models are now ES6 classes. You can set class / instance level methods like this

  Old

  const Model = sequelize.define('Model', {
      ...
  }, {
      classMethods: {
          associate: function (model) {...}
      },
      instanceMethods: {
          someMethod: function () { ...}
      }
  });
  

  New

  const Model = sequelize.define('Model', {
      ...
  });
  
  // Class Method
  Model.associate = function (models) {
      ...associate the models
  };
  
  // Instance Method
  Model.prototype.someMethod = function () {..}
  

• options.order now only accepts values with type of array or Sequelize method. Support for string values (ie {order: 'name DESC'}) has been deprecated.

• With BelongsToMany relationships add/set/create setters now set through attributes by passing them as options.through (previously second argument was used as through attributes, now it's considered options with through being a sub option)

• Raw options for where, order and group like where: { $raw: '..', order: [{ raw: '..' }], group: [{ raw: '..' }] } have been removed to prevent SQL injection attacks.

  Old

  user.addProject(project, { status: 'started' });
  

  New

  user.addProject(project, { through: { status: 'started' } });
  

Data Types

• (MySQL/Postgres) BIGINT now returned as string.
• (MySQL/Postgres) DECIMAL and NEWDECIMAL types now returned as string.
• (MSSQL) DataTypes.DATE now uses DATETIMEOFFSET instead of DATETIME2 sql datatype in case of MSSQL to record timezone. To migrate existing DATETIME2 columns into DATETIMEOFFSET, see #7201.
• DATEONLY now returns string in YYYY-MM-DD format rather than Date type

Transactions / CLS

• Removed autocommit: true default, set this option explicitly to have transactions auto commit.
• Removed default REPEATABLE_READ transaction isolation. The isolation level now defaults to that of the database. Explicitly pass the required isolation level when initiating the transaction.

• The CLS patch does not affect global bluebird promise. Transaction will not automatically get passed to methods when used with Promise.all and other bluebird methods. Explicitly patch your bluebird instance to get CLS to work with bluebird methods.

    $ npm install --save cls-bluebird
  

    const Sequelize = require('sequelize');
    const Promise = require('bluebird');
    const clsBluebird = require('cls-bluebird');
    const cls = require('continuation-local-storage');
  
    const ns = cls.createNamespace('transaction-namespace');
    clsBluebird(ns, Promise);
  
    Sequelize.useCLS(ns);
  

Raw Queries

• Sequelize now supports bind parameters for all dialects. In v3 bind option would fallback to replacements if dialect didn't supported binding. This could be a breaking change for MySQL / MSSQL where now queries will actually use bind parameters instead of replacements fallback.

Others

• Sequelize.Validator is now an independent copy of validator library.
• Model.validate instance method now runs validation hooks by default. Previously you needed to pass { hooks: true }. You can override this behavior by passing { hooks: false }.
• The resulting promise from the Model.validate instance method will be rejected when validation fails. It will fulfill when validation succeeds.
• Sequelize.Utils is not longer part of the public API, use it at your own risk.
• Hooks should return Promises now. Callbacks are deprecated.
• Getters wont run with instance.get({ raw: true }), use instance.get({ plain: true })

• required inside include does not propagate up the include chain.

  To get v3 compatible results you'll need to either set required on the containing include.

  Old

  user.findOne({
    include: {
      model: project,
      include: {
        model: task,
        required: true
      }
    }
  });
  

  New

  User.findOne({
    include: {
      model: Project,
      required: true,
      include: {
        model: Task,
        required: true
      }
    }
  });
  
  User.findOne({
    include: {
      model: Project,
      required: true,
      include: {
        model: Task,
        where: { type: 'important' } //where cause required to default to true
      }
    }
  });
  

  Optionally you can add a beforeFind hook to get v3 compatible behavior -

  function propagateRequired(modelDescriptor) {
    let include = modelDescriptor.include;
  
    if (!include) return false;
    if (!Array.isArray(include)) include = [include];
  
    return include.reduce((isRequired, descriptor) => {
      const hasRequiredChild = propogateRequired(descriptor);
      if ((descriptor.where || hasRequiredChild) && descriptor.required === undefined) {
        descriptor.required = true;
      }
      return descriptor.required || isRequired;
    }, false);
  }
  
  const sequelize = new Sequelize(..., {
    ...,
    define: {
      hooks: {
        beforeFind: propagateRequired
      }
    }
  });
  

Working with legacy tables

While out of the box Sequelize will seem a bit opinionated it's trivial to both legacy and forward proof your application by defining (otherwise generated) table and field names.

Tables

sequelize.define('user', {

}, {
  tableName: 'users'
});

Fields

sequelize.define('modelName', {
  userId: {
    type: Sequelize.INTEGER,
    field: 'user_id'
  }
});

Primary keys

Sequelize will assume your table has a id primary key property by default.

To define your own primary key:

sequelize.define('collection', {
  uid: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true // Automatically gets converted to SERIAL for postgres
  }
});

sequelize.define('collection', {
  uuid: {
    type: Sequelize.UUID,
    primaryKey: true
  }
});

And if your model has no primary key at all you can use Model.removeAttribute('id');

Foreign keys

// 1:1
Organization.belongsTo(User, {foreignKey: 'owner_id'});
User.hasOne(Organization, {foreignKey: 'owner_id'});

// 1:M
Project.hasMany(Task, {foreignKey: 'tasks_pk'});
Task.belongsTo(Project, {foreignKey: 'tasks_pk'});

// N:M
User.hasMany(Role, {through: 'user_has_roles', foreignKey: 'user_role_user_id'});
Role.hasMany(User, {through: 'user_has_roles', foreignKey: 'roles_identifier'});

References

Who's using sequelize?

... we are avid users of sequelize (and have been for the past 18 months) (Feb 2017)

We've been using sequelize since we started in the beginning of 2015. We use it for our graphql servers (in connection with graphql-sequelize), and for all our background workers.

We have used Sequelize in enterprise projects for some of our Fortune 100 and Fortune 500 clients. It is used in deployments that are depended on by hundreds of millions of devices every year.

Using Sequelize in production for two different apps with 30k+ daily users by 2 years. I doubt there is something better at this moment in terms of productivity and features.

Imprint

• Boring legal stuff for the rest of us. As there are people who are suing for fun and glory, you can find the respective information about the author of the page right here. Have fun reading ...

AUTHOR(S)

Main author:

Sascha Depold
Uhlandstr. 160
10719 Berlin
sascha [at] depold [dot] com
[plus] 49 152 [slash] 03878582

INHALTLICHE VERANTWORTUNG

Ich übernehme keine Haftung für ausgehende Links. 
Daher musst du dich bei Problemen an deren Betreiber wenden!