WHERE IN throws `TypeError: str.replace is not a function`

[Nepherpitou]

Queries with WHERE IN ${sql()} fails with error starting from version 3.4.0

await sql`
  select
    *
  from users
  where age in ${ sql([68, 75, 23]) }
`

Error:

TypeError: str.replace is not a function
    at escape (...\node_modules\postgres\cjs\src\types.js:217:20)
    at ...\node_modules\postgres\cjs\src\types.js:213:22
    at Array.map (<anonymous>)
    at escapeIdentifiers (...\node_modules\postgres\cjs\src\types.js:213:13)
    at Object.select [as fn] (...\node_modules\postgres\cjs\src\types.js:139:12)
    at Builder.build (...\node_modules\postgres\cjs\src\types.js:71:17)
    at stringifyValue (...\node_modules\postgres\cjs\src\types.js:109:38)
    at stringify (...\node_modules\postgres\cjs\src\types.js:100:16)
    at build (...\node_modules\postgres\cjs\src\connection.js:223:20)
    at Object.execute (...\node_modules\postgres\cjs\src\connection.js:167:7)
    at go (...\node_modules\postgres\cjs\src\index.js:341:14)
    at Query.handler (...\node_modules\postgres\cjs\src\index.js:330:14)
    at Query.handle (...\node_modules\postgres\cjs\src\query.js:140:65)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at new Query (...\node_modules\postgres\cjs\src\query.js:35:9)
    at Object.sql (...\node_modules\postgres\cjs\src\index.js:112:11)

[rytido]

I've only seen this when there's a nested in (which you can get around with a cte). There also seems to be something going on with whether or not there's whitespace after the in:

sql.unsafe(`drop table if exists t; create table t as (select 1 x);`).then(() => {
  // success: not nested
  sql`select x from t where x in ${sql([1])}`
    .then((data) => console.log(data)) // Result [{x: 1}]
    .catch((err) => console.log(err.message));

  // failure: nested without space after outer 'in'
  sql`select x from t where x in(select x from t where x in ${sql([1])})`
    .then((data) => console.log(data))
    .catch((err) => console.log(err.message)); // str.replace is not a function

  // failure: nested without space after inner 'in'
  sql`select x from t where x in (select x from t where x in${sql([1])})`
    .then((data) => console.log(data))
    .catch((err) => console.log(err.message)); // str.replace is not a function

  // success: space after outer and inner 'in'
  sql`select x from t where x in (select x from t where x in ${sql([1])})`
    .then((data) => console.log(data)) // Result [{x: 1}]
    .catch((err) => console.log(err.message));
});

[porsager]

@Nepherpitou just tried exactly your example and didn't see any issue. Do you have something I can repro?

[porsager]

Very nice tests @rytido !! Thank you - I'll look at fixing those and including your tests.

[alonrbar]

For what it worth, I had encountered this issue too and the comment by @rytido helped me work around it.
This was my original query:

const val1 = "1";
const valuesArray = [
  ["2", "3", "4"]
];

sql`
    DELETE FROM my_table
    WHERE
        col1 = ${val1} AND
        (col2, col3, col4) IN (${sql(valuesArray)})
`;

Removing the space directly after the "IN" made it work:

sql`
    DELETE FROM my_table
    WHERE
        col1 = ${val1} AND
        (col2, col3, col4) IN(${sql(valuesArray)})
`;

[rozenmd]

For anyone else coming in from Google, I managed to get this working in postgres.js 3.4.5 by replacing:

sql`
...
WHERE table.id IN (${sql(valuesArray)});
`

with

sql`
...
WHERE table.id IN ${sql(valuesArray)};
`

Basically, remove the brackets around ${sql(...)}.