Sequelize执行db:migrate报superheroes关系不存在错误排查

相关代码 #

Superhero 模型定义 #

'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Superhero extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate ({ Image, Superpower }) {
      // define association here
      Superhero.hasMany(Image, { foreignKey: 'superheroId' });
      Superhero.hasMany(Superpower, { foreignKey: 'superheroId' });
    }
  }
  Superhero.init(
    {
      nickname: {
        allowNull: false,
        type: DataTypes.STRING(128),
        validate: { notEmpty: true, notNull: true, len: [1, 128] },
      },
      realName: {
        allowNull: false,
        field: 'real_name',
        type: DataTypes.STRING(128),
        validate: {
          notEmpty: true,
          notNull: true,
          len: [1, 128],
        },
      },
      originDescription: {
        allowNull: false,
        field: 'origin_description',
        type: DataTypes.TEXT,
        validate: {
          notEmpty: true,
          notNull: true,
        },
      },
      superpowers: {
        allowNull: false,
        type: DataTypes.TEXT,
        validate: {
          notEmpty: true,
          notNull: true,
        },
      },
      catchPhrase: {
        allowNull: false,
        field: 'catch_phrase',
        type: DataTypes.STRING,
        validate: {
          notEmpty: true,
          notNull: true,
          len: [1, 255],
        },
      },
      images: {
        allowNull: false,
        type: DataTypes.TEXT,
        validate: {
          notNull: true,
          notEmpty: true,
        },
      },
    },
    {
      sequelize,
      modelName: 'Superhero',
      underscored: true,
      tableName: 'superheroes',
    }
  );
  return Superhero;
};

superpowers 表迁移脚本 #

'use strict';
module.exports = {
  async up (queryInterface, Sequelize) {
    await queryInterface.createTable('superpowers', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      superheroId: {
        field: 'superhero_id',
        allowNull: false,
        type: Sequelize.INTEGER,
        references: {
          model: {
            tableName: 'superheroes',
          },
          key: 'id',
        },
        onDelete: 'cascade',
        onUpdate: 'cascade',
      },
      superpowerName: {
        field: 'superpower_name',
        allowNull: false,
        type: Sequelize.STRING,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
  },
  async down (queryInterface, Sequelize) {
    await queryInterface.dropTable('superpowers');
  },
};

报错信息 #

执行命令 npx sequelize db:migrate 时抛出如下错误：

Sequelize CLI [Node: 16.14.2, CLI: 6.4.1, ORM: 6.21.0]

Loaded configuration file "src/config/db.json".
Using environment "development".
== 20220704175320-create-superpower: migrating =======

ERROR: relation "superheroes" does not exist

初步判断 #

superpowers表的迁移执行顺序早于superheroes表，导致创建superpowers表时，尝试将表内superheroId字段关联到尚未创建的superheroes表的id字段，外键约束创建失败。

补充信息 #

• 项目文件结构参考截图：
  
• 补充提供的superhero相关文件内容（注：该内容实际为Superhero模型定义代码，而非建表迁移脚本）：

'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Superhero extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate ({ Image, Superpower }) {
      // define association here
      Superhero.hasMany(Image, { foreignKey: 'superheroId' });
      Superhero.hasMany(Superpower, { foreignKey: 'superheroId' });
    }
  }
  Superhero.init(
    {
      nickname: {
        allowNull: false,
        type: DataTypes.STRING(128),
        validate: { notEmpty: true, notNull: true, len: [1, 128] },
      },
      realName: {
        allowNull: false,
        field: 'real_name',
        type: DataTypes.STRING(128),
        validate: {
          notEmpty: true,
          notNull: true,
          len: [1, 128],
        },
      },
      originDescription: {
        allowNull: false,
        field: 'origin_description',
        type: DataTypes.TEXT,
        validate: {
          notEmpty: true,
          notNull: true,
        },
      },
      superpowers: {
        allowNull: false,
        type: DataTypes.TEXT,
        validate: {
          notEmpty: true,
          notNull: true,
        },
      },
      catchPhrase: {
        allowNull: false,
        field: 'catch_phrase',
        type: DataTypes.STRING,
        validate: {
          notEmpty: true,
          notNull: true,
          len: [1, 255],
        },
      },
      images: {
        allowNull: false,
        type: DataTypes.TEXT,
        validate: {
          notNull: true,
          notEmpty: true,
        },
      },
    },
    {
      sequelize,
      modelName: 'Superhero',
      underscored: true,
      tableName: 'superheroes',
    }
  );
  return Superhero;
};

错误原因 #

你的判断完全正确。Sequelize CLI 是严格按照迁移文件名前缀的时间戳升序执行所有迁移脚本的，从报错信息可以看到，当前正在执行的是时间戳为20220704175320的create-superpower迁移，出现这个错误只有两种可能：

• 你压根没有生成创建superheroes表的迁移文件——你后续补充的所谓superhero相关迁移文件，实际是models目录下的模型定义代码，这类文件只负责ORM层的模型映射，不会自动在数据库中建表，Sequelize CLI迁移流程根本不会读取这部分代码来建表
• 你已经生成了superheroes表的迁移文件，但该文件的时间戳比20220704175320更大，被排在了superpowers迁移的后面执行

两种情况的最终结果一致：执行superpowers建表逻辑、给superhero_id字段添加指向superheroes表的外键约束时，数据库里还不存在superheroes表，自然会抛出relation "superheroes" does not exist的错误。

修复方案 #

• 若还未创建superheroes表的迁移：执行model生成命令补全superheroes的建表迁移，确保新生成的迁移文件时间戳早于superpowers迁移的时间戳
• 若已存在superheroes迁移但时间戳靠后：直接修改superheroes迁移文件名的时间戳前缀，把数值改到比superpowers迁移的时间戳更小，保证主表superheroes先于从表superpowers创建
• 修复文件顺序后，如果之前有迁移失败的残留记录，先执行npx sequelize db:migrate:undo:all回滚所有已执行的迁移，再重新执行npx sequelize db:migrate即可正常完成迁移。

内容的提问来源于stack exchange，提问作者dbUser11