Skip to content

Postgres

Drizzle on Postgres, end to end: a schema module, a Drizzle.Schema resource that generates migration SQL on deploy, a database resource that applies it, and a Worker or Fly Service that queries through Drizzle.Postgres.

Install the toolchain — all optional peers of alchemy:

Terminal window
bun add drizzle-orm @effect/sql-pg pg
bun add -d drizzle-kit

Define the schema

Section titled “Define the schema”

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

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


export const Users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
});


export const Posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  userId: integer("user_id")
    .notNull()
    .references(() => Users.id, { onDelete: "cascade" }),
  title: text("title").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 }),
  },
}));

Declare the schema resource and database

Section titled “Declare the schema resource and database”

Drizzle.Schema diffs the schema module on each deploy and writes pending migration SQL to out. Passing schema.out as the database’s migrations prop creates the dependency edge: generate first, apply second, in one alchemy deploy. On Neon:

src/db.ts
import * as Cloudflare from "alchemy/Cloudflare";
import * as Drizzle from "alchemy/Drizzle";
import * as Neon from "alchemy/Neon";
import * as Effect from "effect/Effect";


export const Db = Effect.gen(function* () {
  const schema = yield* Drizzle.Schema("app-schema", {
    schema: "./src/schema.ts",
    out: "./migrations",
  });


  const project = yield* Neon.Project("app-db");
  const branch = yield* Neon.Branch("app-branch", {
    project,
    migrations: schema,
  });


  return branch;
});


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

Planetscale.PostgresBranch and Fly.Postgres take the same migrations wiring. Register Drizzle.providers() alongside your cloud providers in the Stack. Migrations covers what the schema resource does — and does not — decide on your behalf.

Connect in a Worker

Section titled “Connect in a Worker”

Hyperdrive pools connections at the edge; Drizzle.Postgres takes its connection string:

src/api.ts
import * as Cloudflare from "alchemy/Cloudflare";
import * as Drizzle from "alchemy/Drizzle/Postgres";
import * as Effect from "effect/Effect";
import * as HttpServerResponse from "effect/unstable/http/HttpServerResponse";
import { Hyperdrive } from "./db.ts";
import { relations, Users } from "./schema.ts";


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


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

Nothing connects at init — the pool opens on the first query of an event, is reused for every query in that event, and closes when the event settles (see Connection lifecycle). Plan and deploy never open a connection.

Connect in a Fly Service

Section titled “Connect in a Fly Service”

Fly Managed Postgres uses the same migrations prop. Bind ConnectPostgres instead of Hyperdrive:

import * as Drizzle from "alchemy/Drizzle/Postgres";
import * as Fly from "alchemy/Fly";
import * as Effect from "effect/Effect";
import * as HttpServerResponse from "effect/unstable/http/HttpServerResponse";
import { Db } from "./db.ts";
import { relations, Users } from "./schema.ts";


export default class Api extends Fly.Service<Api>()(
  "Api",
  { app: Site, main: import.meta.url, port: 3000 },
  Effect.gen(function* () {
    const conn = yield* Fly.ConnectPostgres(Db);
    const db = yield* Drizzle.Postgres(conn.connectionString, {
      relations,
    });
    return {
      fetch: Effect.gen(function* () {
        const users = yield* db.select().from(Users);
        return yield* HttpServerResponse.json({ users });
      }),
    };
  }).pipe(Effect.provide(Fly.ConnectPostgresHttp)),
) {}

Queries are Effects

Section titled “Queries are Effects”

Every builder yields directly, with SqlError in the typed error channel:

const [created] = yield* db
  .insert(Users)
  .values({ name, email })
  .returning();


const [removed] = yield* db
  .delete(Users)
  .where(eq(Users.id, id))
  .returning();

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

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

Where next

Section titled “Where next”