Add a sql tagged template function

[nigrosimone]

postgres.js has a nice API for queries:

const users = await sql`SELECT * FROM users WHERE id = ${id}`

Values become parameters, never string concatenation, and the driver prepares and reuses the statement automatically.

I think pg can offer the same with the machinery it already has. A tagged template receives the same frozen strings array on every call from the same call site, so a WeakMap on it gives a stable statement name with no hashing. The tag only has to build { name, text, values } and pass it to query(): named statements already skip re-parsing per connection, and with #3739 the queries also pipeline through pool.query(), postgres.js style.

Open points:

• an option to skip the statement name, for pgbouncer in transaction mode?
• recovery when a schema change invalidates a prepared statement (postgres.js retries once)?
• composition (nested fragments, identifiers) can come later, values only is already useful?

I would like to work on this. Is a PR welcome, and would you prefer it in core or as a separate package?

POC

const { sql } = new Pool({ max: 2, pipeline: true });

const id = 7;
const users = await sql`SELECT * FROM users WHERE id = ${id}`;

WIP master...nigrosimone:node-postgres:sql-template-tag

[abenhamdine]

for this we use https://github.com/felixfbecker/node-sql-template-strings which does exactly this, and has been battle-tested (we use it for our several millions query day production app)

Indeed, It could be included in node-postgres for to ease usage for all new users who perhaps don't know this library

Values become parameters, never string concatenation, and the driver prepares and reuses the statement automatically.

with node-sql-template-strings, you still have to provide a name (with setName) to have a prepared statement, which is well (we don't always want to use a prepared statement because of the cost for very simple queries)

[cesco69]

@abenhamdine
It would be convenient to have this function in pg because you can get the connection directly from the pool

await SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`

instead of

await pg.query(SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`)

or find a "trick" to do it in node-sql-template-strings, eg:

import { magicTrick } from "node-sql-template-strings";

const pool = new Pool();
const SQL = magicTrick(pool);
await SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`

side note: https://github.com/felixfbecker/node-sql-template-strings is active? last commit is 7 years ago, just to understand if it's worth doing a PR

[abenhamdine]

no it's not active anymore, because it has seemingly fulfilled its goal.
Also worth to note that it's not tied to a particular driver so any PR coupling it to node-postgres for example would probably be refused.

[abenhamdine]

worth to mention this (old) page Extras in the wiki : https://github.com/brianc/node-postgres/wiki/Extras#sql-template-tags

[nigrosimone]

I can release a separate npm library, but for me the best place for this feature is into the core of pg