Why is my dynamic SQL resulting in the error shown below?

I am trying to build a SQL statement dynamically and this is one variation of the result.

sqlQuery {
  name: 'fetch-products',
  text: 'select * from products where category = 
  $1 and designer in $2',
  values: [ 'WOMENSCLOTHING', "('Adjavon', 'ALC', 'Adele', 'Bagley')" ]
}

I build the sql with the following code segment:

const {
    category,
    designers,
} = JSON.parse(filters);

let values = [category];

let text = 'select * from products where category = $1';

if(designers) {
    text = text + ' and designer in $2';
    values.push(designers);
}

I execute it in the following segment:

try {
    const allProducts = await pool.query(sqlQuery);

    res.status(200).json(allProducts.rows);
} catch (error) {
    console.error(error);
    return res.status(500).send('Problems getting products by category.')
}

And get the following error:

Error: syntax error at or near "$2"

I am thinking the error may be the double quotes placed around designer when it is pushed on the values array:

values: [ 'WOMENSCLOTHING', "('Adjavon', 'ALC', 'Adele', 'Bagley')" ]

Solution

I don't know what library you are using exactly, but the values property looks highly suspicious.

sqlQuery {
  name: 'fetch-products',
  text: 'select * from products where category = 
  $1 and designer in $2',
  values: [ 'WOMENSCLOTHING', "('Adjavon', 'ALC', 'Adele', 'Bagley')" ]
}

If your drivr/library supports this, the second element in the array should be an actual array and not a string like '("foo", "bat")'. How is the driver supposed to know this is meant as a list and not a single string that has this value?

I guess in a nutshell you have to bring the query in this form:

const query = 'select * from products where category = $1 and designer in ($2, $3, $4, $5)'

const values =  [ 'WOMENSCLOTHING', 'Adjavon', 'ALC', 'Adele', 'Bagley' ]

That requires some extra work on the backend to map the values and bring them into the right shape.

I guess you could get that done with something like this:

const category = 'icecream'
const designers = ['jim', 'maria']

let values = [category];

let text = 'select * from products where category = $1';

if (designers) {
  text += ` and designer in (${designers.map((d, i) => `$${i+2}`).join(', ')})`;
  values = [...values, ...designers];
}

console.log(text);
console.log(values);