Unexpected error on sql helper function

This issue has been tracked since 2022-11-10.

Describe the bug
SafeQL throws an error when using the sql() helper in Postgres. This is a feature in postgres.js but it seems like SafeQL doesn't allow using this helper in writing queries. The queries below would throw this error Invalid Query: the type "Helper<number[], []>" is not supported".

export async function query(a: number[]) {
  return await sql`
    SELECT
      *
    FROM
      try_safe_ql
    WHERE
      id IN ${sql(a)}
  `;
}

To Reproduce
Steps to reproduce the behavior:

Setup SafeQL
Use this code in .ts file

export async function query(a: number[]) {
  return await sql`
    SELECT
      *
    FROM
      try_safe_ql
    WHERE
      id IN ${sql(a)}
  `;
}

Expected behavior
Usage of sql() helper should not throw errors

Screenshots

Desktop (please complete the following information):

OS: MAC OS
PostgreSQL version 14
Version [e.g. 22]

Additional context
Add any other context about the problem here.

Newbie012 wrote this answer on 2022-11-16

Thanks for the detailed report.

This issue is similar to the other issue you've opened (#99) so I'll answer for both of them:

SafeQL tries to get the type for each "expression" in the SQL tag. It doesn't know that sql should be dealt with differently.

for example:

function sum(a, b) {
  return a + b;
}

const q = (x, y) => sql`select from tbl where col = ${sum(x, y)}`;

SafeQL takes the expression sum(x, y), get the return type (which is number) and converts it to pg type (int):

select from tbl where col = $1::int

SafeQL should start supporting query builders, but it shouldn't be specific to Postgres.js. Libraries such as Slonik should also be taken into account.

esdee wrote this answer on 2022-11-30

I think that the main advantage of supporting this is to be able to DRY up some code around column selection.

const cols = ['id', 'name', ...]; // e.g. users table
sql `select ${sql(cols)} from users`;

This is mentioned in the docs for Postgres.js, dynamic columns

Newbie012 wrote this answer on 2022-11-30

Correct. Ideally, I'm not against using sql fragments in code.

I think I have no other option than implementing library-specific behavior { sqlFragmentSyntax: "postgres" | "slonik" | "..." }` to prevent incompatibilities between libraries.

Newbie012 wrote this answer on 2022-12-29

I tweeted about my progress on this matter. Hopefully I'll push a V1 for this.

Your Name

Your Comment

More Details About Repo

Owner Name	ts-safeql
Repo Name	safeql
Full Name	ts-safeql/safeql
Language	TypeScript
Created Date	2022-09-08
Updated Date	2023-03-16
Star Count	795
Watcher Count	5
Fork Count	14
Issue Count	7

YOU MAY BE INTERESTED

Issue Title	Created Date	Updated Date