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