pg

@effect/sql-pg

An Effect SQL client for PostgreSQL with a native wire-protocol driver.

Installation

npm install effect @effect/sql-pg

Links

• Website: documentation, guides, and news.
• Reference: API documentation for this package.
• Discord: ask questions, share what you're building, and talk to the core team.
• Community: meetups and events, or bring Effect to your own.
• Issues: bug reports and feature requests.

Let's talk

Whether your team is considering Effect, rolling it out, or already running it in production, we'd love to hear from you: what you're building, what works, and what you need from Effect next.

• Talk to the maintainers. Introduce your team on Discord or email contact@effectful.co. We're happy to connect privately on Slack or Discord for feedback and help with adoption.
• Production support. We're exploring how to better support teams running Effect in production. If your organization has specific support needs, let's discuss them.
• Adoption help. Our adoption partners offer implementation, consulting, team extension, training, and commercial support.

Session defaults

Use startupParameters to establish PostgreSQL session defaults when each physical connection opens, including replacement connections in a pool:

import { PgClient } from "@effect/sql-pg"
import { Redacted } from "effect"

const PostgresLive = PgClient.layer({
  url: Redacted.make("postgres://user:password@localhost/app"),
  startupParameters: {
    statement_timeout: "5s",
    search_path: "app, public"
  },
  startupOptions: "-c lock_timeout=1000"
})

These settings are sent in the startup packet. PostgreSQL's RESET ALL restores the startup defaults after session-level changes. Both PgConnection.Config and PgClientConfig accept these fields, and pools inherit them.

Parameter names are normalized to lowercase. Empty names and NUL bytes in names or values are rejected before connecting. The names user, database, replication, and options are reserved; use username, database, and startupOptions on the config for identity and opaque options. Replication mode is not supported through startupParameters. The driver accepts only UTF8 or UTF-8 (case-insensitive) for client_encoding and sends the canonical value UTF8. PostgreSQL validates other settings and reports invalid names or values at connect time.

The startupOptions field is an opaque PostgreSQL options string. It can also come from the URL query, for example ?options=-c%20statement_timeout%3D5000. Explicit config startupOptions overrides URL options, including when the explicit value is empty. The URL query key and startup packet field are both named options. Named parameters and opaque options can be sent together, but callers must not set the same GUC in both. The driver does not parse -c flags, detect conflicts, or read PGOPTIONS.

The startup application_name is selected in this order:

1. Explicit applicationName
2. startupParameters.application_name
3. URL application_name
4. "@effect/sql-pg"

The driver does not extract application_name from the opaque options string.

User-defined types

Columns whose type OID has no codec are decoded as UTF-8 text, so user-defined enums read as plain strings:

import { PgClient } from "@effect/sql-pg"
import { Effect } from "effect"

// CREATE TYPE capability AS ENUM ('use_key', 'manage')
const program = Effect.gen(function*() {
  const sql = yield* PgClient.PgClient
  const rows = yield* sql`SELECT 'use_key'::capability AS capability`
  // rows[0].capability === "use_key"
})

Strings bind to enum columns without any setup; the server infers the type.

The fallback applies to every OID without a codec, not only enums. A value that cannot be text fails with PgTypes.CodecError:

• Invalid UTF-8 fails, as it does for text.
• A NUL byte fails too. PostgreSQL text output never contains one, while binary layouts almost always do, so it marks the value as binary data. Columns of a binary user-defined type such as a composite, a PostGIS geometry, or a pgvector vector land here, and so do arrays of user-defined types, including enum arrays, whose array header holds zero bytes.

The error names the column, its type OID, and its table OID and attribute number when the column comes from a table. It fails only the query that read the row: the rest of the result is discarded and the connection stays usable. Register a codec for these types, or cast the column to text in the query.

A binary layout with no zero bytes at all, such as ltree, still decodes as garbled text without any error.

Register a codec for a scalar user-defined type with PgTypes.register(oid, codec); registered codecs take precedence over the fallback. PgTypes.register cannot attach an array type. For arrays, create a registry, register the element codec with its arrayOid, and pass the registry to the client as types. The registry builds the array codec from the element codec:

import { PgClient, PgTypes } from "@effect/sql-pg"
import { Redacted, Result } from "effect"

// SELECT oid, typarray FROM pg_type WHERE typname = 'capability'
declare const capabilityOid: number
declare const capabilityArrayOid: number

const decoder = new TextDecoder("utf-8", { fatal: true })

const utf8: PgTypes.Codec<string> = {
  encode: (value) => Result.succeed(new TextEncoder().encode(value)),
  decode: (bytes) => {
    try {
      return Result.succeed(decoder.decode(bytes))
    } catch {
      return Result.fail(new PgTypes.CodecError({ message: "Invalid UTF-8 in capability" }))
    }
  }
}

const types = PgTypes.makeRegistry()
types.register(capabilityOid, utf8, { arrayOid: capabilityArrayOid })

const PostgresLive = PgClient.layer({
  url: Redacted.make("postgres://user:password@localhost/app"),
  types
})

OIDs of user-defined types are assigned per database, so look them up in pg_type at startup rather than hard-coding them.