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

main
mdx 291 lines 9.44 KB
Raw
1 ---
2 title: Add Drizzle ORM
3 description: Deploy a Cloudflare Worker with Drizzle queries, Hyperdrive connections, and reviewed, committed migrations on Neon or PlanetScale.
4 ---
5
6 import PackageManagers from "../../../../components/PackageManagers.astro";
7
8 export const drizzleDeps = `@alchemy.run/drizzle drizzle-orm@1.0.0-rc.5 @effect/sql-pg`;
9 export const drizzleDevDeps = `drizzle-kit@1.0.0-rc.5`;
10
11 This walkthrough connects a Worker to Postgres through Hyperdrive; [SQL](/sql) owns the portable client APIs. For Cloudflare's SQLite database, use [Drizzle on D1](/cloudflare/data/d1-drizzle).
12
13 ## Install the client and generator
14
15 <PackageManagers add={drizzleDeps} dev={drizzleDevDeps} />
16
17 Keep the exact Drizzle prerelease pins together for the Effect integration; `drizzle-kit` is a development dependency.
18
19 ## Define the schema
20
21 ```typescript
22 // src/schema.ts
23 import { integer, pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";
24
25 export const Users = pgTable("users", {
26 id: serial("id").primaryKey(),
27 email: text("email").notNull().unique(),
28 name: text("name").notNull(),
29 createdAt: timestamp("created_at", { withTimezone: true })
30 .notNull()
31 .defaultNow(),
32 });
33
34 export const Posts = pgTable("posts", {
35 id: serial("id").primaryKey(),
36 userId: integer("user_id")
37 .notNull()
38 .references(() => Users.id, { onDelete: "cascade" }),
39 title: text("title").notNull(),
40 body: text("body").notNull(),
41 createdAt: timestamp("created_at", { withTimezone: true })
42 .notNull()
43 .defaultNow(),
44 });
45 ```
46
47 This is an ordinary Postgres schema; it has no Worker-specific configuration.
48
49 ## Configure migration generation
50
51 ```typescript
52 // drizzle.config.ts
53 import { defineConfig } from "drizzle-kit";
54
55 export default defineConfig({
56 schema: "./src/schema.ts",
57 out: "./migrations",
58 dialect: "postgresql",
59 });
60 ```
61
62 The generator writes SQL and snapshots to `migrations`; it does not need database credentials.
63
64 ## Generate and review migrations
65
66 ```sh
67 bunx drizzle-kit generate
68 git diff -- src/schema.ts migrations
69 git status --short
70 git add src/schema.ts drizzle.config.ts migrations
71 git diff --cached
72 git commit -m "Add database migration"
73 ```
74
75 Generate whenever the schema changes, review the schema, SQL, and snapshots together, and commit them before deployment. The optional [`Drizzle.Schema` resource](/providers/drizzle/schema) is a separate integration, not part of this workflow.
76
77 ## Configure the database
78
79 Use [Neon's database configuration](/neon/guides/drizzle#feed-migrations-from-the-schema) to export `NeonDb` from `src/Db.ts` with `migrations: "./migrations"`. That guide owns the project, branch, and connection options; [PlanetScale configuration](/planetscale/guides/drizzle) is the alternative below.
80
81 ## Add Hyperdrive
82
83 ```typescript
84 // src/Hyperdrive.ts
85 import * as Cloudflare from "@alchemy.run/cloudflare";
86 import * as Effect from "effect/Effect";
87 import { NeonDb } from "./Db.ts";
88
89 export const Hyperdrive = Effect.gen(function* () {
90 const { branch } = yield* NeonDb;
91 return yield* Cloudflare.Hyperdrive.Connection("app-hyperdrive", {
92 origin: branch.origin,
93 dev: branch.pooledOrigin,
94 });
95 });
96 ```
97
98 Hyperdrive pools the direct Neon origin; local development uses the pooled origin when it bypasses Hyperdrive.
99
100 ## Open the connection with `Drizzle.Postgres`
101
102 ```typescript
103 // src/Api.ts
104 import * as Cloudflare from "@alchemy.run/cloudflare";
105 import * as Drizzle from "@alchemy.run/drizzle/Postgres";
106 import * as Effect from "effect/Effect";
107 import * as HttpServerResponse from "effect/http/HttpServerResponse";
108 import { Hyperdrive } from "./Hyperdrive.ts";
109 import { Users } from "./schema.ts";
110
111 export default class Api extends Cloudflare.Worker<Api>()(
112 "Api",
113 {
114 main: import.meta.url,
115 compatibility: { flags: ["nodejs_compat"] },
116 },
117 Effect.gen(function* () {
118 const hd = yield* Cloudflare.Hyperdrive.Connect(Hyperdrive);
119 const db = yield* Drizzle.Postgres(hd.connectionString);
120
121 return {
122 fetch: Effect.gen(function* () {
123 const users = yield* db.select().from(Users);
124 return yield* HttpServerResponse.json(users);
125 }),
126 };
127 }).pipe(Effect.provide(Cloudflare.Hyperdrive.ConnectBinding)),
128 ) {}
129 ```
130
131 `nodejs_compat` enables the Node socket and TLS APIs `@effect/sql-pg` uses, and `ConnectBinding` supplies the Worker's Hyperdrive binding. Queries yield directly as Effects and acquire their pool only within the request's [scope](/sql/effect-sql/lifecycle).
132
133 ## Register the providers
134
135 ```typescript
136 // alchemy.run.ts
137 import * as Alchemy from "alchemy";
138 import * as Cloudflare from "@alchemy.run/cloudflare";
139 import * as Neon from "@alchemy.run/neon";
140 import * as Effect from "effect/Effect";
141 import * as Layer from "effect/Layer";
142 import Api from "./src/Api.ts";
143
144 export default Alchemy.Stack(
145 "MyStack",
146 {
147 providers: Layer.mergeAll(Cloudflare.providers(), Neon.providers()),
148 state: Alchemy.localState(),
149 },
150 Effect.gen(function* () {
151 const api = yield* Api;
152 return { url: api.url };
153 }),
154 );
155 ```
156
157 Only the runtime and database providers are needed; committed migration files do not require `Drizzle.providers()`.
158
159 ## Deploy
160
161 ```sh
162 bun alchemy deploy
163 ```
164
165 The configured branch applies pending committed SQL before the Worker serves queries; deployment does not generate new migrations. Keep an existing `drizzle-kit migrate` workflow unless you deliberately choose Alchemy-managed application, as described in [migration ownership](/sql/drizzle/migrations).
166
167 ## Define relations for typed `db.query`
168
169 ```diff lang="typescript"
170 // src/schema.ts
171 +import { defineRelations } from "drizzle-orm";
172
173 +export const relations = defineRelations({ Users, Posts }, (t) => ({
174 + Users: { posts: t.many.Posts() },
175 + Posts: {
176 + user: t.one.Users({
177 + from: t.Posts.userId,
178 + to: t.Users.id,
179 + }),
180 + },
181 +}));
182 ```
183
184 Append the relations after the table definitions; this metadata enables the relational query API without changing migration SQL.
185
186 ## Pass `relations` to `Drizzle.Postgres`
187
188 ```diff lang="typescript"
189 // src/Api.ts
190 -import { Users } from "./schema.ts";
191 +import { relations, Users } from "./schema.ts";
192
193 -const db = yield* Drizzle.Postgres(hd.connectionString);
194 +const db = yield* Drizzle.Postgres(hd.connectionString, { relations });
195 ```
196
197 The returned client now exposes `db.query.Users` and `db.query.Posts` with typed relation names.
198
199 ## Query related rows
200
201 ```diff lang="typescript"
202 // src/Api.ts
203 -const users = yield* db.select().from(Users);
204 -return yield* HttpServerResponse.json(users);
205 +const user = yield* db.query.Users.findFirst({
206 + where: { id: 1 },
207 + with: { posts: true },
208 +});
209 +return yield* HttpServerResponse.json({ user });
210 ```
211
212 `with: { posts: true }` includes the related posts in the inferred result type.
213
214 ## Iterate on the schema
215
216 ```sh
217 bunx drizzle-kit generate
218 git add src/schema.ts migrations
219 git diff --cached
220 git commit -m "Update database schema"
221 bun alchemy deploy
222 ```
223
224 Review each generated change before committing and deploy only those committed files. Reverting a TypeScript schema does not undo applied SQL; author and review a new migration or follow your database's restore procedure.
225
226 ## Use PlanetScale instead
227
228 ```diff lang="typescript"
229 // alchemy.run.ts
230 -import * as Neon from "@alchemy.run/neon";
231 +import * as Planetscale from "@alchemy.run/planetscale";
232
233 - providers: Layer.mergeAll(Cloudflare.providers(), Neon.providers()),
234 + providers: Layer.mergeAll(Cloudflare.providers(), Planetscale.providers()),
235 ```
236
237 Use the [PlanetScale guide](/planetscale/guides/drizzle) to export `PlanetscaleDb` with committed migrations; it owns both Postgres role and MySQL password configuration.
238
239 ## Connect a PlanetScale Postgres role
240
241 ```typescript
242 // src/Hyperdrive.ts
243 import * as Cloudflare from "@alchemy.run/cloudflare";
244 import * as Effect from "effect/Effect";
245 import { PlanetscaleDb } from "./Db.ts";
246
247 export const Hyperdrive = Effect.gen(function* () {
248 const { role } = yield* PlanetscaleDb;
249 return yield* Cloudflare.Hyperdrive.Connection("app-hyperdrive", {
250 origin: role.origin,
251 dev: role.pooledOrigin,
252 caching: { disabled: true },
253 });
254 });
255 ```
256
257 The Worker, Postgres schema, and queries remain unchanged.
258
259 ## Connect a PlanetScale MySQL password
260
261 ```typescript
262 // Inside the Hyperdrive effect, replacing the Postgres role configuration:
263 const { password } = yield* PlanetscaleDb;
264 return yield* Cloudflare.Hyperdrive.Connection("app-hyperdrive", {
265 origin: password.origin,
266 caching: { disabled: true },
267 });
268 ```
269
270 Select the MySQL resource variant in the [PlanetScale guide](/planetscale/guides/drizzle#mysql-apply-and-connect); there is no pooled password origin.
271
272 ## Select the MySQL client
273
274 ```sh
275 bun add @effect/sql-mysql2
276 ```
277
278 ```diff lang="typescript"
279 // src/Api.ts
280 -import * as Drizzle from "@alchemy.run/drizzle/Postgres";
281 +import * as Drizzle from "@alchemy.run/drizzle/MySQL";
282
283 -const db = yield* Drizzle.Postgres(hd.connectionString, { relations });
284 +const db = yield* Drizzle.MySQL(hd.connectionString, { relations });
285 ```
286
287 Use the [MySQL schema and generator configuration](/sql/drizzle/mysql), then generate, review, and commit MySQL migrations before deploying. The client uses [Workers-safe defaults](/sql/effect-sql/mysql#workers-defaults); MySQL-specific query differences stay in the portable client guide.
288
289 ## Where to from here
290
291 Use [Neon](/neon) or [PlanetScale](/planetscale) for database service behavior and [SQL databases](/sql/databases) to discover other providers. [Branch from a shared database](/cloudflare/data/branch-from-shared-database) covers the Worker deployment pattern for preview environments.