I am testing the pg library (node-postgres) to connect Node.js with my PostgreSQL database. When I test it via pgAdmin, the query 'SELECT * FROM authors WHERE id='56a33651-f6c9-4abf-8375-b10c065724a4';' is working fine. However, in Node.js and Insomnia, the row is coming back empty. Can you help me?
###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}`);
}
});
###console
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 GET
http://localhost:3002/queryauthorbyid/56a33651-f6c9-4abf-8375-b10c065724a4
{
"error": "Author not found"
}
###pgAdmin
select * from authors where id='56a33651-f6c9-4abf-8375-b10c065724a4';
it works
I've tried to change format of the value, like const idAjusted = '${id}'
###FIXED
I've looked at the PostgreSQL log again and find out that I was never reaching the Database because my const DB_NAME = process.env.DB_NAME was WRONG"const DB_USER = process.env.DB_USER;"
console.log(id)to see what value is actually being set. 2) Look at Postgres log(with logging turned up) to see what is reaching the database,.select '56a33651-f6c9-4abf-8375-b10c065724a4'::uuid; 56a33651-f6c9-4abf-8375-b10c065724a4. 2) Show the results ofconsole.log(id)and the Postgres log as update to question text, not in comments. Are you sure there is not hidden characters in the string? 3) Are you sure you are connecting to correct database or table in correct schema?