Alternative to tagged template function?

[digitalnature]

the tagged template works well for basic things, but when it comes to building queries with dynamic parameters, the syntax gets crazy really fast. Because arrays that contain sql tag cannot be joined with a string like comma or and, we are forced to use ternary operator a lot and this creates code that is very hard to read.

Is there an alternate way to do queries, like a simple query function that accepts raw sql with ? placeholders, and function arguments for parameters?

[stephenh]

Hi @digitalnature ; I have some knex-based / "make a SQL string with ?s and a params[]" that I wanted to execute through postgres.js and was able to do it like this:

function convertToSql(sql: Sql, query: string, bindings: any[]): PendingQuery<any> {
  // Split the query string by the placeholder character '?'
  const fragments = query.split("?");
  if (fragments.length - 1 !== bindings.length) {
    throw new Error(`Mismatch between placeholders (${fragments.length - 1}) and bindings (${bindings.length})`);
  }
  // postgres.js does runtime detection of the `raw` property to determine if it's a template string
  const templateStrings = Object.assign(fragments, {
    raw: fragments,
  }) as unknown as TemplateStringsArray;
  return sql(templateStrings, ...bindings);
}

This takes a query like select * from authors where a = ? and b = ? and a bindings array of [1, 2] and calls postgres.js's sql tagged literally with just enough squinting that postgres.js thinks this was written as:

await sql`select * from authors where a = ${1} and b= ${2}`;

This approach also makes the query "look static" to postgres.js's internals, so it will get prepared statement-ized, which the sql.unsafe API doesn't do.

Hope that helps!

[porsager]

This is what sql.unsafe does. Just use parameters with it.

https://github.com/porsager/postgres

[stephenh]

Hi @porsager , I had started out doing that (using sql.unsafe), but it disables prepared statements.

I assume this is b/c unsafe thinks the query isn't "statically structured" (i.e. it cannot hash/cache the query structure) and sets prepare: false.

Do you think this is a fundamental limitation of sql.unsafe, or could it be taught to still support prepared statements?

Thanks!

[porsager]

You just need to pass { prepare: true } as options to unsafe 😉

If it's not in the docs you're more than welcome to PR it.

[stephenh]

@porsager Ack! I should have seen that. :-) Thank you! Opened #1065 adding a paragraph to the readme. 🙏

With that insight, my guess is that sql.unsafe fulfills @digitalnature original ask, and this issue can be closed out.

[porsager]

Well, hard to see if it's not there 😇 thanks for the PR, and yes, I think it answers this issue too 👍

https://github.com/porsager/postgres?tab=readme-ov-file#await-sqlunsafequery-args-options---result