Issue with dynamically building condition clause about postgres HOT 8 OPEN

Currently this requires you to use sql.unsafe to get around it, which is ugly and ... unsafe

const filters = [
    'foo IN ('nice')',
    'foo IN ('cool')
]

await sql`
SELECT foo FROM bar
${sql.unsafe(WHERE ${filters.join (' AND ')})}
`

@porsager I'm not an expert in either lib, but it seems the library-level fix for this would be a sql-safe .join, similar to what slonik has ?

What do you think?

from postgres.

try debugging the query that the client creates, maybe might be helpful to understand if your conditions are correct.

  debug: (
    conn: number,
    query: string,
    params: unknown[],
    paramTypes: unknown[],
  ) => {
    logger.debug(`
    SQL::
      Executing query: "${query.trim()}"
      Params: ${JSON.stringify(params)}
      Param Types: ${JSON.stringify(paramTypes)}
      Connection: ${conn}
    `);
  },

put this in the postgres initialization options

from postgres.

Here is my test results

let dynamicFilters = [
        transactionSql`id=${id}`,
        transactionSql`name=${name}`,
    ];


await transactionSql`
SELECT * FROM table WHERE ${ dynamicFilters.join(' AND ') }
`

The generated sql is SELECT * FROM table WHERE $1 and the param is [object Promise]

let dynamicFilters = [
        `id=${id}`,
        `name=${name}`,
    ];


await transactionSql`
SELECT * FROM table WHERE ${ transactionSql`${ dynamicFilters.join(' AND ') }` }
`

The generated sql is SELECT * FROM table WHERE $1 and the param is id=x AND name=x

let dynamicFilters = [
        transactionSql`id=${id}`,
        transactionSql`name=${name}`,
    ];


await transactionSql`
SELECT * FROM table WHERE ${ transactionSql`${ dynamicFilters.join(' AND ') }` }
`

The generated sql is SELECT * FROM table WHERE $1 and the param is ["[object Promise]"

try debugging the query that the client creates, maybe might be helpful to understand if your conditions are correct.

  debug: (
    conn: number,
    query: string,
    params: unknown[],
    paramTypes: unknown[],
  ) => {
    logger.debug(`
    SQL::
      Executing query: "${query.trim()}"
      Params: ${JSON.stringify(params)}
      Param Types: ${JSON.stringify(paramTypes)}
      Connection: ${conn}
    `);
  },

put this in the postgres initialization options

from postgres.

the filter fragments are being generated as promises instead of SQL query fragments. maybe await before the fragments can help?

from postgres.

the filter fragments are being generated as promises instead of SQL query fragments. maybe await before the fragments can help?

let dynamicFilters = [
        await transactionSql`id=${id}`,
        await transactionSql`name=${name}`,
    ];


await transactionSql`
SELECT * FROM table WHERE ${ dynamicFilters.join(' AND ') }
`

I tried that too, but got error invalid sql id=$1. Seems like it try to run each snippet

from postgres.

bumping this, getting exactly the [object Promise] issue.

from postgres.

Bumping as well - pretty big deal breaker sadly :(

from postgres.

So the problem you've got is that Array.prototype.join returns a string, and sql is not just strings. You need to use a join that correctly handles sql and parameters. You need each part to be an sql fragment, rather than a string. Look at the code in issue #807 if you're unsure.

(Note: Do not use unsafe or await for this; they both will have unexpected outcomes.)

from postgres.