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

import PackageManagers from "../../../../components/PackageManagers.astro";
export const deps = `@effect/sql-d1`;

SQL.D1 opens an @effect/sql-d1 client over a Cloudflare D1 binding. Queries are Effects: they carry SqlError in the error channel, participate in interruption, and trace like everything else in your program.

Install the driver:

Connect

Declare the database, bind it with Cloudflare.D1.QueryDatabase, and hand the client to SQL.D1:

// src/db.ts
import * as Cloudflare from "@alchemy.run/cloudflare";

export const Database = Cloudflare.D1.Database("Database");
// src/api.ts
import * as Cloudflare from "@alchemy.run/cloudflare";
import * as SQL from "@alchemy.run/cloudflare/D1/SQL";
import * as Effect from "effect/Effect";
import * as HttpServerResponse from "effect/http/HttpServerResponse";
import { Database } from "./db.ts";

export default class Api extends Cloudflare.Worker<Api>()(
  "Api",
  { main: import.meta.url },
  Effect.gen(function* () {
    const d1 = yield* Cloudflare.D1.QueryDatabase(Database);
    const sql = yield* SQL.D1(d1);

    return {
      fetch: Effect.gen(function* () {
        const users = yield* sql`SELECT * FROM users`;
        return yield* HttpServerResponse.json({ users });
      }),
    };
  }).pipe(Effect.provide(Cloudflare.D1.QueryDatabaseBinding)),
) {}

The client (and its prepared-statement cache) is built lazily on the first query of an execution and torn down when the event settles — see Connection lifecycle.

Queries

Interpolated values are parameters, never string concatenation:

const user = yield* sql`SELECT * FROM users WHERE id = ${id}`;

yield* sql`INSERT INTO users ${sql.insert({ name, email })}`;

const rows = yield* sql`
  SELECT * FROM users WHERE id IN ${sql.in(ids)}
`;

Rows come back as plain objects typed by your annotation: sql<{ id: number; name: string }>\...``.

Errors

Failures surface as SqlError in the typed error channel:

const users = yield* sql`SELECT * FROM users`.pipe(
  Effect.catchTag("SqlError", (e) =>
    Effect.succeed([]).pipe(Effect.tap(() => Effect.logWarning(e))),
  ),
);

Batches instead of transactions

D1 has no session transactions, so sql.withTransaction and streaming queries are unavailable on this driver. For atomic multi-statement writes, use the native binding's batch through the same QueryDatabase client — statements execute sequentially in one round trip and roll back together on failure:

yield* d1.batch([
  d1.prepare("UPDATE accounts SET balance = balance - ?1 WHERE id = ?2").bind(amount, from),
  d1.prepare("UPDATE accounts SET balance = balance + ?1 WHERE id = ?2").bind(amount, to),
]);

Provide as a service

For services that shouldn't know which database they run on, depend on the generic SqlClient tag and provide the database with SQL.D1Layer:

import * as SqlClient from "effect/sql/SqlClient";

const makeUsers = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient;
  return {
    find: (id: number) => sql<User>`SELECT * FROM users WHERE id = ${id}`,
  };
});

const users = yield* makeUsers.pipe(Effect.provide(SQL.D1Layer(d1)));

The layer provides SqlClient.SqlClient and @effect/sql-d1's D1Client from one per-execution client. Other drivers can satisfy the generic service, but changing engines still requires compatible SQL and transaction semantics.

Local dev

alchemy dev runs the same Worker against a local D1 database via miniflare — SQL.D1 works unchanged because it resolves whatever D1Database binding the runtime provides.

Where next

Cloudflare setup and D1 cover deployment and database behavior; SQL databases compares D1 with other engines. Read Migrations for committed SQL application, Connection lifecycle for cleanup, or Drizzle on D1 for typed schemas.