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

main
mdx 140 lines 4.72 KB
Raw
1 ---
2 title: MySQL
3 description: Provider-neutral MySQL schemas, Effect-native Drizzle queries, and connection configuration.
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-mysql2`;
9 export const devDeps = `drizzle-kit@1.0.0-rc.5`;
10
11 `Drizzle.MySQL` provides typed, Effect-native queries for compatible MySQL databases, independently of where your application runs. Choose a database in [SQL databases](/sql/databases) and supply its connection through your runtime's binding or configuration.
12
13 Install the optional dependencies:
14
15 <PackageManagers add={deps} dev={devDeps} />
16
17 ## Define the schema
18
19 Drizzle schemas are plain TypeScript modules using the `mysql-core`
20 column builders:
21
22 ```typescript
23 // src/schema.ts
24 import { defineRelations } from "drizzle-orm";
25 import { int, mysqlTable, varchar } from "drizzle-orm/mysql-core";
26
27 export const Users = mysqlTable("users", {
28 id: int("id").primaryKey().autoincrement(),
29 email: varchar("email", { length: 255 }).notNull().unique(),
30 name: varchar("name", { length: 255 }).notNull(),
31 });
32
33 export const Posts = mysqlTable("posts", {
34 id: int("id").primaryKey().autoincrement(),
35 userId: int("user_id").notNull(),
36 title: varchar("title", { length: 255 }).notNull(),
37 });
38
39 export const relations = defineRelations({ Users, Posts }, (t) => ({
40 Users: { posts: t.many.Posts() },
41 Posts: {
42 user: t.one.Users({ from: t.Posts.userId, to: t.Users.id }),
43 },
44 }));
45 ```
46
47 ## Connect
48
49 ```typescript
50 // src/queries.ts
51 import * as Drizzle from "@alchemy.run/drizzle/MySQL";
52 import * as Effect from "effect/Effect";
53 import type * as Redacted from "effect/Redacted";
54 import { relations, Users } from "./schema.ts";
55
56 export const makeQueries = <E, R>(
57 connectionString: Effect.Effect<Redacted.Redacted<string>, E, R>,
58 ) =>
59 Effect.gen(function* () {
60 const db = yield* Drizzle.MySQL(connectionString, { relations });
61 return {
62 listUsers: () => db.select().from(Users),
63 };
64 });
65 ```
66
67 Pass an Effect that resolves a redacted URL, such as `Config.Redacted("DATABASE_URL")` or a runtime binding's `connectionString`; wrap an already resolved redacted URL with `Effect.succeed(url)`. Call `listUsers()` inside a request or an explicit `Effect.scoped` block so the pool follows the [connection lifecycle](/sql/effect-sql/lifecycle).
68
69 ## Configure the driver
70
71 ```typescript
72 const db = yield* Drizzle.MySQL(connectionString, {
73 relations,
74 client: { poolConfig: { ssl: { rejectUnauthorized: true } } },
75 });
76 ```
77
78 Pass pool options through `config.client`, including TLS settings for direct connections. Explicit options override the driver's detected defaults.
79
80 ## Connect in a Worker
81
82 The [Cloudflare walkthrough](/cloudflare/data/drizzle#use-planetscale-instead) supplies Hyperdrive's connection string; [PlanetScale](/planetscale/guides/drizzle) owns database and password configuration. On Workers, the client selects text-protocol queries and eval-free row parsers; see [Workers defaults](/sql/effect-sql/mysql#workers-defaults).
83
84 ## Queries are Effects
85
86 Every builder yields directly, with `SqlError` in the typed error
87 channel. MySQL has no `RETURNING` clause — inserts report generated
88 ids via `$returningId()`, and upserts use `onDuplicateKeyUpdate`:
89
90 ```typescript
91 import { eq } from "drizzle-orm";
92
93 const [{ id }] = yield* db
94 .insert(Users)
95 .values({ name, email })
96 .$returningId();
97
98 yield* db
99 .insert(Users)
100 .values({ id, name, email })
101 .onDuplicateKeyUpdate({ set: { name } });
102
103 yield* db.delete(Users).where(eq(Users.id, id));
104 ```
105
106 Because `relations` was passed to `Drizzle.MySQL`, the typed
107 `db.query.*` API is available:
108
109 ```typescript
110 const user = yield* db.query.Users.findFirst({
111 where: { id },
112 with: { posts: true },
113 });
114 ```
115
116 ## Generate and review migrations
117
118 ```typescript
119 // drizzle.config.ts
120 import { defineConfig } from "drizzle-kit";
121
122 export default defineConfig({
123 schema: "./src/schema.ts",
124 out: "./migrations",
125 dialect: "mysql",
126 });
127 ```
128
129 ```sh
130 bunx drizzle-kit generate
131 git add src/schema.ts drizzle.config.ts migrations
132 git diff --cached
133 git commit -m "Add database migration"
134 ```
135
136 Generate on each schema change, stage and review the schema, SQL, and snapshots, then commit them before application. Apply the committed migrations with your existing runner before deploying code that needs the new schema. [Migrations](/sql/drizzle/migrations) covers application ownership: [`Drizzle.Schema`](/providers/drizzle/schema) is optional, and `alchemy deploy` does not replace an existing `drizzle-kit migrate` workflow.
137
138 ## Where next
139
140 Choose a MySQL-compatible database and deployment guide in [SQL databases](/sql/databases). For tagged-template queries without an ORM, use [Effect SQL: MySQL](/sql/effect-sql/mysql).