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
| 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. |