could not determine data type of parameter $1

[Catsvilles]

Hi, I'm getting error when trying to pass dynamic variable into my select query:

const response = await sqlSELECT * FROM posts WHERE data->'topic' @> '[{"slug": "${slug}" }]';

Is there any way around this? I tried passing ${slug} without double quotes around it but then I get invalid json error.

[porsager]

The postgres protocol doesn't accept parameters inside json strings.

You need to pass in the entire JSON as a parameter, and preferably use sql.json() to stringify and send the correct type automatically.

So this should work for instance:

const response = await sql`
  SELECT * FROM posts WHERE data->'topic' @> ${ sql.json([{ slug: slug }]) }
`

[Catsvilles]

@porsager Thank you very much, this works! No beginner's luck this time... tbh, I almost gave up and was thinking that I should just start using ORM or bigger package like pg, and forget chasing the performance, considering that there are so few examples and docs on postgres.js. But anyway I will try to stick with this one and fight my way with it! :)

[Minigugus]

@Catsvilles

or bigger package like pg

With pg you would have the same problem since it's the PostgreSQL server code that doesn't support your syntax, unless you concatenated your parameter directly but this would expose you to SQL injections 😉

Excluding .unsafe, the postgres package will always be safe and efficient thanks to tagged template strings 👍

considering that there are so few examples and docs

A big v2.0 is coming, expect more complete doc once released (with TypeScript and JSDoc especially) 😉