CODEWITHSUNDEEP
pg-promise
Ron I
Ron I

Reputation: 4250

Using arrays with pg-promise

I'm using pg-promise and am not understanding how to run this query. The first query works, but I would like to use pg-promise's safe character escaping, and then I try the second query it doesn't work.

Works:

db.any(`SELECT title FROM books WHERE id = ANY ('{${ids}}') ORDER BY id`)

Doesn't work

db.any(`SELECT title FROM books WHERE id = ANY ($1) ORDER BY id`, ids)

Upvotes: 1

Views: 1917

Answers (1)

vitaly-t
vitaly-t

Reputation: 25840

The example has 2 problems. First, it goes against what the documentation tells you:

IMPORTANT: Never use the reserved ${} syntax inside ES6 template strings, as those have no knowledge of how to format values for PostgreSQL. Inside ES6 template strings you should only use one of the 4 alternatives - $(), $<>, $[] or $//.

Manual query formatting, like in your first example, is a very bad practice, resulting in bad things, ranging from broken queries to SQL injection.

And the second issue is that after switching to the correct SQL formatting, you should use the CSV Filter to properly format the list of values:

db.any(`SELECT title FROM books WHERE id IN ($/ids:csv/) ORDER BY id`, {ids})

or via an index variable:

db.any(`SELECT title FROM books WHERE id IN ($1:csv) ORDER BY id`, [ids])

Note that I also changed from ANY to IN operand, as we are providing a list of open values here.

And you can use filter :list interchangeably, whichever you like.

Upvotes: 6

Related Questions