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

main
mdx 135 lines 4.52 KB
Raw
1 ---
2 title: MySQL
3 description: 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
6 import PackageManagers from "../../../../components/PackageManagers.astro";
7
8 export const deps = `@alchemy.run/sql @effect/sql-mysql2`;
9
10 `SQL.MySQL` provides tagged-template queries over a MySQL connection. Queries are Effects with typed `SqlError` failures, interruption, and tracing.
11
12 Install `@alchemy.run/sql` and the Effect MySQL client:
13
14 <PackageManagers add={deps} />
15
16 ## Connect
17
18 ```typescript
19 import * as SQL from "@alchemy.run/sql/MySQL";
20 import * as Effect from "effect/Effect";
21 import type * as Redacted from "effect/Redacted";
22
23 export const makeQueries = <E, R>(
24 connectionString:
25 | Redacted.Redacted<string>
26 | Effect.Effect<Redacted.Redacted<string>, E, R>,
27 ) =>
28 Effect.gen(function* () {
29 const sql = yield* SQL.MySQL({ url: connectionString });
30 return {
31 listUsers: () => sql<{ id: number; name: string }>`SELECT id, name FROM users`,
32 };
33 });
34 ```
35
36 `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).
37
38 ## Workers defaults
39
40 ```typescript
41 // Applied by default only when workerd is detected:
42 const workersDefaults = {
43 disablePreparedStatements: true,
44 poolConfig: { disableEval: true },
45 };
46 ```
47
48 These 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#bind-hyperdrive-to-your-worker) for Worker connection setup.
49
50 ## Override driver options
51
52 `SQL.MySQL` parses URL fields and query parameters into driver configuration, including `poolConfig`. Explicit options override parsed values and detected defaults:
53
54 ```typescript
55 const sql = yield* SQL.MySQL({
56 url: connectionString,
57 // For a proxy that lacks COM_STMT_PREPARE.
58 disablePreparedStatements: true,
59 // For a direct TLS connection.
60 poolConfig: { ssl: { rejectUnauthorized: true } },
61 });
62 ```
63
64 ## Queries
65
66 Interpolated values are parameters, never string concatenation:
67
68 ```typescript
69 const user = yield* sql`SELECT * FROM users WHERE id = ${id}`;
70
71 yield* sql`INSERT INTO users ${sql.insert({ name, email })}`;
72
73 const rows = yield* sql`
74 SELECT * FROM users WHERE id IN ${sql.in(ids)}
75 `;
76 ```
77
78 Rows are plain objects; supply a row type with `sql<Row>`. The
79 [`effect/sql/Statement`](https://effect.website) API also provides
80 fragments, `sql.csv`, `sql.and`, and identifier escaping (backticks on MySQL).
81
82 ## Errors
83
84 Failures surface as `SqlError` in the typed error channel:
85
86 ```typescript
87 const users = yield* sql`SELECT * FROM users`.pipe(
88 Effect.catchTag("SqlError", (e) =>
89 Effect.succeed([]).pipe(Effect.tap(() => Effect.logWarning(e))),
90 ),
91 );
92 ```
93
94 ## Transactions
95
96 Wrap a group of queries in `sql.withTransaction` — the whole effect
97 commits or rolls back together:
98
99 ```typescript
100 yield* sql.withTransaction(
101 Effect.gen(function* () {
102 yield* sql`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${from}`;
103 yield* sql`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${to}`;
104 }),
105 );
106 ```
107
108 ## Provide as a service
109
110 Depend on the generic `SqlClient` tag, then provide the database layer:
111
112 ```typescript
113 import * as SqlClient from "effect/sql/SqlClient";
114
115 const makeUsers = Effect.gen(function* () {
116 const sql = yield* SqlClient.SqlClient;
117 return {
118 find: (id: number) => sql<User>`SELECT * FROM users WHERE id = ${id}`,
119 };
120 });
121
122 const users = yield* makeUsers.pipe(
123 Effect.provide(SQL.MySQLLayer({ url: connectionString })),
124 );
125 ```
126
127 The 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.
128
129 ## Provider setup
130
131 Choose a MySQL-compatible database and deployment guide in [SQL databases](/sql/databases); the client above only needs a connection URL.
132
133 ## Where next
134
135 Read [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.