Work around Sequelize’s unique constraints in belongsToMany associations

Question

I'm using Sequelize in my project. These are the two models:

const User = db.define('user', {
  name: Sequelize.STRING,
  password: Sequelize.STRING
})
const Product = db.define('product', {
  name: Sequelize.STRING,
  price: Sequelize.INTEGER
})

Now users can purchase products and I have associations setup like below:

Product.belongsToMany(User, {through: 'UserProducts'})
User.belongsToMany(Product, {through: 'UserProducts'})

I also have this UserProducts table with an additional column.

const UserProducts = db.define('UserProducts', {
  status: Sequelize.STRING
})

Sequelize creates a composite key with combination of userId and productId and will only allow one record with a combination of userId and productId. So, for example, userId 2 and productId 14.

This is a problem for me because sometimes people want to purchase multiple times. I need one of the following scenarios to work:

1. Don't use the composite key and instead have a completely new auto-increment column used as key in UserProducts.

2. Instead of making key with userId and productId alone, allow me to add one more column into the key such as the status so that unique key is a combination of the three.

I do want to use the associations as they provide many powerful methods, but want to alter the unique key to work in such a way that I can add multiple rows with the same combination of user id and product id.

And since my models/database is already running, I will need to make use of migrations to make this change.

Any help around this is highly appreciated.

Answer 1

If anyone else is having problems in v5 of Sequelize, it is not enough to specify a primary key on the 'through' model.
You have to explicitly set the unique property on the through model.

User.belongsToMany(Product, { through: { model: UserProducts, unique: false } });
Product.belongsToMany(User, { through: { model: UserProducts, unique: false } });