0

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;"

4
  • 1) Add 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,. Commented Feb 26, 2024 at 21:14
  • I analyzed the database logs and the query parameter is being passed with quotation marks, to resolve it I adjusted it as follows: const query = { name: 'author-by-id', text: 'SELECT * FROM authors WHERE id = ($1)::uuid', values: [id], rowMode: 'array', } to wrap the argument in parentheses before applying the ::uuid type cast so that the prepared statement can properly interpolate argument. Still not working. Commented Feb 27, 2024 at 1:45
  • 1) That is not necessary: select '56a33651-f6c9-4abf-8375-b10c065724a4'::uuid; 56a33651-f6c9-4abf-8375-b10c065724a4. 2) Show the results of console.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? Commented Feb 27, 2024 at 5:38
  • 1
    Thank You @AdrianKlaver 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;" Commented Feb 27, 2024 at 9:57

1 Answer 1

0

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_NAME = process.env.DB_USER;"

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.