Pg-promise: Alternative format for named parameters?

[cymen]

I'd like to use named parameters and ES6 multiline strings via template literals. If I take the example from here:

https://github.com/vitaly-t/pg-promise/wiki/Common-Mistakes#redundant-named-parameter-re-referencing

It would look like:

db.query(
  `INSERT INTO table
  VALUES(${name}, ${title}, ${code})`,
  obj
);

However this doesn't work as ${} is the template literal syntax for literals. Is there a nice way around this or not so much? Right now, I just do:

db.query(
  'INSERT INTO table VALUES(${name}, ${title}, ${code})',
  obj
);

Or for large queries, split over multiple lines like so:

db.query(
  'INSERT INTO table ' +
  'VALUES(${name}, ${title}, ${code})',
  obj
);

But this all feels a little unclean. Would an alternate syntax for named parameters be viable?

[vitaly-t]

It is documented everywhere:

From Named Parameters

The library supports named parameters in query formatting, with the syntax of $_propName_, where * is any of the following open-close pairs: {}, (), <>, [], //

From Named Parameters examples

Named Parameters are defined using syntax $*propName*, where * is any of the following open-close pairs: {}, (), [], <>, //, so you can use one to your liking, but remember that ${} is reserved by ES6 template strings.

i.e. you can use any of $(), $[], $<>, $// inside your ES6 template strings.

[cymen]

@vitaly-t My apologies for overlooking the documentation. Thank you very much!

[leebenson]

Just to follow-on from this, are there any plans to allow template literal strings, a la:

db.query(sql`SELECT * FROM table WHERE id=${id}`);

The benefits being:

• No need to pass in an object/array/scalar as a second value - the value, type-checking and escaping is evaluated inline
• Dedicated template literal syntax, making it clear that it's a query being passed
• No need to check for $(), $[], $<>, $// - one syntax to rule them all (possibly faster because of that?)
• It's clean/modern/simple, and works on every version since Node 4

Thoughts?

[vitaly-t]

@leebenson look at this flame - https://github.com/vitaly-t/pg-promise/issues/204

It is a little idiotic, for which I apologize in advance, but you will find an answer to your question :wink:

[leebenson]

Thanks @vitaly-t. I'm new to this lib, so I apologise in advance for over-looking obvious considerations you no doubt already made. Whenever I see uber-flexible regex, I'm always concerned that will translate to unnecessary CPU cycles, when I myself am sticking to writing in just one style.

I can see that doesn't work in this case. Thanks!

[vitaly-t]

@leebenson Your IO will always eat up most of the PC resources (think 90%+), not query formatting :wink:

Check out this: https://github.com/vitaly-t/pg-promise/wiki/Performance-Boost

[vitaly-t]

@leebenson also consider this - any serious app should use external SQL files, via Query Files. And for those you need regular query formatting.

See also: pg-promise-demo.

[leebenson]

Helpful, thanks @vitaly-t