porsager/postgres

🚀 Fastest full-featured PostgreSQL client
🏷 ES6 Tagged Template Strings at the core
🏄‍♀️ Simple surface API
🖊️ Dynamic query support
🐦 Follow on X

Getting started

Installation

$ npm install postgres

Postgres.js needs Node.js 20 or later, Bun, or Cloudflare Workers. Deno runs it through its Node.js compatibility. It is an ES module with no dependencies.

Upgrading from v3? The API is the same apart from a short list of changes. Read Upgrading from v3 first: parameter types are now decided by the server, which changes how a Date is written to timestamp and date columns outside UTC.

Usage

Create your sql database instance

// db.js
import postgres from 'postgres'

const sql = postgres({ /* options */ }) // will use psql environment variables

export default sql

Simply import for use elsewhere

// users.js
import sql from './db.js'

async function getUsersOver(age) {
  const users = await sql`
    select
      name,
      age
    from users
    where age > ${ age }
  `
  // users = Result [{ name: "Walter", age: 80 }, { name: 'Murray', age: 68 }, ...]
  return users
}


async function insertUser({ name, age }) {
  const users = await sql`
    insert into users
      (name, age)
    values
      (${ name }, ${ age })
    returning name, age
  `
  // users = Result [{ name: "Murray", age: 68 }]
  return users
}

Dynamic imports and CommonJS

const { default: postgres } = await import('postgres')

From CommonJS, on Node.js 20.19 or 22.12 and later, require loads it as well:

const postgres = require('postgres').default

Upgrading from v3
Connection
Queries
Building queries
Advanced query methods
Transactions
Data Transformation
Listen & notify
Realtime subscribe
Numbers, bigint, numeric
Result Array
Connection details
Custom Types
Teardown / Cleanup
Tracing
Error handling
TypeScript support
Reserving connections
Changelog

Connection

`postgres([url], [options])`

You can use either a postgres:// url connection string or the options to define your database connection properties. Options in the object will override any present in the url. Options will fall back to the same environment variables as psql.

const sql = postgres('postgres://username:password@host:port/database', {
  host                 : '',            // Postgres ip address[s] or domain name[s]
  port                 : 5432,          // Postgres server port[s]
  database             : '',            // Name of database to connect to
  username             : '',            // Username of database user
  password             : '',            // Password of database user
  ...and more
})

More options can be found in the Connection details section.

Queries

await sql`...` -> Result[]

Postgres.js utilizes Tagged template functions to process query parameters before interpolation. Using tagged template literals benefits developers by:

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

Any generic value will be serialized according to an inferred type, and replaced by a PostgreSQL protocol placeholder $1, $2, .... The parameters are then sent separately to the database which handles escaping & casting.

All queries will return a Result array, with objects mapping column names to each row.

const xs = await sql`
  insert into users (
    name, age
  ) values (
    'Murray', 68
  )

  returning *
`

// xs = [{ user_id: 1, name: 'Murray', age: 68 }]

Please note that queries are first executed when awaited – or instantly by using .execute().

Query parameters

Parameters are automatically extracted and handled by the database so that SQL injection isn't possible. No special handling is necessary, simply use tagged template literals as usual.

const name = 'Mur'
    , age = 60

const users = await sql`
  select
    name,
    age
  from users
  where
    name like ${ name + '%' }
    and age > ${ age }
`
// users = [{ name: 'Murray', age: 68 }]

Be careful with quotation marks here. Because Postgres infers column types, you do not need to wrap your interpolated parameters in quotes like '${name}'. This will cause an error because the tagged template replaces ${name} with $1 in the query string, leaving Postgres to do the interpolation. If you wrap that in a string, Postgres will see '$1' and interpret it as a string as opposed to a parameter.

Parameter types

The server decides the type of every parameter from the query, as with libpq and psql, and each value is written for the type the server asked for. A Date for a timestamptz or timestamp column is stored as the instant it is, a timestamp is read back as UTC, and a string such as 'true' for a boolean is read by the server.

Where the query doesn't decide the type, such as a value in the select list or an argument to an overloaded function like date_trunc, the server reads the value as text or asks for a cast. A cast always decides:

await sql`select date_trunc('day', ${ date }::timestamptz)`
await sql`select ${ flag }::bool as flag`

infer: true types values from JavaScript instead, as v3 did: a Date as timestamptz, a Buffer or Uint8Array as bytea, a boolean as boolean, a bigint as int8, and an array by its first element. See Upgrading from v3 for what that changes.

Dynamic column selection

const columns = ['name', 'age']

await sql`
  select
    ${ sql(columns) }
  from users
`

// Which results in:
select "name", "age" from users

Dynamic inserts

const user = {
  name: 'Murray',
  age: 68
}

await sql`
  insert into users ${
    sql(user, 'name', 'age')
  }
`

// Which results in:
insert into users ("name", "age") values ($1, $2)

// The columns can also be given with an array
const columns = ['name', 'age']

await sql`
  insert into users ${
    sql(user, columns)
  }
`

You can omit column names and simply execute sql(user) to get all the fields from the object as columns. Be careful not to allow users to supply columns that you do not want to be inserted.

Multiple inserts in one query

If you need to insert multiple rows at the same time it's also much faster to do it with a single insert. Simply pass an array of objects to sql().

const users = [{
  name: 'Murray',
  age: 68,
  garbage: 'ignore'
},
{
  name: 'Walter',
  age: 80
}]

await sql`insert into users ${ sql(users, 'name', 'age') }`

// Is translated to:
insert into users ("name", "age") values ($1, $2), ($3, $4)

// Here you can also omit column names which will use object keys as columns
await sql`insert into users ${ sql(users) }`

// Which results in:
insert into users ("name", "age") values ($1, $2), ($3, $4)

Dynamic columns in updates

This is also useful for update queries

const user = {
  id: 1,
  name: 'Murray',
  age: 68
}

await sql`
  update users set ${
    sql(user, 'name', 'age')
  }
  where user_id = ${ user.id }
`

// Which results in:
update users set "name" = $1, "age" = $2 where user_id = $3

// The columns can also be given with an array
const columns = ['name', 'age']

await sql`
  update users set ${
    sql(user, columns)
  }
  where user_id = ${ user.id }
`

Multiple updates in one query

To create multiple updates in a single query, it is necessary to use arrays instead of objects to ensure that the order of the items correspond with the column names.

const users = [
  [1, 'John', 34],
  [2, 'Jane', 27],
]

await sql`
  update users set name = update_data.name, age = (update_data.age)::int
  from (values ${sql(users)}) as update_data (id, name, age)
  where users.id = (update_data.id)::int
  returning users.id, users.name, users.age
`

Dynamic values and `where in`

Value lists can also be created dynamically, making where in queries simple too.

const users = await sql`
  select
    *
  from users
  where age in ${ sql([68, 75, 23]) }
`

const [{ a, b, c }] = await sql`
  select
    *
  from (values ${ sql(['a', 'b', 'c']) }) as x(a, b, c)
`

Named arguments in function calls

An object in the parentheses of a function call becomes its named arguments.

await sql`
  select make_interval(${ sql({ days: 1, hours: 2 }) })
`

// Which results in:
select make_interval("days" => $1,"hours" => $2)

Building queries

Postgres.js features a simple dynamic query builder by conditionally appending/omitting query fragments. It works by nesting sql`` fragments within other sql`` calls or fragments. This allows you to build dynamic queries safely without risking sql injections through usual string concatenation.

Partial queries

const olderThan = x => sql`and age > ${ x }`

const filterAge = true

await sql`
  select
   *
  from users
  where name is not null ${
    filterAge
      ? olderThan(50)
      : sql``
  }
`
// Which results in:
select * from users where name is not null
// Or
select * from users where name is not null and age > 50

Dynamic filters

await sql`
  select
    *
  from users ${
    id
      ? sql`where user_id = ${ id }`
      : sql``
  }
`

// Which results in:
select * from users
// Or
select * from users where user_id = $1

Identifiers and values combine with operators, such as for a field of a json column:

const column = email ? 'notifications' : 'settings'

await sql`
  select * from users where ${ sql(column) }->>'email' = ${ 'true' }
`

// Which results in:
select * from users where "notifications"->>'email' = $1

Joining fragments

A fragment used as a separator joins a list of fragments and values. Values become parameters, and false, null and undefined are left out, so conditions can be written inline. A space is added on each side of the separator, and around the result, where there isn't one already.

const where = sql`and`.join([
  sql`age > ${ age }`,
  name && sql`name = ${ name }`
])

await sql`
  select * from users ${ where.length ? sql`where ${ where }` : sql`` }
`

// Which results in:
select * from users where age > $1 and name = $2

Dynamic ordering

const id = 1
const order = {
  username: 'asc',
  created_at: 'desc'
}
await sql`
  select
    *
  from ticket
  where account = ${ id }
  order by ${
    Object.entries(order).flatMap(([column, order], i) =>
      [i ? sql`,` : sql``, sql`${ sql(column) } ${ order === 'desc' ? sql`desc` : sql`asc` }`]
    )
  }
`

SQL functions

Using keywords or calling functions dynamically is also possible by using sql`` fragments.

const date = null

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

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

Table names

Dynamic identifiers like table names and column names is also supported like so:

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:

Interpolation syntax	Usage	Example
${ sql`` }	for keywords or sql fragments	await sql`SELECT * FROM users ${sql`order by age desc` }`
`${ sql(string) }`	for identifiers	await sql`SELECT * FROM ${sql('table_name')`
`${ sql([] or {}, ...) }`	for helpers	await sql`INSERT INTO users ${sql({ name: 'Peter'})}`
`${ 'somevalue' }`	for values	await sql`SELECT * FROM users WHERE age = ${42}`

Advanced query methods

Cursors

await sql``.cursor([rows = 1], [fn])

Use cursors if you need to throttle the amount of rows being returned from a query. You can use a cursor either as an async iterable or with a callback function. For a callback function new results won't be requested until the promise / async callback function has resolved.

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.

callback function

await sql`
  select
    *
  from generate_series(1,4) as x
`.cursor(async([row]) => {
  // row = { x: 1 }
  await http.request('https://example.com/wat', { row })
})

for await...of

// for await...of
const cursor = sql`select * from generate_series(1,4) as x`.cursor()

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:

await sql`
  select
    *
  from generate_series(1,1000) as x
`.cursor(10, async rows => {
  // rows = [{ x: 1 }, { x: 2 }, ... ]
  await Promise.all(rows.map(row =>
    http.request('https://example.com/wat', { row })
  ))
})

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.

await sql`
  select * from generate_series(1,1000) as x
`.cursor(row => {
  return Math.random() > 0.9 && sql.CLOSE // or sql.END
})

Instant iteration

await sql``.forEach(fn)

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.

await sql`
  select created_at, name from events
`.forEach(row => {
  // row = { created_at: '2019-11-22T14:22:00Z', name: 'connected' }
})

// No more rows

Query Descriptions

await sql``.describe() -> Result[]

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

sql``.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

sql``.raw()

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

const result = await sql.file('query.sql', ['Murray', 68])

Multiple statements in one query

await sql``.simple()

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 sql``.simple(). That will create it as a simple query.

await sql`select 1; select 2;`.simple()

Copy to/from as Streams

Postgres.js supports COPY ... queries, which are exposed as Node.js streams.

await sql`copy ... from stdin`.writable() -> Writable

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);
query.count // 2, the number of rows copied, once the stream has finished

await sql`copy ... to stdout`.readable() -> Readable

Using Stream Pipeline

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`

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 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.

const query = sql`select pg_sleep 100`.execute()
setTimeout(() => query.cancel(), 100)
const result = await query

.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.

await sql`select * from report()`.signal(AbortSignal.timeout(5000))

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

await sql``.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:

const query = sql`select * from users where id = ${ 1 } and name = ${ 'Murray' }`
query.text       // 'select * from users where id = $1 and name = $2'
query.parameters // [1, 'Murray']

Unsafe raw string queries

Advanced unsafe use cases

`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.

sql.unsafe('select ' + danger + ' from users where id = ' + dragons)

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

sql.unsafe('select * from users where id = $1 and age > $2', [id, age])

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.

const triggerName = 'friend_created'
const triggerFnName = 'on_friend_created'
const eventType = 'insert'
const schema_name = 'app'
const table_name = 'friends'

await sql`
  create or replace trigger ${sql(triggerName)}
  after ${sql.unsafe(eventType)} on ${sql.unsafe(`${schema_name}.${table_name}`)}
  for each row
  execute function ${sql(triggerFnName)}()
`

await sql`
  create role friend_service with login password ${sql.unsafe(`'${password}'`)}
`

Transactions

BEGIN / COMMIT `await sql.begin([options = ''], fn) -> fn()`

Use sql.begin to start a new transaction. Postgres.js will reserve a connection for the transaction and supply a scoped sql instance for all transaction uses in the callback function. sql.begin will resolve with the returned value from the callback function.

BEGIN is automatically sent with the optional options, and if anything fails ROLLBACK will be called so the connection can be released and execution can continue.

A query that fails in a transaction aborts it, whether the server rejected it or Postgres.js could not build it (an undefined value, say), as PostgreSQL does: catching the error does not let the rest commit, and a later COMMIT answers ROLLBACK. The transaction commits only once its cursors have finished, also those the callback did not await.

const [user, account] = await sql.begin(async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
    returning *
  `

  const [account] = await sql`
    insert into accounts (
      user_id
    ) values (
      ${ user.user_id }
    )
    returning *
  `

  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:

const result = await sql.begin(sql => [
  sql`update ...`,
  sql`update ...`,
  sql`insert ...`
])

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:

await sql.begin(async sql => {
  const [movie] = await sql`select * from movie where name = 'Star Wars'`

  await Promise.all([
    sql`insert into movie_actor ...${ movie.id }`,
    sql`insert into movie_release ...${ movie.id }`
  ])

  await sql`select ...`
})

SAVEPOINT `await sql.savepoint([name], fn) -> fn()`

sql.begin('read write', async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
  `

  const [account] = (await sql.savepoint(sql =>
    sql`
      insert into accounts (
        user_id
      ) values (
        ${ user.user_id }
      )
    `
  ).catch(err => {
    // Account could not be created. ROLLBACK SAVEPOINT is called because we caught the rejection.
  })) || []

  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).

async function transfer(sql, from, to, amount) {
  return sql.begin(async sql => {
    await sql`update accounts set balance = balance - ${ amount } where id = ${ from }`
    await sql`update accounts set balance = balance + ${ amount } where id = ${ to }`
  })
}

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.

const result = await sql.begin(async sql => {
  const [order] = await sql`insert into orders ${ sql(input) } returning *`
  if (!await inStock(sql, order))
    return (await sql.rollback(), { saved: false })
  return { saved: true, order }
})

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

Indicates that the transactions should be prepared using the PREPARE TRANSACTION [NAME] statement instead of being committed.

sql.begin('read write', async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
  `

  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:

// Transform the column names to and from camel case
const sql = postgres({ transform: postgres.camel })

await sql`CREATE TABLE IF NOT EXISTS camel_case (a_test INTEGER, b_test TEXT)`
await sql`INSERT INTO camel_case ${ sql([{ aTest: 1, bTest: 1 }]) }`
const data = await sql`SELECT ${ sql('aTest', 'bTest') } FROM camel_case`

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:

// Transform the column names only to camel case
// (for the results that are returned from the query)
postgres({ transform: postgres.toCamel })

await sql`CREATE TABLE IF NOT EXISTS camel_case (a_test INTEGER)`
await sql`INSERT INTO camel_case ${ sql([{ a_test: 1 }]) }`
const data = await sql`SELECT a_test FROM camel_case`

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

// Transform the column names only from camel case
// (for interpolated inserts, updates, and selects)
const sql = postgres({ transform: postgres.fromCamel })

await sql`CREATE TABLE IF NOT EXISTS camel_case (a_test INTEGER)`
await sql`INSERT INTO camel_case ${ sql([{ aTest: 1 }]) }`
const data = await sql`SELECT ${ sql('aTest') } FROM camel_case`

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

// Transform the column names to and from camel case
const sql = postgres({
  transform: {
    undefined: null
  }
})

await sql`CREATE TABLE IF NOT EXISTS transform_undefined (a_test INTEGER)`
await sql`INSERT INTO transform_undefined ${ sql([{ a_test: undefined }]) }`
const data = await sql`SELECT a_test FROM transform_undefined`

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

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

// Transform the column names to and from camel case
const sql = postgres({
  transform: {
    ...postgres.camel,
    undefined: null
  }
})

await sql`CREATE TABLE IF NOT EXISTS transform_undefined (a_test INTEGER)`
await sql`INSERT INTO transform_undefined ${ sql([{ aTest: undefined }]) }`
const data = await sql`SELECT ${ sql('aTest') } FROM transform_undefined`

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.

// Implement your own functions, look at postgres.toCamel, etc
// as a reference:
// toCamel and the others in types.js
function transformColumnToDatabase() { /* ... */ }
function transformColumnFromDatabase() { /* ... */ }

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.

await sql.listen('news', payload => {
  const json = JSON.parse(payload)
  console.log(json.this) // logs 'is'
})

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

await sql.listen(
  'jobs',
  (x) => run(JSON.pars

🚀 Fastest full-featured PostgreSQL client
🏷 ES6 Tagged Template Strings at the core
🏄‍♀️ Simple surface API
🖊️ Dynamic query support
🐦 Follow on X