clover's git

the-knife

1	# @effect/sql-pg
2
3	An Effect SQL client for PostgreSQL with a native wire-protocol driver.
4
5	## Installation
6
7	```sh
8	npm install effect @effect/sql-pg
9	```
10
11	## Links
12
13	- [Website](https://effect.website): documentation, guides, and news.
14	- [Reference](https://effect.website/docs/v4/api/sql-pg): API documentation for this package.
15	- [Discord](https://discord.gg/effect-ts): ask questions, share what you're building, and talk to the core team.
16	- [Community](https://effect.website/community-hub): meetups and events, or bring Effect to your own.
17	- [Issues](https://github.com/Effect-TS/effect/issues): bug reports and feature requests.
18
19	## Let's talk
20
21	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.
22
23	- **Talk to the maintainers.** Introduce your team on [Discord](https://discord.gg/effect-ts) or email [contact@effectful.co](mailto:contact@effectful.co). We're happy to connect privately on Slack or Discord for feedback and help with adoption.
24	- **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.
25	- **Adoption help.** Our [adoption partners](https://effect.website/adoption-partners) offer implementation, consulting, team extension, training, and commercial support.
26
27	## Session defaults
28
29	Use `startupParameters` to establish PostgreSQL session defaults when each physical
30	connection opens, including replacement connections in a pool:
31
32	```ts
33	import { PgClient } from "@effect/sql-pg"
34	import { Redacted } from "effect"
35
36	const PostgresLive = PgClient.layer({
37	url: Redacted.make("postgres://user:password@localhost/app"),
38	startupParameters: {
39	statement_timeout: "5s",
40	search_path: "app, public"
41	},
42	startupOptions: "-c lock_timeout=1000"
43	})
44	```
45
46	These settings are sent in the startup packet. PostgreSQL's `RESET ALL` restores
47	the startup defaults after session-level changes. Both `PgConnection.Config` and
48	`PgClientConfig` accept these fields, and pools inherit them.
49
50	Parameter names are normalized to lowercase. Empty names and NUL bytes in names
51	or values are rejected before connecting. The names `user`, `database`,
52	`replication`, and `options` are reserved; use `username`, `database`, and `startupOptions`
53	on the config for identity and opaque options. Replication mode is not supported
54	through `startupParameters`. The driver accepts only `UTF8` or `UTF-8`
55	(case-insensitive) for `client_encoding` and sends the canonical value `UTF8`.
56	PostgreSQL validates other settings and reports invalid names or values at connect
57	time.
58
59	The `startupOptions` field is an opaque PostgreSQL options string. It can also come
60	from the URL query, for example `?options=-c%20statement_timeout%3D5000`. Explicit
61	config `startupOptions` overrides URL `options`, including when the explicit value
62	is empty. The URL query key and startup packet field are both named `options`.
63	Named parameters and opaque options can be sent together, but callers must not
64	set the same GUC in both. The driver does not parse `-c` flags, detect conflicts,
65	or read `PGOPTIONS`.
66
67	The startup `application_name` is selected in this order:
68
69	1. Explicit `applicationName`
70	2. `startupParameters.application_name`
71	3. URL `application_name`
72	4. `"@effect/sql-pg"`
73
74	The driver does not extract `application_name` from the opaque options string.
75
76	## User-defined types
77
78	Columns whose type OID has no codec are decoded as UTF-8 text, so user-defined
79	enums read as plain strings:
80
81	```ts
82	import { PgClient } from "@effect/sql-pg"
83	import { Effect } from "effect"
84
85	// CREATE TYPE capability AS ENUM ('use_key', 'manage')
86	const program = Effect.gen(function*() {
87	const sql = yield* PgClient.PgClient
88	const rows = yield* sql`SELECT 'use_key'::capability AS capability`
89	// rows[0].capability === "use_key"
90	})
91	```
92
93	Strings bind to enum columns without any setup; the server infers the type.
94
95	The fallback applies to every OID without a codec, not only enums, and it has
96	two failure modes:
97
98	- Invalid UTF-8 fails with `PgTypes.CodecError`, as it does for `text`. A codec
99	failure while reading a row is fatal to the connection, not just to the
100	query: the socket is destroyed, every other query pipelined on that
101	connection fails, an open transaction is lost, `LISTEN` channels on it are
102	torn down, and the pool replaces the connection. Columns of a binary
103	user-defined type such as a composite, a PostGIS geometry, or a pgvector
104	`vector` hold bytes that are usually not valid UTF-8, so register a codec
105	before querying them.
106	- Bytes that happen to be valid UTF-8 decode as garbled text without any
107	error. Arrays of user-defined types, including enum arrays, land here: the
108	array header is all low bytes, so a `capability[]` column reads as a string
109	of control characters followed by the labels.
110
111	Register a codec for a scalar user-defined type with
112	`PgTypes.register(oid, codec)`; registered codecs take precedence over the
113	fallback. `PgTypes.register` cannot attach an array type. For arrays, create a
114	registry, register the element codec with its `arrayOid`, and pass the registry
115	to the client as `types`. The registry builds the array codec from the element
116	codec:
117
118	```ts
119	import { PgClient, PgTypes } from "@effect/sql-pg"
120	import { Redacted, Result } from "effect"
121
122	// SELECT oid, typarray FROM pg_type WHERE typname = 'capability'
123	declare const capabilityOid: number
124	declare const capabilityArrayOid: number
125
126	const decoder = new TextDecoder("utf-8", { fatal: true })
127
128	const utf8: PgTypes.Codec<string> = {
129	encode: (value) => Result.succeed(new TextEncoder().encode(value)),
130	decode: (bytes) => {
131	try {
132	return Result.succeed(decoder.decode(bytes))
133	} catch {
134	return Result.fail(new PgTypes.CodecError({ message: "Invalid UTF-8 in capability" }))
135	}
136	}
137	}
138
139	const types = PgTypes.makeRegistry()
140	types.register(capabilityOid, utf8, { arrayOid: capabilityArrayOid })
141
142	const PostgresLive = PgClient.layer({
143	url: Redacted.make("postgres://user:password@localhost/app"),
144	types
145	})
146	```
147
148	OIDs of user-defined types are assigned per database, so look them up in
149	`pg_type` at startup rather than hard-coding them.