postgres - npm Package Compare versions

Comparing version 3.1.0 to 3.2.0

cjs/src/connection.js

@@ -271,3 +271,7 @@ const net = require('net')
         ? { rejectUnauthorized: false }
-        : ssl
+        : ssl === 'verify-full'
+          ? {}
+          : typeof ssl === 'object'
+            ? ssl
+            : {}
       )
@@ -354,3 +358,3 @@ })
       socket.on('data', data)
-      socket.setKeepAlive && socket.setKeepAlive(true, 1000 * keep_alive)
+      keep_alive != null && socket.setKeepAlive(true, 1000 * keep_alive)
       const s = StartupMessage()
@@ -534,3 +538,3 @@ write(s)
 
-    while (sent.length && (query = sent.shift()) && (query.active = true) && query.cancelled)
+    while (sent.length && (query = sent.shift()) && (query.active = true, query.cancelled))
       Connection(options).cancel(query.state, query.cancelled.resolve, query.cancelled.reject)
@@ -571,3 +575,3 @@
     if (query.options.simple)
-      return
+      return BindComplete()
 
@@ -574,0 +578,0 @@ if (query.cursorFn) {

cjs/src/index.js

@@ -210,3 +210,3 @@ const os = require('os')
     async function scope(c, fn, name) {
-      const sql = Sql(handler, true)
+      const sql = Sql(handler)
       sql.savepoint = savepoint
@@ -379,4 +379,4 @@ let uncaughtError
       , o = (typeof a === 'string' ? b : a) || {}
-      , { url, multihost } = parseUrl(a, env)
-      , query = url.searchParams
+      , { url, multihost } = parseUrl(a)
+      , query = [...url.searchParams].reduce((a, [b, c]) => (a[b] = c, a), {})
       , host = o.hostname || o.host || multihost || url.hostname || env.PGHOST || 'localhost'
@@ -386,3 +386,22 @@ , port = o.port || url.port || env.PGPORT || 5432
 
-  return Object.assign({
+  o.no_prepare && (o.prepare = false)
+  query.sslmode && (query.ssl = query.sslmode, delete query.sslmode)
+  'timeout' in o && (console.log('The timeout option is deprecated, use idle_timeout instead'), o.idle_timeout = o.timeout) // eslint-disable-line
+
+  const defaults = {
+    max             : 10,
+    ssl             : false,
+    idle_timeout    : null,
+    connect_timeout : 30,
+    max_lifetime    : max_lifetime,
+    max_pipeline    : 100,
+    backoff         : backoff,
+    keep_alive      : 60,
+    prepare         : true,
+    debug           : false,
+    fetch_types     : true,
+    publications    : 'alltables'
+  }
+
+  return {
     host            : Array.isArray(host) ? host : host.split(',').map(x => x.split(':')[0]),
@@ -394,12 +413,17 @@ port            : Array.isArray(port) ? port : host.split(',').map(x => parseInt(x.split(':')[1] || port)),
     pass            : o.pass || o.password || url.password || env.PGPASSWORD || '',
-    max             : o.max || query.get('max') || 10,
+    ...Object.entries(defaults).reduce((acc, [k, d]) =>
+      (acc[k] = k in o ? o[k] : k in query
+        ? (query[k] === 'disable' || query[k] === 'false' ? false : query[k])
+        : env['PG' + k.toUpperCase()] || d,
+      acc
+      ),
+      {}
+    ),
+    connection      : {
+      application_name: 'postgres.js',
+      ...o.connection,
+      ...Object.entries(query).reduce((acc, [k, v]) => (k in defaults || (acc[k] = v), acc), {})
+    },
     types           : o.types || {},
-    ssl             : o.ssl || parseSSL(query.get('sslmode') || query.get('ssl')) || false,
-    idle_timeout    : o.idle_timeout || query.get('idle_timeout') || env.PGIDLE_TIMEOUT || warn(o.timeout),
-    connect_timeout : o.connect_timeout || query.get('connect_timeout') || env.PGCONNECT_TIMEOUT || 30,
-    max_lifetime    : o.max_lifetime || url.max_lifetime || max_lifetime,
-    max_pipeline    : o.max_pipeline || url.max_pipeline || 100,
-    backoff         : o.backoff || url.backoff || backoff,
-    keep_alive      : o.keep_alive || url.keep_alive || 60,
-    prepare         : 'prepare' in o ? o.prepare : 'no_prepare' in o ? !o.no_prepare : true,
+    target_session_attrs: tsa(o, url, env),
     onnotice        : o.onnotice,
@@ -409,14 +433,8 @@ onnotify        : o.onnotify,
     onparameter     : o.onparameter,
+    socket          : o.socket,
     transform       : parseTransform(o.transform || { undefined: undefined }),
-    connection      : Object.assign({ application_name: 'postgres.js' }, o.connection),
-    target_session_attrs: tsa(o, url, env),
-    debug           : o.debug,
-    socket          : o.socket,
-    fetch_types     : 'fetch_types' in o ? o.fetch_types : true,
     parameters      : {},
     shared          : { retries: 0, typeArrayMap: {} },
-    publications    : o.publications || query.get('publications') || 'alltables'
-  },
-    mergeUserTypes(o.types)
-  )
+    ...mergeUserTypes(o.types)
+  }
 }
@@ -458,6 +476,2 @@
 
-function parseSSL(x) {
-  return x !== 'disable' && x !== 'false' && x
-}
-
 function parseUrl(url) {
@@ -468,5 +482,4 @@ if (typeof url !== 'string')
   let host = url
-  host = host.slice(host.indexOf('://') + 3)
-  host = host.split(/[?/]/)[0]
-  host = host.slice(host.indexOf('@') + 1)
+  host = host.slice(host.indexOf('://') + 3).split(/[?/]/)[0]
+  host = decodeURIComponent(host.slice(host.indexOf('@') + 1))
 
@@ -479,7 +492,2 @@ return {
 
-function warn(x) {
-  typeof x !== 'undefined' && console.log('The timeout option is deprecated, use idle_timeout instead') // eslint-disable-line
-  return x
-}
-
 function osUsername() {
@@ -486,0 +494,0 @@ try {

cjs/src/subscribe.js

@@ -27,3 +27,3 @@ const noop = () => { /* noop */ }
       state.pid = state.secret = undefined
-      !ended && connected(await init(sql, slot, options.publications))
+      connected(await init(sql, slot, options.publications))
       subscribers.forEach(event => event.forEach(({ onsubscribe }) => onsubscribe()))
@@ -216,3 +216,3 @@ },
       const row = {}
-      i = tuples(x, row, relation.columns, i += 3)
+      tuples(x, row, relation.columns, i + 3)
 
@@ -219,0 +219,0 @@ handle(row, {

cjs/src/types.js

@@ -101,8 +101,3 @@ const { Query } = require('./query.js')
   for (let i = 1; i < q.strings.length; i++) {
-    string += (
-      value instanceof Query ? fragment(value, parameters, types) :
-      value instanceof Identifier ? value.value :
-      value instanceof Builder ? value.build(string, parameters, types, options) :
-      handleValue(value, parameters, types, options)
-    ) + q.strings[i]
+    string += (stringifyValue(string, value, parameters, types, options)) + q.strings[i]
     value = q.args[i]
@@ -114,18 +109,22 @@ }
 
-function fragment(q, parameters, types) {
+function stringifyValue(string, value, parameters, types, o) {
+  return (
+    value instanceof Builder ? value.build(string, parameters, types, o) :
+    value instanceof Query ? fragment(value, parameters, types, o) :
+    value instanceof Identifier ? value.value :
+    value && value[0] instanceof Query ? value.reduce((acc, x) => acc + ' ' + fragment(x, parameters, types, o), '') :
+    handleValue(value, parameters, types, o)
+  )
+}
+
+function fragment(q, parameters, types, options) {
   q.fragment = true
-  return stringify(q, q.strings[0], q.args[0], parameters, types)
+  return stringify(q, q.strings[0], q.args[0], parameters, types, options)
 }
 
 function valuesBuilder(first, parameters, types, columns, options) {
-  let value
   return first.map(row =>
-    '(' + columns.map(column => {
-      value = row[column]
-      return (
-        value instanceof Query ? fragment(value, parameters, types) :
-        value instanceof Identifier ? value.value :
-        handleValue(value, parameters, types, options)
-      )
-    }).join(',') + ')'
+    '(' + columns.map(column =>
+      stringifyValue('values', row[column], parameters, types, options)
+    ).join(',') + ')'
   ).join(',')
@@ -150,3 +149,3 @@ }
     return (
-      value instanceof Query ? fragment(value, parameters, types) :
+      value instanceof Query ? fragment(value, parameters, types, options) :
       value instanceof Identifier ? value.value :
@@ -162,2 +161,3 @@ handleValue(value, parameters, types, options)
   select,
+  as: select,
   returning: select,
@@ -164,0 +164,0 @@

package.json

 {
   "name": "postgres",
-  "version": "3.1.0",
+  "version": "3.2.0",
   "description": "Fastest full featured PostgreSQL client for Node.js",
@@ -5,0 +5,0 @@ "type": "module",

README.md

@@ -75,2 +75,3 @@ <img align="left" width="440" height="180" alt="Fastest full PostgreSQL nodejs client" src="https://raw.githubusercontent.com/porsager/postgres/master/postgresjs.svg?sanitize=true">
 * [Numbers, bigint, numeric](#numbers-bigint-numeric)
+* [Result Array](#result-array)
 * [Connection details](#connection-details)
@@ -344,3 +345,3 @@ * [Custom Types](#custom-types)
   await http.request('https://example.com/wat', { row })
-}
+})
 ```
@@ -370,3 +371,3 @@
   ))
-}
+})
 ```
@@ -431,2 +432,49 @@
 
+### Copy to/from as Streams
+
+Postgres.js supports [`COPY ...`](https://www.postgresql.org/docs/14/sql-copy.html) queries, which are exposed as [Node.js streams](https://nodejs.org/api/stream.html).
+
+#### ```await sql`copy ... from stdin`.writable() -> Writable```
+
+```js
+import { pipeline } from 'node:stream/promises'
+
+// Stream of users with the default tab delimitated cells and new-line delimitated rows
+const userStream = Readable.from([
+  'Murray\t68\n',
+  'Walter\t80\n'
+])
+
+const query = await sql`copy users (name, age) from stdin`.writable()
+await pipeline(userStream, query);
+```
+
+#### ```await sql`copy ... to stdout`.readable() -> Readable```
+
+##### Using Stream Pipeline
+```js
+import { pipeline } from 'node:stream/promises'
+import { createWriteStream } from 'node:fs'
+
+const readableStream = await sql`copy users (name, age) to stdout`.readable()
+await pipeline(readableStream, createWriteStream('output.tsv'))
+// output.tsv content: `Murray\t68\nWalter\t80\n`
+```
+
+##### Using `for await...of`
+```js
+const readableStream = await sql`
+  copy (
+    select name, age 
+    from users 
+    where age = 68
+  ) to stdout
+`.readable()
+for await (const chunk of readableStream) {
+  // chunk.toString() === `Murray\t68\n`
+}
+```
+
+> **NOTE** This is a low-level API which does not provide any type safety. To make this work, you must match your [`copy query` parameters](https://www.postgresql.org/docs/14/sql-copy.html) correctly to your [Node.js stream read or write](https://nodejs.org/api/stream.html) code. Ensure [Node.js stream backpressure](https://nodejs.org/en/docs/guides/backpressuring-in-streams/) is handled correctly to avoid memory exhaustion.
+
 ### Canceling Queries in Progress
@@ -616,7 +664,7 @@
   'insert:events', 
-  function(row, { command, relation, key, old }) => {
+  (row, { command, relation, key, old }) => {
     // Callback function for each row change
     // tell about new event row over eg. websockets or do something else
   },
-  function onsubscribe() => {
+  () => {
     // Callback on initial connect and potential reconnects
@@ -669,3 +717,43 @@ }
 
+## Result Array
 
+The `Result` Array returned from queries is a custom array allowing for easy destructuring or passing on directly to JSON.stringify or general Array usage. It includes the following properties.
+
+### .count
+
+The `count` property is the number of affected rows returned by the database. This is usefull for insert, update and delete operations to know the number of rows since .length will be 0 in these cases if not using `RETURNING ...`.
+
+### .command
+
+The `command` run by the query - eg. one of `SELECT`, `UPDATE`, `INSERT`, `DELETE`
+
+### .columns
+
+The `columns` returned by the query useful to determine types, or map to the result values when using `.values()`
+
+```js
+{
+  name  : String,    // Column name,
+  type  : oid,       // PostgreSQL oid column type
+  parser: Function   // The function used by Postgres.js for parsing
+}
+```
+
+### .statement
+
+The `statement` contains information about the statement implicitly created by Postgres.js.
+
+```js
+{
+  name    : String,  // The auto generated statement name
+  string  : String,  // The actual query string executed
+  types   : [oid],   // An array of oid expected as input parameters
+  columns : [Column] // Array of columns - same as Result.columns
+}
+```
+
+### .state
+
+This is the state `{ pid, secret }` of the connection that executed the query.
+
 ## Connection details
@@ -688,3 +776,3 @@
   connect_timeout      : 30,            // Connect timeout in seconds
-  no_prepare           : false,         // No automatic creation of prepared statements
+  prepare              : true,          // Automatic creation of prepared statements
   types                : [],            // Array of custom types, see more below
@@ -870,3 +958,3 @@ onnotice             : fn,            // Defaults to console.log
   ...options,
-  socket: ({ hostname, port }) => new Promise((resolve, reject) => {
+  socket: ({ host: [host], port: [port] }) => new Promise((resolve, reject) => {
     const ssh = new ssh2.Client()
@@ -876,3 +964,3 @@ ssh
     .on('ready', () => 
-      ssh.forwardOut('127.0.0.1', 12345, hostname, port, 
+      ssh.forwardOut('127.0.0.1', 12345, host, port, 
         (err, socket) => err ? reject(err) : resolve(socket)
@@ -1012,2 +1100,3 @@ )
 - https://github.com/lukeed/ley
+- https://github.com/JAForbes/pgmg
 
@@ -1014,0 +1103,0 @@ ## Thank you

src/connection.js

@@ -271,3 +271,7 @@ import net from 'net'
         ? { rejectUnauthorized: false }
-        : ssl
+        : ssl === 'verify-full'
+          ? {}
+          : typeof ssl === 'object'
+            ? ssl
+            : {}
       )
@@ -354,3 +358,3 @@ })
       socket.on('data', data)
-      socket.setKeepAlive && socket.setKeepAlive(true, 1000 * keep_alive)
+      keep_alive != null && socket.setKeepAlive(true, 1000 * keep_alive)
       const s = StartupMessage()
@@ -534,3 +538,3 @@ write(s)
 
-    while (sent.length && (query = sent.shift()) && (query.active = true) && query.cancelled)
+    while (sent.length && (query = sent.shift()) && (query.active = true, query.cancelled))
       Connection(options).cancel(query.state, query.cancelled.resolve, query.cancelled.reject)
@@ -571,3 +575,3 @@
     if (query.options.simple)
-      return
+      return BindComplete()
 
@@ -574,0 +578,0 @@ if (query.cursorFn) {

src/index.js

@@ -210,3 +210,3 @@ import os from 'os'
     async function scope(c, fn, name) {
-      const sql = Sql(handler, true)
+      const sql = Sql(handler)
       sql.savepoint = savepoint
@@ -379,4 +379,4 @@ let uncaughtError
       , o = (typeof a === 'string' ? b : a) || {}
-      , { url, multihost } = parseUrl(a, env)
-      , query = url.searchParams
+      , { url, multihost } = parseUrl(a)
+      , query = [...url.searchParams].reduce((a, [b, c]) => (a[b] = c, a), {})
       , host = o.hostname || o.host || multihost || url.hostname || env.PGHOST || 'localhost'
@@ -386,3 +386,22 @@ , port = o.port || url.port || env.PGPORT || 5432
 
-  return Object.assign({
+  o.no_prepare && (o.prepare = false)
+  query.sslmode && (query.ssl = query.sslmode, delete query.sslmode)
+  'timeout' in o && (console.log('The timeout option is deprecated, use idle_timeout instead'), o.idle_timeout = o.timeout) // eslint-disable-line
+
+  const defaults = {
+    max             : 10,
+    ssl             : false,
+    idle_timeout    : null,
+    connect_timeout : 30,
+    max_lifetime    : max_lifetime,
+    max_pipeline    : 100,
+    backoff         : backoff,
+    keep_alive      : 60,
+    prepare         : true,
+    debug           : false,
+    fetch_types     : true,
+    publications    : 'alltables'
+  }
+
+  return {
     host            : Array.isArray(host) ? host : host.split(',').map(x => x.split(':')[0]),
@@ -394,12 +413,17 @@ port            : Array.isArray(port) ? port : host.split(',').map(x => parseInt(x.split(':')[1] || port)),
     pass            : o.pass || o.password || url.password || env.PGPASSWORD || '',
-    max             : o.max || query.get('max') || 10,
+    ...Object.entries(defaults).reduce((acc, [k, d]) =>
+      (acc[k] = k in o ? o[k] : k in query
+        ? (query[k] === 'disable' || query[k] === 'false' ? false : query[k])
+        : env['PG' + k.toUpperCase()] || d,
+      acc
+      ),
+      {}
+    ),
+    connection      : {
+      application_name: 'postgres.js',
+      ...o.connection,
+      ...Object.entries(query).reduce((acc, [k, v]) => (k in defaults || (acc[k] = v), acc), {})
+    },
     types           : o.types || {},
-    ssl             : o.ssl || parseSSL(query.get('sslmode') || query.get('ssl')) || false,
-    idle_timeout    : o.idle_timeout || query.get('idle_timeout') || env.PGIDLE_TIMEOUT || warn(o.timeout),
-    connect_timeout : o.connect_timeout || query.get('connect_timeout') || env.PGCONNECT_TIMEOUT || 30,
-    max_lifetime    : o.max_lifetime || url.max_lifetime || max_lifetime,
-    max_pipeline    : o.max_pipeline || url.max_pipeline || 100,
-    backoff         : o.backoff || url.backoff || backoff,
-    keep_alive      : o.keep_alive || url.keep_alive || 60,
-    prepare         : 'prepare' in o ? o.prepare : 'no_prepare' in o ? !o.no_prepare : true,
+    target_session_attrs: tsa(o, url, env),
     onnotice        : o.onnotice,
@@ -409,14 +433,8 @@ onnotify        : o.onnotify,
     onparameter     : o.onparameter,
+    socket          : o.socket,
     transform       : parseTransform(o.transform || { undefined: undefined }),
-    connection      : Object.assign({ application_name: 'postgres.js' }, o.connection),
-    target_session_attrs: tsa(o, url, env),
-    debug           : o.debug,
-    socket          : o.socket,
-    fetch_types     : 'fetch_types' in o ? o.fetch_types : true,
     parameters      : {},
     shared          : { retries: 0, typeArrayMap: {} },
-    publications    : o.publications || query.get('publications') || 'alltables'
-  },
-    mergeUserTypes(o.types)
-  )
+    ...mergeUserTypes(o.types)
+  }
 }
@@ -458,6 +476,2 @@
 
-function parseSSL(x) {
-  return x !== 'disable' && x !== 'false' && x
-}
-
 function parseUrl(url) {
@@ -468,5 +482,4 @@ if (typeof url !== 'string')
   let host = url
-  host = host.slice(host.indexOf('://') + 3)
-  host = host.split(/[?/]/)[0]
-  host = host.slice(host.indexOf('@') + 1)
+  host = host.slice(host.indexOf('://') + 3).split(/[?/]/)[0]
+  host = decodeURIComponent(host.slice(host.indexOf('@') + 1))
 
@@ -479,7 +492,2 @@ return {
 
-function warn(x) {
-  typeof x !== 'undefined' && console.log('The timeout option is deprecated, use idle_timeout instead') // eslint-disable-line
-  return x
-}
-
 function osUsername() {
@@ -486,0 +494,0 @@ try {

src/subscribe.js

@@ -27,3 +27,3 @@ const noop = () => { /* noop */ }
       state.pid = state.secret = undefined
-      !ended && connected(await init(sql, slot, options.publications))
+      connected(await init(sql, slot, options.publications))
       subscribers.forEach(event => event.forEach(({ onsubscribe }) => onsubscribe()))
@@ -216,3 +216,3 @@ },
       const row = {}
-      i = tuples(x, row, relation.columns, i += 3)
+      tuples(x, row, relation.columns, i + 3)
 
@@ -219,0 +219,0 @@ handle(row, {

src/types.js

@@ -101,8 +101,3 @@ import { Query } from './query.js'
   for (let i = 1; i < q.strings.length; i++) {
-    string += (
-      value instanceof Query ? fragment(value, parameters, types) :
-      value instanceof Identifier ? value.value :
-      value instanceof Builder ? value.build(string, parameters, types, options) :
-      handleValue(value, parameters, types, options)
-    ) + q.strings[i]
+    string += (stringifyValue(string, value, parameters, types, options)) + q.strings[i]
     value = q.args[i]
@@ -114,18 +109,22 @@ }
 
-function fragment(q, parameters, types) {
+function stringifyValue(string, value, parameters, types, o) {
+  return (
+    value instanceof Builder ? value.build(string, parameters, types, o) :
+    value instanceof Query ? fragment(value, parameters, types, o) :
+    value instanceof Identifier ? value.value :
+    value && value[0] instanceof Query ? value.reduce((acc, x) => acc + ' ' + fragment(x, parameters, types, o), '') :
+    handleValue(value, parameters, types, o)
+  )
+}
+
+function fragment(q, parameters, types, options) {
   q.fragment = true
-  return stringify(q, q.strings[0], q.args[0], parameters, types)
+  return stringify(q, q.strings[0], q.args[0], parameters, types, options)
 }
 
 function valuesBuilder(first, parameters, types, columns, options) {
-  let value
   return first.map(row =>
-    '(' + columns.map(column => {
-      value = row[column]
-      return (
-        value instanceof Query ? fragment(value, parameters, types) :
-        value instanceof Identifier ? value.value :
-        handleValue(value, parameters, types, options)
-      )
-    }).join(',') + ')'
+    '(' + columns.map(column =>
+      stringifyValue('values', row[column], parameters, types, options)
+    ).join(',') + ')'
   ).join(',')
@@ -150,3 +149,3 @@ }
     return (
-      value instanceof Query ? fragment(value, parameters, types) :
+      value instanceof Query ? fragment(value, parameters, types, options) :
       value instanceof Identifier ? value.value :
@@ -162,2 +161,3 @@ handleValue(value, parameters, types, options)
   select,
+  as: select,
   returning: select,
@@ -164,0 +164,0 @@

types/index.d.ts

@@ -8,3 +8,6 @@ import { Readable, Writable } from 'node:stream'
  */
-declare function postgres<T extends JSToPostgresTypeMap>(options?: postgres.Options<T>): postgres.Sql<JSToPostgresTypeMap extends T ? {} : T>
+declare function postgres<T extends PostgresTypeList>(options?: postgres.Options<T>): postgres.Sql<PostgresTypeList extends T ? {} : { [type in keyof T]: T[type] extends {
+  serialize: (value: infer R) => any,
+  parse: (raw: any) => infer R
+} ? R : never }>
 /**
@@ -16,3 +19,6 @@ * Establish a connection to a PostgreSQL server.
  */
-declare function postgres<T extends JSToPostgresTypeMap>(url: string, options?: postgres.Options<T>): postgres.Sql<JSToPostgresTypeMap extends T ? {} : T>
+declare function postgres<T extends PostgresTypeList>(url: string, options?: postgres.Options<T>): postgres.Sql<PostgresTypeList extends T ? {} : { [type in keyof T]: T[type] extends {
+  serialize: (value: infer R) => any,
+  parse: (raw: any) => infer R
+} ? R : never }>
 
@@ -22,3 +28,3 @@ /**
  */
-interface BaseOptions<T extends JSToPostgresTypeMap> {
+interface BaseOptions<T extends PostgresTypeList> {
   /** Postgres ip address[s] or domain name[s] */
@@ -41,6 +47,6 @@ host: string | string[];
   /**
-   * true, prefer, require or tls.connect options
+   * How to deal with ssl (can be a tls.connect option object)
    * @default false
   */
-  ssl: 'require' | 'allow' | 'prefer' | boolean | object;
+  ssl: 'require' | 'allow' | 'prefer' | 'verify-full' | boolean | object;
   /**
@@ -61,4 +67,4 @@ * Max number of connections
   connect_timeout: number;
-  /** Array of custom types; see more below */
-  types: PostgresTypeList<T>;
+  /** Array of custom types; see more in the README */
+  types: T;
   /**
@@ -80,2 +86,5 @@ * Enables prepare mode.
   transform: {
+    /** Transforms outcoming undefined values */
+    undefined?: any
+
     /** Transforms incoming and outgoing column names */
@@ -121,3 +130,3 @@ column?: ((column: string) => string) | {
   onclose: (connId: number) => void;
-  backoff: boolean | ((attemptNum:number) => number);
+  backoff: boolean | ((attemptNum: number) => number);
   max_lifetime: number | null;
@@ -127,7 +136,5 @@ keep_alive: number | null;
 
-type PostgresTypeList<T> = {
-  [name in keyof T]: T[name] extends (...args: any) => postgres.SerializableParameter
-  ? postgres.PostgresType<T[name]>
-  : postgres.PostgresType<(...args: any) => postgres.SerializableParameter>;
-};
+interface PostgresTypeList {
+  [name: string]: postgres.PostgresType;
+}
 
@@ -166,7 +173,7 @@ interface JSToPostgresTypeMap {
 
-type SerializableObject<T, K extends any[]> =
+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>
+  (Record<Keys & (keyof T) & (K['length'] extends 0 ? string : K[number]), postgres.SerializableParameter<TT> | postgres.JSONValue> & Record<string, any>)
 
-type First<T, K extends any[]> =
+type First<T, K extends readonly any[], TT> =
   // Tagged template string call
@@ -177,9 +184,9 @@ T extends TemplateStringsArray ? TemplateStringsArray :
   // Dynamic values helper (depth 2)
-  T extends readonly any[][] ? postgres.EscapableArray[] :
+  T extends readonly any[][] ? readonly postgres.EscapableArray[] :
   // Insert/update helper (depth 2)
-  T extends (object & infer R)[] ? SerializableObject<R, K>[] :
-  // Dynamic values helper (depth 1)
-  T extends readonly any[] ? postgres.EscapableArray :
+  T extends readonly (object & infer R)[] ? (R extends postgres.SerializableParameter<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)
-  T extends object ? SerializableObject<T, K> :
+  T extends object ? SerializableObject<T, K, TT> :
   // Unexpected type
@@ -190,10 +197,10 @@ never
   T extends TemplateStringsArray ? never : // force fallback to the tagged template function overload
-  T extends string ? string[] :
-  T extends readonly any[][] ? [] :
-  T extends (object & infer R)[] ? (Keys & keyof R)[] :
-  T extends readonly any[] ? [] :
-  T extends object ? (Keys & keyof T)[] :
+  T extends string ? readonly string[] :
+  T extends readonly any[][] ? readonly [] :
+  T extends readonly (object & infer R)[] ? readonly (Keys & keyof R)[] :
+  T extends readonly any[] ? readonly [] :
+  T extends object ? readonly (Keys & keyof T)[] :
   any
 
-type Return<T, K extends any[]> =
+type Return<T, K extends readonly any[]> =
   [T] extends [TemplateStringsArray] ?
@@ -274,9 +281,9 @@ [unknown] extends [T] ? postgres.Helper<T, K> : // ensure no `PendingQuery` with `any` types
 
-  const BigInt: PostgresType<(number: bigint) => string>;
+  const BigInt: PostgresType<bigint>;
 
-  interface PostgresType<T extends (...args: any[]) => unknown> {
+  interface PostgresType<T = any> {
     to: number;
     from: number[];
-    serialize: T;
-    parse: (raw: string) => unknown;
+    serialize: (value: T) => unknown;
+    parse: (raw: any) => T;
   }
@@ -294,3 +301,3 @@
 
-  interface Options<T extends JSToPostgresTypeMap> extends Partial<BaseOptions<T>> {
+  interface Options<T extends PostgresTypeList> extends Partial<BaseOptions<T>> {
     /** @inheritdoc */
@@ -327,3 +334,3 @@ host?: string;
 
-  interface ParsedOptions<T extends JSToPostgresTypeMap> extends BaseOptions<T> {
+  interface ParsedOptions<T extends JSToPostgresTypeMap> extends BaseOptions<{ [name in keyof T]: PostgresType<T[name]> }> {
     /** @inheritdoc */
@@ -337,7 +344,10 @@ host: string[];
     transform: Transform;
-    serializers: Record<number, (...args: any) => SerializableParameter>;
-    parsers: Record<number, (value: string) => unknown>;
+    serializers: Record<number, (value: any) => unknown>;
+    parsers: Record<number, (value: any) => unknown>;
   }
 
   interface Transform {
+    /** Transforms outcoming undefined values */
+    undefined: any
+
     /** Transforms incoming column names */
@@ -379,3 +389,3 @@ column: {
 
-  interface ArrayParameter<T extends SerializableParameter[] = SerializableParameter[]> extends Parameter<T | T[]> {
+  interface ArrayParameter<T extends readonly any[] = readonly any[]> extends Parameter<T | T[]> {
     array: true;
@@ -477,3 +487,4 @@ }
 
-  type SerializableParameter = never
+  type SerializableParameter<T = never> = never
+    | T
     | Serializable
@@ -483,4 +494,18 @@ | Helper<any>
     | ArrayParameter
-    | readonly SerializableParameter[];
+    | readonly SerializableParameter<T>[];
 
+  type JSONValue = // using a dedicated type to detect symbols, bigints, and other non serializable types
+    | null
+    | string
+    | number
+    | Date // serialized as `string`
+    | readonly JSONValue[]
+    | { toJSON(): any } // `toJSON` called by `JSON.stringify`; not typing the return type, typings is strict enough anyway
+    | {
+      readonly [prop: string | number]:
+      | undefined
+      | JSONValue
+      | ((...args: any) => any) // serialized as `undefined`
+    };
+
   interface Row {
@@ -538,4 +563,4 @@ [column: string]: any;
   interface PendingQueryModifiers<TRow extends readonly any[]> {
-    readable(): Readable;
-    writable(): Writable;
+    readable(): Promise<Readable>;
+    writable(): Promise<Writable>;
 
@@ -575,3 +600,3 @@ execute(): this;
 
-  interface Helper<T, U extends any[] = T[]> extends NotAPromise {
+  interface Helper<T, U extends readonly any[] = T[]> extends NotAPromise {
     first: T;
@@ -588,3 +613,3 @@ rest: U;
      */
-    <T, K extends Rest<T>>(first: T & First<T, K>, ...rest: K): Return<T, K>;
+    <T, K extends Rest<T>>(first: T & First<T, K, TTypes[keyof TTypes]>, ...rest: K): Return<T, K>;
 
@@ -597,3 +622,3 @@ /**
      */
-    <T extends readonly any[] = Row[]>(template: TemplateStringsArray, ...parameters: (SerializableParameter | PendingQuery<any>)[]): PendingQuery<AsRowList<T>>;
+    <T extends readonly (object | undefined)[] = Row[]>(template: TemplateStringsArray, ...parameters: readonly (SerializableParameter<TTypes[keyof TTypes]> | PendingQuery<any>)[]): PendingQuery<AsRowList<T>>;
 
@@ -606,15 +631,14 @@ CLOSE: {};
     parameters: ConnectionParameters;
-    types: {
-      [name in keyof TTypes]: TTypes[name] extends (...args: any) => any
-      ? (...args: Parameters<TTypes[name]>) => postgres.Parameter<ReturnType<TTypes[name]>>
-      : (...args: any) => postgres.Parameter<any>;
+    types: this['typed'];
+    typed: (<T>(value: T, oid: number) => Parameter<T>) & {
+      [name in keyof TTypes]: (value: TTypes[name]) => postgres.Parameter<TTypes[name]>
     };
 
-    unsafe<T extends any[] = (Row & Iterable<Row>)[]>(query: string, parameters?: SerializableParameter[], queryOptions?: UnsafeQueryOptions): PendingQuery<AsRowList<T>>;
+    unsafe<T extends any[] = (Row & Iterable<Row>)[]>(query: string, parameters?: SerializableParameter<TTypes[keyof TTypes]>[], queryOptions?: UnsafeQueryOptions): PendingQuery<AsRowList<T>>;
     end(options?: { timeout?: number }): Promise<void>;
 
-    listen(channel: string, cb: (value: string) => void): ListenRequest;
+    listen(channel: string, onnotify: (value: string) => void, onlisten?: () => void): ListenRequest;
     notify(channel: string, payload: string): PendingRequest;
 
-    subscribe(event: string, cb: (row: Row | null, info: ReplicationEvent) => void): Promise<SubscriptionHandle>;
+    subscribe(event: string, cb: (row: Row | null, info: ReplicationEvent) => void, onsubscribe?: () => void): Promise<SubscriptionHandle>;
 
@@ -626,6 +650,6 @@ largeObject(oid?: number, /** @default 0x00020000 | 0x00040000 */ mode?: number): Promise<LargeObject>;
 
-    array<T extends SerializableParameter[] = SerializableParameter[]>(value: T, type?: number): ArrayParameter<T>;
+    array<T extends SerializableParameter<TTypes[keyof TTypes]>[] = SerializableParameter<TTypes[keyof TTypes]>[]>(value: T, type?: number): ArrayParameter<T>;
     file<T extends readonly any[] = Row[]>(path: string | Buffer | URL | number, options?: { cache?: boolean }): PendingQuery<AsRowList<T>>;
-    file<T extends readonly any[] = Row[]>(path: string | Buffer | URL | number, args: SerializableParameter[], options?: { cache?: boolean }): PendingQuery<AsRowList<T>>;
-    json(value: any): Parameter;
+    file<T extends readonly any[] = Row[]>(path: string | Buffer | URL | number, args: SerializableParameter<TTypes[keyof TTypes]>[], options?: { cache?: boolean }): PendingQuery<AsRowList<T>>;
+    json(value: JSONValue): Parameter;
   }
@@ -632,0 +656,0 @@

No alert changes

Improved metrics

Total package byte prevSize

194883

increased by2.32%

Lines of code

4853

increased by0.89%

Number of lines in readme file

1100

increased by8.8%

No dependency changes