1---
2title: Postgres
3description: SQL.Postgres turns a connection string into an @effect/sql-pg client — tagged-template queries with typed errors, one pool per execution.
4---
5
6import { Code, Tabs, TabItem } from "@astrojs/starlight/components";
7
8export const deps = `@effect/sql-pg pg`;
9
10`SQL.Postgres` provides tagged-template queries over a Postgres connection. Queries are Effects with typed `SqlError` failures, interruption, and tracing.
11
12Install the optional Postgres driver:
13
14<Tabs syncKey="pkgManager">
15 <TabItem label="bun" icon="bun">
16 <Code code={`bun add ${deps}`} lang="sh" />
17 </TabItem>
18 <TabItem label="npm" icon="npm">
19 <Code code={`npm install ${deps}`} lang="sh" />
20 </TabItem>
21 <TabItem label="pnpm" icon="pnpm">
22 <Code code={`pnpm add ${deps}`} lang="sh" />
23 </TabItem>
24 <TabItem label="yarn" icon="seti:yarn">
25 <Code code={`yarn add ${deps}`} lang="sh" />
26 </TabItem>
27</Tabs>
28
29## Connect
30
31```typescript
32import * as SQL from "alchemy/SQL/Postgres";
33import * as Effect from "effect/Effect";
34import type * as Redacted from "effect/Redacted";
35
36export const makeQueries = <E, R>(
37 connectionString:
38 | Redacted.Redacted<string>
39 | Effect.Effect<Redacted.Redacted<string>, E, R>,
40) =>
41 Effect.gen(function* () {
42 const sql = yield* SQL.Postgres({ url: connectionString });
43 return {
44 listUsers: () => sql<{ id: number; name: string }>`SELECT id, name FROM users`,
45 };
46 });
47```
48
49`url` accepts a redacted URL or an Effect resolving one, such as `Config.Redacted("DATABASE_URL")` or a runtime binding's `connectionString`. Other `@effect/sql-pg` pool options pass through. Call queries within a request or an explicit `Effect.scoped` block: the pool opens lazily and closes with that [scope](/sql/effect-sql/lifecycle).
50
51## Queries
52
53Interpolated values are parameters, never string concatenation:
54
55```typescript
56const user = yield* sql`SELECT * FROM users WHERE id = ${id}`;
57
58yield* sql`INSERT INTO users ${sql.insert({ name, email })}`;
59
60const rows = yield* sql`
61 SELECT * FROM users WHERE id IN ${sql.in(ids)}
62`;
63```
64
65Rows are plain objects; supply a row type with `sql<Row>`. The
66[`effect/sql/Statement`](https://effect.website) API also provides
67fragments, `sql.csv`, `sql.and`, and identifier escaping.
68
69## Errors
70
71Failures surface as `SqlError` in the typed error channel:
72
73```typescript
74const users = yield* sql`SELECT * FROM users`.pipe(
75 Effect.catchTag("SqlError", (e) =>
76 Effect.succeed([]).pipe(Effect.tap(() => Effect.logWarning(e))),
77 ),
78);
79```
80
81## Transactions
82
83Wrap a group of queries in `sql.withTransaction` — the whole effect
84commits or rolls back together:
85
86```typescript
87yield* sql.withTransaction(
88 Effect.gen(function* () {
89 yield* sql`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${from}`;
90 yield* sql`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${to}`;
91 }),
92);
93```
94
95## Provide as a service
96
97Depend on the generic `SqlClient` tag, then provide the database layer:
98
99```typescript
100import * as SqlClient from "effect/sql/SqlClient";
101
102const makeUsers = Effect.gen(function* () {
103 const sql = yield* SqlClient.SqlClient;
104 return {
105 find: (id: number) => sql<User>`SELECT * FROM users WHERE id = ${id}`,
106 };
107});
108
109const users = yield* makeUsers.pipe(
110 Effect.provide(SQL.PostgresLayer({ url: connectionString })),
111);
112```
113
114The layer provides `SqlClient.SqlClient` and `@effect/sql-pg`'s `PgClient` from one per-execution pool. Other drivers can satisfy the generic service, but changing engines still requires compatible SQL and transaction semantics.
115
116## Provider setup
117
118Choose a database and deployment guide in [SQL databases](/sql/databases); the client above only needs a connection URL. [Hyperdrive](/cloudflare/data/hyperdrive#use-effect-sql) and [Fly Managed Postgres](/fly/data/postgres) cover runtime-specific bindings.
119
120## Where next
121
122Read [Migrations](/sql/effect-sql/migrations) to apply committed SQL files, or [Drizzle on Postgres](/sql/drizzle/postgres) for typed schemas over the same driver. [Connection lifecycle](/sql/effect-sql/lifecycle) covers per-request cleanup.