Search code examples
javascriptnode.jspostgresqlpgadminnode-postgres

Unable to reach the proper tables in Postgres from Node using node-postgres

I'm trying to connect to postgres from Node using node-postgres, but even though the authentication is granted, I'm unable to search reach the tables. For example, when I perform: 

const { Client } = require('pg')
const client = new Client({
    user: 'username',
    password: 'somepassword',
    host: 'hostinfo',
    port: '5432',
    database: 'databaseInfo',
    ssl: true,
})

const execute = async() => {
    try {
        await client.connect()
        await client.query("BEGIN")
        const results = await client.query("select * from User")
        await client.query("COMMIT")
        console.log(results.rows)
    } catch(err) {
        throw new Error(err)
    } finally {
        await client.end()
        console.log("Finished")
    }
}

execute()

it returns the User of the database, which is the 'username' that I used to access the database, not the content of the User table.

If I were to search for any other tables in the database, say the Review table, the error message shows

UnhandledPromiseRejectionWarning: Error: error: relation "review" does not exist

Solution

  • If your table is really named "User" (with a capital "U"), you should be quoting its name when querying, like this:

    const results = await client.query('select * from "User"')

    When unquoted, PostgreSQL will think you're calling the function alias user, which is the same as current_user.

    References: