node.js - Sequelize.sync : Create indexes when table does not exist

Question

根据 documentation sequelize.sync() 没有 {force: true} 或 {alter:true} 应该忽略已经存在的表并只创建/同步新表。但是，当现有表没有被完全忽略并且引入了错误时，至少有两个用例。
我的设置:

sequelize.sync() 用作迁移前步骤，用于创建模式

中不存在的表

sequelize.migrate() 用于更改任何现有表。

注意:Sequelize 模型被视为反射(reflect)数据库模式的单一事实来源。它们总是更新以反射(reflect)数据库中存在的所有索引/字段。
重现 的步骤
第 1 步 :创建具有 User 和 name 两个字段的 email 模型。 Email 有唯一索引

const users = sequelizeClient.define('users', {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
    },
  }, {
    indexes: [
      {
        unique: true,
        fields: ['email'],
      },
    ],
  });

没有迁移，因此应该使用 sequelize.sync() 创建该表。
一切都按预期工作。这是生成的 SQL 脚本。

Executing (default): CREATE TABLE IF NOT EXISTS "users" ("id"  SERIAL , "name" VARCHAR(255) NOT NULL, "email" VARCHAR(255) NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE UNIQUE INDEX "users_email" ON "users" ("email")

步骤 2 向用户表中添加一个新字段 phonenumber 并添加唯一索引。添加将改变表结构并创建索引的迁移。 sequelize.sync() 预计会忽略此表，但迁移永远不会执行，因为 sequelize.sync() 会引发以下错误。

Executing (default): CREATE TABLE IF NOT EXISTS "users" ("id"  SERIAL , "name" VARCHAR(255) NOT NULL, "email" VARCHAR(255) NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE UNIQUE INDEX "users_phonenumber" ON "users" ("phonenumber")
{"_bitField":18087936,"_fulfillmentHandler0":{"name":"SequelizeDatabaseError","parent":{"name":"error","length":101,"severity":"ERROR","code":"42703","file":"indexcmds.c","line":"1083","routine":"ComputeIndexAttrs","sql":"CREATE UNIQUE INDEX \"users_phonenumber\" ON \"users\" (\"phonenumber\")"},"original":{"name":"error","length":101,"severity":"ERROR","code":"42703","file":"indexcmds.c","line":"1083","routine":"ComputeIndexAttrs","sql":"CREATE UNIQUE INDEX \"users_phonenumber\" ON \"users\" (\"phonenumber\")"},"sql":"CREATE UNIQUE INDEX \"users_phonenumber\" ON \"users\" (\"phonenumber\")"},"_trace":{"_promisesCreated":0,"_length":1},"level":"error","message":"Unhandled Rejection at: Promise "}

这是最终模型

const users = sequelizeClient.define('users', {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    phoneNumber: { // new field
      type: DataTypes.STRING,
      allowNull: false,
    },

  }, {
    indexes: [
      {
        unique: true,
        fields: ['email'],
      },
      { // new index
        unique: true,
        fields: ['phoneNumber'],
      },
    ],
  });

有人可以在这里提出解决方法，以便仅在表不存在时才创建索引

另一个用例是当您添加带有注释的新字段时

    fieldWithComment: {
      type: DataTypes.STRING,
      comment: 'my comment goes here',
    },

生成的 SQL 显然会引发错误，因为新列尚不存在。

CREATE TABLE IF NOT EXISTS "users" (
    "id"   SERIAL, 
    "name" VARCHAR(255) NOT NULL, 
    "email" VARCHAR(255) NOT NULL, 
    "phonenumber" VARCHAR(255) NOT NULL, 
    "fieldWithComment" VARCHAR(255) , PRIMARY KEY ("id")); 
        COMMENT ON COLUMN "users"."fieldWithComment" IS 'my comment goes here';

Answer 1

在生产中同步数据库的正确且非破坏性的方法是通过迁移。这样可以确保操作(新字段创建、索引创建等)的顺序。
所以，一般来说，sync 应该只在开发和测试环境中使用。
引用官方文档:

sync({ force: true }) and sync({ alter: true }) can be destructive operations. Therefore, they are not recommended for production-level software. Instead, synchronization should be done with the advanced concept of Migrations, with the help of the Sequelize CLI.

更多信息 here(部分:“生产同步”)。