@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, 04:45 UTC

main
mdx 203 lines 7.78 KB
Raw
1 ---
2 title: Postgres
3 description: Connect Prisma ORM v8 to Postgres and run Effect-native queries, streams, prepared statements, and transactions independently of your cloud provider.
4 ---
5
6 import PackageManagers from "../../../../components/PackageManagers.astro";
7
8 export const deps = `@alchemy.run/prisma @prisma/orm-postgres@8.0.0-rc.11`;
9 export const devDeps = `prisma@8.0.0-rc.15`;
10
11 Prisma ORM v8 uses a contract to type queries and plan migrations. The same
12 Effect-native client works with a connection from configuration or a runtime
13 binding; choose a database in [SQL databases](/sql/databases).
14
15 :::caution
16 This integration targets **PostgreSQL 17+ only** — no MySQL or D1. Prisma v8
17 is a contract-first rewrite, not the earlier Prisma Client API. Use the ORM
18 and CLI release candidates pinned by your Alchemy release; expect breaking
19 changes between RC versions.
20 :::
21
22 Install `@alchemy.run/prisma` with Prisma ORM and its CLI, which are optional Alchemy peers:
23
24 <PackageManagers add={deps} dev={devDeps} />
25
26 ## Start with a contract
27
28 Follow [Contracts](/sql/prisma/contracts) to define `User` and `Post` in PSL
29 or TypeScript and configure `prisma.config.ts`. PSL-first projects must
30 generate the client before compiling or bundling. Both forms use the same
31 query APIs and [migration resources](/sql/prisma/migrations).
32
33 ## Connect
34
35 ```typescript
36 // src/queries.ts
37 import * as PrismaPostgres from "@alchemy.run/prisma/ORM/Postgres";
38 import * as Effect from "effect/Effect";
39 import type * as Redacted from "effect/Redacted";
40 import { contract } from "./prisma/contract.ts";
41
42 export const makeQueries = <E, R>(
43 connectionString: Effect.Effect<Redacted.Redacted<string>, E, R>,
44 ) =>
45 Effect.gen(function* () {
46 const db = yield* PrismaPostgres.Postgres(connectionString, { contract });
47 return {
48 listUsers: () => db.orm.public.User.all(),
49 };
50 });
51 ```
52
53 Pass an Effect that resolves a redacted URL, such as
54 `Config.Redacted("DATABASE_URL")` or a runtime binding's `connectionString`.
55 Wrap an already resolved redacted URL with `Effect.succeed(url)`.
56
57 For a PSL-first contract, replace the database construction with the
58 [generated factory](/sql/prisma/contracts#query-a-psl-contract):
59
60 ```typescript
61 import { makeDatabase } from "./prisma/generated/client.ts";
62
63 const db = yield* makeDatabase(connectionString);
64 ```
65
66 Neither factory connects at initialization. Construct the query service once,
67 then call its methods inside a runtime execution scope or an explicit
68 `Effect.scoped` block. The client is built on first use in each scope and
69 `close()` runs when that scope settles; see [Connection lifecycle](/sql/effect-sql/lifecycle).
70 Migration resources connect separately when applying migrations.
71
72 ## Configure validation for Workers
73
74 Native TypeScript contracts need Arktype's `jitless: true` configuration before
75 importing the contract builder, with the bootstrap preserved from tree-shaking.
76 Follow [Worker validation setup](/cloudflare/data/prisma#configure-validation-for-workers),
77 including its `arktype` dependency. Ordinary Node and Bun applications, and
78 PSL-first Workers using the emitted contract, do not need this bootstrap.
79
80 ## Connect in a Worker
81
82 The [Cloudflare integration](/cloudflare/data/prisma) owns Neon provisioning,
83 Hyperdrive, migration resources, and Worker deployment. Its connection Effect
84 works with either factory above; shared query code does not need those resources.
85
86 ## Queries are Effects
87
88 The `orm` lane is namespaced per Postgres schema (`db.orm.public.*`). Every
89 terminal yields an Effect with typed errors. Queries are lazy and re-runnable:
90 `Effect.retry` re-issues the query.
91
92 ```typescript
93 const user = yield* db.orm.public.User.where({ email })
94 .include("posts")
95 .first();
96
97 if (user) {
98 yield* db.orm.public.Post.create({ title, authorId: user.id });
99 }
100
101 // row types narrow with the chain: select projects, include augments
102 const slim = yield* db.orm.public.User.select("id", "email").all();
103 ```
104
105 `update` and `delete` require a prior `.where(...)` at compile time.
106 SQL integrity violations have distinct `Prisma.*` tags: `UniqueViolationError`,
107 `ForeignKeyViolationError`, `NotNullViolationError`, and `CheckViolationError`.
108 Other statement failures are `QueryError` with `sqlState`; connection failures
109 are `ConnectionError` with the driver's `transient` hint. Prisma's structured
110 codes split into `OrmError` and `RuntimeError` with a typed `code` field:
111
112 ```typescript
113 const outcome = yield* db.orm.public.User.create({ email, name }).pipe(
114 Effect.catchTag("Prisma.UniqueViolationError", () =>
115 Effect.succeed("already registered"),
116 ),
117 );
118 ```
119
120 ## Query polymorphic models
121
122 For PSL contracts declaring `@@discriminator` and `@@base`, narrow the base
123 collection with `variant()`. Both single-table and multi-table variants use
124 the same Effect terminals:
125
126 ```typescript
127 const bugs = yield* db.orm.public.Task.variant("Bug")
128 .where((bug) => bug.severity.eq("critical"))
129 .include("assignee")
130 .all();
131 ```
132
133 The narrowed row keeps its literal discriminator and variant fields. `include`
134 can load relations declared by that variant. `select` removes unselected scalar
135 fields while preserving included relations. Scalar reducers such as `count`
136 and `combine` are only available when refining a to-many relation.
137
138 For generation and validation constraints on polymorphic contracts, see
139 [Contracts: Validate rows without a database](/sql/prisma/contracts#validate-rows-without-a-database).
140
141 ## The sql lane and raw SQL
142
143 `db.sql` is Prisma's pure query builder — plans are data, no
144 connection behind them — and `db.execute` / `db.stream` run them:
145
146 ```typescript
147 const plan = db.sql.public.User.select("id", "email").build();
148 const rows = yield* db.execute(plan); // Effect<{id, email}[]>
149 const feed = db.stream(plan); // Stream<{id, email}>
150 ```
151
152 The Stream surfaces consume Prisma's async iterators. Its long-lived Postgres
153 driver buffers raw results, so using a Stream does not guarantee bounded memory.
154
155 ## Prepared queries
156
157 A prepared query retains its typed parameters and resolves the current
158 execution's client each time it runs:
159
160 ```typescript
161 const findUser = yield* db.prepare({ email: "pg/text@1" }, (sql, params) =>
162 sql.public.User
163 .select("id", "email")
164 .where((fields, fns) => fns.eq(fields.email, params.email))
165 .build(),
166 );
167 const users = yield* findUser.query({ email: "alice@example.com" });
168 const stream = findUser.query({ email: "alice@example.com" }).stream;
169 ```
170
171 An affected-count plan instead returns a handle with `execute(params)`, which
172 produces statement statistics. Inside a transaction, `tx.prepare` binds both
173 query and mutation handles to that transaction's connection.
174
175 ## Transactions
176
177 `db.transaction` runs an Effect on a dedicated connection — commit on
178 success, rollback on failure or interruption. `tx.rollback()` aborts
179 with a typed `Prisma.RollbackError`:
180
181 ```typescript
182 yield* db.transaction((tx) =>
183 Effect.gen(function* () {
184 const user = yield* tx.orm.public.User.create({ email, name });
185 if (!invited) return yield* tx.rollback();
186 return user;
187 }),
188 );
189 ```
190
191 Native extensions or query surfaces outside the Effect facade remain available
192 through `db.use((client) => ...)`. Prefer the Effect-native methods when available
193 so failures and execution remain in the Effect runtime.
194
195 ## Where next
196
197 - [Contracts](/sql/prisma/contracts) — PSL and TypeScript authoring, generation,
198 and standalone Effect row schemas.
199 - [Migrations](/sql/prisma/migrations) — review and apply contract changes.
200 - [SQL databases](/sql/databases) — database choices and canonical deployment guides.
201 - [TypeScript-first example](https://github.com/alchemy-run/alchemy/tree/main/examples/cloudflare-neon-prisma)
202 or [PSL-first example](https://github.com/alchemy-run/alchemy/tree/main/examples/cloudflare-neon-prisma-psl).
203 - [Effect SQL: Postgres](/sql/effect-sql/postgres) — tagged-template SQL without an ORM.