Detailed and easy to use Sequelize interpretation

This article is from Github repo Demopark/Sequelize-docs-zh-cn. This article mainly focuses on mysql. For other dB databases, refer to the original documentation.

In the Node.js community, Sequelize is a widely used Object Relational Mapping (ORM) framework that supports multiple data sources such as MySQL, PostgreSQL, SQLite, and MSSQL.

Sequelize document

• English Document (Official DOC)
• 英 文文档(demopark/ sequelize-docs-zh-cn)

Getting started – Getting started

The installation

// Install via NPM
npm install --save sequelize
Copy the code

You also need to manually install the corresponding database driver:

# Select the corresponding installation: $ 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 ServerCopy the code

Establish a connection

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

const Sequelize = require('sequelize');

// Method 1: Pass the argument separately
const sequelize = new Sequelize('database'.'username'.'password', {
  host: 'localhost'.dialect: / * 'mysql' | 'mariadb' | 'postgres' |' MSSQL one of * /
});

// Method 2: Pass the connection URI
const sequelize = new Sequelize('postgres://user:[email protected]:5432/dbname');
Copy the code

Connection pool (production environment)

If you are connecting to a database from a single process, you should create only one Sequelize instance. Sequelize sets the connection pool at initialization. This connection pool can be configured using the options parameter of the constructor (using options.pool), as shown in the following example:

const sequelize = new Sequelize(/ *... * /, {
  // ...
  pool: {
    max: 5.min: 0.acquire: 30000.idle: 10000}});Copy the code

If you connect to a database from multiple processes, you must create one instance for each process, but each instance should have a maximum connection pool size to comply with the overall maximum size. For example, if you want the maximum connection pool size to be 90 and you have three processes, the maximum connection pool size for each Sequelize instance of the process should be 30.

Test the connection

.authenticate()

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

Close the connection

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

The table model

A Model is a class that inherits from Sequelize.model and can be defined in two ways,

Sequelize.Model.init(attributes, options)

init

const Model = Sequelize.Model;
class User extends Model {}
User.init({
  // attributes
  firstName: {
    type: Sequelize.STRING,
    allowNull: false
  },
  lastName: {
    type: Sequelize.STRING
    // allowNull defaults to true
  }
}, {
  sequelize,
  modelName: 'user'
  // options
});
Copy the code

sequelize.define

const User = sequelize.define('user', {
  // attributes
  firstName: {
    type: Sequelize.STRING,
    allowNull: false
  },
  lastName: {
    type: Sequelize.STRING
    // allowNull defaults to true}}, {// options
});
Copy the code

Sequelize also defines the field ID (primary key), createdAt, and updatedAt for each model by default.

Change the default model parameters

const sequelize = new Sequelize(connectionURI, {
  define: {
    The 'timestamps' field specifies whether the' createdAt 'and' updatedAt 'fields will be created.
    // This value defaults to true, but is currently set to false
    timestamps: false}});// Here 'timestamps' is false, so' createdAt 'and' updatedAt 'fields are not created.
class Foo extends Model {}
Foo.init({ / *... * / }, { sequelize });

// Here 'timestamps' is set to true directly, so the' createdAt 'and' updatedAt 'fields are created.
class Bar extends Model {}
Bar.init({ / *... * / }, { sequelize, timestamps: true });

Copy the code

The Dialect Dialect

In new Sequelize(DB, username, password, options), you need to specify dialect in options.

MySQL

In order for Sequelize to work well with MySQL, you need to have mysql2@^1.5.2 or later installed

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

MariaDB

const sequelize = new Sequelize('database'.'username'.'password', {
  dialect: 'mariadb'.dialectOptions: {connectTimeout: 1000} // mariadb connection parameters
})
Copy the code

or

const sequelize = new Sequelize('mariadb://user:[email protected]:9821/database')
Copy the code

SQLite

const sequelize = new Sequelize('database'.'username'.'password', {
  // sqlite!
  dialect: 'sqlite'.// SqLite storage engine
  // - default ':memory:'
  storage: 'path/to/database.sqlite'
})
Copy the code

or

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

PostgreSQL

PostgreSQL requires two libraries, pg-@ ^7.0.0 and Pg-hStore

const sequelize = new Sequelize('database'.'username'.'password', {
 // postgres!
 dialect: 'postgres'
})
Copy the code

MSSQL

Install the tedious @ ^ 6.0.0

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

Datatypes – Datatypes

CHAR

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

NUMBER

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

Sequelize.FLOAT                       // FLOAT
Sequelize.FLOAT(11)                   // FLOAT(11)
Sequelize.FLOAT(11.10)               / / FLOAT (11, 10)

Sequelize.DOUBLE                      // DOUBLE
Sequelize.DOUBLE(11)                  // DOUBLE(11)
Sequelize.DOUBLE(11.10)              / / DOUBLE (11, 10)

Sequelize.DECIMAL                     // DECIMAL
Sequelize.DECIMAL(10.2)              / / a DECIMAL (1, 2)
Copy the code

TIME

Sequelize.DATE                        Mysql/sqlite is DATETIME, postgres is TIMESTAMP with time zone
Sequelize.DATE(6)                     // DATETIME(6) applies to mysql 5.6.4+. Decimal seconds support up to 6 bits of accuracy
Sequelize.DATEONLY                    // DATE does not contain time.
Copy the code

BOOLEAN

Sequelize.BOOLEAN                     // TINYINT(1)
Copy the code

ENUM

Sequelize.ENUM('value 1'.'value 2')  // An ENUM allowed with 'value 1' and 'value 2'
Copy the code

blob

Sequelize.BLOB                        // BLOB (PostgreSQL for bytea)
Sequelize.BLOB('tiny')                // TINYBLOB (PostgreSQL for bytea. The remaining parameters are medium and long.
Copy the code

GEOMETRY

Sequelize.GEOMETRY                    / / Spatial column. Only PostgreSQL (with PostGIS) or MySQL.
Sequelize.GEOMETRY('POINT')           // Spatial columns with geometry type. Only PostgreSQL (with PostGIS) or MySQL.
Sequelize.GEOMETRY('POINT'.4326)     // Spatial columns with geometry type and SRID. Only PostgreSQL (with PostGIS) or MySQL.
Copy the code

Integer, BigInt, float, and double also support unsigned and Zerofill properties

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

Object representation

// For enumeration:
class MyModel extends Model {}
MyModel.init({
  states: {
    type: Sequelize.ENUM,
    values: ['active'.'pending'.'deleted']
  }
}, { sequelize })
Copy the code

Model definition – Model definition

Define the mapping between the model and the table, using the DEFINE method.

// The Model is mounted on Sequelize,
// const Sequelize = require('sequelize');
// const Model = Sequelize.Model;
class Project extends Model {}
Project.init({
  title: Sequelize.STRING,
  description: Sequelize.TEXT
}, { sequelize, modelName: 'project' });
Copy the code

Model definition example

class Foo extends Model {}
Foo.init({
 // If no value is assigned, the value is automatically set to TRUE
 flag: { type: Sequelize.BOOLEAN, allowNull: false.defaultValue: true},

 // Set the default time to the current time
 myDate: { type: Sequelize.DATE, defaultValue: Sequelize.NOW },

 // Setting allowNull to false adds NOT NULL to the column,
 // This means that an error will be thrown from DB when the query is executed when the column is empty.
 // If you want to check that the value is not null before querying DB, see the validation section below.
 title: { type: Sequelize.STRING, allowNull: false},

 // Creating two objects with the same value throws an error. The only attribute can be a Boolean value or a string.
 // If multiple columns are supplied with the same string, they form a compound unique key.
 uniqueOne: { type: Sequelize.STRING,  unique: 'compositeIndex'},
 uniqueTwo: { type: Sequelize.INTEGER, unique: 'compositeIndex'},

 // The unique attribute is used to create a unique constraint.
 someUnique: {type: Sequelize.STRING, unique: true},
 
 // This is exactly the same as creating an index in the model option.
 {someUnique: {type: Sequelize.STRING}},
 {indexes: [{unique: true.fields: ['someUnique']}]},

 // primaryKey is used to define the primaryKey.
 identifier: { type: Sequelize.STRING, primaryKey: true},

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

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

 // Create a foreign key:
 bar_id: {
   type: Sequelize.INTEGER,

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

     // This is the column name that refers to the model
     key: 'id'.// This declares when to check foreign key constraints. PostgreSQL only.
     deferrable: Sequelize.Deferrable.INITIALLY_IMMEDIATE
   }
 },

 // Only MySQL,PostgreSQL and MSSQL columns can be commented
 commentMe: {
   type: Sequelize.INTEGER,

   comment: 'This is the name of a column with comments'
 }
}, {
  sequelize,
  modelName: 'foo'
});
Copy the code

The time stamp

By default,Sequelize adds createdAt and updatedAt properties to the model so that you know when a database entry is in the database and when it is updated. If you do not want to add automatically, define the following:

const sequelize = new Sequelize(connectionURI, {
  define: {
    The 'timestamps' field specifies whether the' createdAt 'and' updatedAt 'fields will be created.
    // This value defaults to true, but is currently set to false
    timestamps: false}});Copy the code

If you are migrating using Sequelize, you need to add createdAt and updatedAt fields to the migration definition:

module.exports = {
  up(queryInterface, Sequelize) {
    return queryInterface.createTable('my-table', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true.autoIncrement: true,},/ / timestamp
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE,
    })
  },
  down(queryInterface, Sequelize) {
    return queryInterface.dropTable('my-table'); }},Copy the code

Getters & Setters

Getters and Setters can be defined in two ways (you can mix the two):

• As part of the property definition
• As part of the model parameters

Note: If a getter or setter is defined in both places, the function found in the relevant property definition always takes precedence.

Is part of the property definition

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

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
  })
Copy the code

Is defined as part of the model parameters

class Foo extends Model {
  get fullName() {
    return this.firstname + ' ' + this.lastname;
  }

  set fullName(value) {
    const names = value.split(' ');
    this.setDataValue('firstname', names.slice(0.- 1).join(' '));
    this.setDataValue('lastname', names.slice(- 1).join(' '));
  }
}
Foo.init({
  firstname: Sequelize.STRING,
  lastname: Sequelize.STRING
}, {
  sequelize,
  modelName: 'foo'
});

// Or use 'sequelize.define'
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(' ')); }} instead! [] ()});Copy the code

Defines internal Helper methods for getters and setters

• Retrieves underlying property values – always usedthis.getDataValue()

/* A getter for the 'title' property */
get() {
  return this.getDataValue('title')}Copy the code

• Set base property values – always usedthis.setDataValue()

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

Attribute validator

class ValidateMe extends Model {}
ValidateMe.init({
  bar: {
    type: Sequelize.STRING,
    validate: {
      is: ["^[a-z]+$".'i'].// Only letters are allowed
      is: /^[a-z]+$/i.// As in the previous example, real regular expressions are used
      not: ["[a-z]".'i'].// Letters are not allowed
      isEmail: true.// Check the email format ([email protected])
      isUrl: true.// Check the connection format (http://foo.com)
      isIP: true.// Check the IPv4 (129.89.23.1) or IPv6 format
      isIPv4: true.// Check the IPv4 (129.89.23.1) format
      isIPv6: true.// Check the IPv6 format
      isAlpha: true.// Only letters are allowed
      isAlphanumeric: true.// Only alphanumeric characters are allowed
      isNumeric: true.// Only numbers are allowed
      isInt: true.// Check if it is a valid integer
      isFloat: true.// Check if it is a valid floating point number
      isDecimal: true.// Check if it is an arbitrary number
      isLowercase: true.// Check if it is lowercase
      isUppercase: true.// Check if it is uppercase
      notNull: true.// Null is not allowed
      isNull: true.// Only null is allowed
      notEmpty: true.// Empty strings are not allowed
      equals: 'specific value'.// Only one specific value is allowed
      contains: 'foo'.// Check to see if a specific substring is included
      notIn: [['foo'.'bar']],  // Check if the value is not one of them
      isIn: [['foo'.'bar']],   // Check if the value is one of them
      notContains: 'bar'.// Specific substrings are not allowed
      len: [2.10].// Only values between 2 and 10 are allowed
      isUUID: 4.// Only uuids are allowed
      isDate: true.// Only date strings are allowed
      isAfter: "2011-11-05".// Only date strings after a specific date are allowed
      isBefore: "2011-11-05".// Only date strings prior to a specific date are allowed
      max: 23.// Only <= 23 is allowed
      min: 23.// Only values >= 23 are allowed
      isCreditCard: true.// Check for valid credit card numbers

      // Example of a custom validator:
      isEven(value) {
        if (parseInt(value) % 2! = =0) {
          throw new Error('Only even values are allowed! ');
        }
      }
      isGreaterThanOtherField(value) {
        if (parseInt(value) <= parseInt(this.otherField)) {
          throw new Error('Bar must be greater than otherField.');
        }
      }
    }
  }
}, { sequelize });
Copy the code

class Pub extends Model {}
Pub.init({
  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')
      }
    }
  },
  sequelize,
})
Copy the code

configuration

class Bar extends Model {}
Bar.init({ /* bla */ }, {
  // The model name. The model will be stored in 'sequelize.models' with this name.
  // In this case, the default is the class name, Bar.
  // this controls the automatically generated foreignKey and the name of the associated name
  modelName: 'bar'.// Do not add timestamp attribute (updatedAt, createdAt)
  timestamps: false.// Do not delete the database entry, but set the newly added property deletedAt to the current date (when the deletion is complete).
  // Paranoid only works if the timestamp is enabled
  paranoid: true.// Field parameters for all attributes are automatically set to underline.
  // Field options already defined will not be overridden
  underscored: true.// Disable table name change; By default, Sequelize automatically converts all passed model names (the first parameter of define) to complex numbers. If you don't want to do this, set the following
  freezeTableName: true.// Define the name of the table
  tableName: 'my_very_custom_table_name'.// Enable optimistic locking. When enabled, Sequelize adds version count attributes to the model,
  // And raise OptimisticLockingError when saving an outdated instance.
  // A string set to true or with the name of the property to be enabled.
    version: true./ / Sequelize instance
  sequelize,
})
Copy the code

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

class Foo extends Model {}
Foo.init({ /* bla */ }, {
  // Don't forget to enable timestamps!
  timestamps: true.// I don't want createdAt
  createdAt: false.// I think updateAt is actually called updateTimestamp
  updatedAt: 'updateTimestamp'.// And want deletedA t to be called destroyTime(remember to enable paranoid for this to work)
  deletedAt: 'destroyTime'.paranoid: true,

  sequelize,
})
Copy the code

You can also change the database engine, for example to MyISAM. The default is InnoDB.

class Person extends Model {}
Person.init({ /* attributes */ }, {
  engine: 'MYISAM',
  sequelize
})

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

You can specify annotations for tables in MySQL and PG

class Person extends Model {}
Person.init({ /* attributes */ }, {
  comment: "I'm a table comment!",
  sequelize
})
Copy the code

The import

You can also use the import method to store model definitions in a single file. The object returned is exactly the same as defined in the import file functionality. Because the sequelizEV1:5.0 imports are cached, there are no problems when file imports are called two or more times.

// In your server file - for example app.js
const Project = sequelize.import(__dirname + "/path/to/models/project")

Already in the/path/to / / model/models/project. Js defined in the well
// You may notice that DataTypes is the same as above
module.exports = (sequelize, DataTypes) = > {
  class Project extends sequelize.Model { }
  Project.init({
    name: DataTypes.STRING,
    description: DataTypes.TEXT
  }, { sequelize });
  return Project;
}
Copy the code

Import methods can also accept callbacks as arguments.

sequelize.import('project', (sequelize, DataTypes) => {
  class Project extends sequelize.Model {}
  Project.init({
    name: DataTypes.STRING,
    description: DataTypes.TEXT
  }, { sequelize })
  return Project;
})
Copy the code

Optimistic locking

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.

Optimistic locking allows concurrent access to model records for editing and prevents conflicting overwriting of data. It makes changes by checking whether another process has read the record and throws an OptimisticLockError if a conflict is detected.

Querying – query

attribute

Model.findAll({
  attributes: ['foo'.'bar']});// SELECT foo, bar ...
Copy the code

Properties can be renamed using nested arrays:

Model.findAll({
  attributes: ['foo'['bar'.'baz']]});// SELECT foo, bar AS baz ...
Copy the code

You can also use sequelize.fn for aggregation:

Model.findAll({
  attributes: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]});// SELECT COUNT(hats) AS no_hats ...
Copy the code

For detailed functions, please see:

When you use the aggregation feature, you must give it an alias so that it can be accessed from the model. In the example above, you can use instance.get(‘no_hats’) to get the number of hats.

Sometimes, listing all the attributes of the model can be annoying if you just want to add 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 ...
Copy the code

Also, it can exclude some specified table fields:

Model.findAll({
  attributes: { exclude: ['baz']}});// SELECT id, foo, bar, quz ...
Copy the code

Where

You can pass a WHERE object to filter the query. Where typically uses attribute:value key-value pairs to obtain an object, where value can be data matching an equation or a key-value object for other operators, or complex AND/ or conditions can be generated by nesting collections of OR and AND operators.

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;
Copy the code

The operator

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.// id > 6
[Op.gte]: 6.// id >= 6
[Op.lt]: 10.// id < 10
[Op.lte]: 10.// id <= 10
[Op.ne]: 20.// id ! = 20
[Op.eq]: 3./ / = 3
[Op.not]: true./ / it isn't 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'./ / contains' % hat '
[Op.notLike]: '%hat'       // does not contain '%hat'
[Op.iLike]: '%hat'         // contains '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat'      // does not contain '%hat' (PG only)
[Op.startsWith]: 'hat'     / / similar 'hat %'
[Op.endsWith]: 'hat'       Hat / / similar to the '%'
[Op.substring]: 'hat'      / / like '% % hat'
[Op.regexp]: '^[h|a|t]'    / / match the regular expression / ~ '^ t] [h | a |' (only MySQL/PG)
[Op.notRegexp]: '^[h|a|t]' // Does not match the regular expression /! ~ ' '^ t] [h | a |' (only MySQL/PG)
[Op.iRegexp]: '^[h|a|t]'    / / ~ * '^ t] [h | a |' (PG) only
[Op.notIRegexp]: '^[h|a|t]' / /! ~ * '^ t] [h | a |' (PG) only
[Op.like]: { [Op.any]: ['cat'.'hat']} // include any array ['cat', 'hat'] - also applies to iLike and notLike
[Op.overlap]: [1.2]       // && [1, 2] (PG array overlap operator)
[Op.contains]: [1.2]      // @> [1, 2] (PG array contains operators)
[Op.contained]: [1.2]     // <@ [1, 2] (PG array contained in operator)
[Op.any]: [2.3]            // Any array [2, 3]::INTEGER (PG only)

[Op.col]: 'user.organization_id' // = 'user'.'organization_id', using the database language-specific column identifier, PG in this example
Copy the code

combination

{
  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 - 1d]

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

Operator alias

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

[Op.gt]: 6 / / > 6
$gt: 6 // Equivalent to using op. gt (> 6)
Copy the code

Operator security

By default,Sequelize uses the Symbol operator. Using Sequelize without any aliases improves security. No string alias will minimize the likelihood that the operator will be injected, but you should always validate and clean up user input correctly.

For better security, it is strongly recommended to use symbolic operators in sequelize.op, such as op. and/op. or, in your code, and not rely on any string-based operators, such as $and / $OR. You can limit the aliases required by your application by setting the operatorsAliases parameter,

If you want to continue using all default aliases (excluding older aliases) without warning, you can pass the following operator arguments –

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

Copy the code

Limit, offset

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

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

// Skip 5 instances, then take 5
Project.findAll({ offset: 5.limit: 5 })
Copy the code

The order (sort)

Order requires an array of items to sort the query or a sequelize method. In general, you’ll use any of the tuple/array attributes and determine the reverse and reverse directions of the sort.

Subtask.findAll({
  order: [
    // The title will be escaped and DESC will be validated against a list of valid direction parameters
    ['title'.'DESC'].// Sort by maximum value (age)
    sequelize.fn('max', sequelize.col('age')),

    // Will be in maximum order (age) DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'].// Will sort (' col1 ', 12, 'lalala') DESC by otherfunction
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12.'lalala'), 'DESC'].// CreATED_AT of the associated model will be sorted using the model name as the associated 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'].// The associated name will be used and sorted by the created_AT of the associated model.
    ['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.
    [Subtask.associations.Task, 'createdAt'.'DESC'].// Will order by a nested associated model's created_at using association objects.
    [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'].Nested correlation model created_AT simple correlation object sort
    [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt'.'DESC']]// Will be sorted in descending order by maximum age
  order: sequelize.literal('max(age) DESC')

  // The order is sorted in ascending order by the oldest value. The default is ascending order when the sorting condition is omitted
  order: sequelize.fn('max', sequelize.col('age'))

  // Sorting in ascending order is the default order for omitting sorting conditions
  order: sequelize.col('age')
  
  // Will randomly sort by dialect (instead of fn('RAND') or FN ('RANDOM'))
  order: sequelize.random()
})
Copy the code

Common Query methods

findAll

Model.findAll({
  where: {
    attr1: 42.attr2: 'cake'}})// WHERE attr1 = 42 AND attr2 = 'cake'


Model.findAll({
  where: {
    attr1: {
      gt: 50
      },
    attr2: {
      lte: 45
    },
    attr3: {
      in: [1.2.3]},attr4: {
      ne: 5}}})// WHERE attr1 > 50 AND attr2 <= 45 AND attr3 IN (1,2,3) AND attr4 != 5

Model.findAll({
  where: Sequelize.and(
    { name: 'a project' },
    Sequelize.or(
      { id: [1.2.3] {},id: { gt: 10}}))})// WHERE name = 'a project' AND (id` IN (1,2,3) OR id > 10)

Copy the code

findByPk

Model.findByPk(1)
Copy the code

findOne

aggregate

count

findAndCountAll

max

min

sum

create

findOrCreate

upsert

destroy

update