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
| 1 | --- |
| 2 | title: D1 |
| 3 | description: SQLite schemas and Effect-native Drizzle queries over a Cloudflare D1 binding. |
| 4 | --- |
| 5 | |
| 6 | import PackageManagers from "../../../../components/PackageManagers.astro"; |
| 7 | |
| 8 | export const deps = `@alchemy.run/drizzle drizzle-orm@1.0.0-rc.5 @effect/sql-d1`; |
| 9 | export const devDeps = `drizzle-kit@1.0.0-rc.5`; |
| 10 | |
| 11 | `Drizzle.D1` provides typed, Effect-native queries over a Cloudflare D1 binding; unlike Postgres and MySQL, this driver is Cloudflare-specific. See [SQL databases](/sql/databases) to compare engines and [Cloudflare setup](/cloudflare/setup) for deployment choices. |
| 12 | |
| 13 | Install the toolchain — all optional peers of alchemy: |
| 14 | |
| 15 | <PackageManagers add={deps} dev={devDeps} /> |
| 16 | |
| 17 | ## Define the schema |
| 18 | |
| 19 | Drizzle schemas are plain TypeScript modules using the |
| 20 | `sqlite-core` column builders: |
| 21 | |
| 22 | ```typescript |
| 23 | // src/schema.ts |
| 24 | import { defineRelations } from "drizzle-orm"; |
| 25 | import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core"; |
| 26 | |
| 27 | export const Users = sqliteTable("users", { |
| 28 | id: integer("id").primaryKey({ autoIncrement: true }), |
| 29 | email: text("email").notNull().unique(), |
| 30 | name: text("name").notNull(), |
| 31 | }); |
| 32 | |
| 33 | export const Posts = sqliteTable("posts", { |
| 34 | id: integer("id").primaryKey({ autoIncrement: true }), |
| 35 | userId: integer("user_id") |
| 36 | .notNull() |
| 37 | .references(() => Users.id, { onDelete: "cascade" }), |
| 38 | title: text("title").notNull(), |
| 39 | }); |
| 40 | |
| 41 | export const relations = defineRelations({ Users, Posts }, (t) => ({ |
| 42 | Users: { posts: t.many.Posts() }, |
| 43 | Posts: { |
| 44 | user: t.one.Users({ from: t.Posts.userId, to: t.Users.id }), |
| 45 | }, |
| 46 | })); |
| 47 | ``` |
| 48 | |
| 49 | ## Generate and review migrations |
| 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: "sqlite", |
| 59 | }); |
| 60 | ``` |
| 61 | |
| 62 | ```sh |
| 63 | bunx drizzle-kit generate |
| 64 | git add src/schema.ts drizzle.config.ts migrations |
| 65 | git diff --cached |
| 66 | git commit -m "Add database migration" |
| 67 | ``` |
| 68 | |
| 69 | Generate with each schema change and review the schema, SQL, and snapshots together before committing. [Migrations](/sql/drizzle/migrations) covers application ownership; the optional [`Drizzle.Schema` resource](/providers/drizzle/schema) is not required. |
| 70 | |
| 71 | ## Declare the database |
| 72 | |
| 73 | ```typescript |
| 74 | // src/db.ts |
| 75 | import * as Cloudflare from "@alchemy.run/cloudflare"; |
| 76 | |
| 77 | export const Database = Cloudflare.D1.Database("app-db", { |
| 78 | migrations: "./migrations", |
| 79 | }); |
| 80 | ``` |
| 81 | |
| 82 | With `Cloudflare.providers()` registered, the resource applies committed files during deploy without generating SQL. See the [Cloudflare D1 walkthrough](/cloudflare/data/d1-drizzle) for the full deployment; keep an existing migration runner unless you deliberately choose Alchemy-managed application. |
| 83 | |
| 84 | ## Connect in a Worker |
| 85 | |
| 86 | Bind the database with `Cloudflare.D1.QueryDatabase` and hand the |
| 87 | client to `Drizzle.D1`: |
| 88 | |
| 89 | ```typescript |
| 90 | // src/api.ts |
| 91 | import * as Cloudflare from "@alchemy.run/cloudflare"; |
| 92 | import * as Drizzle from "@alchemy.run/drizzle"; |
| 93 | import * as Effect from "effect/Effect"; |
| 94 | import * as HttpServerResponse from "effect/http/HttpServerResponse"; |
| 95 | import { Database } from "./db.ts"; |
| 96 | import { relations, Users } from "./schema.ts"; |
| 97 | |
| 98 | export default class Api extends Cloudflare.Worker<Api>()( |
| 99 | "Api", |
| 100 | { main: import.meta.url }, |
| 101 | Effect.gen(function* () { |
| 102 | const database = yield* Database; |
| 103 | const d1 = yield* Cloudflare.D1.QueryDatabase(database); |
| 104 | const db = yield* Drizzle.D1(d1, { relations }); |
| 105 | |
| 106 | return { |
| 107 | fetch: Effect.gen(function* () { |
| 108 | const users = yield* db.select().from(Users); |
| 109 | return yield* HttpServerResponse.json({ users }); |
| 110 | }), |
| 111 | }; |
| 112 | }).pipe(Effect.provide(Cloudflare.D1.QueryDatabaseBinding)), |
| 113 | ) {} |
| 114 | ``` |
| 115 | |
| 116 | Client construction does not query D1; the first query builds the client and reuses it within the event's [scope](/sql/effect-sql/lifecycle). Resource provisioning and migration application still run during deployment. |
| 117 | |
| 118 | ## Queries are Effects |
| 119 | |
| 120 | Every builder yields directly, with `SqlError` in the typed error |
| 121 | channel: |
| 122 | |
| 123 | ```typescript |
| 124 | import { eq } from "drizzle-orm"; |
| 125 | |
| 126 | const [created] = yield* db |
| 127 | .insert(Users) |
| 128 | .values({ name, email }) |
| 129 | .returning(); |
| 130 | |
| 131 | const [removed] = yield* db |
| 132 | .delete(Users) |
| 133 | .where(eq(Users.id, id)) |
| 134 | .returning(); |
| 135 | ``` |
| 136 | |
| 137 | Because `relations` was passed to `Drizzle.D1`, the typed |
| 138 | `db.query.*` API is available: |
| 139 | |
| 140 | ```typescript |
| 141 | const user = yield* db.query.Users.findFirst({ |
| 142 | where: { id }, |
| 143 | with: { posts: true }, |
| 144 | }); |
| 145 | ``` |
| 146 | |
| 147 | D1 has no session transactions — for atomic multi-statement writes |
| 148 | use the binding's `batch` (see |
| 149 | [Batches instead of transactions](/sql/effect-sql/d1#batches-instead-of-transactions)). |
| 150 | |
| 151 | ## Local dev |
| 152 | |
| 153 | `alchemy dev` runs the same Worker against a local D1 database via |
| 154 | miniflare — `Drizzle.D1` works unchanged because it resolves |
| 155 | whatever `D1Database` binding the runtime provides. |
| 156 | |
| 157 | ## Where next |
| 158 | |
| 159 | The [Cloudflare D1 walkthrough](/cloudflare/data/d1-drizzle) owns deployment, while [Effect SQL: D1](/sql/effect-sql/d1) covers tagged-template queries over the same binding. The [D1 example](https://github.com/alchemy-run/alchemy/tree/main/examples/cloudflare-d1-drizzle) demonstrates schema and query code but uses the optional schema resource rather than this guide's generation workflow. |