Sequelize error with join table: DatabaseError [SequelizeDatabaseError]: column does not exist

Question

I'm trying to run the following code block, for some reason the query tries to insert it into a column labeled "users->user_group"."userUuid", despite the fact that I have not reference the string literal userUuid once in the project (through search not in the code base), also check columns in pg-admin (using PostgreSQL), both columns in the user_group table are user_uuid and group_uuid, both columns are also validated and populated properly.

const result = await group.findAll({
  include: user,
});

Postman body returns the following error "hint": "Perhaps you meant to reference the column "users->user_group.user_uuid".",

I have 3 models user, group and user_group. The relations have been defined per documentation and countless other articles and videos.

user model

module.exports = (sequelize, DataTypes) => {
  const user = sequelize.define(
    "user",
    {
      uuid: {
        type: DataTypes.STRING,
        primaryKey: true,
        allowNull: false,
        unique: true,
      },
      username: {
        type: DataTypes.STRING,
        unique: true,
        allowNull: false,
    },
    {
      freezeTableName: true,
    }
  );

  user.associate = (models) => {
    user.belongsToMany(models.group, {
      // as: "userUuid",
      through: models.user_group,
      foreignKey: "user_uuid",
    });
  };

  return user;
};

group model

module.exports = (sequelize, DataTypes) => {
  const group = sequelize.define(
    "group",
    {
      uuid: {
        type: DataTypes.STRING,
        primaryKey: true,
        allowNull: false,
        unique: true,
      },
      title: {
        type: DataTypes.STRING,
        unique: true,
        allowNull: false,
      },
    },
    {
      freezeTableName: true,
    }
  );

  group.associate = (models) => {
    group.belongsToMany(models.user, {
      // as: "groupUuid",
      through: models.user_group,
      foreignKey: "group_uuid",
    });
  };

  return group;
};

user_group model

module.exports = (sequelize, DataTypes) => {
  const user_group = sequelize.define(
    "user_group",
    {
      uuid: {
        type: DataTypes.STRING,
        primaryKey: true,
        allowNull: false,
        unique: true,
      },
      user_uuid: {
        type: DataTypes.STRING,
        allowNull: false,
        references: {
          model: "user",
          key: "uuid",
        },
      },
      group_uuid: {
        type: DataTypes.STRING,
        allowNull: false,
        references: {
          model: "group",
          key: "uuid",
        },
      },
      author: {
        type: DataTypes.STRING,
        unique: true,
        allowNull: true,
      },
    },
    {
      freezeTableName: true,
    }
  );

  user_group.associate = (models) => {
    user_group.belongsTo(models.user, {
      foreignKey: "user_uuid",
    });
    user_group.belongsTo(models.group, {
      foreignKey: "group_uuid",
    });
  };

  return user_group;
};

Any help is much apprecaited, thanks!

Anatoly · Accepted Answer · 2021-04-06 21:27:01Z

1

You should indicate otherKey option along with foreignKey in belongsToMany in order to indicate a foreign key column on the other model otherwise you will end up with a default name of an other key, see below:

The name of the foreign key in the join table (representing the target model) or an object representing the type definition for the other column (see Sequelize.define for syntax). When using an object, you can add a name property to set the name of the column. Defaults to the name of target + primary key of target (your case: user+uuid)

group.belongsToMany(models.user, {
      // as: "groupUuid",
      through: models.user_group,
      foreignKey: "group_uuid",
      otherKey: "user_uuid"
    });

answered Apr 6, 2021 at 21:27

Anatoly

23.2k3 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yusuf Yasin Over a year ago

yep the answer turned out to be that sequlzie messes up the naming sequence for virtually everything from table names to column names as well (foreign keys only). Had to change everything from _ to camelCase since apparently the underscore object they set (underscor: true) for there models is pretty buggy,

jimmy fernando · Accepted Answer · 2021-04-08 09:50:41Z

0

const result = await group.findAll({
  include: {user},
});

you should to create like this. baecause you missing this {}.

answered Apr 8, 2021 at 9:50

jimmy fernando

1

Collectives™ on Stack Overflow

Sequelize error with join table: DatabaseError [SequelizeDatabaseError]: column does not exist

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related