Node Express app not getting data from Postgres table even with async await

Question

So I'm trying to fetch a row from my Postgres db table by id. In the function that calls the query there is data, but in the controller I'm getting a null. Seems like the controller is resolving before my query, but not sure why since I have await. My code is as follows:

TripQuery.ts

const Pool = require('pg').Pool

const pool = new Pool({
    user: process.env.PG_USER,
    host: process.env.PG_HOST,
    database: process.env.PG_DATABASE,
    password: process.env.PG_PASSWORD,
    port: process.env.PG_PORT,
})

const dbGetTripById = async (id: string) => {
    try {
        await pool.query('SELECT * FROM trips WHERE id = $1', [id], (error: Error, results: QueryResult) => {
            if (error) {
                throw error
            }

            if (results.rows.length > 0) {
                const trip: Trip = {
                    id: results.rows[0].id,
                    userId: results.rows[0].user_id,
                    name: results.rows[0].name,
                }

                console.log('from db:')
                console.log(trip)

                return trip
            } else {
                return null
            }
        })
    } catch (e) {
        console.log(e)
    }

    return null
}

TripController.ts

const getTrip = async (req: Request, res: Response) => {
    const id = req.params.id
    try {
        const trip = await dbGetTripById(id)

        console.log('from controller')
        console.log(trip)

        if (trip != null) {
            res.status(200).json(trip)
        } else {
            res.status(404).send(`Trip with ID: ${id} does not exist`)
        }
    } catch (e) {
        console.log(e)
    }
}

TripRoutes.ts

import * as express from 'express'
import { addTrip, getTrips, getTrip, updateTrip } from '../controllers/TripController'

const router = express.Router()


router.get('/:id', getTrip)

export default router

When I call my endpoint http://localhost:3000/trips/123 I get the following logs:

Application is running on port 3000.
from controller
null
from db:
{ id: '123', userId: 'u123', name: 'trippy' }

And get the 404 error back even though the database query has fetched a value.

What am I doing wrong?

`dbGetTripById` has one return statement and always returns `null`. `getTrip` calls `dbGetTripById`, gets `null`, checks `if (trip != null) {` and sends a 404 response. Do you mean `return await pool.query`? I'm not sure what `pool.query` returns. — Thomas Sablik, Aug 25 '22 at 23:51
Which plugin is `pool` using? Are you sure`pool.query` returns a promise? Also, can you `console.log(JSON.stringify(results, null, 2))` to get an idea of what results contains - and if the rows are returned. — mani, Aug 25 '22 at 23:52
@ThomasSablik pool is the pg package for node, I have updated the code to include it. — FlameDra, Aug 26 '22 at 00:22
@mani i'm not sure if it returns a promise, what is the best way to ensure my controller gets the date from the db that I call from my query file? — FlameDra, Aug 26 '22 at 00:28
What does `pool.query` return? Is it a promise resolving the the expected result? In that case you only to return it. If you're not sure, read the docs or debug your code. — Thomas Sablik, Aug 26 '22 at 00:32
According to the docs I've found, it returns nothing. One way is to promisify the callback API and return a promise resolving to your expected value. I'm not sure if you can use [node:utils.promisify](https://nodejs.org/api/util.html#utilpromisifyoriginal) — Thomas Sablik, Aug 26 '22 at 00:36
If not, you can convert it using [How do I convert an existing callback API to promises?](https://stackoverflow.com/questions/22519784/how-do-i-convert-an-existing-callback-api-to-promises) — Thomas Sablik, Aug 26 '22 at 00:42

mani · Answer 1 · 2022-08-26T10:33:46.687

pool.query likely doesn't return a promise if a callback is passed.

Wrap it in a promise as:

const dbGetTripById = async (id: string) => {
    return new Promise((resolve, reject) => {
        pool.query('SELECT * FROM trips WHERE id = $1', [id], (error: Error, results: QueryResult) => {
            if (error) {
                return reject(error);
            }

            if (results.rows.length > 0) {
                const trip: Trip = {
                    id: results.rows[0].id,
                    userId: results.rows[0].user_id,
                    name: results.rows[0].name,
                }

                console.log('from db:')
                console.log(trip)

                return resolve(trip)
            } else {
                return reject(new Error("Record not found"))
            }
        })
    })
}

Or use promise syntax:

const dbGetTripById = async (id: string) => {
    await pool.query('SELECT * FROM trips WHERE id = $1', [id])
        .then(results => {
            if (results.rows.length > 0) {
                const trip: Trip = {
                    id: results.rows[0].id,
                    userId: results.rows[0].user_id,
                    name: results.rows[0].name,
                }

                console.log('from db:')
                console.log(trip)
                return trip
            } else {
                throw new Error("No results")
            }
      })
}

_"`pool.query` doesn't return a promise"_. Yes, [it does](https://node-postgres.com/features/pooling#single-query). But probably only when you don't pass a callback as well. — robertklep, Aug 26 '22 at 10:25
@robertklep Yes, you're correct: https://node-postgres.com/api/pool#poolquery — Thomas Sablik, Aug 26 '22 at 10:30

Node Express app not getting data from Postgres table even with async await

1 Answers1