Organizational Research By

Surprising Reserch Topic

node postgres how to execute where col in dynamic value list query


node postgres how to execute where col in dynamic value list query  using -'node.js,node-postgres'

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.
    

asked Sep 18, 2015 by maurya
0 votes
11 views



Related Hot Questions

5 Answers

0 votes

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.

answered Sep 18, 2015 by ajit.chavhan
0 votes

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 }
answered Sep 18, 2015 by rolvyrf
0 votes

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.

answered Sep 18, 2015 by badhwar.rohit
0 votes

Using pg-promise, this works with the following approach:

var data = [1,2,3,4];
db.query("SELECT * FROM table WHERE id IN ($1^)", pgp.as.csv(data))
    .then(function (data) {
        console.log(data); // print data;
    }, function (reason) {
        console.log(reason); // print error;
    });

And to address the concern about various data types, as.csv() serializes the array into csv, while converting all values into their proper PostgreSQL format, so all types are supported.

answered Sep 18, 2015 by mannar kande
0 votes

Another possible solution is to use the UNNEST function like this:

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

I've used this in a stored procedure and it works fine. Believe it should work also from node-pg code.

You can read about the UNNEST function here.

answered Sep 18, 2015 by android_master

...