safeql: Unexpected error on sql helper function

Thanks for the detailed report.

This issue is similar to the other issue you’ve opened (https://github.com/ts-safeql/safeql/issues/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.

@Eprince-hub another workaround would be to simply use = ANY instead of IN:

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

@louneskmt While SafeQL won’t support conditional queries due to performance reasons, You could write your query with CASE WHEN expression instead (which personally I think it looks nicer):

export function query(id: number) {
  return sql`
    SELECT *
    FROM try_safe_ql
    WHERE
      test = 'test'
      AND CASE WHEN ${id !== ...} THEN id = ${id} END
  `;
}

Workaround 2

If disabling the ESLint checking is not what you want to do, then you can do something like this.

// Workaround for the Postgres.js sql() helper function that is causing a SafeQL linting error
// https://github.com/ts-safeql/safeql/issues/101#issuecomment-1623699171
export async function query(a: number[]) {
  return await sql`
    SELECT
      *
    FROM
      try_safe_ql
    WHERE
      id IN (SELECT unnest(${a}::int[]));
 `;
}

Hi @Eprince-hub

Thanks for the support 🙂 I think I need to rewrite my implementation for SQL fragments so it can be more robust and easier to maintain. SafeQL doesn’t have a community so it’s a bit hard for me to prioritize features. I hope I’ll continue working on fragment support in the next week. Although, fragments have their own limitations, so be advised.

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

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

I have a private branch that allows 3rd party providers (such as DrizzleORM, kysely, postgres-js and slonik) to “translate” their expressions to sql syntax. I started with postgres-js, but it’s way too dynamic.

I need to rewrite the branch so it could be easily maintained by others, but that will require some time that I don’t have these days. I will get back to it eventually.

Unfortunately, the workaround 1 suppress the error for the whole sql statement and not only the fragment when using a conditional:

export function query(id: number) {
  return sql`
    SELECT *
    FROM try_safe_ql
    WHERE
      test = 'test'
      ${id ? sql`AND id = ${id}` : sql``}
  `;
}

Workaround 1

You can disable the ESLint check for SafeQL for the lines showing this error.

export async function query(a: number[]) {
  return await sql`
    SELECT
      *
    FROM
      try_safe_ql
    WHERE
      id IN ${
        // eslint-disable-next-line @ts-safeql/check-sql -- Postgres.js sql() helper function currently not supported by SafeQL https://github.com/ts-safeql/safeql/issues/101#issuecomment-1623687201
        sql(a)
      }
  `;
}