Sequelize Model - Delete item with belongsToMany n:m association

Question

I'm attempting to set up a DELETE route for my app, which uses Sequelize with a Postgres db. I have two different models, "Product" and "Interest" (think of Interest as a sort of category or tag). Products can have multiple Interests and Interests can have multiple Products, so I'm using a belongsToMany association. As a result, I have three tables: Products, Interests, and ProductInterests. Here's how I have the associations set up:

For Products:

Product.belongsToMany(models.Interest, {
    foreignKey: 'productId',
    through: models.ProductInterest,
    as: 'interests',
    onDelete: 'CASCADE',
    hooks: true
});

For Interests:

Interest.belongsToMany(models.Product, {
    through: models.ProductInterest,
    as: 'products',
    foreignKey: 'interestId',
    onDelete: 'CASCADE',
    hooks: true
});

And this is the method being called in my controller for Products:

destroy(req, res) {
    return Product
        .findById(req.params.productId, {
            include: [
                {
                    association: Product.associations.interests
                }
            ]
        })
        .then((product) => {
            if (!product) {
                return res.status(404).send({
                    message: 'Product not found'
                });
            }
            return product
                .destroy()
                // I've also tried the following:
                // .destroy({
                //    truncate: true,
                //    cascade: true
                // })
                .then(() => res.status(204).send())
                .catch(error => res.status(400).send(error));
        })
        .catch(error => res.status(400).send(error));

This causes an infinite loops, with these queries being logged:

Executing (default): SELECT "Product"."id", "Product"."title", "Product"."brand", "Product"."url", "Product"."imageUrl", "Product"."currentPrice", "Product"."male", "Product"."female", "Product"."asin", "Product"."storeName", "Product"."createdAt", "Product"."updatedAt", "ProductInterest"."createdAt" AS "ProductInterest.createdAt", "ProductInterest"."updatedAt" AS "ProductInterest.updatedAt", "ProductInterest"."interestId" AS "ProductInterest.interestId", "ProductInterest"."productId" AS "ProductInterest.productId" FROM "Products" AS "Product" INNER JOIN "ProductInterests" AS "ProductInterest" ON "Product"."id" = "ProductInterest"."productId" AND "ProductInterest"."interestId" = 1;
Executing (default): SELECT "Interest"."id", "Interest"."name", "Interest"."createdAt", "Interest"."updatedAt", "ProductInterest"."createdAt" AS "ProductInterest.createdAt", "ProductInterest"."updatedAt" AS "ProductInterest.updatedAt", "ProductInterest"."interestId" AS "ProductInterest.interestId", "ProductInterest"."productId" AS "ProductInterest.productId" FROM "Interests" AS "Interest" INNER JOIN "ProductInterests" AS "ProductInterest" ON "Interest"."id" = "ProductInterest"."interestId" AND "ProductInterest"."productId" = 6;

My intention is to a) delete the product in the Product table; b) delete the association record(s) in the ProductInterest table. I do not wish to delete the Interest itself if the Product is being deleted. Likewise, I do not wish to delete associated Product(s) if an Interest is deleted (just the association).

I've tried several different permutations around this and can't find the right method. Any help would be appreciated.

UPDATE

Debugging the sequelize code a bit has led me to think that I shouldn't be using cascade like that. It's basically attempting to delete the product, which then (due to cascade) attempts to delete the Interest (which I don't want), which (due to cascade) then attempts to delete associated product, etc etc. Still not sure what the correct solution is.

Solution 1

First of All, the default behavior of belongsToMany is a cascade on deleting it is written in DOCS

For 1:1 and 1:m associations the default option is SET NULL for deletion, and CASCADE for updates. For n:m, the default for both is CASCADE. This means, that if you delete or update a row from one side of an n:m association, all the rows in the join table referencing that row will also be deleted or updated.

But I think It will not work till you write it explicitly while creating the foreign key references in table creation or migration like this:

queryInterface.createTable('ProductInterest', {
  productId: {
    type: Sequelize.INTEGER,
    references: {
        model: 'Product',
        key: '_id',
      },
    onDelete: 'cascade'
  },
  interestId: {
    type: Sequelize.INTEGER,
    references: {
        model: 'Interest',
        key: '_id',
      },
    onDelete: 'cascade'
  },
});

Solution 2

From my understanding, belongsToMany creates a join-table model for the given source and target - so, for your two tables Product and Interest, doing Product.belongsToMany(Interest) creates a new table ProductInterest that is essentially just a table of ProductId/InterestId pairs.

Sequelize's default behavior is such that when either a Product or Interest is deleted, its entries in ProductInterest are also deleted. In short, it sounds like the deletion constraints you wanted were there by default. I'm not sure what the effects of passing those additional onDelete constraints to the associations is having, but it sounds like you've found them to cause some unintended behavior.