Sequelize创建Provider时报错：provider_id列不存在问题求助

使用PostgreSQL数据库，创建Provider时Sequelize触发SequelizeDatabaseError，提示“column "provider_id" does not exist”。查看执行的SQL发现，Sequelize在插入providers表后尝试返回provider_id字段，但该字段属于users表而非providers表。已在User和Provider模型中定义关联关系（User属于Provider，Provider拥有一个User），且相同的关联方式在其他表中可正常运行，不清楚为何此处报错。

抛出的错误信息 #

name: 'SequelizeDatabaseError',
parent: error: column "provider_id" does not exist

length: 112,
severity: 'ERROR',
code: '42703',
detail: undefined,
hint: undefined,
position: '186',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_relation.c',
line: '3337',
routine: 'errorMissingColumn',
sql: 'INSERT INTO "providers" ("id","advice_number","created_at","updated_at","company_id") VALUES (DEFAULT,$1,$2,$3,$4) RETURNING "id","advice_number","created_at","updated_at","deleted_at","provider_id","company_id","advice_id";',
parameters: [
'00000000',
'2022-10-06 17:11:28.621 +00:00',
'2022-10-06 17:11:28.621 +00:00',
1
]
},
original: error: column "provider_id" does not exist

User模型代码 #

const { Model, DataTypes } = require('sequelize');

class User extends Model {
  static init(sequelize) {
    super.init(
      {
        name: {
          type: DataTypes.STRING,
          allowNull: false,
        },
        email: {
          type: DataTypes.STRING,
          allowNull: false,
        },
        password: {
          type: DataTypes.STRING,
          allowNull: false,
        },
        insuranceNumber: {
          type: DataTypes.STRING,
          allowNull: false,
        },
        phone: {
          type: DataTypes.STRING,
          allowNull: false,
        },
        type: {
          type: DataTypes.STRING,
          allowNull: false,
        },
      },
      {
        scopes: {
          noPassword: {
            attributes: { exclude: ['password'] },
          },
        },
        sequelize,
        paranoid: true,
        tableName: 'users',
      }
    );
  }
  static associate(models) {
    this.hasMany(models.Address, { foreignKey: 'userId', as: 'addresses' });
    this.belongsTo(models.Provider, { foreignKey: 'providerId', as: 'provider' });
    this.belongsTo(models.Customer, { foreignKey: 'customerId', as: 'customer' });
  }
}

module.exports = User;

Provider模型代码 #

const { Model, DataTypes } = require('sequelize');

class Provider extends Model {
  static init(sequelize) {
    super.init(
      {
        adviceNumber: {
          type: DataTypes.STRING,
          allowNull: false,
        },
      },
      {
        sequelize,
        paranoid: true,
        tableName: 'providers',
        underscored: true,
      }
    );
  }
  static associate(models) {
    this.belongsTo(models.Company, { foreignKey: 'companyId', as: 'companies' });
    this.belongsTo(models.Advice, { foreignKey: 'adviceId', as: 'advices' });
    this.hasOne(models.User, { foreignKey: 'providerId', as: 'user' });
  }
}

module.exports = Provider;

Store函数代码 #

async store(req, res) {
    const { name, email, password, insuranceNumber, phone, type } = req.body;
    let { provider} = req.body;

    const { adviceNumber, companyId, adviceId } = provider;
    provider = await Provider.create({ adviceNumber, companyId, adviceId }, { raw: true });
    

    let user = await User.create(
      {
        name,
        email,
        password,
        insuranceNumber,
        phone,
        type,
        providerId: provider.id ,
      },
      { raw: true }
    );

    return res.json(user);
  }

解决方案 #

问题根源在于Provider模型的underscored: true配置与关联定义的冲突：

1. underscored: true会让Sequelize自动将模型中的驼峰字段转换为下划线格式的数据库列名，但在hasOne关联中，你指定的foreignKey: 'providerId'被Sequelize错误映射为providers表的provider_id列，而实际上这个外键应该存在于users表中。
2. Sequelize因此在插入providers表的RETURNING子句中包含了不存在的provider_id字段，导致报错。

修复方式 #

方式一：显式指定关联的源键

修改Provider模型中的hasOne关联定义，明确指定当前模型的主键作为关联的源键，避免underscored配置的干扰：

static associate(models) {
  this.belongsTo(models.Company, { foreignKey: 'companyId', as: 'companies' });
  this.belongsTo(models.Advice, { foreignKey: 'adviceId', as: 'advices' });
  this.hasOne(models.User, { 
    foreignKey: 'providerId', 
    as: 'user',
    sourceKey: 'id' // 显式指定Provider的主键作为关联源
  });
}

方式二：移除underscored: true配置

如果不需要自动将驼峰字段转换为下划线格式，直接删除Provider模型中的underscored: true配置，让Sequelize严格按照你定义的字段名映射数据库列：

{
  sequelize,
  paranoid: true,
  tableName: 'providers',
  // 移除underscored: true
}

额外检查

确认数据库表结构：providers表中不存在provider_id列，而users表中存在provider_id（或providerId，取决于你的配置）列。

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