[Request] Let client.query support passing values with an object

[qdechochen]

Something like:

client.query(`
  SELECT id, name
  FROM table1
  WHERE gender={gender} AND age>{age}
`, { gender: 'M', age: 20});

I wrote a function to do the translation:

const replaceEngine = (() => {
  return (template, data) => {
    let index = 0;
    const values = [];
    return {
      text: template.replace(/\{(\w+)\}/g, (match, key) => {
        index++;
        values.push(data && data.hasOwnProperty(key) ? data[key] : null);
        return `$${index}`;
      }),
      values,
    };
  };
})();

======

replaceEngine(`
  SELECT id, name
  FROM table1
  WHERE gender={gender} AND age>{age}
`, { gender: 'M', age: 20})

you will get:

{
  text: 'SELECT id, name FROM table1 WHERE gender=$1 AND age>$2',
  values: ['M', 20]
}

[sehrope]

The PostgreSQL server only supports numbered parameters, not named parameters. If you want that type of functionality you can have it via a helper library that sits atop node-postgres. It doesn't belong in the core driver.

Check out the list of extras (https://github.com/brianc/node-postgres/wiki/Extras) for some options. The feature you're describing is called "Named Parameters" and there's a number of modules that add that functionality.

To handle this type of thing correctly it's a bit more complicated than a pure regex replace. You need to take into account quoting and comments as well. Otherwise you'll end up substituting values inside literals and identifiers:

> replaceEngine(`SELECT '{test}' AS a`, {})
{ text: 'SELECT \'$1\' AS a', values: [ null ] }

The extra modules that provide this functionality should handle all these use cases and I'd suggest you use one them.