porsager/postgres: Postgres.js - The Fastest full featured PostgreSQL client for Node.js, Deno, Bun and CloudFlare

1. Enforcing safe query generation
2. Giving the sql` function powerful utility and query building features.

const date = null

await sql
  update users set updated_at = ${ date || sqlnow() }

// Which results in:
update users set updated_at = now()

Table names

const table = 'users'
    , column = 'id'
await sql
  select ${ sql(column) } from ${ sql(table) }

// Which results in:
select "id" from "users"

Quick primer on interpolation

Here's a quick oversight over all the ways to do interpolation in a query template string:

Advanced query methods

Cursors

Inside sql.begin or on a reserved connection every cursor has a portal of its own, so several can be open at once and other queries can run between their batches.

for await (const [row] of cursor) { // row = { x: 1 } await http.request('https://example.com/wat', { row }) }

A single row will be returned by default, but you can also request batches by setting the number of rows desired in each batch as the first argument to .cursor:

If an error is thrown inside the callback function no more rows will be requested and the outer promise will reject with the thrown error.

You can close the cursor early either by calling break in the for await...of loop, or by returning the token sql.CLOSE from the callback function.

### Instant iteration

####

If you want to handle rows returned by a query one by one, you can use .forEach which returns a promise that resolves once there are no more rows.

// No more rows

### Query Descriptions
####

Rather than executing a given query, .describe will return information utilized in the query process. This information can include the query identifier, column types, etc.

This is useful for debugging and analyzing your Postgres queries. Furthermore, .describe will give you access to the final generated query string that would be executed.

Rows as Array of Values
####

Using .values will return rows as an array of values for each column, instead of objects.
This can be useful to receive identically named columns, or for specific performance/transformation reasons. The column definitions are still included on the result array, plus access to parsers for each column.
Rows as Raw Array of Buffers
####

Using .raw will return rows as an array with Buffer values for each column, instead of objects.
This can be useful for specific performance/transformation reasons. The column definitions are still included on the result array, plus access to parsers for each column.
Queries in Files
await sql.file(path, [args], [options]) -> Result[]

Using a file for a query is also supported with optional parameters to use if the file includes $1, $2, etc

### Multiple statements in one query
####

The postgres wire protocol supports "simple" and "extended" queries. "simple" queries supports multiple statements, but does not support any dynamic parameters. "extended" queries support parameters but only one statement. To use "simple" queries you can use

await sqlselect 1; select 2;.simple()

Copy to/from as Streams

// Stream of users with the default tab delimitated cells and new-line delimitated rows const userStream = Readable.from([ 'Murray\t68\n', 'Walter\t80\n' ])

##### Using Stream Pipeline

> 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 correctly to your Node.js stream read or write code. Ensure Node.js stream backpressure is handled correctly to avoid memory exhaustion.

Canceling Queries in Progress
Postgres.js supports, canceling queries in progress. It works by opening a new connection with a protocol level startup message to cancel the current query running on a specific connection. That means there is no guarantee that the query will be canceled, and due to the possible race conditions it might even result in canceling another query. This is fine for long running queries, but in the case of high load and fast queries it might be better to simply ignore results instead of canceling.

.signal() cancels the query when an AbortSignal aborts: one still waiting for a connection is rejected without being sent, and a running one gets a cancel request. A cursor canceled while its callback runs, or while an async iterator over it is between batches, is stopped by the client instead: it fetches no more rows, closes its portal and rejects with 57014, with no cancel request sent.

A query waits for a connection when every connection is reserved, in a transaction or has max_pipeline queries in flight. queue_timeout (in seconds) rejects it with QUEUE_TIMEOUT if it waits longer, without ever sending it, and the same goes for sql.begin and sql.reserve. Once a query is sent, PostgreSQL's own statement_timeout is the limit.

Execute
####

The lazy Promise implementation in Postgres.js is what allows it to distinguish Nested Fragments from the main outer query. This also means that queries are always executed at the earliest in the following tick. If you have a specific need to execute the query in the same tick, you can call .execute()

Inspecting a query
query.text is the text sent to the server, with $1, $2 for the parameters, and query.parameters their values. Both work before the query is sent, without a connection:

### Unsafe raw string queries

<details>
<summary>Advanced unsafe use cases</summary>
await sql.unsafe(query, [args], [options]) -> Result[]

If you know what you're doing, you can use unsafe to pass any string you'd like to postgres. Please note that this can lead to SQL injection if you're not careful.

Values go in args and are referred to as $1, $2 and so on, so they are never part of the string itself:

options takes:

| Option | Default | |
| --- | --- | --- |
| prepare | false | Prepare the query as a named statement. Off by default because a string built at run time may be different every time, and each would stay prepared on the server. |
| simple | true without args | Send it with the simple protocol, which allows several statements in one string but no args. |

You can also nest sql.unsafe within a safe sql expression.  This is useful if only part of your fraction has unsafe elements.

return [user, account] })

Do note that you can often achieve the same result using WITH queries (Common Table Expressions) instead of using transactions.

It's also possible to pipeline the requests in a transaction if needed by returning an array with queries from the callback function like this:

Queries that are not awaited one by one are pipelined anywhere in the transaction, so Promise.all pipelines a group of them between other queries. They run in the order they were made, so a later query sees what an earlier one in the group did:

return [user, account] }) .then(([user, account]) => { // great success - COMMIT succeeded }) .catch(() => { // not so good - ROLLBACK was called })

#### Nested sql.begin

Inside a transaction, sql.begin(fn) runs fn in a savepoint, so code that opens a transaction works the same whether it is given the pool or a transaction. Nothing it does is committed until the outer transaction commits. A mode other than the transaction's own, such as 'read only' inside a 'read write' transaction, throws NESTED_TRANSACTION_MODE, since a savepoint can't change it, and so does sql.prepare() inside a nested begin. A savepoint with a name is still sql.savepoint(name, fn).

await transfer(sql, 1, 2, 10) // its own transaction await sql.begin(sql => transfer(sql, 1, 2, 10)) // a savepoint in this one

#### ROLLBACK await sql.rollback()

Rolls back the transaction without throwing, and sql.begin resolves with what the callback returns (#500). Called in a savepoint or a nested sql.begin, it rolls back only that savepoint, and the outer transaction goes on. The rollback is sent at once, after any queries already made. Queries made after it in the same scope are not sent and reject with TRANSACTION_ROLLED_BACK, and so does the sql.begin they were made in.

#### PREPARE TRANSACTION await sql.prepare([name]) -> fn()

Indicates that the transactions should be prepared using the [PREPARE TRANSACTION [NAME]](https://www.postgresql.org/docs/current/sql-prepare-transaction.html) statement
instead of being committed.

await sql.prepare('tx1') })

## Data Transformation

Postgres.js allows for transformation of the data passed to or returned from a query by using the transform option.

Built in transformation functions are:
For camelCase - postgres.camel, postgres.toCamel, postgres.fromCamel

For PascalCase - postgres.pascal, postgres.toPascal, postgres.fromPascal

For Kebab-Case - postgres.kebab, postgres.toKebab, postgres.fromKebab

These built in transformations will only convert to/from snake_case. For example, using { transform: postgres.toCamel } will convert the column names to camelCase only if the column names are in snake_case to begin with. { transform: postgres.fromCamel } will convert camelCase only to snake_case.
By default, using postgres.camel, postgres.pascal and postgres.kebab will perform a two-way transformation - both the data passed to the query and the data returned by the query will be transformed:

console.log(data) // [ { aTest: 1, bTest: '1' } ]

To only perform half of the transformation (eg. only the transformation to or from camel case), use the other transformation functions:

console.log(data) // [ { aTest: 1 } ]

console.log(data) // [ { a_test: 1 } ]

> Note that Postgres.js does not rewrite the static parts of the tagged template strings. So to transform column names in your queries, the sql() helper must be used - eg. ${ sql('columnName') } as in the examples above.

Transform undefined Values

By default, Postgres.js will throw the error UNDEFINED_VALUE: Undefined values are not allowed when undefined values are passed

console.log(data) // [ { a_test: null } ]

To combine with the built in transform functions, spread the transform in the transform object:

console.log(data) // [ { aTest: null } ]

### Custom Transform Functions

To specify your own transformation functions, you can use the column, value and row options inside of transform, each an object possibly including to and from keys:

to: The function to transform the outgoing query column name to, i.e SELECT ${ sql('aName') } to SELECT a_name when using postgres.toCamel.

from: The function to transform the incoming query result column name to, see example below.

> Both parameters are optional, if not provided, the default transformation function will be used.

const sql = postgres({ transform: { column: { to: transformColumnToDatabase, from: transformColumnFromDatabase, }, value: { / ... / }, row: { / ... / } } })

## Listen & notify

When you call .listen, a dedicated connection will be created to ensure that you receive notifications instantly. This connection will be used for any further calls to .listen. The connection will automatically reconnect according to a backoff reconnection pattern to not overload the database server.

Listen await sql.listen(channel, onnotify, [onlisten]) -> { state }

.listen takes the channel name, a function to handle each notify, and an optional function to run every time listen is registered and ready (happens on initial connect and reconnects). It returns a promise which resolves once the LISTEN query to Postgres completes, or if there is already a listener active.

The optional onlisten method is great to use for a very simply queue mechanism:

function run(job) { // And here you do the work you please }

### Notify await sql.notify(channel, payload) -> Result[]
Notify can be done as usual in SQL, or by using the sql.notify method.

## Realtime subscribe

Postgres.js implements the logical replication protocol of PostgreSQL to support subscription to real-time updates of insert, update, delete and truncate operations.

> NOTE To make this work you must create the proper publications in your database, enable logical replication by setting wal_level = logical in postgresql.conf and connect using either a replication or superuser.

Quick start
Create a publication (eg. in migration)

#### Subscribe to updates

const { unsubscribe } = await sql.subscribe( 'insert:events', (row, { command, relation, key, old, lsn, time, xid }) => { // Callback function for each row change // tell about new event row over eg. websockets or do something else }, () => { // Callback on initial connect and potential reconnects }, error => { // A handler failed, or the stream had an error } )

Besides the row, each change comes with the transaction it was part of: its lsn (like '0/1A2B3C4'), its commit time and its xid. An update also has old when the table's replica identity sends it, and a truncate has no row but cascade and restartIdentity.

Handlers that return promises
A handler may be async. Changes are then handled one after the other, in the order they were committed, and the stream waits while a handler is busy instead of piling changes up in memory:

### Persistent slots

By default a subscription uses a temporary replication slot, which the server drops when the connection closes, so changes made while the subscriber is away are not seen.
Give the slot a name to keep it. It is created if it does not exist, and streaming starts where it was last confirmed, so nothing committed while the subscriber was down is missed:

await sql.subscribe('*:orders', async(row, info) => { await handle(row, info) })

With a slot, a transaction is confirmed to the server only after every handler for it has finished. If a handler throws or rejects, the transaction is not confirmed: onerror is called, the stream restarts and the transaction is delivered again. Delivery is at least once, so a handler should cope with seeing a transaction twice, for instance by remembering the lsn it handled last.

While nothing is in flight, the slot is also confirmed as far as the server has got, so a quiet slot does not keep the server holding WAL.
A persistent slot keeps WAL on the server until it is confirmed, so drop it when it is no longer used:

### Subscribe pattern

You can subscribe to specific operations, tables, or even rows with primary keys.
operation      : schema . table = primary_key

operation is one of

Examples

sql.subscribe('',                () => / everything */ )
sql.subscribe('insert',           () => / all inserts / )
sql.subscribe(':users',          () => / all operations on the public.users table */ )
sql.subscribe('delete:users',     () => / all deletes on the public.users table / )
sql.subscribe('update:users=1',   () => / all updates on the users row with a primary key = 1 / )
sql.subscribe('truncate:users',   () => / the public.users table was truncated / )

Numbers, bigint, numeric

const sql = postgres({
  types: {
    bigint: postgres.BigInt
  }
})

Result Array

.count

.command

.columns

{
  name  : String,    // Column name,
  type  : oid,       // PostgreSQL oid column type
  parser: Function   // The function used by Postgres.js for parsing
}

.statement

{
  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

Connection details

All Postgres options

const sql = postgres('postgres://username:password@host:port/database', {
  host                 : '',            // Postgres ip address[es] or domain name[s]
  port                 : 5432,          // Postgres server port[s]
  path                 : '',            // unix socket path (usually '/tmp')
  database             : '',            // Name of database to connect to
  username             : '',            // Username of database user, or a function returning it
  password             : '',            // Password of database user, or a function returning it
  ssl                  : false,         // true, prefer, require, verify-full, tls.connect options
  sslnegotiation       : null,          // 'direct' for TLS from the first byte (PostgreSQL 17+)
  channel_binding      : 'prefer',      // SCRAM-SHA-256-PLUS over TLS: 'disable', 'prefer' or 'require'
  max                  : 10,            // Max number of connections
  min                  : 0,             // Connections kept open once the pool is used
  max_pipeline         : 100,           // Max number of queries pipelined on one connection
  scheduler            : true,          // Pipeline where a query should be done soonest (more info below)
  max_lifetime         : null,          // Max lifetime in seconds (more info below)
  idle_timeout         : 0,             // Idle connection timeout in seconds
  connect_timeout      : 30,            // Connect timeout in seconds
  queue_timeout        : 0,             // Seconds a query may wait for a connection, 0 for no limit
  keep_alive           : 60,            // TCP keep-alive in seconds, 0 or null for none
  backoff              : fn,            // Seconds to wait before reconnecting, given the attempt
  prepare              : true,          // Automatic creation of prepared statements
  infer                : false,         // true types parameters from JS values, as v3 did
  types                : [],            // Array of custom types, see more below
  onnotice             : fn,            // Default console.log, set false to silence NOTICE
  onnotify             : fn,            // (channel, payload, pid) for every notification
  onparameter          : fn,            // (key, value) when server param change
  onclose              : fn,            // (connection id) when a connection closes
  debug                : false,         // true, or a function called with (connection, query, params, types)
  socket               : fn,            // (options, host, port) returning a custom socket to use
  transform            : {
    undefined          : undefined,     // Transforms undefined values (eg. to null)
    column             : fn,            // Transforms incoming column names
    value              : fn,            // Transforms incoming row values
    row                : fn             // Transforms entire rows
  },
  connection           : {
    application_name   : 'postgres.js', // Default application_name
    ...                                 // Other connection parameters, see https://www.postgresql.org/docs/current/runtime-config-client.html
  },
  target_session_attrs : null,          // Use 'read-write' with multiple hosts to
                                        // ensure only connecting to primary
  fetch_types          : true,          // Fetches array types and other types it does not know
  publications         : 'alltables',   // Publications for sql.subscribe
  slot                 : null,          // A permanent replication slot for sql.subscribe
  onread               : true,          // Read into one shared buffer per connection (Node.js, Bun, Deno)
  unref_sockets        : false          // Idle connections don't keep the process alive
})

Dynamic passwords

When clients need to use alternative authentication schemes such as access tokens or connections to databases with rotating passwords, provide either a synchronous or asynchronous function that will resolve the dynamic password value at connection time.

const sql = postgres(url, {
  // Other connection config
  ...
  // Password function for the database user
  password : async () => await signer.getAuthToken(),
})

const sql = postgres({
  database: 'app',
  username: async () => (await secrets.get()).username,
  password: async () => (await secrets.get()).password
})

Password file

SSL

const sql =
  process.env.NODE_ENV === 'production'
    ? // "Unless you're using a Private or Shield Heroku Postgres database, Heroku Postgres does not currently support verifiable certificates"
      // https://help.heroku.com/3DELT3RK/why-can-t-my-third-party-utility-connect-to-heroku-postgres-with-ssl
      postgres({ ssl: { rejectUnauthorized: false } })
    : postgres()

Direct TLS

PostgreSQL 17 and later can start TLS on the first byte instead of being asked first, which saves a round trip when connecting:

const sql = postgres('postgres://host/db?sslmode=require&sslnegotiation=direct')
// or
const sql = postgres({ ssl: 'require', sslnegotiation: 'direct' })

On Node.js this is also the fastest encrypted connection, as it reads into the same shared buffer as a plain one. It needs a server that supports it, and is not available in Cloudflare Workers, whose sockets cannot send the ALPN it requires.

Channel binding

const sql = postgres('postgres://host/db?sslmode=verify-full&channel_binding=require')

Multi-host connections - High Availability (HA)

Connections will be attempted in order of the specified hosts/ports. On a successful connection, all retries will be reset. This ensures that hosts can come up and down seamlessly.

The Connection Pool

No connection will be made until a query is made.

For example:

const sql = postgres() // no connections are opened
await sql... // one connection is now opened
await sql... // previous opened connection is reused

// two connections are opened now
await Promise.all([
  sql...,
  sql...
])