CODEWITHSUNDEEP
javascriptnode.jspostgresqlpg-promise
Azakaria
Azakaria

Reputation: 51

JavaScript (Postgres DB) - How to use a prepared statement with an array as parameter in the WHERE IN ( ) clause

I am currently using the database class from http://vitaly-t.github.io/pg-promise/Database.html and trying to implement an Update statement using a PreparedStatment on my Postgres DB while having an Array passed to the WHERE IN clause ? 

const updatePreparedStatment = new PS('prepared-statement', 'UPDATE mytable SET "MESSAGE"=$1 WHERE "ID" IN ($2)', ["dummy update", ["1","2","3"]]);

Upvotes: 5

Views: 4958

Answers (1)

BlackC0de
BlackC0de

Reputation: 61

It is described in the FAQ of node-postgres https://github.com/brianc/node-postgres/wiki/FAQ#11-how-do-i-build-a-where-foo-in--query-to-find-rows-matching-an-array-of-values

How do I build a WHERE foo IN (...) query to find rows matching an array of values? node-postgres supports mapping simple JavaScript arrays to PostgreSQL arrays, so in most cases you can just pass it like any other parameter.

client.query("SELECT * FROM stooges WHERE name = ANY ($1)", [ ['larry', 'curly', 'moe'] ], ...);

Note that = ANY is another way to write IN (...), but unlike IN (...) it will work how you'd expect when you pass an array as a query parameter.

If you know the length of the array in advance you can flatten it to an IN list:

// passing a flat array of values will work:
client.query("SELECT * FROM stooges WHERE name IN ($1, $2, $3)", ['larry', 'curly', 'moe'], ...);

... but there's little benefit when = ANY works with a JavaScript array.

If you're on an old version of node-postgres or you need to create more complex PostgreSQL arrays (arrays of composite types, etc) that node-postgres isn't coping with, you can generate an array literal with dynamic SQL, but be extremely careful of SQL injection when doing this. The following approach is safe because it generates a query string with query parameters and a flattened parameter list, so you're still using the driver's support for parameterised queries ("prepared statements") to protect against SQL injection:

var stooge_names = ['larry', 'curly', 'moe'];
var offset = 1;
var placeholders = stooge_names.map(function(name,i) { 
    return '$'+(i+offset); 
}).join(',');
client.query("SELECT * FROM stooges WHERE name IN ("+placeholders+")", stooge_names, ...);

Hope that helps since google fails to find this

Upvotes: 6

Related Questions