Postgres.js

Postgres.js supports logical replication to subscribe to insert, update, and delete operations.

Prerequisites:

1. Create a publication in your database (e.g., CREATE PUBLICATION alltables FOR ALL TABLES).
2. Set wal_level = logical in postgresql.conf.
3. Connect using a replication or superuser.

Usage: Initialize the client with the publications option, then call sql.subscribe.

Subscription Pattern: operation:schema.table=primary_key

• operation: *, insert, update, or delete (defaults to *)
• schema: defaults to public
• table: defaults to *
• primary_key: filter by specific row ID.

You can build complex, dynamic queries by nesting sql fragments. This allows you to conditionally append or omit parts of a query (like WHERE clauses or ORDER BY segments) without risking SQL injection.

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

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

Postgres.js supports the TCP socket API in Cloudflare Workers. To use it, you must enable the nodejs_compat compatibility flag in your wrangler.toml. You can connect directly or via Hyperdrive by passing the Hyperdrive connectionString.

# wrangler.toml
compatibility_flags = ["nodejs_compat"]

// Example Worker usage
import postgres from 'postgres'

export default async fetch(req: Request, env: Env, ctx: ExecutionContext) {
    const sql = postgres(env.HYPERDRIVE.connectionString)
    const results = await sql`SELECT * FROM users LIMIT 10`
    return Response.json(results)
}

Connections are created lazily when a query is first made. By default, the pool allows up to max: 10 concurrent connections. You can increase this limit in the configuration.

Note: There are no guarantees about query execution order unless you use a transaction with sql.begin() or set max: 1.

Install the postgres package using npm to get started with the PostgreSQL client.

$ npm install postgres

Create a sql database instance using the postgres function. You can provide a connection URL or an options object. If no arguments are provided, it will use standard psql environment variables. Options in the object will override any values present in the URL.

import postgres from 'postgres'

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

export default sql

You can transform column names between snake_case (database) and camelCase (JavaScript) using the transform option in the postgres constructor.

• postgres.toCamel: Transforms incoming query results from snake_case to camelCase.
• postgres.fromCamel: Transforms outgoing query parameters (inserts, updates, selects) from camelCase to snake_case.

Important: Postgres.js does not rewrite static parts of tagged template strings. To transform column names in your queries, you must use the sql() helper, for example: ${ sql('columnName') }.

By default, passing undefined to a query throws UNDEFINED_VALUE. You can configure the transform object to convert undefined to null (or another value) automatically.

You can combine this with built-in transformations by spreading them into the transform object.

// Convert undefined to null and use camelCase transformations
const sql = postgres({
  transform: {
    ...postgres.camel,
    undefined: null
  }
})

To prevent connections from staying open indefinitely (useful in Serverless environments or when using database services that auto-close connections), use idle_timeout and max_lifetime. Both options are specified in seconds.

• idle_timeout: Seconds to wait before closing an idle connection.
• max_lifetime: Maximum time (in seconds) a connection can exist.

const sql = postgres({
  idle_timeout: 20,
  max_lifetime: 60 * 30
})

When connecting to databases that require SSL (like Heroku Postgres), you can pass an ssl object in the options. For environments like Heroku where verifiable certificates might not be supported, you may need to set rejectUnauthorized: false.

const sql = postgres({
  ssl: { rejectUnauthorized: false }
})