clover's git

the-knife

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