SequelizeDatabaseError error: column does not exist

Question

When I try to create a provider, sequelize apparently is trying to return the provider_id field but it is from the users table and not from the provider (see in sql executed).

In models is defined the relationship by the fields, and I use the same relationship in other tables and works fine, I don't now why this not works.

I'm using postgres db.

Error thrown:

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 model:

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 model:

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 function:

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);
  }

Answer 1

It seems for relations what you have written is not according to your definition

  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' });
  }

in belongsTo foreignKey should be of the source table key so it should be from provider table only, you have defined the relation but the keys are not present in the schema definition.

class Provider extends Model {
  static init(sequelize) {
    super.init(
      {
        adviceNumber: {
          type: DataTypes.STRING,
          allowNull: false,
        },
        adviceId: {
          type: DataTypes.STRING,
          allowNull: false,
        },
        companyId: {
          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' });
  }
}