Feature request: Named parameters in prepared statements / parameterized queries

[emilecantin]

I would like it very much if we could name parameters in queries, instead of the current $1, $2, $3... nonsense. For example:

client.query("INSERT INTO beatles(name, height, birthday) values($1, $2, $3)", ['John', 68, new Date(1944, 10, 13)]);

would become:

client.query("INSERT INTO beatles(name, height, birthday) values(:name, :height, :birthday)", {
  name: 'John',
  height: 68,
  birthday: new Date(1944, 10, 13)
});

It would allow for much cleaner code in more complex cases.

[brianc]

I completely agree with you. Unfortunately, PostgreSQL itself does not allow for this, and I'm trying to keep this driver as close to PostgreSQL as possible. To that end this will never be added as a feature of node-postgres directly.

There are a few options, but the easiest would probably be to monkeypatch the query method in your own code and apply whichever template you see fit.

Another would be to create a module called pg-plus-awesomeness or something, and do the monkeypatching or something similar there. Then you could reuse that code and add other sugar as you see fit.

[bwestergard]

I've followed brianc's advice and put together a monkeypatching solution that should work with node-postgres and node-postgres-pure alike.

https://github.com/bwestergard/node-postgres-named

[brianc]

👍!

[sehrope]

Here's another monkey patch I put together that adds named parameter support and SQL logging as well. I'd like it to eventually include a couple other additions too.

https://github.com/sehrope/node-pg-spice

[XeCycle]

I added in wiki a hint that ES6 tagged template strings may help.

https://github.com/brianc/node-postgres/wiki/Prepared-Statements

[pihvi]

Here's another "cleaner" way to solve this with a library. No need for monkeypatching:

var sql = require('yesql').pg;

client.query(sql("INSERT INTO beatles(name, height, birthday) values(:name, :height, :birthday)")({
  name: 'John',
  height: 68,
  birthday: new Date(1944, 10, 13)
}));

[pihvi]

Postgres syntax for type casting has two colons (::) so this does not clash with that. https://www.postgresql.org/docs/9.6/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC

…

On Fri, Apr 14, 2017 at 12:45 PM, Gajus Kuizinas ***@***.***> wrote: Here's another "cleaner" way to solve this with a library. No need for monkeypatching: Using syntax :[a-z]+ in postgres is probably not a good idea as it clashes with type casting syntax. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#268 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAEa5h0fxL0yxAUFXP03ycfkAP1vhVNaks5rv0AqgaJpZM4AbRFw> .

[gajus]

Postgres syntax for type casting has two colons (::) so this does not clash
with that.

Yes, sorry – corrected myself, deleted the comment. :-)

[pihvi]

Here's an example (test case) how it looks and works with type cast:

it('pg type cast', function() {
  assert.deepEqual(
    yesql.pg('SELECT id::int FROM user WHERE id=:id;')({id: '5'}),
    {
      text: 'SELECT id::int FROM user WHERE id=$1;',
      values: ['5']
    })
})

[brianc]

I've actually started using this a lot which is IMO another awesome way yet to build sql statements w/ parameters!

[gajus]

I've actually started using this a lot which is IMO another awesome way yet to build sql statements w/ parameters!

Is dangerous. For one, if the query allows either string | SQL input, then it is easy to mistake:

query(SQL`SELECT ${foo}`)

with:

query(`SELECT ${foo}`)

[gajus]

I am working on bringing the best of mysqljs world into Postgres, https://github.com/gajus/mightyql.

[brianc]

Really nice! Can't wait to see how it turns out! 👍