全面的 Sequelize 备忘单目录安装依赖项设置连接定义模型关联实例使用模型查看我的博客仓库

2025-05-26

全面的 Sequelize 备忘单

目录

安装依赖项

建立连接

定义模型

协会

实例

使用模型

查看我的博客仓库

Sequelize 是最著名的 Node ORM，功能相当丰富，但在使用它的时候，我花了很多时间在文档和各种谷歌搜索之间徘徊。
这份 Cheatsheet 正是我一直想要的，但却一直找不到。
发现任何错误或缺失的内容？请在下方评论，或者更好的是，向文末链接的仓库发送拉取请求。

目录

  1. 安装依赖项
    1. 安装 Sequelize
    2. 安装数据库驱动程序
  2. 建立连接
    1. 实例创建
    2. 测试连接
    3. 关闭连接
  3. 定义模型
    1. 基本定义
    2. 扩展列定义
      1. 基本扩展
      2. 复合唯一键
      3. Getter 和 Setter
      4. 验证
        1. 每个属性验证
        2. 模型范围验证
      5. 时间戳
      6. 数据库同步
      7. 模型扩展
      8. 索引
  4. 协会
    1. 定义关联
      1. hasOne
      2. 属于
      3. hasMany
      4. 属于多个
    2. 关系
      1. 一对一
      2. 一对多
      3. 多对多
  5. 实例
    1. 创建实例
      1. 建造
      2. 创造
    2. 变异实例
      1. 更新
      2. 删除
  6. 使用模型
    1. 方法
      1. findByPk
      2. 找到一个
      3. 查找或创建
      4. 查找全部
      5. findAndCountAll
      6. 数数
      7. 最大限度
      8. 分钟
      9. 和
    2. 过滤
      1. 在哪里
        1. 运算符
      2. 命令
      3. 分页和限制
  7. 我没有包含在这份备忘单中的内容（带有官方文档的链接）
    1. 钩子
    2. 交易
    3. 作用域
    4. 原始查询
    5. 预先加载

安装依赖项

安装 Sequelize



npm install --save sequelize
Enter fullscreen mode Exit fullscreen mode

安装数据库驱动程序

您还需要安装您正在使用的数据库的驱动程序。



# One of the following:
npm install --save pg pg-hstore # Postgres If node version < 14 use pg@7.12.1 instead
npm install --save mysql2
npm install --save mariadb
npm install --save sqlite3
npm install --save tedious # Microsoft SQL Server
Enter fullscreen mode Exit fullscreen mode

建立连接

必须创建一个 Sequelize 实例才能连接到数据库。默认情况下，此连接保持打开并用于所有查询，但可以显式关闭。

实例创建



const Sequelize = require('sequelize');

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

// Option 2: Passing a connection URI
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');

// For SQLite, use this instead
const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'path/to/database.sqlite'
});
Enter fullscreen mode Exit fullscreen mode

有关连接不同方言的更多详细信息，请查看官方文档

测试连接

.authenticate()可以与创建的实例一起使用来检查连接是否正常工作。



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

关闭连接



sequelize.close();
Enter fullscreen mode Exit fullscreen mode

定义模型

基本定义

要定义模型和表之间的映射，我们可以使用该.define()方法
设置仅具有属性及其数据类型的基本模型



const ModelName = sequelize.define("tablename", {
  // s will be appended automatically to the tablename
  firstColumn: Sequelize.INTEGER,
  secondColumn: Sequelize.STRING,
});
Enter fullscreen mode Exit fullscreen mode

要获取 Sequelize 支持的所有数据类型的列表，请查看官方文档

扩展列定义

基本扩展

除了数据类型之外，还可以在每列上设置许多其他选项



const ModelName = sequelize.define("tablename", {
  firstColumn: {
    // REQUIRED
    type: Sequelize.INTEGER,
    // OPTIONAL
    allowNull: false, // true by default
    defaultValue: 1,
    primaryKey: true, // false by default
    autoIncrement: true, // false by default
    unique: true,
    field: "first_column", // To change the field name in actual table
  },
});
Enter fullscreen mode Exit fullscreen mode

复合唯一键

要创建复合唯一键，请为要包含在复合唯一键中的所有列中的约束赋予相同的名称



const ModelName = sequelize.define("tablename", {
  firstColumn: {
    type: Sequelize.INTEGER,
    unique: "compositeIndex",
  },
  secondColumn: {
    type: Sequelize.INTEGER,
    unique: "compositeIndex",
  },
});
Enter fullscreen mode Exit fullscreen mode

也可以使用索引创建它们



const ModelName = sequelize.define(
  "tablename",
  {
    firstColumn: Sequelize.INTEGER,
    secondColumn: Sequelize.INTEGER,
  },
  {
    indexes: [
      {
        unique: true,
        fields: ["firstColumn", "secondColumn"],
      },
    ],
  }
);
Enter fullscreen mode Exit fullscreen mode

Getter 和 Setter

Getter 可用于在经过某些处理后获取列的值。Setter
可用于在将值保存到表中之前对其进行处理。



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

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

有关 Getters 和 Setters 的更多详细信息，请查看官方文档

验证

验证自动运行于create，update并且save

每个属性验证



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

      // Examples of custom validators:
      isEven(value) {
        if (parseInt(value) % 2 !== 0) {
          throw new Error("Only even values are allowed!");
        }
      },
    },
  },
});
Enter fullscreen mode Exit fullscreen mode

模型范围验证



const ModelName = sequelize.define(
  "tablename",
  {
    firstColumn: Sequelize.INTEGER,
    secondColumn: Sequelize.INTEGER,
  },
  {
    validate: {
      // Define your Model Wide Validations here
      checkSum() {
        if (this.firstColumn + this.secondColumn < 10) {
          throw new Error("Require sum of columns >=10");
        }
      },
    },
  }
);
Enter fullscreen mode Exit fullscreen mode

时间戳



const ModelName = sequelize.define(
  "tablename",
  {
    firstColumn: Sequelize.INTEGER,
  },
  {
    timestamps: true, // Enable timestamps
    createdAt: false, // Don't create createdAt
    updatedAt: false, // Don't create updatedAt
    updatedAt: "updateTimestamp", // updatedAt should be called updateTimestamp
  }
);
Enter fullscreen mode Exit fullscreen mode

数据库同步

Sequelize 可以自动创建模型中定义的表、关系和约束



ModelName.sync(); // Create the table if not already present

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

ModelName.drop(); // drop the tables
Enter fullscreen mode Exit fullscreen mode

您可以使用 sequelize 一次管理所有模型



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

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

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

sequelize.drop(); // Drop all tables
Enter fullscreen mode Exit fullscreen mode

模型扩展

Sequelize 模型是 ES6 类。我们可以轻松添加自定义实例或类级别的方法。



const ModelName = sequelize.define("tablename", {
  firstColumn: Sequelize.STRING,
  secondColumn: Sequelize.STRING,
});
// Adding a class level method
ModelName.classLevelMethod = function () {
  return "This is a Class level method";
};

// Adding a instance level method
ModelName.prototype.instanceLevelMethod = function () {
  return [this.firstColumn, this.secondColumn].join(" ");
};
Enter fullscreen mode Exit fullscreen mode

索引



const User = sequelize.define(
  "User",
  {
    /* attributes */
  },
  {
    indexes: [
      // Create a unique index on email
      {
        unique: true,
        fields: ["email"],
      },

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

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

      // A BTREE index with an ordered field
      {
        name: "title_index",
        using: "BTREE",
        fields: [
          "author",
          {
            attribute: "title",
            collate: "en_US",
            order: "DESC",
            length: 5,
          },
        ],
      },
    ],
  }
);
Enter fullscreen mode Exit fullscreen mode

协会

定义关联

定义有四种类型。它们成对使用。
例如，我们定义两个模型



const Foo = sequelize.define("foo" /* ... */);
const Bar = sequelize.define("bar" /* ... */);
Enter fullscreen mode Exit fullscreen mode

我们将要调用其函数的模型称为源模型，作为参数传递的模型称为目标模型。

hasOne



Foo.hasOne(Bar, {
  /* options */
});
Enter fullscreen mode Exit fullscreen mode

这说明 Foo 和 Bar 之间存在一对一关系，外键定义在 Bar 中

属于



Foo.belongsTo(Bar, {
  /* options */
});
Enter fullscreen mode Exit fullscreen mode

这说明 Foo 和 Bar 之间存在一对一或一对多关系，且外键定义在 Foo 中

hasMany



Foo.hasMany(Bar, {
  /* options */
});
Enter fullscreen mode Exit fullscreen mode

这说明 Foo 和 Bar 之间存在一对多关系，外键定义在 Bar 中

属于多个



Foo.belongsToMany(Bar, {
  // REQUIRED
  through: "C", // Model can also be passed here
  /* options */
});
Enter fullscreen mode Exit fullscreen mode

这表明 Foo 和 Bar 之间存在多对多关系，通过连接表 C

关系

一对一

要建立一对一关系，我们只需编写



Foo.hasOne(Bar);
Bar.belongsTo(Foo);
Enter fullscreen mode Exit fullscreen mode

在上述情况下，未传递任何选项。这将自动在 Bar 中创建一个外键列，引用 Foo 的主键。如果 Foo 的主键列名为 email，则在 Bar 中生成的列将为 fooEmail。

选项

可以传递以下选项来定制关系。



Foo.hasOne(Bar, {
  foreignKey: "customNameForFKColumn", // Name for new column added to Bar
  sourceKey: "email", // Column in Foo that FK will reference to
  // The possible choices are RESTRICT, CASCADE, NO ACTION, SET DEFAULT and SET NULL
  onDelete: "RESTRICT", // Default is SET NULL
  onUpdate: "RESTRICT", // Default is CASCADE
});
Bar.belongsTo(Foo, {
  foreignKey: "customNameForFKColumn", // Name for new column added to Bar
});
Enter fullscreen mode Exit fullscreen mode

一对多

要建立一对一关系，我们只需编写



Foo.hasMany(Bar);
Bar.belongsTo(Foo);
Enter fullscreen mode Exit fullscreen mode

在上述情况下，未传递任何选项。这将自动在 Bar 中创建一个外键列，引用 Foo 的主键。如果 Foo 的主键列名为 email，则在 Bar 中生成的列将为 fooEmail。

选项

可以传递以下选项来定制关系。



Foo.hasMany(Bar, {
  foreignKey: "customNameForFKColumn", // Name for new column added to Bar
  sourceKey: "email", // Column in Foo that FK will reference to
  // The possible choices are RESTRICT, CASCADE, NO ACTION, SET DEFAULT and SET NULL
  onDelete: "RESTRICT", // Default is SET NULL
  onUpdate: "RESTRICT", // Default is CASCADE
});
Bar.belongsTo(Foo, {
  foreignKey: "customNameForFKColumn", // Name for new column added to Bar
});
Enter fullscreen mode Exit fullscreen mode

多对多

要建立多对多关系，我们只需编写



// This will create a new table rel referencing the PK(by default) of both the tables
Foo.belongsToMany(Bar, { through: "rel" });
Bar.belongsToMany(Foo, { through: "rel" });
Enter fullscreen mode Exit fullscreen mode

选项

可以传递以下选项来定制关系。



Foo.belongsToMany(Bar, {
  as: "Bar",
  through: "rel",
  foreignKey: "customNameForFoo", // Custom name for column in rel referencing to Foo
  sourceKey: "name", // Column in Foo which rel will reference to
});
Bar.belongsToMany(Foo, {
  as: "Foo",
  through: "rel",
  foreignKey: "customNameForBar", // Custom name for column in rel referencing to Bar
  sourceKey: "name", // Column in Foo which rel will reference to
});
Enter fullscreen mode Exit fullscreen mode

实例

创建实例

创建实例有两种方式

建造

我们可以使用 build 方法创建非持久化（不保存到表中）实例。它们将自动获取定义模型时指定的默认值。
要保存到表中，我们需要显式地保存这些实例。



const instance = ModelName.build({
  firstColumn: "Lorem Ipsum",
  secondColumn: "Dotor",
});
// To save this instance to the db
instance.save().then((savedInstance) => {});
Enter fullscreen mode Exit fullscreen mode

创造

我们可以创建一个方法来创建持久（保存到表中）实例



const instance = ModelName.create({
  firstColumn: "Lorem Ipsum",
  secondColumn: "Dotor",
});
Enter fullscreen mode Exit fullscreen mode

变异实例

更新

有两种方法可以更新任何实例



// Way 1
instance.secondColumn = "Updated Dotor";
instance.save().then(() => {});
// To update only some of the modified fields
instance.save({ fields: ["secondColumn"] }).then(() => {});

// Way 2
instance
  .update({
    secondColumn: "Updated Dotor",
  })
  .then(() => {});
// To update only some of the modified fields
instance
  .update(
    {
      secondColumn: "Updated Dotor",
    },
    { fields: ["secondColumn"] }
  )
  .then(() => {});
Enter fullscreen mode Exit fullscreen mode

删除

删除/销毁任何实例



instance.destroy().then(() => {});
Enter fullscreen mode Exit fullscreen mode

使用模型

方法

findByPk

返回具有给定主键值的行。



ModelName.findByPK(PKvalue).then((foundResult) => {});
Enter fullscreen mode Exit fullscreen mode

找到一个

返回符合给定条件的第一行。



ModelName.findOne({
  // Optional options
  // Filtering results using where
  where: { firstColumn: "value" },
  // Returning only specified columns
  attributes: ["firstColumn", "secondColumn"],
}).then((foundResult) => {});
Enter fullscreen mode Exit fullscreen mode

查找或创建

返回符合指定条件的行。如果不存在这样的行，则创建并返回该行。



ModelName.findOrCreate({
  // Conditions that must be met
  where: { firstColumn: "lorem ipsum" },
  // Value of other columns to be set if no such row found
  defaults: { secondColumn: "dotor" },
}).then(([result, created]) => {}); //Created is a bool which tells created or not
Enter fullscreen mode Exit fullscreen mode

查找全部

返回满足条件的所有行



ModelName.findAll({
  // Optional Options
  where: {
    firstColumn: "lorem ipsum",
  },
  offset: 10,
  limit: 2,
}).then((results) => {});
Enter fullscreen mode Exit fullscreen mode

findAndCountAll

返回满足条件的所有行及其计数



ModelName.findAndCountAll({
  where: {
    firstColumn: "lorem ipsum",
  },
}).then((results) => {
  console.log(results.count);
  console.log(results.rows);
});
Enter fullscreen mode Exit fullscreen mode

数数

返回满足条件的行数



ModelName.count({
  where: {
    firstColumn: "lorem ipsum",
  },
}).then((c) => {});
Enter fullscreen mode Exit fullscreen mode

最大限度

返回具有给定条件的最大值的列的值



ModelName.max("age", {
  where: {
    firstColumn: "lorem ipsum",
  },
}).then((maxAge) => {});
Enter fullscreen mode Exit fullscreen mode

分钟

返回具有给定条件的最小值的列的值



ModelName.min("age", {
  where: {
    firstColumn: "lorem ipsum",
  },
}).then((minAge) => {});
Enter fullscreen mode Exit fullscreen mode

和

返回满足给定条件的列的所有值的总和



ModelName.sum({
  where: {
    firstColumn: "lorem ipsum",
  },
}).then((sumAge) => {});
Enter fullscreen mode Exit fullscreen mode

过滤

在哪里

哪里可以用来过滤我们处理的结果

我们可以直接传递值



ModelName.findAll({
  where: {
    firstColumn: "lorem ipsum",
  },
});
Enter fullscreen mode Exit fullscreen mode

我们可以使用 AND 和 OR



const Op = Sequelize.Op;
ModelName.findAll({
  where: {
    [Op.and]: [{ secondColumn: 5 }, { thirdColumn: 6 }],
    [Op.or]: [{ secondColumn: 5 }, { secondColumn: 6 }],
  },
});
Enter fullscreen mode Exit fullscreen mode

我们可以使用其他各种运算符



const Op = Sequelize.Op;
ModelName.findAll({
  where: {
    firstColumn: {
      [Op.ne]: "lorem ipsum dotor", // Not equal to
    },
  },
});
Enter fullscreen mode Exit fullscreen mode

我们也可以混合搭配



const Op = Sequelize.Op;
ModelName.findAll({
  where: {
    [Op.or]: {
      [Op.lt]: 1000,
      [Op.eq]: null,
    },
  },
});
Enter fullscreen mode Exit fullscreen mode

运算符

以下是运营商的完整列表



const Op = Sequelize.Op

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

[Op.col]: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example
[Op.gt]: { [Op.all]: literal('SELECT 1') }
                          // > ALL (SELECT 1)
[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)
Enter fullscreen mode Exit fullscreen mode

命令



ModelName.findAll({
  order: [
    ["firstColumn", "DESC"],
    ["secondColumn", "ASC"],
  ],
});
Enter fullscreen mode Exit fullscreen mode

有关订购的更多详细信息，请查看官方文档

分页和限制



ModelName.findAll({
  offset: 5, // Skip the first five results
  limit: 5, // Return only five results
});
Enter fullscreen mode Exit fullscreen mode

查看我的博客仓库

发送拉取请求以添加/修改此帖子。

GitHub 徽标 projectescape /博客参考

包含我撰写的所有博客的补充源的存储库


在 GitHub 上查看


文章来源：https://dev.to/projectescape/the-compressive-sequelize-cheatsheet-3m1m
Devto 翻译
PREV
10 个有用但难记的 CSS 选择器
NEXT
React Router 使用 react-router 在 React 中重定向程序化导航