node-postgres doesn't show db tables

21 Views Asked by At

I have psql db with tables: album, genre, artist, song.

This query is supposed to return all tables that exist in my db:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
  AND table_type = 'BASE TABLE';

When querying it in pgAdmin, or in Terminal, I have expected result: enter image description here

But, when trying to query it using node-postgres and express I have empty array in return.

This is my db configuration:

const Pool = require("pg").Pool;


const env = process.env;
const db = new Pool({
    user: env.DB_USER,
    host: env.DB_HOST,
    database: env.DB_NAME,
    password: env.DB_PASSWORD,
    port: env.DB_PORT,
})

module.exports = db;

This is testing route I use for querying:

// Route to get all table names
app.get('/list-tables', async (req, res) => {
    try {
      const queryText = `
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_type = 'BASE TABLE';
      `;
      const result = await db.query(queryText);
      const tables = result.rows.map(row => row.table_name);
      res.json(tables);
    } catch (err) {
      console.error(err);
      res.status(500).send('Error fetching table names');
    }
});

I tested it in Postman and it gives me empty list. Connection itself is fine, queries like

SELECT NOW()

work. Encountered this problem while trying to make query to one of the tables. DB name, port, host, user are double checked. User is owner of the table.

How can I fix it?

0

There are 0 best solutions below