Custom table query condition on node pg

user_16782552 0
Viewed 41

Using pg https://www.npmjs.com/package/pg

I am planning on using a getUserBy(fields) function to fetch a single user, the parameter fields is an object that contains a single key (eg) {email:tst@tst.com}

My idea is to use this function with different types of fields (id,username,phone etc) instead of writing multiple functions I can keep it compact.

This is what my query looks like (I thought it was going to work) (db is the connection to pg)

db.query(`
    SELECT * FROM "Users"
    WHERE $1 = $2
`,[Object.keys(fields)[0],Object.values(fields)[0]])

Ideally this would be translated into

SELECT * FROM "Users"
WHERE email = tst@tst.com

But I dont know how I achieve this without using template literals. Is there away to make this possible?

node.js postgresql javascript
1 Answers

You cannot use a parameter for a column name. You'll need to build the part of the SQL with the identifiers dynamically:

const entries = Object.entries(fields);
const conditions = entries.map((e, i) => pgClient.escapeIdentifier(e[0])+' = $'+(i+1))
return pgClient.query(
  `SELECT *
  FROM "Users"
  WHERE ${conditions.join(' AND ') || 'true'}`,
  entries.map(e => e[1])
);
user_10485720
Related