Sequelize 4 and 5 ES6 Cheat Sheet

This is a cheat sheet to ramp up your Sequelize productivity.

const { Model } = require("sequelize");
class MyModel extends Model {
  static init(sequelize, DataTypes) {
    return super.init(
      {
        myField: DataTypes.STRING
      },
      { sequelize }
    );
  }
}
module.exports = MyModel;

It’s like the better Sequelize docs, you’ve never had.

All the examples are written using ES6 JavaScript syntax, they’re real-world snippets for Sequelize for you to use.

This is beyond the introductory examples for Sequelize, used in the latest version of Node (hence async/await support and other ES6+ features).

The best way to navigate is to use the table of contents which is on the left on desktop and which you can jump to on mobile.

DataTypes

DataTypes contains both the types and generators for default values: eg. NOW, UUIDV1 and UUIDV4 are special default values.

The places where you’re likely to encounter DataTypes are in the model field definition and in the migration definitions. Note that for a given model and field, the data type should be the same in the model and in the migration.

Text types

STRING
- A variable length string.
- Default length 255.
- Supports BINARY
- Usage: a 100 length binary string DataTypes.STRING(100).BINARY
CHAR
- A fixed length string.
- Default length 255.
- Supports BINARY
- Usage: a 100 length binary char DataTypes.CHAR(100).BINARY
TEXT: An unlimited length text column

Default values

NOW: A default value of the current timestamp
UUIDV1: A default unique universal identifier generated following the UUID v1 standard
UUIDV4: A default unique universal identifier generated following the UUID v2 standard

Number

All the following support these properties: UNSIGNED, ZEROFILL.

eg.

DataTypes.INTEGER.UNSIGNED.ZEROFILL
// or 
DataTypes.INTEGER.ZEROFILL.UNSIGNED

The same can be done using BIGINT.UNSIGNED, FLOAT.UNSIGNED etc.

INTEGER: A 32 bit integer.
BIGINT: A 64 bit integer.
FLOAT: Floating point number (4-byte precision). Accepts one or two arguments for precision
REAL: Floating point number (4-byte precision). Accepts one or two arguments for precision
DOUBLE: Floating point number (8-byte precision). Accepts one or two arguments for precision
DECIMAL: Decimal number. Accepts one or two arguments for precision

Fancy primitive types

BOOLEAN: Boolean/tinyint column that gets coerced to a JavaScript Boolean.
UUID: A column storing a unique universal identifier, shape is validated, use with UUIDV1 or UUIDV4 default values

Date/Time

TIME: A time column
DATE: A datetime column
DATEONLY: A date only column

Fancy types

BLOB: Binary storage. Available lengths: tiny, medium, long eg. DataTypes.BLOG('tiny')
VIRTUAL
- A virtual value that is not stored in the DB. This could for example be useful if you want to provide a default value in your model that is returned to the user but not stored in the DB.
- See the docs

ENUM

An enumeration.
DataTypes.ENUM('value', 'another value')

Ideally should be used with strings stored in constants

const FIRST_ENUM_VALUE = 'FIRST_ENUM_VALUE';
const OTHER_ENUM_VALUE = 'OTHER_ENUM_VALUE';
// In migration or model definition
DataTypes.ENUM(FIRST_ENUM_VALUE, OTHER_ENUM_VALUE)

Postgres fancy types

HSTORE: A key/value column
JSON: A JSON string column.
JSONB: A pre-processed JSON data column.
RANGE: For Postgres 9.4+, range types are data types representing a range of values of some element type (called the range’s subtype).
ARRAY
- An array of type, e.g. DataTypes.ARRAY(DataTypes.DECIMAL)

Models

Aggregate all models under a db module

const Sequelize = require('sequelize');

const sequelizeOptions = {
  operatorsAliases: Sequelize.Op
};
const sequelize = new Sequelize(process.env.DATABASE_URL, sequelizeOptions);

const FirstModel = require('./first-model');
const OtherModel = require('./other-model');
const AnotherAssociatedModel = require('./another-associated-model');

const models = {
  First: FirstModel.init(sequelize, Sequelize),
  Other: OtherModel.init(sequelize, Sequelize),
  AnotherAssociated: AnotherAssociatedModel.init(sequelize, Sequelize)
};

// Run .associate if it exists, this creates the ORM's relationships
Object.values(models)
  .filter(model => typeof model.associate === 'function')
  .forEach(model => model.associate(models));

const db = {
  ...models,
  sequelize
};

module.exports = db;

About this approach:

models are imported “manually” and explicitly, this means a bit more wiring work whenever you add a new model. This cost is offset by the increased readability of regular import statements.

Individual model initialisation method

const { Model } = require("sequelize");
class MyModel extends Model {
  static init(sequelize, DataTypes) {
    return super.init(
      {
        myField: DataTypes.STRING
      },
      { sequelize }
    );
  }
}
module.exports = MyModel;

Running associations

Assuming your models are setup like in Aggregate all models under a db module.

const { Model } = require("sequelize");
class MyModel extends Model {
  static associate (models) {
    // associations can be defined here
    this.association = models.MyModel.hasMany(models.Associated);
    this.other = models.MyModel.belongsTo(models.Other);
  }
}

Setting a custom table name

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

const tableName = 'myModelTableName';

class MyModel extends Model {
  static init (sequelize, DataTypes) {
    return super.init(
      {},
      {
        sequelize,
        tableName
      }
    );
  }
}

module.exports = MyModel;

About this approach:

{ tableName } is using ES6 shorthand object notation.
Having the tableName be a const outside of the init method

Setting up a custom model name

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

const modelName = 'myCustomerModelName';

class MyModel extends Model {
  static init (sequelize, DataTypes) {
    return super.init(
      {},
      {
        sequelize,
        modelName
      }
    );
  }
}

module.exports = MyModel;

About this approach: see Setting a custom table name.

Querying

Create instance of (multiple-level) associated model

create{NestedModelName} like so:

const model = MyModel.findOne({
  where: { id: 'my-id' }
});
await model.createNestedModel({ /* some attributes */ });

// or

const model = MyModel.findOne({
  where: { id: 'my-id' },
  // This next line is quite important
  models: [ models.NestedModel ]
});
await model.nestedModel.createDoublyNestedModel({ /* some attributes */ });

// associations setup
class DoublyNestedModel extends Model {
  static associate (models) {
    this.nestedModel = models.DoublyNestedModel.belongsTo(models.NestedModel);
  }
}
class NestedModel extends Model {
  static associate (models) {
    this.myModel = models.NestedModel.hasOne(models.MyModel);
    this.doublyNestedModel = models.NestedModel.hasMany(models.DoubleNestedModel);
  }
}
class MyModel extends Model {
  static associate (models) {
    this.nestedModel = models.MyModel.belongsTo(models.NestedModel);
  }
}

Order by (multiple-level) nested association model field

Remember to fill out attributes and any further includes.

class MyModel extends Model {
  static getNotificationsDesc(where) {
    return this.getAssociation({
       where,
       // attributes: [],
       order: [ [ models.Relation, models.NestedRelation, 'createdAt', 'DESC' ]]
       include: {
         model: models.Relation,
         // attributes: [],
         include: {
           model: models.NestedRelation,
           // attributes: [],
           // include: {
           //   model: models.TwoNestedRelation,
           //   attributes: []
           // }
         }
       }
    });
  }
}

Fetch latest of association by field

The following gets the most recently created association (using createdAt).

To do something similar for most recently updated association would necessitate replacing createdAt with updatedAt.

This also assumes the model at hand has createdAt and updatedAt fields.

class MyModel extends Model {
  async getLatestAssocationRecord() {
    const [ firstRecord ] = await this.getAssociations({
      order: [ [ 'createdAt', 'DESC'] ],
      limit: 1
    });
    return firstRecord;
  }
}

Fetch one of association

This approach should usually be complemented by an order clause, see Fetch latest of association by field

class MyModel extends Model {
  async first() {
    const [ firstRecord ] = await this.getAssociations({
      // order: [],
      limit: 1
    });
    return firstRecord;
  }
}

Hydrate an association load without eager loading it

class MyModel extends Model {
  static associate (models) {
    this.association = models.MyModel.belongsTo(models.Association);
    // or
    this.association = models.MyModel.hasOne(models.Association);
}

myModelInstance.association = await myModelInstance.getAssociation();

Hydrate a 1-many association load without eager loading

class MyModel extends Model {
  static associate (models) {
    this.associations = models.MyModel.hasMany(models.Association);
  }
}

myModelInstance.associations = await myModelInstance.getAssociations();

Leveraging transactions

Transactions are crucial to making sure your database doesn’t end up in a weird state where part of an action has been done (eg. a user has been created) but other fields that are required for correct operation aren’t (eg. shooter isn’t set), here’s how to write ES6 with transactions:

sequelize.transaction(async transaction => {
  const user = await User.create({
    firstName: 'Abraham',
    lastName: 'Lincoln'
  }, { transaction });
  return user.setShooter({
    firstName: 'John',
    lastName: 'Boothe'
  }, { transaction });
});

Select if association meets a criteria

required: true is the key, if that wasn’t present, the instances could contain { association: null }. For the full list of operators, see the Sequelize documentation for operators

const someCriteria = {
    data: {
        [Op.not]: null
    }
};
const someOtherCriteria = {
    id: {
        [Op.gt]: 5
    }
};

const myModels = await db.MyModel.findAll({
    attributes: ['id', 'associationId' ],
    include: {
      association: db.MyModel.association,
      attributes: ['id'],
      required: true,
      include: {
        association: db.Association.nestedAssociations,
        attributes: ['id', 'data' ],
        where: {
          [Op.and]: [
            someCriteria,
            someOtherCriteria,
          ]
        }
      }
    }
  });

Migrations and data model

Create model and add index

With a foreign key constraint set to CASCADE.

module.exports = {
  async up (sequelize, DataTypes) {
    await sequelize.createTable('myModelPlural', {
      id: {
        allowNull: false,
        primaryKey: true,
        
        autoIncrement: true,
        type: DataTypes.INTEGER
        // or 
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4
      },
      relationId: {
        allowNull: false,
        type: DataTypes.INTEGER,
        references: {
          model: 'relationPlural',
          key: 'id'
        },
        onDelete: 'CASCADE'
      },
      createdAt: {
        allowNull: false,
        type: DataTypes.DATE
      },
      updatedAt: {
        allowNull: false,
        type: DataTypes.DATE
      }
    });
    return sequelize.addIndex('myModelPlural', ['relationId']);
  },
  down (sequelize) {
    return sequelize.dropTable('myModelPlural');
  }
};

Add column

module.exports = {
  up (sequelize, DataTypes) {
    return sequelize.addColumn('myModelPlural', 'columnName', {
      type: DataTypes.STRING,
      allowNull: false
    });
  },
  down (sequelize, DataTypes) {
    return sequelize.removeColumn('myModelPlural', 'columnName');
  }
};

Add compound unique constraint

A compound unique constraint is a constraint that an entity can’t have the same value for all the fields in the constraint at the same time.

module.exports = {
  up (sequelize, DataTypes) {
    return sequelize.addConstraint('myModelPlural', ['relatedId', 'otherRelatedId'], {
      type: 'unique',
      name: 'my_model_plural_related_id_other_related_id_unique_constraint'
    });
  },
  down (sequelize, DataTypes) {
    return sequelize.removeConstraint(
      'myModelPlural',
      'my_model_plural_related_id_other_related_id_unique_constraint'
    );
  }
};

Add index to table

module.exports = {
  up (sequelize) {
    return sequelize.addIndex('myModelPlural', ['relatedId'], /* options */);
  },
  down (sequelize) {
    return sequelize.removeIndex('myModelPlural', ['relatedId'], /* options */);
  }
};

CLI

For the following examples to make sense/work, install my fork of the sequelize CLI with:

npm i --save @hugodf/sequelize-cli

You can read about what extras are in the fork in “Progress and roadblocks: a journey into open source”

Create migration

npx sequelize migration:create --name my-migration-name

--name is required

Create model

npx sequelize model:create --name MyModel --attributes foo:string --class

--name and --attributes are required
--class will create ES6 class models instead of sequelize.define()-type ones

Advanced Configuration

Custom path for models/migrations/config

.sequelizerc, for this to work, you need a src directory with models and migrations subdirectories, as well as a config/database.js file (see Set up connection config from a database URL environment variable)

const path = require('path');
module.exports = {
  'config': path.resolve('config', 'database.js'),
  'models-path': path.resolve('src', 'models'),
  'migrations-path': path.resolve('src', 'migrations')
};

Set up connection config from a database URL environment variable

config/database.js

const url = require('url');

function extractSequelizeDatabaseConfigFromUrl (databaseUrl) {
  if (!databaseUrl) {
    throw new Error('No DATABASE_URL environment variable is set');
  }
  const parsedUrl = url.parse(databaseUrl);
  const [username, password] = parsedUrl.auth.split(':');
  const { hostname: host } = parsedUrl; // hostname doesn't contain the port
  const database = parsedUrl.path.replace(/\//g, '');
  const dialect = parsedUrl.protocol.replace(':', ''); // this should be postgres
  return {
    username,
    password,
    database,
    host,
    dialect
  };
}

module.exports = extractSequelizeDatabaseConfigFromUrl(
  process.env.DATABASE_URL
);