Installing

Sequelize is available via npm (or yarn).

npm install --save sequelize

You'll also have to manually install the driver for your database of choice:

# One of the following:
$ npm install --save pg pg-hstore # Postgres
$ npm install --save mysql2
$ npm install --save mariadb
$ npm install --save sqlite3
$ npm install --save tedious # Microsoft SQL Server

Connecting to a database

To connect to the database, you must create a Sequelize instance. This can be done by either passing the connection parameters separately to the Sequelize constructor or by passing a single connection URI:

const { Sequelize } = require('sequelize');

// Option 1: Passing a connection URI
const sequelize = new Sequelize('sqlite::memory:') // Example for sqlite
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname') // Example for postgres

// Option 2: Passing parameters separately (sqlite)
const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'path/to/database.sqlite'
});

// Option 3: Passing parameters separately (other dialects)
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: /* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */
});

The Sequelize constructor accepts a lot of options. They are documented in the API Reference.

Testing the connection

You can use the .authenticate() function to test if the connection is OK:

try {
  await sequelize.authenticate();
  console.log("Connection has been established successfully.");
} catch (error) {
  console.error("Unable to connect to the database:", error);
}

Closing the connection

Sequelize will keep the connection open by default, and use the same connection for all queries. If you need to close the connection, call sequelize.close() (which is asynchronous and returns a Promise).

Terminology convention

Observe that, in the examples above, Sequelize refers to the library itself while sequelize refers to an instance of Sequelize, which represents a connection to one database. This is the recommended convention and it will be followed throughout the documentation.

Tip for reading the docs

You are encouraged to run code examples locally while reading the Sequelize docs. This will help you learn faster. The easiest way to do this is using the SQLite dialect:

const { Sequelize, Op, Model, DataTypes } = require("sequelize");
const sequelize = new Sequelize("sqlite::memory:");

// Code here! It works!

To experiment with the other dialects, which are harder to setup locally, you can use the Sequelize SSCCE GitHub repository, which allows you to run code on all supported dialects directly from GitHub, for free, without any setup!

New databases versus existing databases

If you are starting a project from scratch, and your database is still empty, Sequelize can be used since the beginning in order to automate the creation of every table in your database.

Also, if you want to use Sequelize to connect to a database that is already filled with tables and data, that works as well! Sequelize has got you covered in both cases.

Logging

By default, Sequelize will log to console every SQL query it performs. The options.logging option can be used to customize this behavior, by defining the function that gets executed every time Sequelize would log something. The default value is console.log and when using that only the first log parameter of log function call is displayed. For example, for query logging the first parameter is the raw query and the second (hidden by default) is the Sequelize object.

Common useful values for options.logging:

const sequelize = new Sequelize("sqlite::memory:", {
  // Choose one of the logging options
  logging: console.log, // Default, displays the first parameter of the log function call
  logging: (...msg) => console.log(msg), // Displays all log function call parameters
  logging: false, // Disables logging
  logging: (msg) => logger.debug(msg), // Use custom logger (e.g. Winston or Bunyan), displays the first parameter
  logging: logger.debug.bind(logger), // Alternative way to use custom logger, displays all messages
});

Promises and async/await

Most of the methods provided by Sequelize are asynchronous and therefore return Promises. They are all Promises , so you can use the Promise API (for example, using then, catch, finally) out of the box.

Of course, using async and await works normally as well.

Concept

Models are the essence of Sequelize. A model is an abstraction that represents a table in your database. In Sequelize, it is a class that extends Model.

The model tells Sequelize several things about the entity it represents, such as the name of the table in the database and which columns it has (and their data types).

A model in Sequelize has a name. This name does not have to be the same name of the table it represents in the database. Usually, models have singular names (such as User) while tables have pluralized names (such as Users), although this is fully configurable.

Model Definition

Models can be defined in two equivalent ways in Sequelize:

• Calling sequelize.define(modelName, attributes, options)
• Extending Model and calling init(attributes, options)

After a model is defined, it is available within sequelize.models by its model name.

To learn with an example, we will consider that we want to create a model to represent users, which have a firstName and a lastName. We want our model to be called User, and the table it represents is called Users in the database.

Both ways to define this model are shown below. After being defined, we can access our model with sequelize.models.User.

Using sequelize.define:

const { Sequelize, DataTypes } = require("sequelize");
const sequelize = new Sequelize("sqlite::memory:");

const User = sequelize.define(
  "User",
  {
    // Model attributes are defined here
    firstName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    lastName: {
      type: DataTypes.STRING,
      // allowNull defaults to true
    },
  },
  {
    // Other model options go here
  }
);

// `sequelize.define` also returns the model
console.log(User === sequelize.models.User); // true

Extending Model

const { Sequelize, DataTypes, Model } = require("sequelize");
const sequelize = new Sequelize("sqlite::memory:");

class User extends Model {}

User.init(
  {
    // Model attributes are defined here
    firstName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    lastName: {
      type: DataTypes.STRING,
      // allowNull defaults to true
    },
  },
  {
    // Other model options go here
    sequelize, // We need to pass the connection instance
    modelName: "User", // We need to choose the model name
  }
);

// the defined model is the class itself
console.log(User === sequelize.models.User); // true

Internally, sequelize.define calls Model.init, so both approaches are essentially equivalent.

Caveat with Public Class Fields

Adding a Public Class Field with the same name as one of the model's attribute is going to cause issues. Sequelize adds a getter & a setter for each attribute defined through Model.init. Adding a Public Class Field will shadow those getter and setters, blocking access to the model's actual data.

// Invalid
class User extends Model {
  id; // this field will shadow sequelize's getter & setter. It should be removed.
  otherPublicField; // this field does not shadow anything. It is fine.
}

User.init(
  {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
    },
  },
  { sequelize }
);

const user = new User({ id: 1 });
user.id; // undefined

// Valid
class User extends Model {
  otherPublicField;
}

User.init(
  {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
    },
  },
  { sequelize }
);

const user = new User({ id: 1 });
user.id; // 1

In TypeScript, you can add typing information without adding an actual public class field by using the declare keyword:

// Valid
class User extends Model {
  declare id: number; // this is ok! The 'declare' keyword ensures this field will not be emitted by TypeScript.
}

User.init(
  {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
    },
  },
  { sequelize }
);

const user = new User({ id: 1 });
user.id; // 1

Table name inference

Observe that, in both methods above, the table name (Users) was never explicitly defined. However, the model name was given (User).

By default, when the table name is not given, Sequelize automatically pluralizes the model name and uses that as the table name. This pluralization is done under the hood by a library called inflection, so that irregular plurals (such as person -> people) are computed correctly.

Of course, this behavior is easily configurable.

Enforcing the table name to be equal to the model name

You can stop the auto-pluralization performed by Sequelize using the freezeTableName: true option. This way, Sequelize will infer the table name to be equal to the model name, without any modifications:

sequelize.define(
  "User",
  {
    // ... (attributes)
  },
  {
    freezeTableName: true,
  }
);

The example above will create a model named User pointing to a table also named User.

This behavior can also be defined globally for the sequelize instance, when it is created:

const sequelize = new Sequelize("sqlite::memory:", {
  define: {
    freezeTableName: true,
  },
});

This way, all tables will use the same name as the model name.

Providing the table name directly

You can simply tell Sequelize the name of the table directly as well:

sequelize.define(
  "User",
  {
    // ... (attributes)
  },
  {
    tableName: "Employees",
  }
);

Model synchronization

When you define a model, you're telling Sequelize a few things about its table in the database. However, what if the table actually doesn't even exist in the database? What if it exists, but it has different columns, less columns, or any other difference?

This is where model synchronization comes in. A model can be synchronized with the database by calling model.sync(options), an asynchronous function (that returns a Promise). With this call, Sequelize will automatically perform an SQL query to the database. Note that this changes only the table in the database, not the model in the JavaScript side.

• User.sync() - This creates the table if it doesn't exist (and does nothing if it already exists)
• User.sync({ force: true }) - This creates the table, dropping it first if it already existed
• User.sync({ alter: true }) - This checks what is the current state of the table in the database (which columns it has, what are their data types, etc), and then performs the necessary changes in the table to make it match the model.

Example:

await User.sync({ force: true });
console.log("The table for the User model was just (re)created!");

Synchronizing all models at once

You can use sequelize.sync() to automatically synchronize all models. Example:

await sequelize.sync({ force: true });
console.log("All models were synchronized successfully.");

Dropping tables

To drop the table related to a model:

await User.drop();
console.log("User table dropped!");

To drop all tables:

await sequelize.drop();
console.log("All tables dropped!");

Database safety check

As shown above, the sync and drop operations are destructive. Sequelize accepts a match option as an additional safety check, which receives a RegExp:

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

Synchronization in production

As shown above, sync({ force: true }) and sync({ alter: true }) can be destructive operations. Therefore, they are not recommended for production-level software. Instead, synchronization should be done with the advanced concept of Migrations, with the help of the Sequelize CLI.

Timestamps

By default, Sequelize automatically adds the fields createdAt and updatedAt to every model, using the data type DataTypes.DATE. Those fields are automatically managed as well - whenever you use Sequelize to create or update something, those fields will be set correctly. The createdAt field will contain the timestamp representing the moment of creation, and the updatedAt will contain the timestamp of the latest update.

Note: This is done in the Sequelize level (i.e. not done with SQL triggers). This means that direct SQL queries (for example queries performed without Sequelize by any other means) will not cause these fields to be updated automatically.

This behavior can be disabled for a model with the timestamps: false option:

sequelize.define(
  "User",
  {
    // ... (attributes)
  },
  {
    timestamps: false,
  }
);

It is also possible to enable only one of createdAt/updatedAt, and to provide a custom name for these columns:

class Foo extends Model {}
Foo.init(
  {
    /* attributes */
  },
  {
    sequelize,

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

Column declaration shorthand syntax

If the only thing being specified about a column is its data type, the syntax can be shortened:

// This:
sequelize.define("User", {
  name: {
    type: DataTypes.STRING,
  },
});

// Can be simplified to:
sequelize.define("User", { name: DataTypes.STRING });

Default Values

By default, Sequelize assumes that the default value of a column is NULL. This behavior can be changed by passing a specific defaultValue to the column definition:

sequelize.define("User", {
  name: {
    type: DataTypes.STRING,
    defaultValue: "John Doe",
  },
});

Some special values, such as DataTypes.NOW, are also accepted:

sequelize.define("Foo", {
  bar: {
    type: DataTypes.DATETIME,
    defaultValue: DataTypes.NOW,
    // This way, the current date/time will be used to populate this column (at the moment of insertion)
  },
});

Data Types

Every column you define in your model must have a data type. Sequelize provides a lot of built-in data types. To access a built-in data type, you must import DataTypes:

const { DataTypes } = require("sequelize"); // Import the built-in data types

Strings

DataTypes.STRING; // VARCHAR(255)
DataTypes.STRING(1234); // VARCHAR(1234)
DataTypes.STRING.BINARY; // VARCHAR BINARY
DataTypes.TEXT; // TEXT
DataTypes.TEXT("tiny"); // TINYTEXT
DataTypes.CITEXT; // CITEXT          PostgreSQL and SQLite only.
DataTypes.TSVECTOR; // TSVECTOR        PostgreSQL only.

Boolean

DataTypes.BOOLEAN; // TINYINT(1)

Numbers

DataTypes.INTEGER; // INTEGER
DataTypes.BIGINT; // BIGINT
DataTypes.BIGINT(11); // BIGINT(11)

DataTypes.FLOAT; // FLOAT
DataTypes.FLOAT(11); // FLOAT(11)
DataTypes.FLOAT(11, 10); // FLOAT(11,10)

DataTypes.REAL; // REAL            PostgreSQL only.
DataTypes.REAL(11); // REAL(11)        PostgreSQL only.
DataTypes.REAL(11, 12); // REAL(11,12)     PostgreSQL only.

DataTypes.DOUBLE; // DOUBLE
DataTypes.DOUBLE(11); // DOUBLE(11)
DataTypes.DOUBLE(11, 10); // DOUBLE(11,10)

DataTypes.DECIMAL; // DECIMAL
DataTypes.DECIMAL(10, 2); // DECIMAL(10,2)

Unsigned & Zerofill integers - MySQL/MariaDB only

In MySQL and MariaDB, the data types INTEGER, BIGINT, FLOAT and DOUBLE can be set as unsigned or zerofill (or both), as follows:

DataTypes.INTEGER.UNSIGNED;
DataTypes.INTEGER.ZEROFILL;
DataTypes.INTEGER.UNSIGNED.ZEROFILL;
// You can also specify the size i.e. INTEGER(10) instead of simply INTEGER
// Same for BIGINT, FLOAT and DOUBLE

Dates

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

UUIDs

For UUIDs, use DataTypes.UUID. It becomes the UUID data type for PostgreSQL and SQLite, and CHAR(36) for MySQL. Sequelize can generate UUIDs automatically for these fields, simply use DataTypes.UUIDV1 or DataTypes.UUIDV4 as the default value:

{
  type: DataTypes.UUID,
  defaultValue: DataTypes.UUIDV4 // Or DataTypes.UUIDV1
}

Others

There are other data types, covered in a separate guide.

Column Options

When defining a column, apart from specifying the type of the column, and the allowNull and defaultValue options mentioned above, there are a lot more options that can be used. Some examples are below.

const { Model, DataTypes, Deferrable } = require("sequelize");

class Foo extends Model {}
Foo.init(
  {
    // instantiating will automatically set the flag to true if not set
    flag: { type: DataTypes.BOOLEAN, allowNull: false, defaultValue: true },

    // default values for dates => current time
    myDate: { type: DataTypes.DATE, defaultValue: DataTypes.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: DataTypes.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: DataTypes.STRING, unique: "compositeIndex" },
    uniqueTwo: { type: DataTypes.INTEGER, unique: "compositeIndex" },

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

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

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

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

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

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

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

        // With PostgreSQL, it is optionally possible to declare when to check the foreign key constraint, passing the Deferrable type.
        deferrable: Deferrable.INITIALLY_IMMEDIATE,
        // Options:
        // - `Deferrable.INITIALLY_IMMEDIATE` - Immediately check the foreign key constraints
        // - `Deferrable.INITIALLY_DEFERRED` - Defer all foreign key constraint check to the end of a transaction
        // - `Deferrable.NOT` - Don't defer the checks at all (default) - This won't allow you to dynamically change the rule in a transaction
      },
    },

    // Comments can only be added to columns in MySQL, MariaDB, PostgreSQL and MSSQL
    commentMe: {
      type: DataTypes.INTEGER,
      comment: "This is a column name that has a comment",
    },
  },
  {
    sequelize,
    modelName: "foo",

    // Using `unique: true` in an attribute above is exactly the same as creating the index in the model's options:
    indexes: [{ unique: true, fields: ["someUnique"] }],
  }
);

Taking advantage of Models being classes

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

class User extends Model {
  static classLevelMethod() {
    return "foo";
  }
  instanceLevelMethod() {
    return "bar";
  }
  getFullname() {
    return [this.firstname, this.lastname].join(" ");
  }
}
User.init(
  {
    firstname: Sequelize.TEXT,
    lastname: Sequelize.TEXT,
  },
  { sequelize }
);

console.log(User.classLevelMethod()); // 'foo'
const user = User.build({ firstname: "Jane", lastname: "Doe" });
console.log(user.instanceLevelMethod()); // 'bar'
console.log(user.getFullname()); // 'Jane Doe'

Model Instances

As you already know, a model is an ES6 class. An instance of the class represents one object from that model (which maps to one row of the table in the database). This way, model instances are DAOs.

For this guide, the following setup will be assumed:

const { Sequelize, Model, DataTypes } = require("sequelize");
const sequelize = new Sequelize("sqlite::memory:");

const User = sequelize.define("user", {
  name: DataTypes.TEXT,
  favoriteColor: {
    type: DataTypes.TEXT,
    defaultValue: "green",
  },
  age: DataTypes.INTEGER,
  cash: DataTypes.INTEGER,
});

(async () => {
  await sequelize.sync({ force: true });
  // Code here
})();

Creating an instance

Although a model is a class, you should not create instances by using the new operator directly. Instead, the build method should be used:

const jane = User.build({ name: "Jane" });
console.log(jane instanceof User); // true
console.log(jane.name); // "Jane"

However, the code above does not communicate with the database at all (note that it is not even asynchronous)! This is because the build method only creates an object that represents data that can be mapped to a database. In order to really save (i.e. persist) this instance in the database, the save method should be used:

await jane.save();
console.log("Jane was saved to the database!");

Note, from the usage of await in the snippet above, that save is an asynchronous method. In fact, almost every Sequelize method is asynchronous; build is one of the very few exceptions.

A very useful shortcut: the create method

Sequelize provides the create method, which combines the build and save methods shown above into a single method:

const jane = await User.create({ name: "Jane" });
// Jane exists in the database now!
console.log(jane instanceof User); // true
console.log(jane.name); // "Jane"

Note: logging instances

Trying to log a model instance directly to console.log will produce a lot of clutter, since Sequelize instances have a lot of things attached to them. Instead, you can use the .toJSON() method (which, by the way, automatically guarantees the instances to be JSON.stringify-ed well).

const jane = await User.create({ name: "Jane" });
// console.log(jane); // Don't do this
console.log(jane.toJSON()); // This is good!
console.log(JSON.stringify(jane, null, 4)); // This is also good!

Default values

Built instances will automatically get default values:

const jane = User.build({ name: "Jane" });
console.log(jane.favoriteColor); // "green"

Updating an instance

If you change the value of some field of an instance, calling save again will update it accordingly:

const jane = await User.create({ name: "Jane" });
console.log(jane.name); // "Jane"
jane.name = "Ada";
// the name is still "Jane" in the database
await jane.save();
// Now the name was updated to "Ada" in the database!

You can update several fields at once with the set method:

const jane = await User.create({ name: "Jane" });

jane.set({
  name: "Ada",
  favoriteColor: "blue",
});
// As above, the database still has "Jane" and "green"
await jane.save();
// The database now has "Ada" and "blue" for name and favorite color

Note that the save() here will also persist any other changes that have been made on this instance, not just those in the previous set call. If you want to update a specific set of fields, you can use update:

const jane = await User.create({ name: "Jane" });
jane.favoriteColor = "blue";
await jane.update({ name: "Ada" });
// The database now has "Ada" for name, but still has the default "green" for favorite color
await jane.save();
// Now the database has "Ada" for name and "blue" for favorite color

Deleting an instance

You can delete an instance by calling destroy:

const jane = await User.create({ name: "Jane" });
console.log(jane.name); // "Jane"
await jane.destroy();
// Now this entry was removed from the database

Reloading an instance

You can reload an instance from the database by calling reload:

const jane = await User.create({ name: "Jane" });
console.log(jane.name); // "Jane"
jane.name = "Ada";
// the name is still "Jane" in the database
await jane.reload();
console.log(jane.name); // "Jane"

The reload call generates a SELECT query to get the up-to-date data from the database.

Saving only some fields

It is 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, for example, when you get the values of an object via a form of a web app. Furthermore, this is used internally in the update implementation. This is how it looks like:

const jane = await User.create({ name: "Jane" });
console.log(jane.name); // "Jane"
console.log(jane.favoriteColor); // "green"
jane.name = "Jane II";
jane.favoriteColor = "blue";
await jane.save({ fields: ["name"] });
console.log(jane.name); // "Jane II"
console.log(jane.favoriteColor); // "blue"
// The above printed blue because the local object has it set to blue, but
// in the database it is still "green":
await jane.reload();
console.log(jane.name); // "Jane II"
console.log(jane.favoriteColor); // "green"

Change-awareness of save

The save method is optimized internally to only update fields that really changed. This means that if you don't change anything and call save, Sequelize will know that the save is superfluous and do nothing, i.e., no query will be generated (it will still return a Promise, but it will resolve immediately).

Also, if only a few attributes have changed when you call save, only those fields will be sent in the UPDATE query, to improve performance.

Incrementing and decrementing integer values

In order to increment/decrement values of an instance without running into concurrency issues, Sequelize provides the increment and decrement instance methods.

const jane = await User.create({ name: "Jane", age: 100 });
const incrementResult = await jane.increment("age", { by: 2 });
// Note: to increment by 1 you can omit the `by` option and just do `user.increment('age')`

// In PostgreSQL, `incrementResult` will be the updated user, unless the option
// `{ returning: false }` was set (and then it will be undefined).

// In other dialects, `incrementResult` will be undefined. If you need the updated instance, you will have to call `user.reload()`.

You can also increment multiple fields at once:

const jane = await User.create({ name: "Jane", age: 100, cash: 5000 });
await jane.increment({
  age: 2,
  cash: 100,
});

// If the values are incremented by the same amount, you can use this other syntax as well:
await jane.increment(["age", "cash"], { by: 2 });

Decrementing works in the exact same way.

Model Querying - Basics

Sequelize provides various methods to assist querying your database for data.

Important notice: to perform production-ready queries with Sequelize, make sure you have read the Transactions guide as well. Transactions are important to ensure data integrity and to provide other benefits.

This guide will show how to make the standard CRUD queries.

Simple INSERT queries

First, a simple example:

// Create a new user
const jane = await User.create({ firstName: "Jane", lastName: "Doe" });
console.log("Jane's auto-generated ID:", jane.id);

The Model.create() method is a shorthand for building an unsaved instance with Model.build() and saving the instance with instance.save().

It is also possible to define which attributes can be set in the create method. This can be especially useful 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 an username but not an admin flag (i.e., isAdmin):

const user = await User.create(
  {
    username: "alice123",
    isAdmin: true,
  },
  { fields: ["username"] }
);
// let's assume the default of isAdmin is false
console.log(user.username); // 'alice123'
console.log(user.isAdmin); // false

Simple SELECT queries

You can read the whole table from the database with the findAll method:

// Find all users
const users = await User.findAll();
console.log(users.every((user) => user instanceof User)); // true
console.log("All users:", JSON.stringify(users, null, 2));

SELECT * FROM ...

Specifying attributes for SELECT queries

To select only some attributes, you can use the attributes option:

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

SELECT foo, bar FROM ...

Attributes can be renamed using a nested array:

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

SELECT foo, bar AS baz, qux FROM ...

You can use sequelize.fn to do aggregations:

Model.findAll({
  attributes: [
    "foo",
    [sequelize.fn("COUNT", sequelize.col("hats")), "n_hats"],
    "bar",
  ],
});

SELECT foo, COUNT(hats) AS n_hats, bar FROM ...

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.n_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 (along with every column)
Model.findAll({
  attributes: [
    "id",
    "foo",
    "bar",
    "baz",
    "qux",
    "hats", // We had to list all attributes...
    [sequelize.fn("COUNT", sequelize.col("hats")), "n_hats"], // To add the aggregation...
  ],
});

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

SELECT id, foo, bar, baz, qux, hats, COUNT(hats) AS n_hats FROM ...

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

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

-- Assuming all columns are 'id', 'foo', 'bar', 'baz' and 'qux'
SELECT id, foo, bar, qux FROM ...

Applying WHERE clauses

The where option is used to filter the query. There are lots of operators to use for the where clause, available as Symbols from Op.

The basics

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

Observe that no operator (from Op) was explicitly passed, so Sequelize assumed an equality comparison by default. The above code is equivalent to:

const { Op } = require("sequelize");
Post.findAll({
  where: {
    authorId: {
      [Op.eq]: 2,
    },
  },
});
// SELECT * FROM post WHERE authorId = 2;

Multiple checks can be passed:

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

Just like Sequelize inferred the Op.eq operator in the first example, here Sequelize inferred that the caller wanted an AND for the two checks. The code above is equivalent to:

const { Op } = require("sequelize");
Post.findAll({
  where: {
    [Op.and]: [{ authorId: 12 }, { status: "active" }],
  },
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';

An OR can be easily performed in a similar way:

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

Since the above was an OR involving the same field, Sequelize allows you to use a slightly different structure which is more readable and generates the same behavior:

const { Op } = require("sequelize");
Post.destroy({
  where: {
    authorId: {
      [Op.or]: [12, 13],
    },
  },
});
// DELETE FROM post WHERE authorId = 12 OR authorId = 13;

Operators

Sequelize provides several operators.

const { Op } = require("sequelize");
Post.findAll({
  where: {
    [Op.and]: [{ a: 5 }, { b: 6 }],            // (a = 5) AND (b = 6)
    [Op.or]: [{ a: 5 }, { b: 6 }],             // (a = 5) OR (b = 6)
    someAttribute: {
      // Basics
      [Op.eq]: 3,                              // = 3
      [Op.ne]: 20,                             // != 20
      [Op.is]: null,                           // IS NULL
      [Op.not]: true,                          // IS NOT TRUE
      [Op.or]: [5, 6],                         // (someAttribute = 5) OR (someAttribute = 6)

      // Using dialect specific column identifiers (PG in the following example):
      [Op.col]: 'user.organization_id',        // = "user"."organization_id"

      // Number comparisons
      [Op.gt]: 6,                              // > 6
      [Op.gte]: 6,                             // >= 6
      [Op.lt]: 10,                             // < 10
      [Op.lte]: 10,                            // <= 10
      [Op.between]: [6, 10],                   // BETWEEN 6 AND 10
      [Op.notBetween]: [11, 15],               // NOT BETWEEN 11 AND 15

      // Other operators

      [Op.all]: sequelize.literal('SELECT 1'), // > ALL (SELECT 1)

      [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.startsWith]: 'hat',                  // LIKE 'hat%'
      [Op.endsWith]: 'hat',                    // LIKE '%hat'
      [Op.substring]: 'hat',                   // 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.any]: [2, 3],                        // ANY ARRAY[2, 3]::INTEGER (PG only)
      [Op.match]: Sequelize.fn('to_tsquery', 'fat & rat') // match text search for strings 'fat' and 'rat' (PG only)

      // In Postgres, Op.like/Op.iLike/Op.notLike can be combined to Op.any:
      [Op.like]: { [Op.any]: ['cat', 'hat'] }  // LIKE ANY ARRAY['cat', 'hat']

      // There are more postgres-only range operators, see below
    }
  }
});

Shorthand syntax for Op.in

Passing an array directly to the where option will implicitly use the IN operator:

Post.findAll({
  where: {
    id: [1, 2, 3], // Same as using `id: { [Op.in]: [1,2,3] }`
  },
});
// SELECT ... FROM "posts" AS "post" WHERE "post"."id" IN (1, 2, 3);

Logical combinations with operators

The operators Op.and, Op.or and Op.not can be used to create arbitrarily complex nested logical comparisons.

Examples with Op.and and Op.or

const { Op } = require("sequelize");

Foo.findAll({
  where: {
    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%'
  }
});

Examples with Op.not

Project.findAll({
  where: {
    name: "Some Project",
    [Op.not]: [
      { id: [1, 2, 3] },
      {
        description: {
          [Op.like]: "Hello%",
        },
      },
    ],
  },
});

The above will generate:

SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name` = 'Some Project'
  AND NOT (
    `Projects`.`id` IN (1,2,3)
    AND
    `Projects`.`description` LIKE 'Hello%'
  )
)

Advanced queries with functions (not just columns)

What if you wanted to obtain something like WHERE char_length("content") = 7?

Post.findAll({
  where: sequelize.where(
    sequelize.fn("char_length", sequelize.col("content")),
    7
  ),
});
// SELECT ... FROM "posts" AS "post" WHERE char_length("content") = 7

Note the usage of the sequelize.fn and sequelize.col methods, which should be used to specify an SQL function call and a table column, respectively. These methods should be used instead of passing a plain string (such as char_length(content)) because Sequelize needs to treat this situation differently (for example, using other symbol escaping approaches).

What if you need something even more complex?

Post.findAll({
  where: {
    [Op.or]: [
      sequelize.where(sequelize.fn("char_length", sequelize.col("content")), 7),
      {
        content: {
          [Op.like]: "Hello%",
        },
      },
      {
        [Op.and]: [
          { status: "draft" },
          sequelize.where(
            sequelize.fn("char_length", sequelize.col("content")),
            {
              [Op.gt]: 10,
            }
          ),
        ],
      },
    ],
  },
});

The above generates the following SQL:

SELECT
  ...
FROM "posts" AS "post"
WHERE (
  char_length("content") = 7
  OR
  "post"."content" LIKE 'Hello%'
  OR (
    "post"."status" = 'draft'
    AND
    char_length("content") > 10
  )
)

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

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

Deprecated: Operator Aliases

In Sequelize v4, it was possible to specify strings to refer to operators, instead of using Symbols. This is now deprecated and heavily discouraged, and will probably be removed in the next major version. If you really need it, you can pass the operatorAliases option in the Sequelize constructor.

For example:

const { Sequelize, Op } = require("sequelize");
const sequelize = new Sequelize("sqlite::memory:", {
  operatorsAliases: {
    $gt: Op.gt,
  },
});

// Now we can use `$gt` instead of `[Op.gt]` in where clauses:
Foo.findAll({
  where: {
    $gt: 6, // Works like using [Op.gt]
  },
});

Simple UPDATE queries

Update queries also accept the where option, just like the read queries shown above.

// Change everyone without a last name to "Doe"
await User.update(
  { lastName: "Doe" },
  {
    where: {
      lastName: null,
    },
  }
);

Simple DELETE queries

Delete queries also accept the where option, just like the read queries shown above.

// Delete everyone named "Jane"
await User.destroy({
  where: {
    firstName: "Jane",
  },
});

To destroy everything the TRUNCATE SQL can be used:

// Truncate the table
await User.destroy({
  truncate: true,
});

Creating in bulk

Sequelize provides the Model.bulkCreate method to allow creating multiple records at once, with only one query.

The usage of Model.bulkCreate is very similar to Model.create, by receiving an array of objects instead of a single object.

const captains = await Captain.bulkCreate([
  { name: "Jack Sparrow" },
  { name: "Davy Jones" },
]);
console.log(captains.length); // 2
console.log(captains[0] instanceof Captain); // true
console.log(captains[0].name); // 'Jack Sparrow'
console.log(captains[0].id); // 1 // (or another auto-generated value)

However, by default, bulkCreate does not run validations on each object that is going to be created (which create does). To make bulkCreate run these validations as well, you must pass the validate: true option. This will decrease performance. Usage example:

const Foo = sequelize.define("foo", {
  bar: {
    type: DataTypes.TEXT,
    validate: {
      len: [4, 6],
    },
  },
});

// This will not throw an error, both instances will be created
await Foo.bulkCreate([{ name: "abc123" }, { name: "name too long" }]);

// This will throw an error, nothing will be created
await Foo.bulkCreate([{ name: "abc123" }, { name: "name too long" }], {
  validate: true,
});

If you are accepting values directly from the user, it might be beneficial to limit the columns that you want to actually insert. To support this, bulkCreate() accepts a fields option, an array defining which fields must be considered (the rest will be ignored).

await User.bulkCreate([{ username: "foo" }, { username: "bar", admin: true }], {
  fields: ["username"],
});
// Neither foo nor bar are admins.

Ordering and Grouping

Sequelize provides the order and group options to work with ORDER BY and GROUP BY.

Ordering

The order option takes an array of items to order the query by or a sequelize method. These items are themselves arrays in the form [column, direction]. The column will be escaped correctly and the direction will be checked in a whitelist of valid directions (such as ASC, DESC, NULLS FIRST, etc).

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 createdAt using the model name as the association's name.
    [Task, "createdAt", "DESC"],

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

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

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

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

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

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

    // Will order by a nested associated model's createdAt 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(),
});

Foo.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 array can be the following:

• A string (which will be automatically quoted)
• An array, whose first element will be quoted, second will be appended verbatim

• An object with a raw field:

  • The content of raw will be added verbatim without quoting
  • Everything else is ignored, and if raw is not set, the query will fail
• A call to Sequelize.fn (which will generate a function call in SQL)
• A call to Sequelize.col (which will quoute the column name)

Grouping

The syntax for grouping and ordering are equal, except that grouping does not accept a direction as last argument of the array (there is no ASC, DESC, NULLS FIRST, etc).

You can also pass a string directly to group, which will be included directly (verbatim) into the generated SQL. Use with caution and don't use with user generated content.

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

Limits and Pagination

The limit and offset options allow you to work with limiting / pagination:

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

Usually these are used alongside the order option.

Utility methods

Sequelize also provides a few utility methods.

count

The count method simply counts the occurrences of elements in the database.

console.log(`There are ${await Project.count()} projects`);

const amount = await Project.count({
  where: {
    id: {
      [Op.gt]: 25,
    },
  },
});
console.log(`There are ${amount} projects with an id greater than 25`);

max, min and sum

Sequelize also provides the max, min and sum convenience methods.

Let's assume we have three users, whose ages are 10, 5, and 40.

await User.max("age"); // 40
await User.max("age", { where: { age: { [Op.lt]: 20 } } }); // 10
await User.min("age"); // 5
await User.min("age", { where: { age: { [Op.gt]: 5 } } }); // 10
await User.sum("age"); // 55
await User.sum("age", { where: { age: { [Op.gt]: 5 } } }); // 50

increment, decrement

Sequelize also provides the increment convenience method.

Let's assume we have a user, whose age is 10.

await User.increment({ age: 5 }, { where: { id: 1 } }); // Will increase age to 15
await User.increment({ age: -5 }, { where: { id: 1 } }); // Will decrease age to 5

Migrations:

A Migration in Sequelize is javascript file which exports two functions, up and down, that dictate how to perform the migration and undo it. You define those functions manually, but you don't call them manually; they will be called automatically by the CLI. In these functions, you should simply perform whatever queries you need, with the help of sequelize.query and whichever other methods Sequelize provides to you. There is no extra magic beyond that.

Installing the CLI

To install the Sequelize CLI:

npm install --save-dev sequelize-cli

For details see the CLI GitHub repository.

Project bootstrapping

To create an empty project you will need to execute init command

npx sequelize-cli 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 the CLI how to connect to the 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_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

Note that the Sequelize CLI assumes mysql by default. If you're using another dialect, you need to change the content of the "dialect" option.

Now edit this file and set correct database credentials and dialect. The keys of the objects (e.g. "development") are used on model/index.js for matching process.env.NODE_ENV (When undefined, "development" is a default value).

Sequelize will use the default connection port for each dialect (for example, for postgres, it is port 5432). If you need to specify a different port, use the "port" field (it is not present by default in config/config.js but you can simply add it).

Note: If your database doesn't exist yet, you can just call db:create command. With proper access it will create that database for you.

Creating the 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: the name of the model;
• attributes: the list of model attributes.

Let's create a model named User.

npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string

This will:

• 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 the required model and migration files for our first model, User. Now to actually create that table in the database you need to run db:migrate command.

npx sequelize-cli 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 the 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 the recent migration.

npx sequelize-cli db:migrate:undo

You can revert back to the initial state by undoing all migrations with the db:migrate:undo:all command. You can also revert back to a specific migration by passing its name with the --to option.

npx sequelize-cli db:migrate:undo:all --to XXXXXXXXXXXXXX-create-posts.js

Creating the first Seed

Suppose we want to insert some data into a few tables by default. If we follow up on the previous example we can consider creating a demo user for the 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 tables with sample or test data.

Let's create a seed file which will add a demo user to our User table.

npx sequelize-cli 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.

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('Users', [{
      firstName: 'John',
      lastName: 'Doe',
      email: 'example@example.com',
      createdAt: new Date(),
      updatedAt: new Date()
    }]);
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('Users', null, {});
  }
};

Running Seeds

In last step you created a seed file; however, it has not been committed to the database. To do that we run a simple command.

npx sequelize-cli db:seed:all

This will execute that seed file and a demo user will be inserted into the User table.

Note: Seeder execution history is not stored anywhere, unlike migrations, which use the SequelizeMeta table. If you wish to change this behavior, please read the 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 the most recent seed:

npx sequelize-cli db:seed:undo

If you wish to undo a specific seed:

npx sequelize-cli db:seed:undo --seed name-of-seed-as-in-data

If you wish to undo all seeds:

npx sequelize-cli db:seed:undo:all

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

We can generate this file using migration:generate. This will create xxx-migration-skeleton.js in your migration folder.

npx sequelize-cli migration:generate --name migration-skeleton

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.DataTypes.STRING,
      isBetaMember: {
        type: Sequelize.DataTypes.BOOLEAN,
        defaultValue: false,
        allowNull: false
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Person');
  }
};

The following is an example of a migration that performs two changes in the database, using an automatically-managed transaction to ensure that all instructions are successfully executed or rolled back in case of failure:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.transaction(t => {
      return Promise.all([
        queryInterface.addColumn('Person', 'petName', {
          type: Sequelize.DataTypes.STRING
        }, { transaction: t }),
        queryInterface.addColumn('Person', 'favoriteColor', {
          type: Sequelize.DataTypes.STRING,
        }, { transaction: t })
      ]);
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.transaction(t => {
      return Promise.all([
        queryInterface.removeColumn('Person', 'petName', { transaction: t }),
        queryInterface.removeColumn('Person', 'favoriteColor', { transaction: t })
      ]);
    });
  }
};

The next example is of a migration that has a foreign key. You can use references to specify a foreign key:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Person', {
      name: Sequelize.DataTypes.STRING,
      isBetaMember: {
        type: Sequelize.DataTypes.BOOLEAN,
        defaultValue: false,
        allowNull: false
      },
      userId: {
        type: Sequelize.DataTypes.INTEGER,
        references: {
          model: {
            tableName: 'users',
            schema: 'schema'
          },
          key: 'id'
        },
        allowNull: false
      },
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Person');
  }
}

The next example is of a migration that uses async/await where you create an unique index on a new column, with a manually-managed transaction:

module.exports = {
  async up(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    try {
      await queryInterface.addColumn(
        'Person',
        'petName',
        {
          type: Sequelize.DataTypes.STRING,
        },
        { transaction }
      );
      await queryInterface.addIndex(
        'Person',
        'petName',
        {
          fields: 'petName',
          unique: true,
          transaction,
        }
      );
      await transaction.commit();
    } catch (err) {
      await transaction.rollback();
      throw err;
    }
  },
  async down(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    try {
      await queryInterface.removeColumn('Person', 'petName', { transaction });
      await transaction.commit();
    } catch (err) {
      await transaction.rollback();
      throw err;
    }
  }
};

The next example is of a migration that creates an unique index composed of multiple fields with a condition, which allows a relation to exist multiple times but only one can satisfy the condition:

module.exports = {
  up: (queryInterface, Sequelize) => {
    queryInterface.createTable('Person', {
      name: Sequelize.DataTypes.STRING,
      bool: {
        type: Sequelize.DataTypes.BOOLEAN,
        defaultValue: false
      }
    }).then((queryInterface, Sequelize) => {
      queryInterface.addIndex(
        'Person',
        ['name', 'bool'],
        {
          indicesType: 'UNIQUE',
          where: { bool : 'true' },
        }
      );
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Person');
  }
}

The .sequelizerc file

This is a special configuration file. It lets you specify the following options that you would usually pass as arguments to CLI:

• env: The environment to run the command in
• config: The path to the config file
• options-path: The path to a JSON file with additional options
• migrations-path: The path to the migrations folder
• seeders-path: The path to the seeders folder
• models-path: The path to the models folder
• url: The database connection string to use. Alternative to using --config files
• debug: When available show various debug information

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.

To begin, let's create the .sequelizerc file in the root directory of your project, with the following content:

// .sequelizerc

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

The configuration file is by default a JSON file called config.json. But sometimes you need a dynamic configuration, for example to access environment variables or execute some other code to determine the configuration.

Thankfully, the Sequelize CLI can read from both .json and .js files. This can be setup with .sequelizerc file. You just have to provide the path to your .js file as the config option of your exported object:

const path = require('path');

module.exports = {
  'config': path.resolve('config', 'config.js')
}

Now the Sequelize CLI will load config/config.js for getting configuration options.

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',
    port: 3306,
    dialect: 'mysql',
    dialectOptions: {
      bigNumberStrings: true
    }
  },
  test: {
    username: process.env.CI_DB_USERNAME,
    password: process.env.CI_DB_PASSWORD,
    database: process.env.CI_DB_NAME,
    host: '127.0.0.1',
    port: 3306,
    dialect: 'mysql',
    dialectOptions: {
      bigNumberStrings: true
    }
  },
  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,
    port: process.env.PROD_DB_PORT,
    dialect: 'mysql',
    dialectOptions: {
      bigNumberStrings: true,
      ssl: {
        ca: fs.readFileSync(__dirname + '/mysql-ca-main.crt')
      }
    }
  }
};

The example above also shows how to add custom dialect options to the configuration.

Using Babel

To enable more modern constructions in your migrations and seeders, you can simply install babel-register and require it at the beginning of .sequelizerc:

npm i --save-dev babel-register

// .sequelizerc

require("babel-register");

const path = require('path');

module.exports = {
  'config': path.resolve('config', 'config.json'),
  'models-path': path.resolve('models'),
  'seeders-path': path.resolve('seeders'),
  'migrations-path': path.resolve('migrations')
}

Of course, the outcome will depend upon your babel configuration (such as in a .babelrc file). Learn more at babeljs.io.

Security tip

Use environment variables for config settings. This is because secrets such as passwords should never be part of the source code (and especially not committed to version 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. Also you can define a different schema for the SequelizeMeta table by providing the migrationStorageTableSchema property.

{
  "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",

    // Use a different schema for the SequelizeMeta table
    "migrationStorageTableSchema": "custom_schema"
  }
}

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:

npx sequelize-cli db:migrate --url 'mysql://root:password@mysql_host.com/database_name'

Programmatic usage

Sequelize has a sister library called umzug for programmatically handling execution and logging of migration tasks.