基于Sequelize创建数据后执行decrement操作的技术问询

参考《Sequelize increment函数报错》一文后，我编写了以下代码：

exports.create = function (req, res) {
  models.sparepart_request.create({
    codWO: req.body.codWO,
    codSparePart: req.body.codSparePart,
    quantity: req.body.quantity,
    date_request: req.body.date_request,
    codUser: req.body.codUser,
    request_return: req.body.request_return,
    received: req.body.received
  }).then(function (item) {
    return models.sparepart.decrement(
      'stock',
      { by: req.body.quantity, where: { ... 

现针对该Sequelize数据操作实现寻求技术答疑。

Hey there! Let's work through the potential issues and optimizations for your Sequelize code. Here are key points to check and fix:

1. 补全decrement的where条件 #

Your code cuts off at where: { ... — this will definitely throw a syntax error. You need to specify which sparepart record to update. Typically, you'd match the codSparePart from the request, like:

return models.sparepart.decrement(
  'stock',
  { 
    by: req.body.quantity, 
    where: { codSparePart: req.body.codSparePart } // 补全匹配条件
  }
)

Without a complete where clause, Sequelize can't target the correct row, or might accidentally update all rows (if you omit where entirely, which is risky!).

2. 用事务保证原子性 #

Creating a sparepart_request and decrementing sparepart.stock are two linked operations. If one fails, the other should roll back to avoid data inconsistency. Add a transaction like this:

exports.create = async function (req, res) {
  const t = await models.sequelize.transaction(); // 启动事务
  try {
    const item = await models.sparepart_request.create({
      codWO: req.body.codWO,
      codSparePart: req.body.codSparePart,
      quantity: req.body.quantity,
      date_request: req.body.date_request,
      codUser: req.body.codUser,
      request_return: req.body.request_return,
      received: req.body.received
    }, { transaction: t });

    await models.sparepart.decrement(
      'stock',
      { 
        by: req.body.quantity, 
        where: { codSparePart: req.body.codSparePart },
        transaction: t // 关联事务
      }
    );

    await t.commit(); // 提交事务
    res.status(201).json(item);
  } catch (err) {
    await t.rollback(); // 失败则回滚
    res.status(500).json({ error: err.message });
  }
};

3. 验证数据类型与合法性 #

• Make sure the stock field in your sparepart model is defined as a numeric type (e.g., INTEGER, DECIMAL). If it's a string, decrement will throw a type error.
• Add validation for req.body.quantity: ensure it's a positive number (you don't want to decrement by 0 or a negative value). You can add this before the database operations:

if (!req.body.quantity || req.body.quantity <= 0) {
  return res.status(400).json({ error: "Quantity must be a positive number" });
}

4. 添加错误处理 #

Your original code lacks a .catch() block, so any errors (like database connection issues, invalid data) won't be handled properly. Always include error handling to return meaningful responses to the client.

5. 检查输入完整性 #

Ensure that req.body contains all required fields (e.g., codSparePart, quantity). You can add simple checks or use middleware like express-validator to validate inputs before reaching this controller function.

内容的提问来源于stack exchange，提问作者Miguel-Ángel