1---
2title: MySQL
3description: SQL.MySQL turns a connection string into an @effect/sql-mysql2 client — portable tagged-template queries with typed errors and one pool per execution.
4---
5
6import { Code, Tabs, TabItem } from "@astrojs/starlight/components";
7
8export const deps = `@effect/sql-mysql2 mysql2`;
9
10`SQL.MySQL` provides tagged-template queries over a MySQL connection. Queries are Effects with typed `SqlError` failures, interruption, and tracing.
11
12Install the optional MySQL driver:
13
14<Tabs syncKey="pkgManager">
15 <TabItem label="bun" icon="bun">
16 <Code code={`bun add ${deps}`} lang="sh" />
17 </TabItem>
18 <TabItem label="npm" icon="npm">
19 <Code code={`npm install ${deps}`} lang="sh" />
20 </TabItem>
21 <TabItem label="pnpm" icon="pnpm">
22 <Code code={`pnpm add ${deps}`} lang="sh" />
23 </TabItem>
24 <TabItem label="yarn" icon="seti:yarn">
25 <Code code={`yarn add ${deps}`} lang="sh" />
26 </TabItem>
27</Tabs>
28
29## Connect
30
31```typescript
32import * as SQL from "alchemy/SQL/MySQL";
33import * as Effect from "effect/Effect";
34import type * as Redacted from "effect/Redacted";
35
36export const makeQueries = <E, R>(
37 connectionString:
38 | Redacted.Redacted<string>
39 | Effect.Effect<Redacted.Redacted<string>, E, R>,
40) =>
41 Effect.gen(function* () {
42 const sql = yield* SQL.MySQL({ url: connectionString });
43 return {
44 listUsers: () => sql<{ id: number; name: string }>`SELECT id, name FROM users`,
45 };
46 });
47```
48
49`url` accepts a redacted URL or an Effect resolving one, such as `Config.Redacted("DATABASE_URL")` or a runtime binding's `connectionString`. Other `@effect/sql-mysql2` options pass through. Call queries within a request or an explicit `Effect.scoped` block: the pool opens lazily and closes with that [scope](/sql/effect-sql/lifecycle).
50
51## Workers defaults
52
53```typescript
54// Applied by default only when workerd is detected:
55const workersDefaults = {
56 disablePreparedStatements: true,
57 poolConfig: { disableEval: true },
58};
59```
60
61These defaults avoid Hyperdrive's unsupported prepared-statement protocol and Workers' prohibition on eval-based row parsers. Direct connections from Node or Bun retain prepared statements and mysql2's normal parsers by default. See [Hyperdrive](/cloudflare/data/hyperdrive#use-effect-sql) for Worker connection setup.
62
63## Override driver options
64
65`SQL.MySQL` parses URL fields and query parameters into driver configuration, including `poolConfig`. Explicit options override parsed values and detected defaults:
66
67```typescript
68const sql = yield* SQL.MySQL({
69 url: connectionString,
70 // For a proxy that lacks COM_STMT_PREPARE.
71 disablePreparedStatements: true,
72 // For a direct TLS connection.
73 poolConfig: { ssl: { rejectUnauthorized: true } },
74});
75```
76
77## Queries
78
79Interpolated values are parameters, never string concatenation:
80
81```typescript
82const user = yield* sql`SELECT * FROM users WHERE id = ${id}`;
83
84yield* sql`INSERT INTO users ${sql.insert({ name, email })}`;
85
86const rows = yield* sql`
87 SELECT * FROM users WHERE id IN ${sql.in(ids)}
88`;
89```
90
91Rows are plain objects; supply a row type with `sql<Row>`. The
92[`effect/sql/Statement`](https://effect.website) API also provides
93fragments, `sql.csv`, `sql.and`, and identifier escaping (backticks on MySQL).
94
95## Errors
96
97Failures surface as `SqlError` in the typed error channel:
98
99```typescript
100const users = yield* sql`SELECT * FROM users`.pipe(
101 Effect.catchTag("SqlError", (e) =>
102 Effect.succeed([]).pipe(Effect.tap(() => Effect.logWarning(e))),
103 ),
104);
105```
106
107## Transactions
108
109Wrap a group of queries in `sql.withTransaction` — the whole effect
110commits or rolls back together:
111
112```typescript
113yield* sql.withTransaction(
114 Effect.gen(function* () {
115 yield* sql`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${from}`;
116 yield* sql`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${to}`;
117 }),
118);
119```
120
121## Provide as a service
122
123Depend on the generic `SqlClient` tag, then provide the database layer:
124
125```typescript
126import * as SqlClient from "effect/sql/SqlClient";
127
128const makeUsers = Effect.gen(function* () {
129 const sql = yield* SqlClient.SqlClient;
130 return {
131 find: (id: number) => sql<User>`SELECT * FROM users WHERE id = ${id}`,
132 };
133});
134
135const users = yield* makeUsers.pipe(
136 Effect.provide(SQL.MySQLLayer({ url: connectionString })),
137);
138```
139
140The layer provides `SqlClient.SqlClient` and `@effect/sql-mysql2`'s `MysqlClient` from one per-execution pool. Other drivers can satisfy the generic service, but changing engines still requires compatible SQL and transaction semantics.
141
142## Provider setup
143
144Choose a MySQL-compatible database and deployment guide in [SQL databases](/sql/databases); the client above only needs a connection URL.
145
146## Where next
147
148Read [Migrations](/sql/effect-sql/migrations) to apply committed SQL files, or [Drizzle on MySQL](/sql/drizzle/mysql) for typed schemas over the same driver. [Connection lifecycle](/sql/effect-sql/lifecycle) covers per-request cleanup.