CODEWITHSUNDEEP
postgresqlnode.js
Matt Berry
Matt Berry

Reputation: 109

postgres - empty query result when run in node/express (res.rows) but data returned fine when manually queried

I'm having an issue with a query not returning any data in my node/express application (postgres db) when testing with postman, but when I run the below sql query in psql terminal, or another program (dbvisualizer) the results are output perfectly. When testing with postman I get a 200 back, with an empty object, so there's no communication issues, it's just that no data is returned.

app.get('/orgVendor2', (request, response, next) => {
  pool.query('SELECT o.organisation_name AS "org", STRING_AGG(v.name, ', ') AS "ven" FROM vendor v JOIN organisation o ON v.id_organisation = o.organisation_id GROUP BY 1 ORDER BY 1', (err, res) => {
    if (err) return next(err);
    response.json(res.rows);
  });
});

I don't know why it won't work. If I replace the sql query with something simple like 'select * from organisation', then hit localhost:3000/orgVendor2 with postman I get a full response back as expected. I think the issue is either that node doesn't like the query for some reason, or res.rows isn't able to parse the response, even though it's just two columns of data, with several hundred rows.

Upvotes: 1

Views: 2339

Answers (1)

András Váczi
András Váczi

Reputation: 3002

Your issue is using quotes inside quotes - that is, the query you pass is the following:

'SELECT o.organisation_name AS "org", STRING_AGG(v.name, '

What your framework does with the rest (that is, , ') AS "ven" FROM vendor v JOIN organisation o ON v.id_organisation = o.organisation_id GROUP BY 1 ORDER BY 1' ), maybe interpreting it as a second parameter to pool.query(), I cannot tell. However, Postgres has a solution for such cases - it's called dollar quoting, and the documentation describes it and its use cases and advantages nicely.

In your case, modify your query literal to the following (line breaks added for readability):

SELECT o.organisation_name AS "org", STRING_AGG(v.name, $$, $$) AS "ven" 
  FROM vendor v 
  JOIN organisation o ON v.id_organisation = o.organisation_id 
 GROUP BY 1 
 ORDER BY 1'

Upvotes: 2

Related Questions