Search code examples
postgresqlpg-promise

Is there a benefit to using $1 over a Template Literal with pg-promise queries?

Using pg-promise, is there any benefit to using $1 over Template Literals? Template Literals are just much more familiar to me, but I have to assume they do it differently for a reason with this package.

The "Normal" Way:

db.any('SELECT * FROM users WHERE active = $1', [true])

The Way I did it:

const isTrue = true;

db.any(`SELECT * FROM users WHERE active = ${isTrue}`)

UPDATED The Way I did it:

const isTrue = true;
const name = 'Matt'
db.any('SELECT * FROM users WHERE active = $1 AND name = $2', [isTrue, name])

OR

const isTrue = true;
const name = 'Matt'
db.any(`SELECT * FROM users WHERE active = $/isTrue/ AND name = $/name/`, {isTrue, name})
Solution

  • is there any benefit to using $1 over Template Literals?

    You cannot use Template Literals, which documentation emphasizes:

    IMPORTANT: Never use the reserved ${} syntax inside ES6 template strings, as those have no knowledge of how to format values for PostgreSQL. Inside ES6 template strings you can only use one of the 4 alternatives - $(), $<>, $[] or $//.

    You should use Named Parameters, they offer a lot of benefits over the Index Variables:

    For your own example, if you really need Template Strings there:

    const isTrue = true;

await db.any(`SELECT * FROM users WHERE active = $/isTrue/`, {isTrue});

    or you can just use the standard strings:

    await db.any('SELECT * FROM users WHERE active = ${isTrue}', {isTrue});