1import * as Effect from "effect/Effect";
2import type * as HttpServerRequest from "effect/http/HttpServerRequest";
3import * as HttpServerResponse from "effect/http/HttpServerResponse";
4import * as Sql from "effect/sql/SqlClient";
5
6/**
7 * Dialect-agnostic route handlers exercising the full `SqlClient` constructor
8 * surface through an alchemy `SQL.*` client (a `proxyChain`-wrapped client,
9 * so every helper call below also regression-tests deferred-proxy replay —
10 * nested fragments used to be handed to the driver as opaque proxies).
11 *
12 * Each fixture worker defines an init effect that pulls its own bindings,
13 * builds the clients, and calls {@link makeSqlRoutes} to get the handler
14 * interface; `fetch` just delegates to it.
15 */
16export interface SqlRoutesOptions {
17 /** The `proxyChain`-wrapped client under test. */
18 readonly sql: Sql.SqlClient;
19 /**
20 * The users service built against the generic `SqlClient` tag with
21 * `SQL.D1Layer` / `SQL.PostgresLayer` provided (see {@link makeLayerUsers})
22 * — proves the Layer wiring end-to-end.
23 */
24 readonly layerUsers: LayerUsers;
25 /** Dialect-specific `CREATE TABLE IF NOT EXISTS` DDL for {@link table}. */
26 readonly ddl: string;
27 /** Users table name — always referenced via the `sql(table)` identifier helper. */
28 readonly table: string;
29}
30
31export type UserRow = {
32 id: number;
33 name: string;
34 email: string;
35};
36
37export interface LayerUsers {
38 readonly list: Effect.Effect<ReadonlyArray<UserRow>, unknown>;
39}
40
41/**
42 * A tiny cloud-agnostic service written against the generic `SqlClient` tag —
43 * the idiomatic consumer of `SQL.D1Layer` / `SQL.PostgresLayer`. Fixtures
44 * build it with the dialect's layer provided:
45 *
46 * ```typescript
47 * const layerUsers = yield* makeLayerUsers(TABLE).pipe(
48 * Effect.provide(SQL.D1Layer(d1)),
49 * );
50 * ```
51 */
52export const makeLayerUsers = (
53 table: string,
54): Effect.Effect<LayerUsers, never, Sql.SqlClient> =>
55 Effect.gen(function* () {
56 const sql = yield* Sql.SqlClient;
57 return {
58 list: sql<UserRow>`SELECT id, name, email FROM ${sql(table)} ORDER BY id`,
59 };
60 });
61
62export interface SqlRoutes {
63 /**
64 * Handle one request against the shared route table. Returns the response,
65 * or `undefined` when the route is not part of the shared surface (so the
66 * caller can fall through to its own routes / 404).
67 */
68 readonly handle: (
69 request: HttpServerRequest.HttpServerRequest,
70 ) => Effect.Effect<
71 HttpServerResponse.HttpServerResponse | undefined,
72 unknown
73 >;
74}
75
76export const makeSqlRoutes = (options: SqlRoutesOptions): SqlRoutes => {
77 const { sql, layerUsers, ddl, table } = options;
78
79 return {
80 handle: Effect.fn(function* (request) {
81 const [path, query] = request.url.split("?") as [
82 string,
83 string | undefined,
84 ];
85 const params = new URLSearchParams(query);
86
87 // POST /init — `sql.unsafe` (raw DDL, no params).
88 if (request.method === "POST" && path === "/init") {
89 yield* sql.unsafe(ddl);
90 return yield* HttpServerResponse.json({ ok: true });
91 }
92
93 // POST /reset — identifier helper `sql(table)`.
94 if (request.method === "POST" && path === "/reset") {
95 yield* sql`DELETE FROM ${sql(table)}`;
96 return yield* HttpServerResponse.json({ ok: true });
97 }
98
99 // POST /users — single-row `sql.insert(row)` nested in an outer template
100 // (the original proxyChain regression), read back with `sql.and` over
101 // nested `sql\`...\`` fragments.
102 if (request.method === "POST" && path === "/users") {
103 const row = (yield* request.json) as UserRow;
104 yield* sql`INSERT INTO ${sql(table)} ${sql.insert(row)}`;
105 const rows = yield* sql`
106 SELECT id, name, email FROM ${sql(table)}
107 WHERE ${sql.and([sql`name = ${row.name}`, sql`email = ${row.email}`])}
108 `;
109 return yield* HttpServerResponse.json({ rows });
110 }
111
112 // POST /users/batch — multi-row `sql.insert(rows)`, counted back with
113 // `sql.literal` (raw select-list text).
114 if (request.method === "POST" && path === "/users/batch") {
115 const rows = (yield* request.json) as UserRow[];
116 yield* sql`INSERT INTO ${sql(table)} ${sql.insert(rows)}`;
117 const [count] = yield* sql<{
118 n: number;
119 }>`SELECT ${sql.literal("COUNT(*) AS n")} FROM ${sql(table)}`;
120 return yield* HttpServerResponse.json({ count: Number(count!.n) });
121 }
122
123 // PUT /users — single-row `sql.update(row, omit)` keyed by a bound id.
124 if (request.method === "PUT" && path === "/users") {
125 const row = (yield* request.json) as UserRow;
126 yield* sql`
127 UPDATE ${sql(table)}
128 SET ${sql.update(row, ["id"])}
129 WHERE id = ${row.id}
130 `;
131 const rows =
132 yield* sql`SELECT id, name, email FROM ${sql(table)} WHERE id = ${row.id}`;
133 return yield* HttpServerResponse.json({ rows });
134 }
135
136 // GET /users/in?ids=1,2 — `sql.in(column, values)`.
137 if (request.method === "GET" && path === "/users/in") {
138 const ids = (params.get("ids") ?? "").split(",").map(Number);
139 const rows = yield* sql`
140 SELECT id, name, email FROM ${sql(table)}
141 WHERE ${sql.in("id", ids)}
142 ORDER BY id
143 `;
144 return yield* HttpServerResponse.json({ rows });
145 }
146
147 // GET /users/filter?name=&email=&id= — `sql.or` over a nested `sql.and`
148 // and a plain fragment (fragments nested two levels deep in arrays).
149 if (request.method === "GET" && path === "/users/filter") {
150 const rows = yield* sql`
151 SELECT id, name, email FROM ${sql(table)}
152 WHERE ${sql.or([
153 sql.and([
154 sql`name = ${params.get("name")}`,
155 sql`email = ${params.get("email")}`,
156 ]),
157 sql`id = ${Number(params.get("id"))}`,
158 ])}
159 ORDER BY id
160 `;
161 return yield* HttpServerResponse.json({ rows });
162 }
163
164 // GET /users/ordered — prefix-form `sql.csv("ORDER BY", ...)` mixing a
165 // raw-string clause with a fragment clause.
166 if (request.method === "GET" && path === "/users/ordered") {
167 const rows = yield* sql`
168 SELECT id, name FROM ${sql(table)}
169 ${sql.csv("ORDER BY", [sql`name DESC`, "id ASC"])}
170 `;
171 return yield* HttpServerResponse.json({ rows });
172 }
173
174 // GET /layer/users — the same table through the generic `SqlClient`
175 // service (see makeLayerUsers).
176 if (request.method === "GET" && path === "/layer/users") {
177 const rows = yield* layerUsers.list;
178 return yield* HttpServerResponse.json({ rows });
179 }
180
181 return undefined;
182 }),
183 };
184};