bun:postgres and templateing queries

[frigid fiber]

Hi everyone,

I have this code:

const query = sql`select * from "collections" c where c."label" ilike '%' || ${label} || '%'`
const rawRows = await query

this works fine. However, I would like to separate the query into a template and then use it in several places. The parameter expansion should still be SQL-safe.

This is "conceptually" what I would like to do, but i somehow can't get it to work.

const QUERY_TEMPLATES = {
  GetByLabel = `select * from "collections" c where c."label" ilike '%' || $1 || '%'`
}
// ...

const rows = await sql(QUERY_TEMPLATES.GetByLabel, [param1])

Does anyone know how this is done?

[dull canopy]

to run the code like this you should try await sql.unsafe(QUERY_TEMPLATES.GetByLabel, [param1]) or even await sql.file(filename, [1,2,3]) see https://bun.sh/docs/api/sql#queries-in-files

if the string is well know and in memory I would recommend the sql.unsafe because it will not read a file and the same as sql.file

[frigid fiber]

I have already tried what you suggest. Won't do it somehow.

[dull canopy]

what error are you getting? and what version of bun?

[frigid fiber]

I am on bun v1.2.4; it depends in some cases, i get POSTRGES syntax errors and in some cases I get simply an empty list.

so no results at all.

wait, it's this:
code: "ERR_POSTGRES_NOT_TAGGED_CALL"

when doing this:

const QQ = `
    SELECT *
    FROM table
    WHERE id in $1`

const rows = await sql(QQ, [collectionIds])

brakcets dont change a thing here :/

[dull canopy]

you cannot use sql() you need sql.unsafe for this

[frigid fiber]

so no safe way then? 😄

i mean, would this help: sql(QQ, [sql(allIDs)])

this should sanitize the IDs before putting them in the query, right?

[dull canopy]

if you are executing a SQL from a string or file is unsafe by nature, just be careful to use parameters instead of concating args and you will be fine

[frigid fiber]

do i have to take care of escaping quotes, strings, etc?

[dull canopy]

also in this case with WHERE in this will not work

but this will work:

const query = sql.unsafe("SELECT * FROM users WHERE id in");
console.log(await sql${query} ${sql([1, 2, 3])});

[frigid fiber]

in case i have something lile select * from user where name = $1, would things go wrong if $1 = '""; DROP DATABASE;' ?

[dull canopy]

because the parameters will be properly expanded at runtime

[dull canopy]

the $1 will be passed as a prepare statment

so will be safe to pass any string

just dont manually concat strings

[frigid fiber]

this example works, because the query ends with the parameter, i suppose. i can't have multiple params/params at weird positions?

so i basically treat everything as a big template string and sql`` things later on?

[dull canopy]

sql helpers only works when using tagged templates

not in file or unsafe directly because this would need fixed amount of parameters

and a array is N parameters not 1 parameter

[frigid fiber]

i see. ok, makes more sense now.

The sql.file() would really help, but as you said, maybe it'll happen in the future 🙂