Express+Sequelize删除关联记录后重现问题求助

在ExpressJS中使用Sequelize执行删除关联记录再创建新记录的操作时，数据库中同时保留了旧记录和新记录。需求是删除所有与quotationId关联的QuotationItem记录，再创建新的关联记录，但多次尝试都没解决问题。相关代码如下：

const Quotation = require('../models/quotation');
const QuotationItem = require('../models/quotation_item');
const Category = require('../models/category');
const Users = require('../models/users');
const Item = require('../models/item');

exports.postEditQuotation = async (req,res,next) => {
    const quotationId = req.body.quotationId;
    const itemsCodes = req.body.code;
    const itemsPicture = req.body.picture;
    const itemsDesc = req.body.desc;
    const itemsUnit = req.body.unit;
    const itemsQty = req.body.quotationQty;
    const itemsTotal = req.body.quotationTotal;
    const quotationDate = req.body.quotationDate;
    const quotationClient = req.body.quotationClient;
    const quotationAttention = req.body.quotationAttention;
    const quotationLocation = req.body.quotationLocation;
    const quotationSubject = req.body.quotationSubject;

    await Quotation.findOne({ where: { id: quotationId } }).then(quotation => {
        quotation.date = quotationDate;
        quotation.client = quotationClient;
        quotation.attention = quotationAttention;
        quotation.location = quotationLocation;
        quotation.subject = quotationSubject;
        quotation.save();
    }).then(async () => {
        await QuotationItem.destroy({
            where: {
                id: quotationId
            }
        }).then(async () => {
            if(Array.isArray(itemsCodes)) {
                for(let i=0; i< itemsCodes.length; i++){
                    await QuotationItem.create({
                        code : itemsCodes[i],
                        picture : itemsPicture[i],
                        desc : itemsDesc[i],
                        unit : itemsUnit[i],
                        qty : itemsQty[i],
                        total : itemsTotal[i],
                        quotationId : quotationId
                    })
                }
            } else {
                await QuotationItem.create({
                    code : itemsCodes,
                    picture : itemsPicture,
                    desc : itemsDesc,
                    unit : itemsUnit,
                    qty : itemsQty,
                    total : itemsTotal,
                    quotationId : quotationId
                })
            }

        })
    }).then(() => {
        res.redirect('/quotations');
    }).catch(err => {
        const error = new Error(err);
        error.httpStatusCode = 500;
        return next(error);
    })
}

核心问题排查 #

• 删除条件错误：QuotationItem.destroy的where条件写了id: quotationId，但quotationId是关联外键，不是QuotationItem自身的主键id，这导致删除操作根本没匹配到要删除的记录。
• 异步操作未等待：quotation.save()没有加await，会导致后续的删除、创建操作在主表更新完成前就执行，同时混用await和.then()的嵌套写法也容易导致异步时序混乱。

修复后的代码 #

const Quotation = require('../models/quotation');
const QuotationItem = require('../models/quotation_item');
const Category = require('../models/category');
const Users = require('../models/users');
const Item = require('../models/item');

exports.postEditQuotation = async (req, res, next) => {
    try {
        const quotationId = req.body.quotationId;
        const itemsCodes = req.body.code;
        const itemsPicture = req.body.picture;
        const itemsDesc = req.body.desc;
        const itemsUnit = req.body.unit;
        const itemsQty = req.body.quotationQty;
        const itemsTotal = req.body.quotationTotal;
        const quotationDate = req.body.quotationDate;
        const quotationClient = req.body.quotationClient;
        const quotationAttention = req.body.quotationAttention;
        const quotationLocation = req.body.quotationLocation;
        const quotationSubject = req.body.quotationSubject;

        // 更新主表Quotation，等待保存完成
        const quotation = await Quotation.findOne({ where: { id: quotationId } });
        quotation.date = quotationDate;
        quotation.client = quotationClient;
        quotation.attention = quotationAttention;
        quotation.location = quotationLocation;
        quotation.subject = quotationSubject;
        await quotation.save();

        // 删除所有关联的QuotationItem，修正where条件为外键quotationId
        await QuotationItem.destroy({
            where: {
                quotationId: quotationId
            }
        });

        // 创建新的关联记录
        if (Array.isArray(itemsCodes)) {
            for (let i = 0; i < itemsCodes.length; i++) {
                await QuotationItem.create({
                    code: itemsCodes[i],
                    picture: itemsPicture[i],
                    desc: itemsDesc[i],
                    unit: itemsUnit[i],
                    qty: itemsQty[i],
                    total: itemsTotal[i],
                    quotationId: quotationId
                });
            }
        } else {
            await QuotationItem.create({
                code: itemsCodes,
                picture: itemsPicture,
                desc: itemsDesc,
                unit: itemsUnit,
                qty: itemsQty,
                total: itemsTotal,
                quotationId: quotationId
            });
        }

        res.redirect('/quotations');
    } catch (err) {
        const error = new Error(err);
        error.httpStatusCode = 500;
        return next(error);
    }
}

额外优化建议 #

• 可以开启Sequelize的日志功能，直接查看执行的SQL语句，方便快速验证删除、创建操作是否符合预期。
• 建议用Sequelize事务包裹整个更新、删除、创建流程，一旦中间步骤出错，所有操作都会回滚，避免出现数据不一致的情况。

内容的提问来源于stack exchange，提问作者othman ali