postgres.md

Postgres

The same builder writes Postgres SQL. Queries, params, tenant scoping and row decoding work as they do for ClickHouse. @maple-dev/effect-orm/postgres is the one import for it: the builder, column types whose codecs read what Postgres drivers send, functions spelled the Postgres way, table with its keys, indexes, unique constraints and foreign keys, enumType, and a compile that defaults to the Postgres dialect. Nothing from /clickhouse is needed.

import { PGlite } from "@electric-sql/pglite"
import { Effect } from "effect"
import * as PG from "@maple-dev/effect-orm/postgres"
import * as S from "@maple-dev/effect-orm/schema"

const Requests = PG.table("requests", {
	columns: {
		Id: PG.column(PG.int8, { identity: "always" }),
		OrgId: PG.text,
		Route: PG.text,
		DurationMs: PG.int8,
		At: PG.timestamptz,
	},
	primaryKey: ["Id"],
	tenantColumn: "OrgId",
})

const query = PG.from(Requests)
	.select(($) => ({
		route: $.Route,
		count: PG.count(),
		slow: PG.countIf($.DurationMs.gte(500)),
		p50: PG.percentileCont(0.5, $.DurationMs),
	}))
	.where(($) => [$.OrgId.eq(PG.param.string("orgId")), $.At.gte(PG.param.dateTime("since"))])
	.groupBy("route")
	.orderBy(["count", "desc"])

export const compiled = PG.compileUnsafe(query, { orgId: "org_1", since: new Date("2026-01-01T00:00:00Z") })
// compiled.sql:        ... FILTER (WHERE "requests"."DurationMs" >= $1) ...
//                      ... WHERE "requests"."OrgId" = $2 AND "requests"."At" >= $3 ...
// compiled.parameters: [500, "org_1", "2026-01-01T00:00:00.000Z"]

const db = new PGlite()
// The CREATE TABLE comes from the definition itself; migrations.md shows the managed way.
for (const statement of S.renderPgSchema(Effect.runSync(S.pgEntitiesOf([Requests])))) await db.exec(statement)
await db.exec(`
	INSERT INTO requests ("OrgId", "Route", "DurationMs", "At") VALUES
		('org_1', '/checkout', 120, '2026-01-01T10:00:00Z'),
		('org_1', '/checkout', 900, '2026-01-01T10:01:00Z'),
		('org_1', '/search', 40, '2026-01-01T10:02:00Z');
`)
const result = await db.query<Record<string, unknown>>(compiled.sql, [...compiled.parameters])
export const rows = await Effect.runPromise(compiled.decodeRows(result.rows))
// [{ route: "/checkout", count: 2, slow: 1, p50: 510 }, { route: "/search", count: 1, slow: 0, p50: 40 }]
await db.close()

PGlite stands in for any driver that takes (sql, values): node-postgres, postgres.js, or @effect/sql-pg's unsafe. The builder never runs the query. Id is an identity column, so an insert may leave it out: PG.InsertRowOf<typeof Requests> makes it optional. See Tables and column types for column options and external tables, and Schema and migrations for indexes, unique constraints, foreign keys, and generate; Enums for enum types.

What the dialect changes

	ClickHouse (clickhouseDialect)	Postgres (postgresDialect)
Identifiers	Bare: events.OrgId	Quoted: "events"."OrgId"
String literals	Backslash escapes: 'it\'s'	Doubled quotes: 'it''s'
Params	Written in as literals; parameters empty	Bound as $1, $2, … in parameters
Values compared with a column, LIKE patterns	Written in as literals	Bound too, one param per distinct value
param.bool	1 / 0	true / false
param.dateTime	'2026-01-01 00:00:00' (UTC, zoneless)	'2026-01-01T00:00:00.000Z'
GROUP BY keys	Select aliases	Select-list positions (GROUP BY 1)
Wrapped union	SELECT * FROM (…)	SELECT * FROM (…) AS "__union"
.format()	FORMAT JSON	Refused at compile time

Postgres reads a bare name in GROUP BY as an input column before a select alias, so select({ Service: lower($.Service) }).groupBy("Service") would group by the raw column. Writing the position instead keeps ClickHouse's meaning.

A value compared with a column ($.email.eq(email), in_, between, a LIKE pattern) is bound like a param, so the statement text carries no values: it stays out of logs and traces, and one query shape is one statement. Two exceptions stay literals: an onConflict* targetWhere, which Postgres matches against a partial index's predicate as written, and DDL.

Every string that reaches the SQL as a literal is escaped for Postgres. A value that spells the param marker __PARAM_ is written as an E'…' string with the marker hex-escaped, and a literal that still contained it would fail the compile with InvalidLiteral.

Column types

Constructor	Postgres type	Decodes to	Accepts on the wire
text, uuid	text, uuid	string	string
bool	boolean	boolean	boolean
int2, int4, int8, float4, float8, numeric	same	number	number, numeric string, bigint
timestamptz	timestamptz	DateTime.Utc	Date, or text such as 2026-01-01 00:00:00+00
timestamptzMillis	timestamptz	epoch milliseconds (number)	the same
jsonb(schema?)	jsonb	the schema's type (unknown by default)	a parsed value
enumType(name, labels, { schema? })	the enum type	one of its labels	string
array(type)	type[]	ReadonlyArray	array
nullable(type)	the same type	T | null	the same, or null
custom(sql, schema, literalSchema?)	anything	the schema's type	whatever the schema reads
brand(type, schema)	the base type	the schema's type	what the base type reads

int8 and numeric decode to number, so values beyond 2^53 or a double's precision lose digits. Where exact digits matter, declare PG.custom("int8", Schema.Union([Schema.BigInt, Schema.BigIntFromString])): drivers send int8 as a bigint (PGlite, postgres.js with types.bigint) or as a string (node-postgres), and this reads both as a bigint. A timestamptz compared against a Date, a DateTime.Utc or a string is written as an ISO-8601 instant, which no session time zone can reinterpret; a zoneless string is read as UTC.

Comparisons with a literal-union column take only its members: with status typed "open" | "closed", $.status.eq("opne") is a type error. A param of the primitive (param.string) still compares, for a value known only at run time.

Enums

PG.enumType(name, labels) declares a Postgres enum type. The value is a column type, so rows decode to the union of its labels and inserts and comparisons take only those, and it is a schema object, so generate writes CREATE TYPE ... AS ENUM before any table that uses it and alters the type when its labels change. Use it bare, in PG.column with a default, or inside PG.nullable and PG.array:

import { Effect } from "effect"
import * as PG from "@maple-dev/effect-orm/postgres"
import * as S from "@maple-dev/effect-orm/schema"

export const ChannelType = PG.enumType("channel_type", ["public", "private", "direct"])

export const Channels = PG.table("channels", {
	columns: {
		id: PG.text,
		org_id: PG.text,
		slug: PG.column(PG.text, { unique: true }),
		type: PG.column(ChannelType, { default: "public" }),
		allowed: PG.nullable(PG.array(ChannelType)),
	},
	primaryKey: ["id"],
	uniqueConstraints: [PG.unique("channels_org_slug_unique", ["org_id", "slug"])],
})

export type Channel = PG.SelectRowOf<typeof Channels>
// { ...; readonly type: "public" | "private" | "direct"; readonly allowed: ReadonlyArray<...> | null }

export const shared = PG.compileUnsafe(
	PG.from(Channels)
		.select("id", "type")
		.where(($) => [$.type.in_("public", "private")]),
	{},
)

export const upsert = PG.compileUnsafe(
	PG.insertInto(Channels)
		.values({ id: "c1", org_id: "o", slug: "general", type: "private" })
		.onConflictDoUpdate({ target: ["slug"], set: { type: "private" } }),
	{},
)

export const ddl = Effect.map(S.pgEntitiesOf([ChannelType, Channels]), (entities) => S.renderPgSchema(entities))
// CREATE TYPE "channel_type" AS ENUM ('public', 'private', 'direct')   (skipped if it exists)
// CREATE TABLE IF NOT EXISTS "channels" (... "type" channel_type NOT NULL DEFAULT 'public', ...)
// ALTER TABLE "channels" ADD CONSTRAINT "channels_org_slug_unique" UNIQUE ("org_id", "slug")
// ALTER TABLE "channels" ADD CONSTRAINT "channels_slug_unique" UNIQUE ("slug")

ChannelType.values lists the labels for application code. Exporting the enum from a schema module is optional: the tables that use it bring it along. Pass { schema: "auth" } for a type in another schema; leave it out for the schema migrations run in (usually public), which is how columns of the type are spelled there.

Drivers parse arrays of built-in types but not of an enum, whose array type they do not know: node-postgres and PGlite send {public,private} as text. An array(enumType) column reads either form, and writes its value as array text, which Postgres casts to the column's type.

PG.brand(PG.text, Schema.Literals([...])) and PG.custom("public.channel_type", ...) still work for an enum generate does not manage; see migrations for moving one to enumType.

JSON paths

A jsonb(schema) column knows the shape of its documents, so a path into one is typed and decoded by the same schema. jsonPath(column, ...keys) reads the value at a path with ->; a string key steps into an object, a number into an array. Only paths the schema has type-check, and the result decodes with the field's own schema (a NumberFromString field reads as a number). A key that is absent reads as null.

import { PGlite } from "@electric-sql/pglite"
import { Effect, Schema } from "effect"
import * as PG from "@maple-dev/effect-orm/postgres"

const Settings = Schema.Struct({
	notifications: Schema.Struct({ sms: Schema.Boolean, email: Schema.Boolean }),
	tags: Schema.Array(Schema.String),
})

const Users = PG.table("users", {
	columns: { id: PG.int4, settings: PG.jsonb(Settings) },
	primaryKey: ["id"],
})

// Read and filter: `sms` is an Expr<boolean | null>.
export const read = PG.compileUnsafe(
	PG.from(Users)
		.select(($) => ({ id: $.id, sms: PG.jsonPath($.settings, "notifications", "sms") }))
		.where(($) => [PG.jsonPath($.settings, "notifications", "email").eq(true), PG.jsonContains($.settings, { tags: ["beta"] })]),
	{},
)
// read.sql:        SELECT "users"."id" AS "id", ("users"."settings" -> 'notifications' -> 'sms') AS "sms" FROM "users"
//                  WHERE ("users"."settings" -> 'notifications' -> 'email') = $1 AND ("users"."settings" @> $2::jsonb)
// read.parameters: ["true", '{"tags":["beta"]}']

// Write one path, without reading the row first.
export const write = PG.compileUnsafe(
	PG.update(Users)
		.set(($) => ({ settings: PG.jsonSet($.settings, ["notifications", "sms"], false) }))
		.where(($) => [$.id.eq(1)]),
	{},
)
// write.sql: UPDATE "users" SET "settings" = jsonb_set("settings", ARRAY['notifications', 'sms'], $1::jsonb) WHERE "id" = $2

const db = new PGlite()
await db.exec(`
	CREATE TABLE users (id int4 PRIMARY KEY, settings jsonb NOT NULL);
	INSERT INTO users VALUES (1, '{"notifications": {"sms": true, "email": true}, "tags": ["beta"]}');
`)
await db.query(write.sql, [...write.parameters])
const result = await db.query<Record<string, unknown>>(read.sql, [...read.parameters])
export const rows = await Effect.runPromise(read.decodeRows(result.rows))
// [{ id: 1, sms: false }]
await db.close()

Function	SQL	Result
jsonPath(x, ...keys)	(x -> 'k1' -> 'k2')	the field's type, or null
jsonPathText(x, ...keys)	(x -> 'k1' ->> 'k2')	string | null: a JSON string without its quotes
jsonContains(x, part)	(x @> $n::jsonb)	a condition
jsonSet(x, [keys], value)	jsonb_set(x, ARRAY['k1', 'k2'], $n::jsonb)	the document's type

• A value compared with a jsonPath, and the value of a jsonSet, is encoded by the field's schema and bound as JSON text: .eq("Kyiv") matches the JSON string "Kyiv". For text comparisons and like, use jsonPathText.
• jsonContains takes part of the document, at any depth: objects match on the keys given, arrays on the elements given, in any order. It accepts a jsonPath too. Its value is written as given, so a field whose schema transforms (a date stored as text) is written in its stored form. A GIN index on the column serves @>.
• jsonSet creates the last key if it is absent. A missing parent leaves the document as it is, and a NULL document stays NULL. Nest calls to set more than one path.
• Keys are written into the SQL as literals, not bound, so an expression index on (settings -> 'notifications') matches.
• The schema walk follows structs, records, arrays and tuples, through unions, NullOr, optional keys and Schema.suspend. A path through a field whose stored form is not its type (Schema.encodeKeys, a class) is refused when the query compiles. A jsonb() column with no schema takes any keys and reads unknown.
• These are Postgres functions. Compiled for ClickHouse, SQLite or D1 they die with a QueryBuilderDefect ("is a Postgres function") instead of sending SQL the server would reject. SQLite and D1 have no typed JSON paths yet: a SQLite.json(schema) column is read and written whole.

Functions

Function	SQL	Notes
count(), countDistinct(x)	count(*), count(DISTINCT x)
countIf(c), sumIf(x, c)	count(*) FILTER (WHERE c), sum(x) FILTER (WHERE c)	ClickHouse's -If combinators
sum, avg, min, max	same	null over no rows, where ClickHouse returns 0 for sum
percentileCont(f, x)	percentile_cont(f) WITHIN GROUP (ORDER BY x)	Interpolated
arrayAgg(x)	array_agg(x)
dateTrunc(unit, ts)	date_trunc(unit, ts, 'UTC')	UTC buckets; Postgres 12+
dateBin(seconds, ts)	date_bin(…, ts, epoch)	Epoch-aligned buckets; Postgres 14+
now()	now()
lower, upper, length	same
coalesce(x, fallback)	coalesce(x, fallback)	No longer nullable
jsonText(x, key)	(x ->> key)	null when absent
jsonPath, jsonPathText, jsonContains, jsonSet	->, ->>, @>, jsonb_set	Typed by the column schema; see JSON paths
greatest(a, ...), least(a, ...)	same	NULL arguments are skipped
caseWhen([[c, v], ...], otherwise)	CASE WHEN c THEN v ... ELSE otherwise END
asBoolean(c)	(c)	A condition as a value, to select or set
typedValue(type, value)	a bound param	Encoded as type writes it: typedValue(T.columns.at, ms)

undecoded($.column) reads a column without its codec: a jsonb document as unknown, a branded id as its string. Use it where the reader decodes stored values itself (to tolerate an older document shape, or to name the bad row in its own error); every other read stays typed.

The shared operators (eq, in_, like, ilike, and, or, not, arithmetic, lit) work unchanged. /postgres exports only functions Postgres has, plus nullIf, which renders the same on both. The ClickHouse catalog on /clickhouse (quantile, toStartOfInterval, its count(), …) writes ClickHouse SQL, so a query that uses one is a type error where it is compiled or run for Postgres, and the Postgres functions above are one on ClickHouse (see Checked in the type). Past the types, the compile still fails with a QueryBuilderDefect naming the function. A custom Dialect opts in with functions: "clickhouse" or "postgres"; without it, nothing is checked.

Known differences

• Integer division truncates in Postgres (7 / 2 is 3) and promotes to a float in ClickHouse.
• rawExpr, untypedExpr, dynamicColumn and outerRef take SQL as written; quote identifiers yourself ("OrgId") when targeting Postgres.
• An outer join fills missing columns with NULL in Postgres and with type defaults in ClickHouse (unless join_use_nulls is set). Joined columns are typed nullable either way.

See design/dialects.md for how dialects are built and what a new one needs to provide.