Request for Generic Type Support in 'Queryable.query'

[mustakimkr]

Issue:
The "Queryable.query" method in the @databases/pg library currently does not support generic types, while the "pool.query" from pg method does.

Request:
I kindly request adding support for generic types in the Queryable.query method, similar to how it's implemented in "pool.query" from pg. This would greatly benefit users of the library by allowing them to specify the expected result types when executing SQL queries.

Use Case:
Consider the following example where generic type support would be beneficial:

const db = createConnectionPool(/* connection details */);

async function getUserById(id: number): Promise<User | null> {
  const result = await db.query<User>("SELECT * FROM users WHERE id = $1", [id]);
  // Currently, 'result' is of type 'any[]', but we want it to be 'User[]'
  return result[0] || null;
}

In the code above, we want to query the database for a user and return it as a User object. With generic type support, we could specify that the db.query method should return an array of User objects (User[]), providing better type safety and code clarity.

Proposed Solution:
Extend the existing type definition for the query method in @databases/pg to include generic type support. Change this:
query(query: SQLQuery): Promise<any[]>;

to this:
query<T>(query: SQLQuery): Promise<T[]>;

This change would allow developers to specify the expected result type when calling the query method, providing improved type safety and a better developer experience.

Environment:
Library Version: @databases/pg 5.4.1
Node.js Version: 18

[ForbesLindesay]

The result of running arbitrary SQL is always an array of unknown values. Adding generic type parameters introduces a false sense of security.

Instead of:

  const records = await db.query<{screen_name: string; age: number}>(
    sql`SELECT screen_name, age FROM ${table} ORDER BY screen_name ASC`,
  );

You should just write:

  const records: {screen_name: string; age: number}[] = await db.query(
    sql`SELECT screen_name, age FROM ${table} ORDER BY screen_name ASC`,
  );

or

  const records = await db.query(
    sql`SELECT screen_name, age FROM ${table} ORDER BY screen_name ASC`,
  ) as {screen_name: string; age: number}[];

It's nearly the exact same number of characters, and requires no changes to the library. I prefer this syntax as it makes it clearer that I'm not passing a generic type parameter to db.query that's then being checked in some way, I'm just running a query and then telling TypeScript what type the returned data has.