1---
2title: MySQL
3description: Provider-neutral MySQL schemas, Effect-native Drizzle queries, and connection configuration.
4---
5
6import { Code, Tabs, TabItem } from "@astrojs/starlight/components";
7
8export const deps = `drizzle-orm@1.0.0-rc.5-ab785fc @effect/sql-mysql2 mysql2`;
9export const devDeps = `drizzle-kit@1.0.0-rc.5-ab785fc`;
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
13Install the optional dependencies:
14
15<Tabs syncKey="pkgManager">
16 <TabItem label="bun" icon="bun">
17 <Code code={`bun add ${deps}\nbun add -d ${devDeps}`} lang="sh" />
18 </TabItem>
19 <TabItem label="npm" icon="npm">
20 <Code code={`npm install ${deps}\nnpm install -D ${devDeps}`} lang="sh" />
21 </TabItem>
22 <TabItem label="pnpm" icon="pnpm">
23 <Code code={`pnpm add ${deps}\npnpm add -D ${devDeps}`} lang="sh" />
24 </TabItem>
25 <TabItem label="yarn" icon="seti:yarn">
26 <Code code={`yarn add ${deps}\nyarn add -D ${devDeps}`} lang="sh" />
27 </TabItem>
28</Tabs>
29
30## Define the schema
31
32Drizzle schemas are plain TypeScript modules using the `mysql-core`
33column builders:
34
35```typescript
36// src/schema.ts
37import { defineRelations } from "drizzle-orm";
38import { int, mysqlTable, varchar } from "drizzle-orm/mysql-core";
39
40export const Users = mysqlTable("users", {
41 id: int("id").primaryKey().autoincrement(),
42 email: varchar("email", { length: 255 }).notNull().unique(),
43 name: varchar("name", { length: 255 }).notNull(),
44});
45
46export const Posts = mysqlTable("posts", {
47 id: int("id").primaryKey().autoincrement(),
48 userId: int("user_id").notNull(),
49 title: varchar("title", { length: 255 }).notNull(),
50});
51
52export const relations = defineRelations({ Users, Posts }, (t) => ({
53 Users: { posts: t.many.Posts() },
54 Posts: {
55 user: t.one.Users({ from: t.Posts.userId, to: t.Users.id }),
56 },
57}));
58```
59
60## Connect
61
62```typescript
63// src/queries.ts
64import * as Drizzle from "alchemy/Drizzle/MySQL";
65import * as Effect from "effect/Effect";
66import type * as Redacted from "effect/Redacted";
67import { relations, Users } from "./schema.ts";
68
69export const makeQueries = <E, R>(
70 connectionString: Effect.Effect<Redacted.Redacted<string>, E, R>,
71) =>
72 Effect.gen(function* () {
73 const db = yield* Drizzle.MySQL(connectionString, { relations });
74 return {
75 listUsers: () => db.select().from(Users),
76 };
77 });
78```
79
80Pass 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).
81
82## Configure the driver
83
84```typescript
85const db = yield* Drizzle.MySQL(connectionString, {
86 relations,
87 client: { poolConfig: { ssl: { rejectUnauthorized: true } } },
88});
89```
90
91Pass pool options through `config.client`, including TLS settings for direct connections. Explicit options override the driver's detected defaults.
92
93## Connect in a Worker
94
95The [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).
96
97## Queries are Effects
98
99Every builder yields directly, with `SqlError` in the typed error
100channel. MySQL has no `RETURNING` clause — inserts report generated
101ids via `$returningId()`, and upserts use `onDuplicateKeyUpdate`:
102
103```typescript
104import { eq } from "drizzle-orm";
105
106const [{ id }] = yield* db
107 .insert(Users)
108 .values({ name, email })
109 .$returningId();
110
111yield* db
112 .insert(Users)
113 .values({ id, name, email })
114 .onDuplicateKeyUpdate({ set: { name } });
115
116yield* db.delete(Users).where(eq(Users.id, id));
117```
118
119Because `relations` was passed to `Drizzle.MySQL`, the typed
120`db.query.*` API is available:
121
122```typescript
123const user = yield* db.query.Users.findFirst({
124 where: { id },
125 with: { posts: true },
126});
127```
128
129## Generate and review migrations
130
131```typescript
132// drizzle.config.ts
133import { defineConfig } from "drizzle-kit";
134
135export default defineConfig({
136 schema: "./src/schema.ts",
137 out: "./migrations",
138 dialect: "mysql",
139});
140```
141
142```sh
143bunx drizzle-kit generate
144git add src/schema.ts drizzle.config.ts migrations
145git diff --cached
146git commit -m "Add database migration"
147```
148
149Generate 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.
150
151## Where next
152
153Choose 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).