TypeScript ergonomic improvements

[baoshan]

Thanks very much for providing TypeScript definitions. Here are some of my initial thoughts:

More Natural JSONB and JSON Support

Suppose there is a database function defined as public.register_user(jsonb), this is the natural way to invoke it:

const sql = postgres();
const [user] = await sql`SELECT register_user(${{name: "jack"}})`;

Current it is impossible because:

Argument of type '{ name: string; }' is not assignable to parameter of type 'SerializableParameter<never> | PendingQuery<any>'.

sql.json({name: "jack"}) does not work as I tested (even it works, the experience is suboptimal IMHO). The only option (as I can see) is to define the client as:

const sql = postgres() as postgres.Sql<Record<string, unknown>>;

Type definitions should reflect the JavaScript implementation as much as possible. Since postgres.js properly passes objects to the database, why the type definition prohibits such usage?

jsonb and json are first class types of PostgreSQL from my humble point of view. It is the end user’s responsibility to provide the correct value (and thus type) for their queries.

Why is sql Client Generic?

This leads to my primary confusion: Why does the generic parameter of the sql client affects types of parameters I can pass to a query? Is the same sql client expected to support different queries?

Use Tuple Type (Rather Than Relation Type) as Generic Variable of Query

There is another suggestion. It bothers me less than the previous ones. Since the result of a query is always a row list, this looks better:

const [user] = await sql<{id: number, name: string}>`SELECT register_user(${{name: "jack"}})`;

than:

const [user] = await sql<{id: number, name: string}[]>`SELECT register_user(${{name: "jack"}})`;

Please correct me @Minigugus and @porsager if some of my thoughts are immature. I’d like to contribute when the time is ready. Thanks.

[Minigugus]

why the type definition prohibits such usage?

Because I thought implicit JSON would cause TS issues but it don't seams to with the correct fix 😅

--- postgres/types/index.d.ts
+++ postgres/types/index.d.ts
@@ -164,7 +164,7 @@
 
 type SerializableObject<T, K extends readonly any[], TT> =
   number extends K['length'] ? {} :
-  (Record<Keys & (keyof T) & (K['length'] extends 0 ? string : K[number]), postgres.SerializableParameter<TT> | postgres.JSONValue> & Record<string, any>)
+  (Record<Keys & (keyof T) & (K['length'] extends 0 ? string : K[number]), postgres.SerializableParameter<TT>> & Record<string, any>)
 
 type First<T, K extends readonly any[], TT> =
   // Tagged template string call
@@ -174,7 +174,7 @@
   // Dynamic values helper (depth 2)
   T extends readonly any[][] ? readonly postgres.EscapableArray[] :
   // Insert/update helper (depth 2)
-  T extends readonly (object & infer R)[] ? (R extends postgres.SerializableParameter<TT> ? readonly postgres.SerializableParameter<TT>[] : readonly SerializableObject<R, K, TT>[]) :
+  T extends readonly (object & infer R)[] ? (R extends postgres.SerializableParameterWithoutJSON<TT> ? readonly postgres.SerializableParameter<TT>[] : readonly SerializableObject<R, K, TT>[]) :
   // Dynamic values/ANY helper (depth 1)
   T extends readonly any[] ? (readonly postgres.SerializableParameter<TT>[]) :
   // Insert/update helper (depth 1)
@@ -465,12 +465,17 @@
     | Date
     | Uint8Array;
 
-  type SerializableParameter<T = never> = never
+  type SerializableParameterWithoutJSON<T = never> = never
     | T
     | Serializable
     | Helper<any>
     | Parameter<any>
     | ArrayParameter
+    | readonly SerializableParameterWithoutJSON<T>[];
+
+  type SerializableParameter<T = never> = never
+    | JSONValue
+    | SerializableParameterWithoutJSON<T>
     | readonly SerializableParameter<T>[];
 
   type JSONValue = // using a dedicated type to detect symbols, bigints, and other non serializable types

Why does the generic parameter of the sql client affects types of parameters I can pass to a query?

That's because postgres allows to specify custom types, then in order to provide accurate types, custom types description have to be passed down to the sql function:

// no bigint support because the relevant custom type is missing

const sql0 = postgres() // sql is of type `postgres.Sql<{}>`

await sql0`SELECT ${42n}` // error: `bigint` not assignable to `SerializableParameter<never>`

// with bigint custom type registered

const sql1 = postgres({
  types: {
    BigInt: postgres.BigInt
  }
}) // sql is of type `postgres.Sql<{ BigInt: bigint }>`

await sql1`SELECT ${42n}` // ok (`bigint` is assignable to `SerializableParameter<bigint>`)

await sql1`SELECT ${[42n, 42n]}` // ok too (`SerializableParameter<bigint>` recursively allows custom types in nested arrays)

postgres is not an easy library to type correctly, I'm doing my best to keep decent parameters completion, but TypeScript is not magic (e.g. undefined transforms, sql() helpers depending on SQL context, ...), and I'm not always aware of new features before they got release (or even so I don't especially have time to update types). That's why PR are welcomed, and why there is a Discussion tab enabled 😁

PRO TIP: disable parameters validation by casting sql to postgres.Sql<any> 😉

[baoshan]

❤️ Well, thanks very much for the details. That all make sense.

I’ll find some time to digest the existing types and see if it is possible to make some margin improvement.