@devries-cameron20 / t3code Mirror · read-only
Fork 0

The best way to control Claude Code, Codex and more from any computer or phone

Mirrored from https://github.com/Camo67/t3code.git

Synced Nov 21, 06:00 UTC

main
md 149 lines 6.13 KB
Rendered Raw
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.