The best way to control Claude Code, Codex and more from any computer or phone
| 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. |