node.js 使用 SequelizeJS 编写带有外键的迁移

Writing Migrations with Foreign Keys Using SequelizeJS

提问by slifty

The Background

背景

I'm building a project with SequelizeJS, a popular ORM for NodeJS. When designing a schema, there appears to be two tactics:

我正在使用SequelizeJS构建一个项目，SequelizeJS 是 NodeJS的流行 ORM。在设计模式时，似乎有两种策略：

1. Create model code and use the .sync() function to automatically generate tables for your models.
2. Create model code and write manual migrationsusing QueryInterfaceand umzug.

1. 创建模型代码并使用 .sync() 函数为您的模型自动生成表。
2. 使用QueryInterface和umzug创建模型代码并编写手动迁移。

My understanding is that #1 is better for rapid prototyping, but that #2 is a best practice for projects that are expected to evolve over time and where production data needs to be able to survive migrations.

我的理解是，#1 更适合快速原型设计，但 #2 是预计会随着时间发展以及生产数据需要能够在迁移中幸存下来的项目的最佳实践。

This question pertains to tactic #2.

这个问题与策略 #2 有关。

The Question(s)

问题

My tables have relationships which must be reflected through foreign keys.

我的表具有必须通过外键反映的关系。

• How do I create tables with foreign key relationships with one another through the Sequelize QueryInterface?

• What columns and helper tables are required by Sequelize? For example, it appears that specific columns such as createdAt or updatedAt are expected.

• 如何通过 Sequelize QueryInterface 创建彼此具有外键关系的表？

• Sequelize 需要哪些列和辅助表？例如，似乎需要特定的列，例如 createdAt 或 updatedAt。

回答by slifty

How do I create tables with foreign key relationships with one another through the Sequelize QueryInterface?

如何通过 Sequelize QueryInterface 创建彼此具有外键关系的表？

The .createTable()method takes in a dictionary of columns. You can see the list of valid attributes in the documentation for .define(), specifically by looking at the [attributes.column.*]rows within the params table.

该.createTable()方法接受一个列字典。您可以在 的文档中查看.define()有效属性列表，特别是通过查看[attributes.column.*]params 表中的行。

To create an attribute with a foreign key relationship, use the "references" and "referencesKey" fields:

要创建具有外键关系的属性，请使用“references”和“referencesKey”字段：

For example, the following would create a userstable, and a user_emailstable which references the users table.

例如，以下将创建一个users表和一个user_emails引用用户表的表。

queryInterface.createTable('users', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  }
}).then(function() {
  queryInterface.createTable('user_emails', {
    userId: {
      type: Sequelize.INTEGER,
      references: { model: 'users', key: 'id' }
    }
  })
});

What columns and helper tables are required by sequelize? For example, it appears that specific columns such as createdAt or updatedAt are expected.

sequelize 需要哪些列和辅助表？例如，似乎需要特定的列，例如 createdAt 或 updatedAt。

It appears that a standard model will expect an id, updatedAt, and createdAtcolumn for each table.

看来，一个标准模型将期望的id，updatedAt和createdAt每个表列。

queryInterface.createTable('users', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  createdAt: {
    type: Sequelize.DATE
  },
  updatedAt: {
    type: Sequelize.DATE
  }
}

If you set paranoidto trueon your model, you also need a deletedAttimestamp.

如果您在模型上设置paranoid为true，则还需要deletedAt时间戳。

回答by galki

I want to offer another more manual alternativebecause when using manual migrations and queryInterface I ran across the following problem: I had 2 files in the migration folder like so

我想提供另一个更手动的替代方案，因为在使用手动迁移和 queryInterface 时，我遇到了以下问题：我在迁移文件夹中有 2 个文件，如下所示

migrations/create-project.js
migrations/create-projectType.js

because projecthad column projectTypeIdit referenced projectType, which wasnt created yet due to the order of the files and this was causing an error.

因为project有projectTypeId它引用的列projectType，由于文件的顺序，该列尚未创建，这导致了错误。

I solved it by adding a foreign key constraint after creating both tables. In my case I decided to write it inside create-projectType.js:

我通过在创建两个表后添加外键约束来解决它。就我而言，我决定将其写在里面create-projectType.js：

queryInterface.createTable('project_type', {
  // table attributes ...
})
.then(() => queryInterface.addConstraint('project', ['projectTypeId'], {
  type: 'FOREIGN KEY',
  name: 'FK_projectType_project', // useful if using queryInterface.removeConstraint
  references: {
    table: 'project_type',
    field: 'id',
  },
  onDelete: 'no action',
  onUpdate: 'no action',
}))

回答by Shashikant Pandit

This is to create migration file for adding a column.

这是创建用于添加列的迁移文件。

Here I want to add a column area_idin userstable. Run command:

这里我想在users表中添加一个列area_id。运行命令：

sequelize migration:create --name add-area_id-in-users

Once it gets executed creates a migration file timestamp-add-region_id-in-usersin the migrations folder.

一旦它被执行，就会在迁移文件夹中创建一个迁移文件timestamp-add-region_id-in-users。

In the created migration file paste the below code:

在创建的迁移文件中粘贴以下代码：

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
        return Promise.all([
          queryInterface.addColumn('users', 'region_id',
            {
              type: Sequelize.UUID,
              references: {
                model: 'regions',
                key: 'id',
              },
              onUpdate: 'CASCADE',
              onDelete: 'SET NULL',
              defaultValue: null, after: 'can_maintain_system'
            }),
        ]);
      },

      down: (queryInterface, Sequelize) => {
        return Promise.all([
          queryInterface.removeColumn('users', 'region_id'),
        ]);
      }
    };

Here in the users table I am going to create a column named region_id along with type and relation/foreign key/references. That's it.

在用户表中，我将创建一个名为 region_id 的列以及类型和关系/外键/引用。就是这样。

回答by nagaraj

So first one goes as the below solution and the second question: you need not explicitly mention createdAtand updatedAtbecause they are generated by the Sequelize for you.

所以第一个作为下面的解决方案和第二个问题：你不需要明确提及createdAt，updatedAt因为它们是由 Sequelize 为你生成的。

The solution is:

解决办法是：

queryInterface.createTable('Images', {

  //...

}).then(

  return queryInterface.addConstraint('Images', ['postId'], {

    type: 'foreign key',

    name: 'custom_fkey_images',

    references: { //Required field

      table: 'Posts',

      field: 'id'

    },

    onDelete: 'cascade',

    onUpdate: 'cascade'

  })
)