Multi-statement queries causes wrong result interpretation

[matis-dk]

Executing a multi-statement raw sql query towards PostgreSQL, doesn't seem to be supported currently. For example:

const result = await sql.raw(`
    BEGIN;

    DO $$
    BEGIN
      PERFORM "hello_world"();
    END $$;

    SELECT current_user;

    COMMIT; 
  `)
  .execute(client);

The issue seems to be that the executeQuery inside the postgres-driver always expects an object return type from the this.#client.query, when in fact, it could be either an object, or a list of objects. This causes the return evaluation to swallow the result by returning an empty array return { rows: result.rows ?? []}; .

This behavior is documented here inside the pg package, but unfortunately, the ts type signature for pg, seems to get this wrong as well.

I'm unsure about which type of solution you would prefer moving forward - and which implication it might have. Currently, I'm patching this package with Array.isArray check of the result, but this might not be doable as a whole.

[koskimas]

Unfortunately we can't support this. We'd need to change the output for every query since we can't detect compile-time if the query would return an array or multiple arrays. Adding a separate method is not an option either since we'd need to modify the driver interface, which is used by all third party dialects.

[matis-dk]

Would expanding QueryResult type be an option? Exposing the raw response from the pg client inside the QueryResult payload.

This comment The rows returned by the query. This is always defined and is empty if the query returned no rows. for the rows property is misleading, because the response from pg actually have rows.