使用node-postgres查询PostgreSQL返回空结果问题求助与排查

问题现象 #

• 在pgAdmin中执行SELECT * FROM authors WHERE id='56a33651-f6c9-4abf-8375-b10c065724a4';可正常获取目标数据
• Node.js（使用pg库）代码及Insomnia工具请求同一ID时，返回{"error": "Author not found"}，控制台输出空数组[]
• 尝试调整ID格式（如const idAjusted = ${id}``）后问题仍未解决

错误根源 #

在queries.js的数据库配置段，DB_NAME变量被错误赋值为process.env.DB_USER，导致代码实际连接的数据库并非目标库，因此无法查询到对应数据。

修正方法 #

将queries.js中的DB_NAME改为对应目标数据库名称的环境变量（通常为process.env.DB_NAME）：

错误配置代码 #

const DB_NAME = process.env.DB_USER;

修正后代码 #

const DB_NAME = process.env.DB_NAME;

相关代码与输出 #

index.js #

import express from "express";
import dotenv from "dotenv";
import cors from "cors";
import { getAuthorById } from "./src/queries/queries.js";
// environment constants
dotenv.config();
const APP_PORT = process.env.APP_PORT;
const APP_NAME = process.env.APP_NAME;

// express app
const app = express();


// set middleware CORS
app.use(cors({
    origin: '*',
    methods: 'GET, PUT, POST, DELETE, HEAD, OPTIONS',
}));


app.get("/queryauthorbyid/:id", async (req, res) => {
    const id = req.params.id
    try {
        const author = await getAuthorById(id)
        res.status(200).json(author);
    } catch (error) {
        res.status(500).json({ error: 'Internal Server Error' });
    }
})


// Middleware
app.use((req, res, next) => {
    res.sendStatus(404);
    // Allow requests
    res.header('Access-Control-Allow-Origin', '*');
    // Supported methods
    res.header('Access-Control-Allow-Methods', 'GET, PUT, POST, DELETE, HEAD, OPTIONS');
});

// Starting server
app.listen(APP_PORT, (err) => {
    if (err) {
        console.log('error');
    } else {
        console.log(`${APP_NAME} is up on ${APP_PORT}`);
    }
});

错误配置时的控制台输出 #

My Blog Queries is up on 3002
Connected to the database
[]
No authors found with the given ID.
Connection closed

完整错误配置的queries.js #

import dotenv from "dotenv";
import pkg from 'pg';
const { Client } = pkg;

// environment constant
dotenv.config();
const DB_HOST_IP = process.env.DB_HOST_IP;
const DB_HOST_PORT = process.env.DB_HOST_PORT;
const DB_NAME = process.env.DB_USER; // 此处为错误配置
const DB_USER = process.env.DB_USER;
const DB_USER_PWD = process.env.DB_USER_PWD;


export const getAuthorById = async (id) => {
    const client = new Client({
        host: DB_HOST_IP,
        port: DB_HOST_PORT,
        database: DB_NAME,
        user: DB_USER,
        password: DB_USER_PWD,
    });

    try {
        // Connecting to the database
        await client.connect();
        console.log('Connected to the database');
        
        
        // query option 1
        const query = {
            name: 'author-by-id',
            text: 'SELECT * FROM authors WHERE id = $1',
            values: [id],
            rowMode: 'array',
        }
        // query option 2
        // const query = 'SELECT * FROM authors WHERE id = $1';

        // query
        const res = await client.query(query);
        // show result
        const authors = res.rows;
        console.log(authors);

        if (authors.length === 0) {
            console.log('No authors found with the given ID.');
            return { error: 'Author not found' };
        }

        const resJson = JSON.stringify(authors, null, 2);
        console.log(authors);
        return resJson;
    } catch (error) {
        console.error('Error connecting or querying the database:', error);
        return { error: 'Internal Server Error' };
    } finally {
        // Make sure to close the connection regardless of the outcome
        await client.end();
        console.log('Connection closed');
    }
};

Insomnia请求结果（错误配置时） #

http://localhost:3002/queryauthorbyid/56a33651-f6c9-4abf-8375-b10c065724a4

{
    "error": "Author not found"
}

pgAdmin测试语句（正常返回结果） #

select * from authors where id='56a33651-f6c9-4abf-8375-b10c065724a4';

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