How do I parameterize a Postgres array value in Node / pg-pool

Question

I am querying an array column (style text[]) using the contains operator @>. My raw query is:

SELECT * FROM songs WHERE style @> '{Acoustic}'

When I drop this into node (using pg-pool) and attempt to parameterize it, the braces surrounding the value reference seem to prevent it from working properly. Note these examples:

const style = 'Acoustic';

// this works, but I don't want to in-line like this:
const { rows } = await pool.query(`SELECT * FROM songs WHERE style @> '{` + style + `}'`);

// this fails with 'error: bind message supplies 1 parameters, but prepared statement "" requires 0'
const { rows } = await pool.query(`SELECT * FROM songs WHERE style @> '{$1}'`, [style]);

What's the proper way to parameterize this?

Answer 1

const { rows } = await pool.query(`SELECT * FROM songs WHERE style @> '{$1}'`, [style]);

The $1 here, being in quotes, is just regarded as part of the string, not as a substitutable variable. If you wish to pass in a scalar and have it treated as an array, you should be able to do it this way:

const { rows } = await pool.query(`SELECT * FROM songs WHERE style @> ARRAY[$1]`, [style]);

An alternative might be to have node.js bind an array directly (not tested):

const { rows } = await pool.query(`SELECT * FROM songs WHERE style @> $1`, [[style]]);