sailfork / t3code-github / .repos/ alchemy-effect/ website/ src/ content/ docs/ sql/ drizzle/ mysql.mdx

import PackageManagers from "../../../../components/PackageManagers.astro";
export const deps = `@alchemy.run/drizzle drizzle-orm@1.0.0-rc.5 @effect/sql-mysql2`;
export const devDeps = `drizzle-kit@1.0.0-rc.5`;

Drizzle.MySQL provides typed, Effect-native queries for compatible MySQL databases, independently of where your application runs. Choose a database in SQL databases and supply its connection through your runtime's binding or configuration.

Install the optional dependencies:

Define the schema

Drizzle schemas are plain TypeScript modules using the mysql-core column builders:

// src/schema.ts
import { defineRelations } from "drizzle-orm";
import { int, mysqlTable, varchar } from "drizzle-orm/mysql-core";

export const Users = mysqlTable("users", {
  id: int("id").primaryKey().autoincrement(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 255 }).notNull(),
});

export const Posts = mysqlTable("posts", {
  id: int("id").primaryKey().autoincrement(),
  userId: int("user_id").notNull(),
  title: varchar("title", { length: 255 }).notNull(),
});

export const relations = defineRelations({ Users, Posts }, (t) => ({
  Users: { posts: t.many.Posts() },
  Posts: {
    user: t.one.Users({ from: t.Posts.userId, to: t.Users.id }),
  },
}));

Connect

// src/queries.ts
import * as Drizzle from "@alchemy.run/drizzle/MySQL";
import * as Effect from "effect/Effect";
import type * as Redacted from "effect/Redacted";
import { relations, Users } from "./schema.ts";

export const makeQueries = <E, R>(
  connectionString: Effect.Effect<Redacted.Redacted<string>, E, R>,
) =>
  Effect.gen(function* () {
    const db = yield* Drizzle.MySQL(connectionString, { relations });
    return {
      listUsers: () => db.select().from(Users),
    };
  });

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.

Configure the driver

const db = yield* Drizzle.MySQL(connectionString, {
  relations,
  client: { poolConfig: { ssl: { rejectUnauthorized: true } } },
});

Pass pool options through config.client, including TLS settings for direct connections. Explicit options override the driver's detected defaults.

Connect in a Worker

The Cloudflare walkthrough supplies Hyperdrive's connection string; PlanetScale owns database and password configuration. On Workers, the client selects text-protocol queries and eval-free row parsers; see Workers defaults.

Queries are Effects

Every builder yields directly, with SqlError in the typed error channel. MySQL has no RETURNING clause — inserts report generated ids via $returningId(), and upserts use onDuplicateKeyUpdate:

import { eq } from "drizzle-orm";

const [{ id }] = yield* db
  .insert(Users)
  .values({ name, email })
  .$returningId();

yield* db
  .insert(Users)
  .values({ id, name, email })
  .onDuplicateKeyUpdate({ set: { name } });

yield* db.delete(Users).where(eq(Users.id, id));

Because relations was passed to Drizzle.MySQL, the typed db.query.* API is available:

const user = yield* db.query.Users.findFirst({
  where: { id },
  with: { posts: true },
});

Generate and review migrations

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/schema.ts",
  out: "./migrations",
  dialect: "mysql",
});
bunx drizzle-kit generate
git add src/schema.ts drizzle.config.ts migrations
git diff --cached
git commit -m "Add database migration"

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 covers application ownership: Drizzle.Schema is optional, and alchemy deploy does not replace an existing drizzle-kit migrate workflow.

Where next

Choose a MySQL-compatible database and deployment guide in SQL databases. For tagged-template queries without an ORM, use Effect SQL: MySQL.