@devries-cameron20 / t3code Mirror · read-only
Fork 0

The best way to control Claude Code, Codex and more from any computer or phone

Mirrored from https://github.com/Camo67/t3code.git

Synced Nov 21, 04:45 UTC

main
mdx 148 lines 4.19 KB
Raw
1 ---
2 title: "Part 3: Query Postgres"
3 description: Create Prisma Postgres, bind its connection to Compute, and query it with Effect SQL.
4 sidebar:
5 order: 3
6 ---
7
8 import PackageManagers from "../../../../components/PackageManagers.astro";
9
10 Continue from [Part 2](/prisma/tutorial/part-2). Add an explicit Postgres
11 database and a read-only endpoint that returns the database's current time.
12 This query needs no tables or migrations.
13
14 ## Install the SQL client
15
16 <PackageManagers add="@alchemy.run/sql @effect/sql-pg" />
17
18 Alchemy's SQL helper uses the Effect Postgres client. Match its version to
19 the other Effect packages.
20
21 ## Define the database
22
23 Append to `src/Database.ts`, adding the Effect import at the top:
24
25 ```diff lang="typescript"
26 // src/Database.ts
27 import * as Prisma from "@alchemy.run/prisma";
28 +import * as Effect from "effect/Effect";
29
30 +export const Postgres = Prisma.Postgres(
31 + "Postgres",
32 + Effect.gen(function* () {
33 + const project = yield* Project;
34 + return { project, region: "eu-west-3" as const, branchGitName: "main" };
35 + }),
36 +);
37 ```
38
39 The database belongs to the existing Project's `main` branch. Keep the
40 Project definition from Part 1, including `createDatabase: false`.
41
42 ## Define a connection
43
44 ```diff lang="typescript"
45 // src/Database.ts
46 +export const Connection = Prisma.Connection(
47 + "Connection",
48 + Effect.gen(function* () {
49 + const database = yield* Postgres;
50 + return { database, name: "api" };
51 + }),
52 +);
53 ```
54
55 A Connection supplies the credentials that the API will use. Its database
56 dependency means you do not need to separately yield Postgres in the Stack.
57
58 ## Bind the connection to Compute
59
60 ```diff lang="typescript"
61 // src/Api.ts
62 -import { Project } from "./Database.ts";
63 +import { Connection, Project } from "./Database.ts";
64
65 Effect.gen(function* () {
66 + const db = yield* Prisma.Connect(Connection);
67 return {
68 fetch: Effect.gen(function* () {
69 // Keep the request handler from Part 2.
70 }),
71 };
72 - }),
73 + }).pipe(Effect.provide(Prisma.ConnectBinding)),
74 ```
75
76 Apply this change to the **runtime** Effect, the third argument to
77 `Prisma.Compute`, not to the properties Effect. The binding makes the
78 connection URL available at runtime without placing credentials in source.
79
80 ## Create the SQL client
81
82 ```diff lang="typescript"
83 // src/Api.ts
84 +import * as SQL from "@alchemy.run/sql/Postgres";
85
86 const db = yield* Prisma.Connect(Connection);
87 + const sql = yield* SQL.Postgres({ url: db.databaseUrl });
88 ```
89
90 The helper resolves the redacted URL and opens a connection lazily when a
91 request executes a query. The request scope closes its pool afterward.
92 Do not return the database URL from an HTTP handler or Stack output.
93
94 ## Query the database
95
96 Insert the new route after `/api/health` and before the final 404 response:
97
98 ```diff lang="typescript"
99 // src/Api.ts
100 + if (request.url === "/api/time") {
101 + const rows = yield* sql<{ time: string }>`SELECT current_timestamp::text AS time`;
102 + return yield* HttpServerResponse.json(rows[0]);
103 + }
104 return HttpServerResponse.text("Not found", { status: 404 });
105 ```
106
107 The timestamp comes from Postgres, not the API process.
108
109 ## Handle database failures
110
111 Attach a typed error handler to the `fetch` Effect:
112
113 ```diff lang="typescript"
114 // src/Api.ts
115 return HttpServerResponse.text("Not found", { status: 404 });
116 - }),
117 + }).pipe(
118 + Effect.catchTag("SqlError", () =>
119 + Effect.succeed(HttpServerResponse.text("Database unavailable", {
120 + status: 503,
121 + })),
122 + ),
123 + ),
124 ```
125
126 A failed SQL query returns HTTP 503 without exposing connection details.
127 Handling `SqlError` also satisfies the HTTP handler's typed error contract.
128
129 ## Deploy the database and API
130
131 <PackageManagers run="alchemy deploy" />
132
133 Alchemy creates the database and connection before deploying the API with
134 its binding. Existing resources retain their logical IDs.
135
136 ## Read the database time
137
138 ```sh
139 curl https://YOUR_API_HOST/api/time
140 ```
141
142 Expect a JSON object with a `time` string. `/api/health` continues to work
143 without querying Postgres, and `/missing` still returns HTTP 404.
144
145 ## Next
146
147 [Part 4: A Vite Frontend](/prisma/tutorial/part-4) adds a browser interface,
148 local frontend development, and cleanup.