Optimize SQL transactions in API

Question

I had a job interview recently and I got a little homework. Then I received feedback that I had an unnecessary combination of queries and transactions in my update endpoint.

export const update = async (req: Request, res: Response, next: NextFunction) => {
    try {
        const reportId = parseInt(req.params.id)
        const { name, age, info } = req.body

        const report = await ReportModel.findOne({
            where: {
                id: reportId
            }
        })
        if (!report) return next(new EntityNotExistError("Report does not exist"))

        await ReportModel.update({
            name,
            age,
            info
        }, {
            where: {
                id: reportId
            }
        })

        const _report = await ReportModel.findOne({
            where: {
                id: reportId
            }
        })

        return res.json({
            message: "Report edited successfully",
            report: _report
        })
    } catch (error) {
        return next(error)
    }
}

As you can see, the first query checks if the entity exists, then I perform an update on the entity, and the last query returns the updated entity. Is there some way to optimize communication with the database?

Answer 1

Your code involves three different interactions with the database for a single update operation:

1. Check whether the entity exists | Code: ReportModel.findOne()
2. Update entity | Code: ReportModel.update()
3. Get the updated entity to return in the response | Code: ReportModel.findOne()

Reducing database queries gets the job done and improves performance.

**Your fixed code:**

export const update = async(req: Request, res: Response, next: NextFunction) => {
  try {
    const reportId = parseInt(req.params.id);
    const {
      name,
      age,
      info
    } = req.body;

    // 执行更新并获取受影响的行数
    const [numberOfAffectedRows] = await ReportModel.update({
      name,
      age,
      info
    }, {
      where: {
        id: reportId
      },
      // 此标志检查更新函数是否返回更新后的对象
      returning: true
    });

    // 检查实体是否被找到和更新
    if (numberOfAffectedRows === 0) {
      return next(new EntityNotExistError("报告不存在"));
    }

    // 获取更新后的报告
    const updatedReport = await ReportModel.findOne({
      where: {
        id: reportId
      }
    });

    return res.json({
      message: "报告已成功编辑",
      report: updatedReport
    });

  } catch (error) {
    return next(error);
  }
}