Adding relation between specified instances and source instance in a M:N relationship with multiple foreign keys not supported

[razvan286]

Issue

The .$add seems to be resulting in a faulty SQL query when there are multiple M:N relationships set on a junction table.

The issue happens only inside of a Docker container, localhost works as expected.

Versions

Dialect: postgres
Database version: 15
Sequelize version: 6.25.3
Sequelize-typescript version: 2.1.6
typescript: 5.2.2

Issue type

• feature request

Context

I have three models in place Activity, Item, and Label.
M:N relationship between Activity and Label
M:N relationship between Item and Label
I also have an explicit junction model named LabelAssociations where I define all attributes. I explicitly defined this model to specify all of its foreign keys which in this case are labelId, activityId, and itemId.

(I need to mention that I have multiple successful M:N relationships that work properly. These relationships are usually set between just two models, not three as in the current issue).

Model definition

Activity model:

@Table({ tableName: 'activities' })
export default class Activity extends Model {
  @PrimaryKey
  @IsUUID(4)
  @Default(DataType.UUIDV4)
  @Column(DataType.UUID)
  declare id: string;

  @AllowNull(false)
  @Column(DataType.STRING)
  declare name: string;

  @UpdatedAt
  declare updatedAt: Date;

  @CreatedAt
  declare createdAt: Date;

  @BelongsToMany(() => Label, {
    foreignKey: 'activityId',
    otherKey: 'labelId',
    through: 'labelAssociations',
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
  })
  declare labels: ReturnType<() => Label[]>;

  // Declare 'super many-to-many' association to be able to query all associated subLabels
  @HasMany(() => LabelAssociations, {
    foreignKey: 'activityId',
    as: 'associatedSubLabels',
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
  })
  declare associatedSubLabels: ReturnType<() => LabelAssociations[]>;

Item model:

@Table({ tableName: 'items' })
export default class Item extends Model {
@PrimaryKey
@IsUUID(4)
@Default(DataType.UUIDV4)
@Column(DataType.UUID)
declare id: string;

@Column(DataType.STRING)
declare targetWord: string;

@Column(DataType.STRING)
declare questionPhrase: string;

@UpdatedAt
declare updatedAt: Date;

@CreatedAt
declare createdAt: Date;

// Associations

@BelongsToMany(() => Label, {
  foreignKey: 'itemId',
  otherKey: 'labelId',
  through: 'labelAssociations',
  onDelete: 'CASCADE',
  onUpdate: 'CASCADE',
})
declare labels: ReturnType<() => Label[]>;

// Declare 'super many-to-many' association to be able to query all associated subLabels
@HasMany(() => LabelAssociations, {
  foreignKey: 'itemId',
  as: 'associatedSubLabels',
  onDelete: 'CASCADE',
  onUpdate: 'CASCADE',
})
declare associatedSubLabels: ReturnType<() => LabelAssociations[]>;

Label model:

@Table({ tableName: 'labels' })
export default class Label extends Model {
  @PrimaryKey
  @IsUUID(4)
  @Default(DataType.UUIDV4)
  @Column(DataType.UUID)
  declare id: string;

  @AllowNull(false)
  @Column(DataType.STRING)
  declare name: string;

  @UpdatedAt
  declare updatedAt: Date;

  @CreatedAt
  declare createdAt: Date;

  // Associations

  @BelongsToMany(() => Activity, {
    foreignKey: 'labelId',
    otherKey: 'activityId',
    through: 'labelAssociations',
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
  })
  declare activities: ReturnType<() => Activity[]>;

  @BelongsToMany(() => Item, {
    foreignKey: 'labelId',
    otherKey: 'itemId',
    through: 'labelAssociations',
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
  })
  declare items: ReturnType<() => Item[]>;

LabelAssociations model:

@Table({ tableName: 'labelAssociations' })
export default class LabelAssociations extends Model {
  @PrimaryKey
  @IsUUID(4)
  @Default(DataType.UUIDV4)
  @Column(DataType.UUID)
  declare id: string;

  @AllowNull(true)
  @ForeignKey(() => Activity)
  @Unique(false)
  @Default(null)
  @Column(DataType.UUID)
  declare activityId: string;

  @AllowNull(true)
  @ForeignKey(() => Item)
  @Unique(false)
  @Default(null)
  @Column(DataType.UUID)
  declare itemId: string;

  @AllowNull(false)
  @ForeignKey(() => Label)
  @Unique(false)
  @Column(DataType.UUID)
  declare labelId: string;

  @UpdatedAt
  declare updatedAt: Date;

  @CreatedAt
  declare createdAt: Date;
}

Steps to reproduce

 if (object instanceof Activity) {
    return object.$add('labels', sequelizeLabels as Label[], {
      through: { activityId: object.dataValues.id },
    });
  } else if (object instanceof Item) {
    return object.$add('labels', sequelizeLabels as Label[], {
      through: { itemId: object.dataValues.id },
    });
  }

The .$add command on both instances won't ever find the activityId column in the LabelAssociations. It always just validates the itemId column.

** SQL error:**

 STATEMENT:  INSERT INTO "labelAssociations" ("createdAt","updatedAt","labelId","itemId") VALUES ('2024-03-26 09:33:54.651 +00:00','2024-03-26 09:33:54.651 +00:00','ffffffff-bbbb-bbbb-bbbb-aaaaaaaaaa21',NULL) RETURNING "createdAt","updatedAt","labelId","itemId"
 
  ERROR:  null value in column "id" of relation "labelAssociations" violates not-null constraint

I can see that the .$add command has one more parameter of type AssociationActionOptions. Is it possible to provide an alias there or even the foreignKey explicitly?

Any help would be appreciated. Thank you in advance.