Sequelize ORM: Using Deferrable constraints on junction table

Question

I'm trying to use a Many:Many association with Sequelize (against a Postgre db).

I want to create a venue and associate it with a user in a single transaction. The venues and users are set up as normal Sequelize models, and are each set to "belongToMany" of the other through junction table "userVenue".

The issue is I can't seem to create a new venue and also its first user association in the same transaction. Postgre throws the following error:

insert or update on table "userVenues" violates foreign key constraint "userVenues_venueId_fkey"

since the new venue hasn't been committed yet.

I think I need to set the foreign key constraints on "userId" and "venueId" in the "userVenues" junction table to be initially deferrable as per Sequelize docs, however, as Sequelize is defining the junction table's "userId" and "venueId" automatically, I can't figure out how to set them to be INITIALLY_DEFERRABLE.

I have tried simply setting the transaction to SET_DEFERRED (as per the same doc), and I can see in the logs that it does issue the command "SET CONSTRAINTS ALL DEFERRED" to the DB, however since Sequelize seems to default the constraints to NOT (deferrable) I still fail with the same error. If I don't use a transaction the two sequential INSERTS work just fine.

Is there a way to define these foreign keys as deferrable? Or am I approaching this the wrong way?

Many thanks!

Relevant code below:

  // *** ASSOCIATIONS ***
  
  // The Venue association
  Venue.belongsToMany(models.user, {
    through: models.userVenue,
  });
  
    // The User association
  User.belongsToMany(models.venue, {
    through: models.userVenue,
  });
  
  
  
  // *** THE TRANSACTION ***
  const newVenue = await db.sequelize.transaction(
  { deferrable: Sequelize.Deferrable.SET_DEFERRED },
  async (t) => {
    const newRow = await db.venue.create(
      { ...payload },
      { transaction: t }
    );

    await newRow.addUser(
      socket.user,
      {
        through: { permit: 'edit' },
      },
      { transaction: t }
    );

    return newRow;
  }

Are you sure this { through: { permit: 'edit' }, }, { transaction: t } Should not be { through: { permit: 'edit' }, transaction: t }? — Anatoly
Hey, you're absolutely right! Thanks for pointing that out. I realized this yesterday but forgot to update this post. The transaction was not specified correctly, but I didn't realize because when this line failed the whole transaction rolled back, so I assumed it was correctly included in the transaction. However I forgot that throwing any error in the middle of the managed transaction will abort the whole thing... oops! I didn't think I should need a deferrable constraint in this simple transaction, and turns out I didn't... just better attention to syntax! — awanuch

awanuch awanuch · Accepted Answer · 2020-05-27T18:33:25

After further research, it seems like this might be a bug in Sequelize: https://github.com/sequelize/sequelize/issues/8194

Though it has been flagged as a bug there has been no fix yet. One helpful user replied to my post in that thread with a potential fix, but at the moment there is no pull request.

The workaround I am currently using, should anyone find it helpful, is the run this raw query after the DB Sync to alter the constraint. It does require knowledge of the constraint name.

    await sequelize.query(
      'ALTER TABLE "userVenues" ALTER CONSTRAINT "userVenues_venueId_fkey" DEFERRABLE INITIALLY DEFERRED'
    );

Sequelize ORM: Using Deferrable constraints on junction table

1 Answers