sailfork / t3code-github / .repos/ alchemy-effect/ website/ src/ content/ docs/ cloudflare/ data/ drizzle.mdx

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

This walkthrough connects a Worker to Postgres through Hyperdrive; SQL owns the portable client APIs. For Cloudflare's SQLite database, use Drizzle on D1.

Install the client and generator

Keep the exact Drizzle prerelease pins together for the Effect integration; drizzle-kit is a development dependency.

Define the schema

// src/schema.ts
import { integer, pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";

export const Users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

export const Posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  userId: integer("user_id")
    .notNull()
    .references(() => Users.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
  body: text("body").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

This is an ordinary Postgres schema; it has no Worker-specific configuration.

Configure migration generation

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

export default defineConfig({
  schema: "./src/schema.ts",
  out: "./migrations",
  dialect: "postgresql",
});

The generator writes SQL and snapshots to migrations; it does not need database credentials.

Generate and review migrations

bunx drizzle-kit generate
git diff -- src/schema.ts migrations
git status --short
git add src/schema.ts drizzle.config.ts migrations
git diff --cached
git commit -m "Add database migration"

Generate whenever the schema changes, review the schema, SQL, and snapshots together, and commit them before deployment. The optional Drizzle.Schema resource is a separate integration, not part of this workflow.

Configure the database

Use Neon's database configuration to export NeonDb from src/Db.ts with migrations: "./migrations". That guide owns the project, branch, and connection options; PlanetScale configuration is the alternative below.

Add Hyperdrive

// src/Hyperdrive.ts
import * as Cloudflare from "@alchemy.run/cloudflare";
import * as Effect from "effect/Effect";
import { NeonDb } from "./Db.ts";

export const Hyperdrive = Effect.gen(function* () {
  const { branch } = yield* NeonDb;
  return yield* Cloudflare.Hyperdrive.Connection("app-hyperdrive", {
    origin: branch.origin,
    dev: branch.pooledOrigin,
  });
});

Hyperdrive pools the direct Neon origin; local development uses the pooled origin when it bypasses Hyperdrive.

Open the connection with Drizzle.Postgres

// src/Api.ts
import * as Cloudflare from "@alchemy.run/cloudflare";
import * as Drizzle from "@alchemy.run/drizzle/Postgres";
import * as Effect from "effect/Effect";
import * as HttpServerResponse from "effect/http/HttpServerResponse";
import { Hyperdrive } from "./Hyperdrive.ts";
import { Users } from "./schema.ts";

export default class Api extends Cloudflare.Worker<Api>()(
  "Api",
  {
    main: import.meta.url,
    compatibility: { flags: ["nodejs_compat"] },
  },
  Effect.gen(function* () {
    const hd = yield* Cloudflare.Hyperdrive.Connect(Hyperdrive);
    const db = yield* Drizzle.Postgres(hd.connectionString);

    return {
      fetch: Effect.gen(function* () {
        const users = yield* db.select().from(Users);
        return yield* HttpServerResponse.json(users);
      }),
    };
  }).pipe(Effect.provide(Cloudflare.Hyperdrive.ConnectBinding)),
) {}

nodejs_compat enables the Node socket and TLS APIs @effect/sql-pg uses, and ConnectBinding supplies the Worker's Hyperdrive binding. Queries yield directly as Effects and acquire their pool only within the request's scope.

Register the providers

// alchemy.run.ts
import * as Alchemy from "alchemy";
import * as Cloudflare from "@alchemy.run/cloudflare";
import * as Neon from "@alchemy.run/neon";
import * as Effect from "effect/Effect";
import * as Layer from "effect/Layer";
import Api from "./src/Api.ts";

export default Alchemy.Stack(
  "MyStack",
  {
    providers: Layer.mergeAll(Cloudflare.providers(), Neon.providers()),
    state: Alchemy.localState(),
  },
  Effect.gen(function* () {
    const api = yield* Api;
    return { url: api.url };
  }),
);

Only the runtime and database providers are needed; committed migration files do not require Drizzle.providers().

Deploy

bun alchemy deploy

The configured branch applies pending committed SQL before the Worker serves queries; deployment does not generate new migrations. Keep an existing drizzle-kit migrate workflow unless you deliberately choose Alchemy-managed application, as described in migration ownership.

Define relations for typed db.query

// src/schema.ts
+import { defineRelations } from "drizzle-orm";

+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,
+    }),
+  },
+}));

Append the relations after the table definitions; this metadata enables the relational query API without changing migration SQL.

Pass relations to Drizzle.Postgres

// src/Api.ts
-import { Users } from "./schema.ts";
+import { relations, Users } from "./schema.ts";

-const db = yield* Drizzle.Postgres(hd.connectionString);
+const db = yield* Drizzle.Postgres(hd.connectionString, { relations });

The returned client now exposes db.query.Users and db.query.Posts with typed relation names.

// src/Api.ts
-const users = yield* db.select().from(Users);
-return yield* HttpServerResponse.json(users);
+const user = yield* db.query.Users.findFirst({
+  where: { id: 1 },
+  with: { posts: true },
+});
+return yield* HttpServerResponse.json({ user });

with: { posts: true } includes the related posts in the inferred result type.

Iterate on the schema

bunx drizzle-kit generate
git add src/schema.ts migrations
git diff --cached
git commit -m "Update database schema"
bun alchemy deploy

Review each generated change before committing and deploy only those committed files. Reverting a TypeScript schema does not undo applied SQL; author and review a new migration or follow your database's restore procedure.

Use PlanetScale instead

// alchemy.run.ts
-import * as Neon from "@alchemy.run/neon";
+import * as Planetscale from "@alchemy.run/planetscale";

-    providers: Layer.mergeAll(Cloudflare.providers(), Neon.providers()),
+    providers: Layer.mergeAll(Cloudflare.providers(), Planetscale.providers()),

Use the PlanetScale guide to export PlanetscaleDb with committed migrations; it owns both Postgres role and MySQL password configuration.

Connect a PlanetScale Postgres role

// src/Hyperdrive.ts
import * as Cloudflare from "@alchemy.run/cloudflare";
import * as Effect from "effect/Effect";
import { PlanetscaleDb } from "./Db.ts";

export const Hyperdrive = Effect.gen(function* () {
  const { role } = yield* PlanetscaleDb;
  return yield* Cloudflare.Hyperdrive.Connection("app-hyperdrive", {
    origin: role.origin,
    dev: role.pooledOrigin,
    caching: { disabled: true },
  });
});

The Worker, Postgres schema, and queries remain unchanged.

Connect a PlanetScale MySQL password

// Inside the Hyperdrive effect, replacing the Postgres role configuration:
const { password } = yield* PlanetscaleDb;
return yield* Cloudflare.Hyperdrive.Connection("app-hyperdrive", {
  origin: password.origin,
  caching: { disabled: true },
});

Select the MySQL resource variant in the PlanetScale guide; there is no pooled password origin.

Select the MySQL client

bun add @effect/sql-mysql2
// src/Api.ts
-import * as Drizzle from "@alchemy.run/drizzle/Postgres";
+import * as Drizzle from "@alchemy.run/drizzle/MySQL";

-const db = yield* Drizzle.Postgres(hd.connectionString, { relations });
+const db = yield* Drizzle.MySQL(hd.connectionString, { relations });

Use the MySQL schema and generator configuration, then generate, review, and commit MySQL migrations before deploying. The client uses Workers-safe defaults; MySQL-specific query differences stay in the portable client guide.

Where to from here

Use Neon or PlanetScale for database service behavior and SQL databases to discover other providers. Branch from a shared database covers the Worker deployment pattern for preview environments.