如何在PostgreSQL Sequelize事务中获取真实数据库错误而非通用事务终止错误

问题场景 #

在Sequelize-Typescript中使用事务创建Borrower记录及关联映射时，Borrower表部分字段设有唯一约束：

• 不使用事务直接执行创建操作时，会返回明确的唯一约束冲突错误：original: error: duplicate key value violates unique constraint "borrowers_unique_cin"，可直接反馈给前端用户。
• 使用事务时，若请求包含重复字段值，会抛出通用事务终止错误：current transaction is aborted, commands ignored until end of transaction block，错误码为25P02，外层try-catch无法捕获原始的唯一约束冲突信息。

事务代码 #

return await this.sequelize.transaction(async (transaction) => {
  const borrower = await this.onBoardingServiceDao.createBorrower(
    body,
    transaction,
  );
  await this.onBoardingServiceDao.createUserBorrowerMapping(
    { userid: userId, borrowerid: borrower.id },
    transaction,
  );
  return borrower;
});

createBorrower方法实现 #

public async createBorrower(body, transaction: Transaction) {
  return await this.borrowerModel.create(body, { transaction });
}

事务错误堆栈示例 #

{
  "name": "SequelizeDatabaseError",
  "parent": {
    "length": 145,
    "name": "error",
    "severity": "ERROR",
    "code": "25P02",
    "detail": undefined,
    "hint": undefined,
    "position": undefined,
    "internalPosition": undefined,
    "internalQuery": undefined,
    "where": undefined,
    "schema": undefined,
    "table": undefined,
    "column": undefined,
    "dataType": undefined,
    "constraint": undefined,
    "file": "postgres.c",
    "line": "1498",
    "routine": "exec_parse_message",
    "sql": "INSERT INTO \"public\".\"borrowers\" (\"id\",\"company_name\",\"company_pan\",\"company_cin\",\"contact_email\",\"contact_phone1\",\"contact_phone2\",\"company_website\",\"company_linkedin\",\"registration_type\",\"status\",\"operating_sectors\",\"incorporation_year\") VALUES (gen_random_uuid(),$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) RETURNING \"id\",\"company_name\",\"company_pan\",\"company_cin\",\"contact_email\",\"contact_phone1\",\"contact_phone2\",\"company_website\",\"company_linkedin\",\"registration_type\",\"status\",\"operating_sectors\",\"incorporation_year\",\"onboarding_completed_at\",\"gdrive_folder_id\";",
    "parameters": [
      "ABC Software Technologies",
      "ABC",
      "CFE",
      "test@email.com",
      "9999999999",
      "",
      "",
      "",
      1,
      3,
      [
        "ADVISORY_SERVICES"
      ],
      2009
    ]
  },
  "original": {
    "length": 145,
    "name": "error",
    "severity": "ERROR",
    "code": "25P02",
    "detail": undefined,
    "hint": undefined,
    "position": undefined,
    "internalPosition": undefined,
    "internalQuery": undefined,
    "where": undefined,
    "schema": undefined,
    "table": undefined,
    "column": undefined,
    "dataType": undefined,
    "constraint": undefined,
    "file": "postgres.c",
    "line": "1498",
    "routine": "exec_parse_message",
    "sql": "INSERT INTO \"public\".\"borrowers\" (\"id\",\"company_name\",\"company_pan\",\"company_cin\",\"contact_email\",\"contact_phone1\",\"contact_phone2\",\"company_website\",\"company_linkedin\",\"registration_type\",\"status\",\"operating_sectors\",\"incorporation_year\") VALUES (gen_random_uuid(),$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) RETURNING \"id\",\"company_name\",\"company_pan\",\"company_cin\",\"contact_email\",\"contact_phone1\",\"contact_phone2\",\"company_website\",\"company_linkedin\",\"registration_type\",\"status\",\"operating_sectors\",\"incorporation_year\",\"onboarding_completed_at\",\"gdrive_folder_id\";",
    "parameters": [
      "ABC Software Technologies",
      "ABC",
      "CFE",
      "test@email.com",
      "9999999999",
      "",
      "",
      "",
      1,
      3,
      [
        "ADVISORY_SERVICES"
      ],
      2009
    ]
  },
  "sql": "INSERT INTO \"public\".\"borrowers\" (\"id\",\"company_name\",\"company_pan\",\"company_cin\",\"contact_email\",\"contact_phone1\",\"contact_phone2\",\"company_website\",\"company_linkedin\",\"registration_type\",\"status\",\"operating_sectors\",\"incorporation_year\") VALUES (gen_random_uuid(),$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) RETURNING \"id\",\"company_name\",\"company_pan\",\"company_cin\",\"contact_email\",\"contact_phone1\",\"contact_phone2\",\"company_website\",\"company_linkedin\",\"registration_type\",\"status\",\"operating_sectors\",\"incorporation_year\",\"onboarding_completed_at\",\"gdrive_folder_id\";",
  "parameters": [
    "ABC Software Technologies",
    "ABC",
    "CFE",
    "test@email.com",
    "9999999999",
    "",
    "",
    "",
    1,
    3,
    [
      "ADVISORY_SERVICES"
    ],
    2009
  ]
}

解决方案 #

1. 在事务回调内即时捕获原始错误 #

PostgreSQL中，事务内第一个操作抛出错误后，后续操作会触发25P02事务中止错误。因此必须在第一个出错的操作（createBorrower）后立即捕获错误，才能拿到原始的唯一约束冲突信息（错误码23505）。

修改后的事务代码：

return await this.sequelize.transaction(async (transaction) => {
  let borrower;
  try {
    // 直接在事务回调内捕获createBorrower的错误
    borrower = await this.onBoardingServiceDao.createBorrower(body, transaction);
  } catch (err) {
    // 判断是否为PostgreSQL唯一约束冲突错误（错误码23505）
    if (err.parent?.code === '23505') {
      // 提取约束名称，构造友好错误信息
      throw new Error(`字段值重复：违反唯一约束 ${err.parent.constraint}`);
    }
    // 其他错误正常抛出，事务自动回滚
    throw err;
  }
  // 若createBorrower成功，再执行关联映射创建
  await this.onBoardingServiceDao.createUserBorrowerMapping(
    { userid: userId, borrowerid: borrower.id },
    transaction,
  );
  return borrower;
});

2. 关键原理说明 #

Sequelize的自动事务（传入async回调的模式）会在回调抛出任何错误时自动执行回滚。但如果错误是在事务内后续操作触发的，此时事务已处于中止状态，只能拿到25P02的通用错误。只有在第一个出错的操作执行后立即捕获，才能获取到原始的23505错误及约束信息。

3. 可选：DAO层错误透传 #

确保DAO层不会吞掉原始错误，保持错误信息的完整性：

public async createBorrower(body, transaction: Transaction) {
  try {
    return await this.borrowerModel.create(body, { transaction });
  } catch (err) {
    // 直接透传错误，不做额外包装，保留原始错误的所有属性
    throw err;
  }
}

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