node-postgres: how to execute "WHERE col IN (<dynamic value list>)" query?

Question

I'm trying to execute a query like this:

SELECT * FROM table WHERE id IN (1,2,3,4)

The problem is that the list of ids I want to filter against is not constant and needs to be different at every execution. I would also need to escape the ids, because they might come from untrusted sources, though I would actually escape anything that goes in a query regardless of the trustworthiness of the source.

node-postgres appears to work exclusively with bound parameters: client.query('SELECT * FROM table WHERE id = $1', [ id ]); this will work if I had a known number of values (client.query('SELECT * FROM table WHERE id IN ($1, $2, $3)', [ id1, id2, id3 ])), but will not work with an array directly: client.query('SELECT * FROM table WHERE id IN ($1)', [ arrayOfIds ]), as there does not seem to be any special handling of array parameters.

Building the query template dynamically according to the number of items in the array and expanding the ids array into the query parameters array (which in my actual case also contains other parameters besides the list of ids) seems unreasonably burdensome. Hard-coding the list of ids in the query template seems not viable either, as node-postgres does not provide any value escaping methods.

This seems like a very common use-case, so my guess is that I'm actually overlooking something, and not that it is not possible to use the common IN (values) SQL operator with node-postgres.

If anybody has solved this problem in a more elegant manner than those I listed above, or if I'm really missing something about node-postgres, please help.

Answer 1

It looks like you may have been close based on your comment to @ebohlman's answer. You can use WHERE id = ANY($1::int[]). PostgreSQL will convert the array to the type the parameter is cast to in $1::int[]. So here's a contrived example that works for me:

var ids = [1,3,4]; 

var q = client.query('SELECT Id FROM MyTable WHERE Id = ANY($1::int[])',[ids]);

q.on('row', function(row) {
  console.log(row);
})

// outputs: { id: 1 }
//          { id: 3 }
//          { id: 4 }

Answer 2

We've seen this question before on the github issues list. The correct way is to dynamically generate your list of parameters based on the array. Something like this:

var arr = [1, 2, "hello"];
var params = [];
for(var i = 1; i <= arr.length; i++) {
  params.push('$' + i);
}
var queryText = 'SELECT id FROM my_table WHERE something IN (' + params.join(',') + ')';
client.query(queryText, arr, function(err, cb) {
 ...
});

That way you get the postgres parameterized escaping.

Answer 3

The best solution I've found has been to use the ANY function with Postgres' array coercion. This lets you match a column with an arbitrary array of values as if you had written out col IN (v1, v2, v3). This is the approach in pero's answer but here I show that the performance of ANY is the same as IN.

Query

Your query should look like:

SELECT * FROM table WHERE id = ANY($1::int[])

That bit at the end that says $1::int[] can be changed to match the type of your "id" column. For example, if the type of your IDs is uuid, you'd write $1::uuid[] to coerce the argument to an array of UUIDs. See here for the list of Postgres datatypes.

This is simpler than writing code to construct a query string and is safe against SQL injections.

Example

With node-postgres, a complete JavaScript example looks like:

var pg = require('pg');

var client = new pg.Client('postgres://username:password@localhost/database');
client.connect(function(err) {
  if (err) {
    throw err;
  }

  var ids = [23, 65, 73, 99, 102];
  client.query(
    'SELECT * FROM table WHERE id = ANY($1::int[])',
    [ids],  // array of query arguments
    function(err, result) {
      console.log(result.rows);
    }
  );
});

Performance

One of the best ways to understand the performance of a SQL query is to look at how the database processes it. The sample table has about 400 rows and a primary key called "id" of type text.

EXPLAIN SELECT * FROM tests WHERE id = ANY('{"test-a", "test-b"}');
EXPLAIN SELECT * FROM tests WHERE id IN ('test-a', 'test-b');

In both cases, Postgres reported the same query plan:

Bitmap Heap Scan on tests  (cost=8.56..14.03 rows=2 width=79)
  Recheck Cond: (id = ANY ('{test-a,test-b}'::text[]))
  ->  Bitmap Index Scan on tests_pkey  (cost=0.00..8.56 rows=2 width=0)
        Index Cond: (id = ANY ('{test-a,test-b}'::text[]))

You might see a different query plan depending on the size of your table, where there's an index, and your query. But for queries like the ones above, ANY and IN are processed the same way.

Answer 4

Using pg-promise, this works well via the CSV Filter (comma-separated values):

const values = [1, 2, 3, 4];

db.any('SELECT * FROM table WHERE id IN ($1:csv)', [values])
    .then(data => {
        console.log(data);
    })
    .catch(error => {
        console.log(error);
    });

And to address the concern about various data types, :csv modifier serializes the array into csv, while converting all values into their proper PostgreSQL format, according to their JavaScript type, even supporting the Custom Type Formatting.

And if you have mixed-type values like this: const values = [1, 'two', null, true], you still will get the correctly escaped SQL:

SELECT * FROM table WHERE id IN (1, 'two', null, true)

UPDATE

From v7.5.1, pg-promise started supporting :list as an interchangeable alias for the :csv filter:

db.any('SELECT * FROM table WHERE id IN ($1:list)', [values])