Node-postgres: 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]

:+1:!

[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 [e-mail]
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
https://github.com/brianc/node-postgres/issues/268#issuecomment-294126826,
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! :+1:

[avbentem]

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

@gajus, just curious: the same problem would exist in your https://github.com/gajus/mightyql#tagged-template-literals, wouldn't it?

Tagged template literals

Query methods can be executed using sql tagged template literal, e.g.

import {
  sql
} from 'mightyql'

connection.query(sql`INSERT INTO reservation_ticket (reservation_id, ticket_id) VALUES ${values}`);

[gajus]

@avbentem What problem?

[gajus]

Oops. Missed the quote:

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

Yes, it does.

[gajus]

@avbentem I forgot to mention – I've created an ESLint plugin to prevent this, https://github.com/gajus/eslint-plugin-sql.

[brandonros]

const convertParameterizedQuery = (sql, bindingsMap = {}) => {
  const matches = sql.match(/(:[^ \),]*)/g) || []
  const bindingIndexMatchMap = {}
  let bindingIndex = 1
  for (const match of matches) {
    if (!bindingIndexMatchMap[match]) {
      bindingIndexMatchMap[match] = bindingIndex
      bindingIndex += 1
    }
    sql = sql.replace(match, `$${bindingIndexMatchMap[match]}`)
  }
  return {
    sql,
    bindings: Object.keys(bindingIndexMatchMap).reduce((prev, key) => {
      return prev.concat(bindingsMap[key.substr(1)])
    }, [])
  }
}
const sql = `select * from table where foo = :bar or bar = :foo or bizz = :foo`
const bindings = {
  foo: '123',
  bar: '456'
}
console.log(convertParameterizedQuery(sql, bindings))
{
  sql: 'select * from table where foo = $1 or bar = $2 or bizz = $2',
  bindings: [ '456', '123' ]
}

[charmander]

/(:[^ \),]*)/g

will interfere with casts;

 return {
   sql,
   bindings: Object.keys(bindingIndexMatchMap).reduce((prev, key) => {
     return prev.concat(bindingsMap[key.substr(1)])
   }, [])
 }

takes quadratic time on the number of bindings and will do the wrong thing when a value is an array. map exists.

I recommend a query builder or SQL template tag (the most starred one in the Extras page is sql-template-strings). The same kind of query/value separation possible with :name-style parameters can be preserved using functions.