clover's git

the-knife

1	---
2	title: D1
3	description: SQL.D1 wraps a bound D1 database in an @effect/sql-d1 client — tagged-template queries with typed errors over the native binding.
4	---
5
6	import { Code, Tabs, TabItem } from "@astrojs/starlight/components";
7
8	export const deps = `@effect/sql-d1`;
9
10	`SQL.D1` opens an `@effect/sql-d1` client over a Cloudflare D1
11	binding. Queries are Effects: they carry `SqlError` in the error
12	channel, participate in interruption, and trace like everything else
13	in your program.
14
15	Install the driver — an optional peer of alchemy:
16
17	<Tabs syncKey="pkgManager">
18	<TabItem label="bun" icon="bun">
19	<Code code={`bun add ${deps}`} lang="sh" />
20	</TabItem>
21	<TabItem label="npm" icon="npm">
22	<Code code={`npm install ${deps}`} lang="sh" />
23	</TabItem>
24	<TabItem label="pnpm" icon="pnpm">
25	<Code code={`pnpm add ${deps}`} lang="sh" />
26	</TabItem>
27	<TabItem label="yarn" icon="seti:yarn">
28	<Code code={`yarn add ${deps}`} lang="sh" />
29	</TabItem>
30	</Tabs>
31
32	## Connect
33
34	Declare the database, bind it with
35	`Cloudflare.D1.QueryDatabase`, and hand the client to `SQL.D1`:
36
37	```typescript
38	// src/db.ts
39	import * as Cloudflare from "alchemy/Cloudflare";
40
41	export const Database = Cloudflare.D1.Database("Database");
42	```
43
44	```typescript
45	// src/api.ts
46	import * as Cloudflare from "alchemy/Cloudflare";
47	import * as SQL from "alchemy/SQL/D1";
48	import * as Effect from "effect/Effect";
49	import * as HttpServerResponse from "effect/http/HttpServerResponse";
50	import { Database } from "./db.ts";
51
52	export default class Api extends Cloudflare.Worker<Api>()(
53	"Api",
54	{ main: import.meta.url },
55	Effect.gen(function* () {
56	const d1 = yield* Cloudflare.D1.QueryDatabase(Database);
57	const sql = yield* SQL.D1(d1);
58
59	return {
60	fetch: Effect.gen(function* () {
61	const users = yield* sql`SELECT * FROM users`;
62	return yield* HttpServerResponse.json({ users });
63	}),
64	};
65	}).pipe(Effect.provide(Cloudflare.D1.QueryDatabaseBinding)),
66	) {}
67	```
68
69	The client (and its prepared-statement cache) is built lazily on the
70	first query of an execution and torn down when the event settles —
71	see [Connection lifecycle](/sql/effect-sql/lifecycle).
72
73	## Queries
74
75	Interpolated values are parameters, never string concatenation:
76
77	```typescript
78	const user = yield* sql`SELECT * FROM users WHERE id = ${id}`;
79
80	yield* sql`INSERT INTO users ${sql.insert({ name, email })}`;
81
82	const rows = yield* sql`
83	SELECT * FROM users WHERE id IN ${sql.in(ids)}
84	`;
85	```
86
87	Rows come back as plain objects typed by your annotation:
88	`sql<{ id: number; name: string }>\`...\``.
89
90	## Errors
91
92	Failures surface as `SqlError` in the typed error channel:
93
94	```typescript
95	const users = yield* sql`SELECT * FROM users`.pipe(
96	Effect.catchTag("SqlError", (e) =>
97	Effect.succeed([]).pipe(Effect.tap(() => Effect.logWarning(e))),
98	),
99	);
100	```
101
102	## Batches instead of transactions
103
104	D1 has no session transactions, so `sql.withTransaction` and
105	streaming queries are unavailable on this driver. For atomic
106	multi-statement writes, use the native binding's `batch` through the
107	same `QueryDatabase` client — statements execute sequentially in one
108	round trip and roll back together on failure:
109
110	```typescript
111	yield* d1.batch([
112	d1.prepare("UPDATE accounts SET balance = balance - ?1 WHERE id = ?2").bind(amount, from),
113	d1.prepare("UPDATE accounts SET balance = balance + ?1 WHERE id = ?2").bind(amount, to),
114	]);
115	```
116
117	## Provide as a service
118
119	For services that shouldn't know which database they run on, depend
120	on the generic `SqlClient` tag and provide the database with
121	`SQL.D1Layer`:
122
123	```typescript
124	import * as SqlClient from "effect/sql/SqlClient";
125
126	const makeUsers = Effect.gen(function* () {
127	const sql = yield* SqlClient.SqlClient;
128	return {
129	find: (id: number) => sql<User>`SELECT * FROM users WHERE id = ${id}`,
130	};
131	});
132
133	const users = yield* makeUsers.pipe(Effect.provide(SQL.D1Layer(d1)));
134	```
135
136	The layer provides `SqlClient.SqlClient` and `@effect/sql-d1`'s `D1Client` from one per-execution client. Other drivers can satisfy the generic service, but changing engines still requires compatible SQL and transaction semantics.
137
138	## Local dev
139
140	`alchemy dev` runs the same Worker against a local D1 database via
141	miniflare — `SQL.D1` works unchanged because it resolves whatever
142	`D1Database` binding the runtime provides.
143
144	## Where next
145
146	[Cloudflare setup](/cloudflare/setup) and [D1](/cloudflare/data/d1) cover deployment and database behavior; [SQL databases](/sql/databases) compares D1 with other engines. Read [Migrations](/sql/effect-sql/migrations) for committed SQL application, [Connection lifecycle](/sql/effect-sql/lifecycle) for cleanup, or [Drizzle on D1](/sql/drizzle/d1) for typed schemas.