Trying to work with node and postgres using pool. I have a couple of test queries, that are basically the same, only the first returns rows and the second returns undfined and I cannot for the life of me see why...

(async () => {
  const {rows} = await pool.query('SELECT * FROM accounts');
  console.log(rows);
})();

^this^ returns all (2) rows from test db... but, the next query only returns undefined.

  (async () => {
    const {user} = await pool.query(`SELECT * FROM accounts WHERE username = '${req.body.username}'`);
    console.log(user);
  })();

The req.body.username is definitely correct, and the query should return 1 row...

Oddly, I have just found that if I drop the {} from user on the second query I do get a return, but with a whole list of field definitions... so no and very confused! i.e. const user not const {user}

  (async () => {
    const user = await pool.query(`SELECT * FROM accounts WHERE username = '${req.body.username}'`);
    console.log(user);
  })();

Trying to work with node and postgres using pool. I have a couple of test queries, that are basically the same, only the first returns rows and the second returns undfined and I cannot for the life of me see why...

(async () => {
  const {rows} = await pool.query('SELECT * FROM accounts');
  console.log(rows);
})();

^this^ returns all (2) rows from test db... but, the next query only returns undefined.

  (async () => {
    const {user} = await pool.query(`SELECT * FROM accounts WHERE username = '${req.body.username}'`);
    console.log(user);
  })();

The req.body.username is definitely correct, and the query should return 1 row...

Oddly, I have just found that if I drop the {} from user on the second query I do get a return, but with a whole list of field definitions... so no and very confused! i.e. const user not const {user}

  (async () => {
    const user = await pool.query(`SELECT * FROM accounts WHERE username = '${req.body.username}'`);
    console.log(user);
  })();

• node.js
• postgresql

• 3 You didn't specify which lib you use but it's unlikely that there's "user" property. I'd expect it to work if you use "rows" – Estus Flask Commented 2 days ago
• 2 Your second and third query are a SQL injection waiting to happen. – robertklep Commented 2 days ago
• oh thats mad... using any word other than rows causes an issue... e.g. const {rows} = works fine but const {queryResults} = doesn't... that's been driving me crazy for hours! – Paul Commented 2 days ago
• 1 @Paul It's const {rows: queryResults} = See developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/… . – Estus Flask Commented 2 days ago
• Thank you so much sir... I was assuming I was either having a dsylexia issue (im prone to) or some issue with managing connection I haddn't worked out. – Paul Commented 2 days ago

2 Answers 2

pool.query returns a Promise that resolves to a pg.Result. That object has a rows member, which is an array where each element is an object mapping the columns to the values they have in that row.
i.e., to get the user from the query, you should do something like this:

const {rows} =
    await pool.query(`SELECT * FROM accounts WHERE username = '${req.body.username}'`);
const user = rows[0].user;

Side note - interpolating user-controlled strings into a query like this makes your code vulnerable to SQL injection attacks. A more secure approach would be to use bind variables:

const {rows} = 
    await pool.query('SELECT * FROM accounts WHERE username = $1', [req.body.username]);
const user = rows[0].user;

Thanks to Estus Flask this has been solved:

Using const {rows} in my second query instead of const {user} everything is now behaving properly... i.e.:

  (async () => {
    const {rows} = await pool.query(`SELECT * FROM accounts WHERE username = '${req.body.username}'`);
    console.log(rows);
  })();

Just in case anyone else is having this issue!!!

Mureinik's answer is also good, but for completeness for noobs like me, the other, better(?) way of putting the results into a variable name of your choice say varName should you really need to would be:

  (async () => {
    const {rows: varName} = await pool.query(`SELECT * FROM accounts WHERE username = '${req.body.username}'`);
    console.log(varName);
  })();

And this is how you can implement my answer securely using a Parameterized query for anyone new to PostgreSQL.

(async () => {
    const text = 'SELECT * FROM accounts WHERE username = $1';
    const values = [req.body.username];
    const {rows: varName} = await pool.query(text, values);
    console.log(varName);
})();

Thank you very much to Estus for solving this instantly in the comments!