node-postgres: how to prepare a statement without executing the query?

Question

I want to create a "prepared statement" in postgres using the node-postgres module. I want to create it without binding it to parameters because the binding will take place in a loop.

In the documentation i read :

query(object config, optional function callback) : Query
If _text_ and _name_ are provided within the config, the query will result in the creation of a prepared statement.

I tried

client.query({"name":"mystatement", "text":"select id from mytable where id=$1"});

but when I try passing only the text & name keys in the config object, I get an exception :

(translated) message is binding 0 parameters but the prepared statement expects 1

Is there something I am missing ? How do you create/prepare a statement without binding it to specific value in order to avoid re-preparing the statement in every step of a loop ?

Answer 1

I just found an answer on this issue by the author of node-postgres.

With node-postgres the first time you issue a named query it is parsed, bound, and executed all at once. Every subsequent query issued on the same connection with the same name will automatically skip the "parse" step and only rebind and execute the already planned query.

Currently node-postgres does not support a way to create a named, prepared query and not execute the query. This feature is supported within libpq and the client/server protocol (used by the pure javascript bindings), but I've not directly exposed it in the API. I thought it would add complexity to the API without any real benefit. Since named statements are bound to the client in which they are created, if the client is disconnected and reconnected or a different client is returned from the client pool, the named statement will no longer work (it requires a re-parsing).

Answer 2

You can use pg-prepared for that:

var prep = require('pg-prepared')

// First prepare statement without binding parameters
var item = prep('select id from mytable where id=${id}')

// Then execute the query and bind parameters in loop
for (i in [1,2,3]) {
  client.query(item({id: i}), function(err, result) {...})
}