MySQL 如何使用 Sequelize CLI 从 Sequelize 模型自动生成迁移？

How to auto generate migrations with Sequelize CLI from Sequelize models?

提问by Michael Schmidt

I have a set of Sequelize models. I want to use migrations, not DB Sync.

我有一组 Sequelize 模型。我想使用迁移，而不是数据库同步。

Sequelize CLIseems to be able to do this, according to this article: "When you use the CLI for the model generation, you will gain the migration scripts for free as well."

Sequelize CLI似乎能够做到这一点，根据这篇文章：“当您使用 CLI 进行模型生成时，您还将免费获得迁移脚本。”

How to auto generate the migrations with Sequelize CLI from existing Sequelize models?

如何使用 Sequelize CLI 从现有 Sequelize 模型自动生成迁移？

回答by Dor Rotman

You cannot create migration scripts for existing models.

您不能为现有模型创建迁移脚本。

Resources:

资源：

• Tutorial video on migrations.

• 关于迁移的教程视频。

If going the classic way, you'll have to recreate the models via the CLI:

如果采用经典方式，则必须通过 CLI 重新创建模型：

sequelize model:create --name MyUser --attributes first_name:string,last_name:string,bio:text

It will generate these files:

它将生成这些文件：

models/myuser.js:

模型/myuser.js：

"use strict";
module.exports = function(sequelize, DataTypes) {
  var MyUser = sequelize.define("MyUser", {
    first_name: DataTypes.STRING,
    last_name: DataTypes.STRING,
    bio: DataTypes.TEXT
  }, {
    classMethods: {
      associate: function(models) {
        // associations can be defined here
      }
    }
  });
  return MyUser;
};

migrations/20150210104840-create-my-user.js:

迁移/20150210104840-create-my-user.js：

"use strict";
module.exports = {
  up: function(migration, DataTypes, done) {
    migration.createTable("MyUsers", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER
      },
      first_name: {
        type: DataTypes.STRING
      },
      last_name: {
        type: DataTypes.STRING
      },
      bio: {
        type: DataTypes.TEXT
      },
      createdAt: {
        allowNull: false,
        type: DataTypes.DATE
      },
      updatedAt: {
        allowNull: false,
        type: DataTypes.DATE
      }
    }).done(done);
  },
  down: function(migration, DataTypes, done) {
    migration.dropTable("MyUsers").done(done);
  }
};

回答by john_mc

If you don't want to recreate your model from scratch, you can manually generate a migration file using the following CLI command:

如果您不想从头开始重新创建模型，可以使用以下 CLI 命令手动生成迁移文件：

sequelize migration:generate --name [name_of_your_migration]

sequelize migration:generate --name [name_of_your_migration]

This will generate a blank skeleton migration file. While it doesn't copy your model structure over to the file, I do find it easier and cleaner than regenerating everything. Note: make sure to run the command from the containing directory of your migrations directory; otherwise the CLI will generate a new migration dir for you

这将生成一个空白的骨架迁移文件。虽然它不会将您的模型结构复制到文件中，但我确实发现它比重新生成所有内容更容易、更干净。注意：确保从迁移目录的包含目录运行命令；否则 CLI 会为你生成一个新的迁移目录

回答by Kallaste

You can now use the npm package sequelize-auto-migrations to automatically generate a migrations file. https://www.npmjs.com/package/sequelize-auto-migrations

您现在可以使用 npm 包 sequelize-auto-migrations 自动生成迁移文件。https://www.npmjs.com/package/sequelize-auto-migrations

Using sequelize-cli, initialize your project with

使用 sequelize-cli，初始化你的项目

sequelize init

Create your models and put them in your models folder.

创建您的模型并将它们放在您的模型文件夹中。

Install sequelize-auto-migrations:

安装 sequelize-auto-migrations：

npm install sequelize-auto-migrations

Create an initial migration file with

创建一个初始迁移文件

node ./node_modules/sequelize-auto-migrations/bin/makemigration --name <initial_migration_name>

Run your migration:

运行您的迁移：

node ./node_modules/sequelize-auto-migrations/bin/runmigration

You can also automatically generate your models from an existing database, but that is beyond the scope of the question.

您还可以从现有数据库自动生成模型，但这超出了问题的范围。

回答by PaulMest

It's 2020 and many of these answers no longer apply to the Sequelize v4/v5 ecosystem.

现在是 2020 年，其中许多答案不再适用于 Sequelize v4/v5 生态系统。

The one good answer says to use sequelize-auto-migrations, but probably is not prescriptive enough to use in your project. So here's a bit more color...

一个好的答案是使用sequelize-auto-migrations，但可能不够规范，无法在您的项目中使用。所以这里的颜色有点多……

Setup

设置

My team uses a fork of sequelize-auto-migrationsbecause the original repo is has not been merged a few critical PRs. #56#57#58#59

我的团队使用了一个fork，sequelize-auto-migrations因为原始 repo 还没有合并一些关键的 PR。#56 #57 #58 #59

$ yarn add github:scimonster/sequelize-auto-migrations#a063aa6535a3f580623581bf866cef2d609531ba

Edit package.json:

编辑 package.json:

"scripts": {
  ...
  "db:makemigrations": "./node_modules/sequelize-auto-migrations/bin/makemigration.js",
  ...
}

Process

过程

Note: Make sure you're using git (or some source control) and database backups so that you can undo these changes if something goes really bad.

注意：确保您正在使用 git（或某些源代码控制）和数据库备份，以便在出现问题时可以撤消这些更改。

1. Delete all old migrations if any exist.
2. Turn off .sync()
3. Create a mega-migration that migrates everything in your current models (yarn db:makemigrations --name "mega-migration").
4. Commit your 01-mega-migration.jsand the _current.jsonthat is generated.
5. if you've previously run .sync()or hand-written migrations, you need to “Fake” that mega-migration by inserting the name of it into your SequelizeMeta table. INSERT INTO SequelizeMeta Values ('01-mega-migration.js').
6. Now you should be able to use this as normal…
7. Make changes to your models (add/remove columns, change constraints)
8. Run $ yarn db:makemigrations --name whatever
9. Commit your 02-whatever.jsmigration and the changes to _current.json, and _current.bak.json.
10. Run your migration through the normal sequelize-cli: $ yarn sequelize db:migrate.
11. Repeat 7-10 as necessary

1. 删除所有旧迁移（如果存在）。
2. 关掉 .sync()
3. 创建一个大型迁移，迁移当前模型中的所有内容 ( yarn db:makemigrations --name "mega-migration")。
4. 提交您的01-mega-migration.js和_current.json生成的。
5. 如果您以前运行过.sync()或手写迁移，则需要通过将其名称插入 SequelizeMeta 表来“伪造”该大型迁移。INSERT INTO SequelizeMeta Values ('01-mega-migration.js').
6. 现在你应该可以正常使用它了……
7. 对模型进行更改（添加/删除列、更改约束）
8. 跑 $ yarn db:makemigrations --name whatever
9. 将您的02-whatever.js迁移和更改提交到_current.json、 和_current.bak.json。
10. 通过正常的 sequelize-cli: 运行迁移$ yarn sequelize db:migrate。
11. 根据需要重复 7-10

Known Gotchas

已知问题

1. Renaming a column will turn into a pair of removeColumnand addColumn. This will lose data in production. You will need to modify the up and down actions to use renameColumninstead.

1. 重命名列将变成一对removeColumn和addColumn。这将丢失生产中的数据。您将需要修改要使用的向上和向下操作renameColumn。

For those who confused how to use renameColumn, the snippet would look like this. (switch "column_name_before" and "column_name_after" for the rollbackCommands)

对于那些对如何使用 感到困惑的人renameColumn，代码片段看起来像这样。（为 切换“column_name_before”和“column_name_after” rollbackCommands）

{
    fn: "renameColumn",
    params: [
        "table_name",
        "column_name_before",
        "column_name_after",
        {
            transaction: transaction
        }
    ]
}

1. If you have a lot of migrations, the down action may not perfectly remove items in an order consistent way.

2. The maintainer of this library does not actively check it. So if it doesn't work for you out of the box, you will need to find a different community fork or another solution.

2. 如果您有大量迁移，则向下操作可能无法以一致的顺序完美删除项目。

3. 这个库的维护者不会主动检查它。因此，如果开箱即用它对您不起作用，您将需要找到不同的社区分支或其他解决方案。

回答by Manuel Bieh

I created a small working "migration file generator". It creates files which are working perfectly fine using sequelize db:migrate- even with foreign keys!

我创建了一个小的工作“迁移文件生成器”。它创建的文件可以很好地使用sequelize db:migrate- 即使使用外键！

You can find it here: https://gist.github.com/manuelbieh/ae3b028286db10770c81

你可以在这里找到它：https: //gist.github.com/manuelbieh/ae3b028286db10770c81

I tested it in an application with 12 different models covering:

我在一个包含 12 个不同模型的应用程序中测试了它：

• STRING, TEXT, ENUM, INTEGER, BOOLEAN, FLOAT as DataTypes

• Foreign key constraints (even reciprocal (user belongsTo team, team belongsTo user as owner))

• Indexes with name, methodand uniqueproperties

• 字符串、文本、枚举、整数、布尔值、浮点数作为数据类型

• 外键约束（甚至互惠（用户属于团队，团队属于用户作为所有者））

• 具有name,method和unique属性的索引

回答by T Ratnayake

While it doesn't auto generate, one way to generate new migrations on a change to a model is: (assuming that you're using the stock sequelize-cli file structure where migrations, and models are on the same level)

虽然它不会自动生成，但在模型更改时生成新迁移的一种方法是：（假设您使用的是迁移和模型处于同一级别的库存 sequelize-cli 文件结构）

1. (Same as Manuel Bieh's suggestion, but using a require instead of an import) In your migration file (if you don't have one, you can generate one by doing "sequelize migration:create") have the following code:

   'use strict';
   var models = require("../models/index.js")
   module.exports = {
     up: function(queryInterface, Sequelize) {
       return queryInterface.createTable(models.User.tableName, 
         models.User.attributes);
     },
     down: function(queryInterface, Sequelize) {
       return queryInterface.dropTable('Users');
     }
   };
   

2. Make a change to the User model.

3. Delete table from database.
4. Undo all migrations: sequelize db:migrate:undo:all
5. Re-migrate to have changes saved in db. sequelize db:migrate

1. （与 Manuel Bieh 的建议相同，但使用 require 而不是导入）在您的迁移文件中（如果您没有，您可以通过执行“ sequelize migration:create”来生成一个）具有以下代码：

   'use strict';
   var models = require("../models/index.js")
   module.exports = {
     up: function(queryInterface, Sequelize) {
       return queryInterface.createTable(models.User.tableName, 
         models.User.attributes);
     },
     down: function(queryInterface, Sequelize) {
       return queryInterface.dropTable('Users');
     }
   };
   

2. 对用户模型进行更改。

3. 从数据库中删除表。
4. 撤消所有迁移： sequelize db:migrate:undo:all
5. 重新迁移以将更改保存在 db 中。 sequelize db:migrate

回答by Shashikant Pandit

If you want to create model along with migration use this command:-

如果要在迁移的同时创建模型，请使用以下命令：-

sequelize model:create --name regions --attributes name:string,status:boolean --underscored

--underscoredit is used to create column having underscore like:- created_at,updated_at or any other column having underscore and support user defined columns having underscore.

--underscored它用于创建具有下划线的列，例如：- created_at,updated_at 或任何其他具有下划线的列并支持用户定义的具有下划线的列。

回答by Manuel Bieh

I have recently tried the following approach which seems to work fine, although I am not 100% sure if there might be any side effects:

我最近尝试了以下似乎工作正常的方法，尽管我不能 100% 确定是否可能有任何副作用：

'use strict';

import * as models from "../../models";

module.exports = {

  up: function (queryInterface, Sequelize) {

    return queryInterface.createTable(models.Role.tableName, models.Role.attributes)
    .then(() => queryInterface.createTable(models.Team.tableName, models.Team.attributes))
    .then(() => queryInterface.createTable(models.User.tableName, models.User.attributes))

  },

  down: function (queryInterface, Sequelize) {
    ...
  }

};

When running the migration above using sequelize db:migrate, my console says:

使用 运行上述迁移时sequelize db:migrate，我的控制台显示：

Starting 'db:migrate'...
Finished 'db:migrate' after 91 ms
== 20160113121833-create-tables: migrating =======
== 20160113121833-create-tables: migrated (0.518s)

All the tables are there, everything (at least seems to) work as expected. Even all the associations are there if they are defined correctly.

所有的表都在那里，一切（至少似乎）都按预期工作。如果定义正确，即使所有关联都存在。

回答by Igor Volnyi

Another solution is to put data definition into a separate file.

另一种解决方案是将数据定义放入单独的文件中。

The idea is to write data common for both model and migration into a separate file, then require it in both the migration and the model. Then in the model we can add validations, while the migration is already good to go.

这个想法是将模型和迁移通用的数据写入一个单独的文件，然后在迁移和模型中都需要它。然后在模型中我们可以添加验证，而迁移已经很好了。

In order to not clutter this post with tons of code i wrote a GitHub gist.

为了不让这篇文章堆满大量代码，我写了一个 GitHub 要点。

See it here: https://gist.github.com/igorvolnyi/f7989fc64006941a7d7a1a9d5e61be47

在这里查看：https: //gist.github.com/igorvolnyi/f7989fc64006941a7d7a1a9d5e61be47