PostgreSQL增删接口返回成功但未实际修改数据问题排查

• 现象：Postman调用Insert/Delete接口返回201/200成功状态，但数据库实际无数据新增或删除；GET请求正常，pgAdmin/DBeaver直接执行SQL语句可正常生效。
• 已排查：数据库账号具备读写权限，Postman参数传递后返回状态正常，注意到prebuildEmptyResultObject存在null值，暂不确定是否影响JSON提交。

相关代码实现 #

控制器代码（POST和DELETE请求实现） #

const dbConfig = require('../configs/dbconfig')


const createIncident = async(req,res)=>{
  try {
    let { mine_id, latitude, longitude, description, severity } = req.body;
    console.log("Received data", mine_id, latitude, longitude, description, severity)

    const query = 'INSERT INTO incidents (mine_id,latitude,longitude,description,severity) values ($1, $2, $3, $4, $5) RETURNING *';
    const values = [mine_id, latitude, longitude, description, severity];

    const result = await dbConfig.pool.query(query,values);
    await dbConfig.pool.query('COMMIT')

    res.status(201).send({message:'Incident inserted successfully',result});
    
  } catch (error) {
    console.error('Error inserting data',error);
    res.status(500).json({error:'Internal server error'});
    
  }
};


const deleteIncident = (request, response) => {
  const id = parseInt(request.params.id)

  dbConfig.pool.query('DELETE FROM incidents WHERE id = $1', [id], (error, results) => {
    if (error) {
      throw error
    }
    response.status(200).send(`User deleted with ID: ${id}`)
  })
}
module.exports = {
  createIncident,
  deleteIncident 
}

路由代码 #

module.exports = app => {
  const incidents = require("../controllers/incidents.controller.js");

  var router = require("express").Router();

  router.post("/incidents", incidents.createIncident);
  router.delete("/incidents/:id",incidents.deleteIncident);
  
  app.use("/api", router);
};

app.js代码 #

const express = require('express');
const app = express();
const port = 5000;

const bodyParser = require('body-parser');
const cors = require('cors');
app.use(cors({
  origin: 'http://localhost:4200'
}));

app.use(express.json());
app.use(bodyParser.json());
app.use(
  bodyParser.urlencoded({
    extended: false,
  })
);

require("./routes/incidents.routes")(app);

app.listen(port, () =>{
  console.log(`running on ${port}`)
});

依赖包配置 #

{
  "name": "mine_incidents",
  "version": "1.0.0",
  "description": "People centred mining modernization",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon app.js"
  },
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.20.2",
    "cors": "^2.8.5",
    "express": "^4.18.2",
    "pg": "^8.11.3",
    "pg-promise": "^11.5.4",
    "postgres": "^3.3.5"
  },
  "devDependencies": {
    "nodemon": "^3.0.1"
  }
}

问题根源与修复方案 #

1. INSERT接口：手动COMMIT导致事务异常 #

pg模块的pool.query默认自动提交事务，手动执行COMMIT会触发无事务可提交的隐性错误（未被catch捕获），导致代码返回成功但数据未持久化。

修复代码：

const createIncident = async(req,res)=>{
  try {
    let { mine_id, latitude, longitude, description, severity } = req.body;
    console.log("Received data", mine_id, latitude, longitude, description, severity)

    const query = 'INSERT INTO incidents (mine_id,latitude,longitude,description,severity) values ($1, $2, $3, $4, $5) RETURNING *';
    const values = [mine_id, latitude, longitude, description, severity];

    // 移除手动COMMIT，依赖pg默认自动提交机制
    const result = await dbConfig.pool.query(query,values);

    res.status(201).send({message:'Incident inserted successfully',result});
    
  } catch (error) {
    console.error('Error inserting data',error);
    res.status(500).json({error:'Internal server error'});
    
  }
};

2. DELETE接口：未校验删除结果与错误处理不规范 #

当前回调方式未校验results.rowCount确认是否真的删除数据，且throw error会导致Express未捕获异常，掩盖真实问题。

修复代码：

const deleteIncident = async (request, response) => {
  try {
    const id = parseInt(request.params.id);
    const result = await dbConfig.pool.query('DELETE FROM incidents WHERE id = $1', [id]);
    
    // 校验是否真的删除了数据
    if (result.rowCount === 0) {
      return response.status(404).send(`No incident found with ID: ${id}`);
    }
    response.status(200).send(`Incident deleted with ID: ${id}`);
  } catch (error) {
    console.error('Error deleting incident', error);
    response.status(500).json({error:'Internal server error'});
  }
};

3. 冗余中间件导致JSON解析冲突 #

app.use(express.json())与app.use(bodyParser.json())功能重复，保留一个即可避免潜在解析问题。

修改app.js：

// 移除其中一个JSON解析中间件，保留express.json()即可
app.use(express.json());
// app.use(bodyParser.json()); // 注释或删除此行
app.use(
  bodyParser.urlencoded({
    extended: false,
  })
);

4. 数据库连接配置检查 #

确认dbconfig.js中连接池的autoCommit属性为true（默认值），若手动设置为false需显式管理事务：

// dbconfig.js示例
const { Pool } = require('pg');
const pool = new Pool({
  user: 'your_user',
  host: 'localhost',
  database: 'your_db',
  password: 'your_password',
  port: 5432,
  autoCommit: true // 确保自动提交开启
});
module.exports = { pool };

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