Coming from Drizzle or Prisma

import { PgliteClient } from "@effect/sql-pglite"
import { Effect, Layer, Schema } from "effect"
import * as Db from "@maple-dev/effect-orm/database"
import * as PG from "@maple-dev/effect-orm/postgres"
import * as S from "@maple-dev/effect-orm/schema"

const Profile = Schema.Struct({ city: Schema.String })

// model User { id String @id @default(uuid()); email String @unique; orgId String @map("org_id") ... }
export const Users = PG.table("users", {
	columns: {
		id: PG.column(PG.uuid, { defaultExpr: "gen_random_uuid()" }),
		orgId: PG.column(PG.text, { name: "org_id" }),
		email: PG.column(PG.text, { unique: true }),
		profile: PG.jsonb(Profile),
		updatedAt: PG.column(PG.timestamptz, { name: "updated_at", defaultExpr: "now()" }),
	},
	primaryKey: ["id"],
	tenantColumn: "orgId",
})

export const Posts = PG.table("posts", {
	columns: {
		id: PG.int4,
		orgId: PG.column(PG.text, { name: "org_id" }),
		authorEmail: PG.column(PG.text, { name: "author_email" }),
		published: PG.bool,
	},
	primaryKey: ["id"],
	foreignKeys: [PG.foreignKey({ columns: ["authorEmail"], references: Users, foreignColumns: ["email"], onDelete: "cascade" })],
	tenantColumn: "orgId",
})

const org = "org_1"

// prisma.user.findUnique({ where: { email } })
const findByEmail = (email: string) =>
	Db.run(
		PG.from(Users)
			.select("id", "email")
			.where(($) => [$.orgId.eq(org), $.email.eq(email)])
			.limit(1),
	).pipe(Effect.map((rows) => rows[0]))

export const program = Effect.gen(function* () {
	for (const statement of S.renderPgSchema(yield* S.pgEntitiesOf([Users, Posts])))
		yield* Db.execute({ sql: statement, parameters: [] })

	// prisma.user.createMany({ data: [...] })
	yield* Db.run(
		PG.insertInto(Users).values([
			{ orgId: org, email: "ada@example.com", profile: { city: "London" } },
			{ orgId: org, email: "bob@example.com", profile: { city: "Paris" } },
		]),
	)
	yield* Db.run(
		PG.insertInto(Posts).values([
			{ id: 1, orgId: org, authorEmail: "ada@example.com", published: true },
			{ id: 2, orgId: org, authorEmail: "ada@example.com", published: false },
		]),
	)

	const found = yield* findByEmail("ada@example.com")

	// prisma.user.findMany({ where: { email: { contains: "EXAMPLE", mode: "insensitive" } }, orderBy, take })
	const insensitive = yield* Db.run(
		PG.from(Users)
			.select("email")
			.where(($) => [$.orgId.eq(org), $.email.ilike("%EXAMPLE%")])
			.orderBy(["email", "asc"])
			.limit(10),
	)

	// include: { _count: { select: { posts: true } } }
	const counts = yield* Db.run(
		PG.from(Users, "u")
			.leftJoin(Posts, "p", (u, p) => u.email.eq(p.authorEmail).and(u.orgId.eq(p.orgId)))
			.select(($) => ({ email: $.email, posts: PG.countDistinct($.p.id) }))
			.where(($) => [$.orgId.eq(org)])
			.groupBy("email")
			.orderBy(["email", "asc"]),
	)

	// where: { posts: { some: { published: true } } }
	const published = PG.from(Posts)
		.select(($) => ({ author: $.authorEmail }))
		.where(($) => [$.orgId.eq(org), $.published.eq(true)])
	const withPublished = yield* Db.run(
		PG.from(Users)
			.select("email")
			.where(($) => [$.orgId.eq(org), PG.inSubquery($.email, published)]),
	)

	// JSON field: select profile->>'city'
	const cities = yield* Db.run(
		PG.from(Users)
			.select(($) => ({ email: $.email, city: PG.jsonText($.profile, "city") }))
			.where(($) => [$.orgId.eq(org)])
			.orderBy(["email", "asc"]),
	)

	// cursor pagination: findMany({ cursor: { email }, skip: 1, take: 1, orderBy: { email: "asc" } })
	const page = yield* Db.run(
		PG.from(Users)
			.select("email")
			.where(($) => [$.orgId.eq(org), $.email.gt("ada@example.com")])
			.orderBy(["email", "asc"])
			.limit(1),
	)

	// upsert
	yield* Db.run(
		PG.insertInto(Users)
			.values({ orgId: org, email: "bob@example.com", profile: { city: "Rome" } })
			.onConflictDoUpdate({
				target: ["email"],
				set: PG.excluded(["profile"]),
				// The target is not tenant-keyed, so pin the row it would update to this tenant.
				where: (existing) => existing.orgId.eq(org),
			}),
	)

	// updateMany, with @updatedAt written by hand
	const unpublished = yield* Db.run(
		PG.update(Posts)
			.set({ published: false })
			.where(($) => [$.orgId.eq(org), $.authorEmail.eq("ada@example.com")])
			.returning("id"),
	)
	yield* Db.run(
		PG.update(Users)
			.set(() => ({ updatedAt: PG.now() }))
			.where(($) => [$.orgId.eq(org), $.email.eq("bob@example.com")]),
	)

	// P2002: create with a taken email
	const duplicate = yield* Db.run(
		PG.insertInto(Users).values({ orgId: org, email: "ada@example.com", profile: { city: "Oslo" } }),
	).pipe(
		Effect.as("created" as const),
		Effect.catchTag("@maple-dev/effect-orm/DatabaseError", (error) =>
			error.reason === "UniqueViolation" ? Effect.succeed("taken" as const) : Effect.fail(error),
		),
	)

	// $transaction(async (tx) => ...) and delete
	yield* Effect.gen(function* () {
		yield* Db.run(PG.deleteFrom(Posts).where(($) => [$.orgId.eq(org), $.id.eq(2)]))
		yield* Db.run(PG.deleteFrom(Users).where(($) => [$.orgId.eq(org), $.email.eq("bob@example.com")]))
	}).pipe(Db.transaction())

	// $queryRaw
	const raw = yield* Db.query(
		Db.sql`SELECT count(*)::int AS n FROM users WHERE org_id = ${org}`,
		Schema.Struct({ n: Schema.Number }),
	)

	return {
		found: found?.email,
		insensitive: insensitive.map((row) => row.email),
		counts,
		withPublished: withPublished.map((row) => row.email),
		cities,
		page: page.map((row) => row.email),
		unpublished: unpublished.map((row) => row.id),
		duplicate,
		usersLeft: raw[0]?.n,
	}
})

const DatabaseLive = Db.layerSqlClient({ dialect: PG.postgresDialect, requireTenantScope: true }).pipe(
	Layer.provideMerge(PgliteClient.layer({ postgresqlconf: "timezone = 'UTC'" })),
)

export const result = await Effect.runPromise(program.pipe(Effect.provide(DatabaseLive)))

import { Effect, Layer } from "effect"
import * as Db from "@maple-dev/effect-orm/database"
import * as PG from "@maple-dev/effect-orm/postgres"

export const Tours = PG.table("tours", {
	columns: { id: PG.int4, orgId: PG.column(PG.text, { name: "org_id" }), unit: PG.text },
	primaryKey: ["id"],
	tenantColumn: "orgId",
})

// One request: read the org's tours. The org comes from the verified session in a real
// Worker; a header keeps the sketch short.
const listTours = (request: Request) =>
	Effect.gen(function* () {
		const org = request.headers.get("x-org-id")
		if (org === null) return new Response("missing org", { status: 401 })
		const tours = yield* Db.run(
			PG.from(Tours)
				.select("id", "unit")
				.where(($) => [$.orgId.eq(org)])
				.orderBy(["id", "asc"]),
		)
		return Response.json(tours)
	})

// The Worker's fetch handler. `database` builds a Database for one request: on Workers a
// connection must not outlive the request that opened it.
export const makeWorker = <Env>(database: (env: Env) => Layer.Layer<Db.Database, unknown>) => ({
	fetch: (request: Request, env: Env): Promise<Response> =>
		listTours(request).pipe(
			Effect.provide(database(env)),
			Effect.catchCause(() => Effect.succeed(new Response("database error", { status: 500 }))),
			Effect.runPromise,
		),
})

import { PGlite } from "@electric-sql/pglite"
import { PgliteClient } from "@effect/sql-pglite"
import { Layer } from "effect"
import * as Db from "@maple-dev/effect-orm/database"
import * as PG from "@maple-dev/effect-orm/postgres"
import { makeWorker } from "./cafe-worker"

// One PGlite instance plays the database behind Hyperdrive. Each request opens its own
// client on it, as the Worker does with the Hyperdrive connection string.
const origin = new PGlite()
await origin.exec(`
	CREATE TABLE tours (id int4 PRIMARY KEY, org_id text NOT NULL, unit text NOT NULL);
	INSERT INTO tours VALUES (1, 'org_1', '4B'), (2, 'org_2', '7A'), (3, 'org_1', '2C');
`)

const worker = makeWorker((env: { readonly origin: PGlite }) =>
	Db.layerSqlClient({ dialect: PG.postgresDialect, requireTenantScope: true }).pipe(
		Layer.provide(PgliteClient.layer({ liveClient: env.origin })),
	),
)

const get = (headers: Record<string, string>) =>
	worker.fetch(new Request("https://cafe.example/tours", { headers }), { origin })

export const org1 = await (await get({ "x-org-id": "org_1" })).json()
// [{ id: 1, unit: "4B" }, { id: 3, unit: "2C" }]
export const anonymous = (await get({})).status
// 401
await origin.close()