Sequelize bulkCreate报错：PaymentOptionRates不存在RateId列求助

问题场景 #

我有rate、paymentOption和paymentOptionRate三张表，对paymentOptionRate执行bulkCreate时传入以下数据：

const data = [
  {
    rateId: 'ccf19a9f-9897-4328-84ba-f2558fbbe10a',
    optionRateId: '9f873f92-7fbd-492e-9416-dd12f7a71dee'
  },
  {
    rateId: '4329965a-89d0-46da-83f3-fbea27da8206',
    optionRateId: 'fae5edcc-f03e-4744-ba52-0da73406640c'
  },
]

触发错误：

ERROR: column "RateId" of relation "PaymentOptionRates" does not exist

模型代码 #

Rate模型 #

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Rate extends Model {
    static associate(models) {}
  }

  Rate.init({
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    rate: {
      type: DataTypes.DECIMAL(10, 2),
      allowNull: false,
    },
    subsidiaryId: {
      type: DataTypes.UUID,
      references: {
        model: 'Subsidiaries',
        key: 'id',
      },
      onUpdate: 'CASCADE',
      onDelete: 'SET NULL',
    },
  }, {
    sequelize,
    modelName: 'Rate',
    timestamps: true,
  });

  Rate.associate(models => {
    Rate.belongsTo(models.Subsidiary, {
      foreignKey: 'subsidiaryId',
      as: 'subsidiary',
    });
    Rate.belongsToMany(models.PaymentOption, {
      through: models.PaymentOptionRate,
      foreignKey: 'rateId',
      as: 'paymentOptions',
    });
  });

  return Rate;
};

PaymentOption模型 #

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class PaymentOption extends Model {
    static associate(models) {}
  }

  PaymentOption.init({
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
  }, {
    sequelize,
    modelName: 'PaymentOption',
    timestamps: true,
  });

  PaymentOption.associate = function(models) {
    PaymentOption.belongsToMany(models.Rate, {
      through: models.PaymentOptionRate,
      foreignKey: 'paymentOptionId',
      as: 'rates',
    });
  };

  return PaymentOption;
};

PaymentOptionRate模型 #

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class PaymentOptionRate extends Model {
    static associate(models) {}
  }

  PaymentOptionRate.init({
    paymentOptionId: {
      type: DataTypes.UUID,
      allowNull: false,
      references: {
        model: 'PaymentOptions',
        key: 'id',
      },
      onUpdate: 'CASCADE',
      onDelete: 'CASCADE',
    },
    rateId: {
      type: DataTypes.UUID,
      allowNull: false,
      references: {
        model: 'Rates',
        key: 'id',
      },
      onUpdate: 'CASCADE',
      onDelete: 'CASCADE',
    },
  }, {
    sequelize,
    modelName: 'PaymentOptionRate',
    timestamps: true,
  });

  PaymentOptionRate.associate = function(models) {
    PaymentOptionRate.belongsTo(models.PaymentOption, {
      through: models.PaymentOptionRate,
      foreignKey: 'paymentOptionId',
      as: 'paymentOption',
    });
    PaymentOptionRate.belongsTo(models.Rate, {
      through: models.PaymentOptionRate,
      foreignKey: 'rateId',
      as: 'rate',
    });
  };

  return PaymentOptionRate;
};

排查信息 #

执行console.log(await PaymentOptionRate.describe());确认表结构：

{
  paymentOptionId: {
    type: 'UUID',
    allowNull: false,
    defaultValue: null,
    comment: null,
    special: [],
    primaryKey: false
  },
  rateId: {
    type: 'UUID',
    allowNull: false,
    defaultValue: null,
    comment: null,
    special: [],
    primaryKey: false
  },
  createdAt: {
    type: 'TIMESTAMP WITH TIME ZONE',
    allowNull: false,
    defaultValue: null,
    comment: null,
    special: [],
    primaryKey: false
  },
  updatedAt: {
    type: 'TIMESTAMP WITH TIME ZONE',
    allowNull: false,
    defaultValue: null,
    comment: null,
    special: [],
    primaryKey: false
  }
}

但生成的SQL错误尝试插入RateId列：

sql: 'INSERT INTO "PaymentOptionRates" ("paymentOptionId","rateId","createdAt","updatedAt","RateId") VALUES ($1,$2,$3,$4,$5) RETURNING "paymentOptionId","rateId","createdAt","updatedAt","RateId";',
  parameters: [
    '3f5a8209-b5ae-47b1-b838-84d3dc563ecc',
    '8121ce6e-a2aa-42a0-b2a9-186227af3720',
    '2023-11-25 07:50:01.187 +00:00',
    '2023-11-25 07:50:01.187 +00:00',
    null
  ]

解决方案 #

1. 修正中间表关联配置 #

问题出在PaymentOptionRate模型的belongsTo关联上：through选项仅用于belongsToMany关联，中间表用belongsTo时添加该选项会导致Sequelize错误生成额外字段。

修改PaymentOptionRate的associate方法，移除多余的through选项：

PaymentOptionRate.associate = function(models) {
  PaymentOptionRate.belongsTo(models.PaymentOption, {
    foreignKey: 'paymentOptionId',
    as: 'paymentOption',
  });
  PaymentOptionRate.belongsTo(models.Rate, {
    foreignKey: 'rateId',
    as: 'rate',
  });
};

2. 修正bulkCreate数据字段名 #

传入的数据中optionRateId是错误字段名，应改为paymentOptionId，否则无法正确映射到表字段：

const data = [
  {
    rateId: 'ccf19a9f-9897-4328-84ba-f2558fbbe10a',
    paymentOptionId: '9f873f92-7fbd-492e-9416-dd12f7a71dee'
  },
  {
    rateId: '4329965a-89d0-46da-83f3-fbea27da8206',
    paymentOptionId: 'fae5edcc-f03e-4744-ba52-0da73406640c'
  },
]

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