👋 Is it possible to provide more information in the error output?

[HHogg]

Hi 👋

I'm struggling to debug this query (even after reading the documentation... several times). I know it's highly likely I'm doing something wrong, but I'm wondering if it's possible to add any additional information to the error that is being thrown to help track down the issue? Like outputting the final SQL query with some formatting to highlight which part is failing?

const selectKeys = ['name', ...];
const department = 'food';
const category = undefined;
const field = 'price';
const direction = 'asc';

const productsQuery = sql`
  select ${sql(selectKeys)}
  from products
  where department = ${department}
  ${category ? sql`and ${category} = any(categories)` : sql``}
  ${sql`order by ${sql(field)} ${direction}`}
  limit ${limit}`;

// PostgresError: syntax error at or near "$2"

Side note: I really like the API of the library, I think you did a great job keeping it as close to writing standard sql while also being super powerful.

[HHogg]

With the help of ChatGPT (😬) I can now see it's the ${direction} part that was the issue.

const productsQuery = sql`
    select ${sql(selectKeys)}
    from products
    where department = ${department}
    ${category ? sql`and ${category} = any(categories)` : sql``}
    order by ${sql(field)} ${direction === 'asc' ? sql`asc` : sql`desc`}
    limit ${limit}`;

... is what was needed.

However I guess my question is still relevant, in that is it possible to provide more hints in the error message?

[karlhorky]

Would be great to get more information in the error output 👍

I think the current open issue requesting this is:

• https://github.com/porsager/postgres/issues/380

[josh-i386g]

Hmmm I think yes sir maybe the error messages could be improved.

But another approach could be avoiding those errors by using a query builder.

I used to write queries this manual way too until I used Kysely (it's a query builder, not an ORM, I hate ORMs).

• https://github.com/kysely-org/kysely/
• https://github.com/kysely-org/kysely-postgres-js (this lets you use porsager's postgres.js instead of brianc's node-postgres)
• https://kysely.dev/
• https://kysely.dev/docs/getting-started
• https://kysely.dev/docs/category/select
• https://kysely.dev/docs/category/where
• https://kysely-org.github.io/kysely-apidoc/interfaces/SelectQueryBuilder.html (higher-level helpers)
• https://kysely-org.github.io/kysely-apidoc/interfaces/Sql.html (lower-level helpers)

const query = db
  .withSchema(schemaName)
  .selectFrom(tableName)
  .select(selectKeys)
  .where('department', '=', department);

if (categories instanceof Array && categories.length > 0) {
  query = query.where('categories', in, categories);
}

if (typeof sortDirection === 'string') {
  query = query.orderBy(sortField, sortDirection);
} else {
  query = query.orderBy(sortDefaultField, sortDefaultDirection);
}

if (typeof limit === 'number') {
  query = query.limit(limit);
}

const compiled = query.compile();
console.log({ compiled });

const items = await query.execute();
console.log({ items });

Above example may or may not be the exact code you need but maybe just tweak it better.

[karlhorky]

Hm, @josh-i386g not so sure this is the best recommendation for this project.

I think the ability of writing raw SQL in Postgres.js and getting verbose, helpful error messages should be the default - not recommending that users download, install and integrate a separate query builder.