extra dynamic queries

[basaran]

Hi again :)

Could you please advise the possibility of achieving this? or if it at all feasible to do?

const key = "yay";
 
const query = `
   SELECT * from ahoy
   WHERE 1 = 1
   ${key && `AND ahoy_key = '${key}'`}
`;

[basaran]

Besides the unsafe way that is.

[porsager]

Hey there 🙂

Dynamically composing sql like that is not possible yet, but I've got a plan outlined and the progress is tracked in #12

The reason it's not simply concatenating strings is because I want to prevent accidental sql injection.

Usually there are ways of achieving it without resolving to .unsafe(), for instance your case above could be written like this:

const key = "yay"; // or null
 
const result = await sql`
   SELECT * from ahoy
   WHERE 1 = 1 and (${ key } is null or key = ${ key })
`

[basaran]

hi, your example is giving me:

could not determine data type of parameter $1

  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42P18',
  file: 'postgres.c',
  line: '1496',
  routine: 'exec_parse_message',
  query: ';\n' +
    '        SELECT * from ahoy\n' +
    "        WHERE 1 = 1 and ($1 is null or ahoy_key = '$2')",
  parameters: [
    { type: 0, value: '/2212', raw: '/2212' },
    { type: 0, value: '/2212', raw: '/2212' }
  ]

am I missing anything?

[basaran]

${key}::text worked however. Thank you,

[porsager]

Ah ok. Out of curiosity what type is the ahoy_key column in your example?

[basaran]

it was just a character varying.

const key = false || null

was also fine, with ::text

[porsager]

Ah alright, thanks 👍

[basaran]

well, thank you kind sir :)

Here's the complete snippet for the future trouble maker:

const key = "/2212" || null;
//const key = false || null;

const result = await sql`
    SELECT * from ahoy
    WHERE 1 = 1
    and (${key}::text is null or ahoy_key = ${key})
`;

[arxpoetica]

I've got a query with this:

WHERE lower(tt.tags::text)::text[] @> ${sql.array(tags)}

It works great when there's a tag string or more in the array. But when sql.array(tags) is [] it only returns items with something tagged. I need to return all results searching against an empty array.

1=1 is doing nothing for me.

Would really like to avoid unsafe. Help?

[arxpoetica]

Hey! This worked:

WHERE lower(tt.tags::text)::text[] @> ${sql.array(tags)}
	OR ${sql.array(tags)} = '{}'