Increment or Decrement value using Sequelize upsert()

Question

In Sequelize I am using upsert() to insert a new row if it doesn't exist or increment one of its field values by some other value if it exists.

Inserting works fine but when the value gets updated it is not getting incremented but completely updated

let x = 5;

OrderDetails.upsert({ 
   menuItemId: 1234, 
   orderId: 4321, 
   quantity: sequelize.literal((`quantity + ${x}`)
})

This results in this query:

INSERT INTO `OrderDetails` (`menuItemId`,`orderId`,`quantity`,`createdAt`,`updatedAt`) VALUES (?,?,quantity + 5,?,?) 
ON DUPLICATE KEY UPDATE `menuItemId`=VALUES(`menuItemId`),`orderId`=VALUES(`orderId`),`quantity`=VALUES(`quantity`),`updatedAt`=VALUES(`updatedAt`);

So if for example the previous value was 10 then after the query the updated value will be 5 instead of 15.

My guess is that there is a kind of bug in Sequelize when using upsert() with sequelize.literal(). Apparently, it is only recognized in the insertion and not in the update.

user_9643819 · Answer

Apparently, there is a bug in Sequelize so I opened an issue on Github about it (you can check it out here). This question was asked with Sequelize version 6.3.5 so maybe if you are reading this, the bug has already being solved.

What I ended up doing was getting the previous value of the field and then adding it up with the amount to be incremented:

let extra = 5;
let previousValue = /....LOGIC.../

OrderDetails.upsert({ 
   menuItemId: 1234, 
   orderId: 4321, 
   quantity: previousValue + extra
})

Increment or Decrement value using Sequelize upsert()

1 Answers