1---
2title: Postgres
3description: Provider-neutral Postgres schemas, Effect-native Drizzle queries, and connections for Workers, Lambda, and servers.
4---
5
6import { Code, Tabs, TabItem } from "@astrojs/starlight/components";
7
8export const deps = `drizzle-orm@1.0.0-rc.5-ab785fc @effect/sql-pg pg`;
9export const devDeps = `drizzle-kit@1.0.0-rc.5-ab785fc`;
10
11`Drizzle.Postgres` provides typed, Effect-native queries for any compatible Postgres connection, whether your application runs on Workers, Lambda, or a server. Choose a database in [SQL databases](/sql/databases) and supply its connection through your runtime's binding or configuration.
12
13Install the optional dependencies:
14
15<Tabs syncKey="pkgManager">
16 <TabItem label="bun" icon="bun">
17 <Code code={`bun add ${deps}\nbun add -d ${devDeps}`} lang="sh" />
18 </TabItem>
19 <TabItem label="npm" icon="npm">
20 <Code code={`npm install ${deps}\nnpm install -D ${devDeps}`} lang="sh" />
21 </TabItem>
22 <TabItem label="pnpm" icon="pnpm">
23 <Code code={`pnpm add ${deps}\npnpm add -D ${devDeps}`} lang="sh" />
24 </TabItem>
25 <TabItem label="yarn" icon="seti:yarn">
26 <Code code={`yarn add ${deps}\nyarn add -D ${devDeps}`} lang="sh" />
27 </TabItem>
28</Tabs>
29
30## Define the schema
31
32Drizzle schemas are plain TypeScript modules using the `pg-core`
33column builders:
34
35```typescript
36// src/schema.ts
37import { defineRelations } from "drizzle-orm";
38import { integer, pgTable, serial, text } from "drizzle-orm/pg-core";
39
40export const Users = pgTable("users", {
41 id: serial("id").primaryKey(),
42 email: text("email").notNull().unique(),
43 name: text("name").notNull(),
44});
45
46export const Posts = pgTable("posts", {
47 id: serial("id").primaryKey(),
48 userId: integer("user_id")
49 .notNull()
50 .references(() => Users.id, { onDelete: "cascade" }),
51 title: text("title").notNull(),
52});
53
54export const relations = defineRelations({ Users, Posts }, (t) => ({
55 Users: { posts: t.many.Posts() },
56 Posts: {
57 user: t.one.Users({ from: t.Posts.userId, to: t.Users.id }),
58 },
59}));
60```
61
62## Connect
63
64```typescript
65// src/queries.ts
66import * as Drizzle from "alchemy/Drizzle/Postgres";
67import * as Effect from "effect/Effect";
68import type * as Redacted from "effect/Redacted";
69import { relations, Users } from "./schema.ts";
70
71export const makeQueries = <E, R>(
72 connectionString: Effect.Effect<Redacted.Redacted<string>, E, R>,
73) =>
74 Effect.gen(function* () {
75 const db = yield* Drizzle.Postgres(connectionString, { relations });
76 return {
77 listUsers: () => db.select().from(Users),
78 };
79 });
80```
81
82Pass an Effect that resolves a redacted URL, such as `Config.Redacted("DATABASE_URL")` or a runtime binding's `connectionString`; wrap an already resolved redacted URL with `Effect.succeed(url)`. Call `listUsers()` inside a request or an explicit `Effect.scoped` block so the pool follows the [connection lifecycle](/sql/effect-sql/lifecycle).
83
84## Connect in a Worker
85
86The [Cloudflare walkthrough](/cloudflare/data/drizzle) supplies the URL through Hyperdrive and configures `nodejs_compat`. Database-specific setup stays in [Neon](/neon/guides/drizzle) and [PlanetScale](/planetscale/guides/drizzle).
87
88## Connect in a Fly Service
89
90The [Fly deployment guide](/fly/data/drizzle-postgres) covers deployment and `ConnectPostgres`; its `connectionString` works with the same client above.
91
92## Queries are Effects
93
94Every builder yields directly, with `SqlError` in the typed error
95channel:
96
97```typescript
98import { eq } from "drizzle-orm";
99
100const [created] = yield* db
101 .insert(Users)
102 .values({ name, email })
103 .returning();
104
105const [removed] = yield* db
106 .delete(Users)
107 .where(eq(Users.id, id))
108 .returning();
109```
110
111Because `relations` was passed to `Drizzle.Postgres`, the typed
112`db.query.*` API is available:
113
114```typescript
115const user = yield* db.query.Users.findFirst({
116 where: { id },
117 with: { posts: true },
118});
119```
120
121## Generate and review migrations
122
123```typescript
124// drizzle.config.ts
125import { defineConfig } from "drizzle-kit";
126
127export default defineConfig({
128 schema: "./src/schema.ts",
129 out: "./migrations",
130 dialect: "postgresql",
131});
132```
133
134```sh
135bunx drizzle-kit generate
136git add src/schema.ts drizzle.config.ts migrations
137git diff --cached
138git commit -m "Add database migration"
139```
140
141Generate on each schema change, stage and review the schema, SQL, and snapshots, then commit them before application. Apply the committed migrations with your existing runner before deploying code that needs the new schema. [Migrations](/sql/drizzle/migrations) covers application ownership: [`Drizzle.Schema`](/providers/drizzle/schema) is optional, and `alchemy deploy` does not replace an existing `drizzle-kit migrate` workflow.
142
143## Where next
144
145Choose a database and deployment guide in [SQL databases](/sql/databases). For tagged-template queries without an ORM, use [Effect SQL: Postgres](/sql/effect-sql/postgres).