@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 132 lines 4.3 KB
Raw
1 ---
2 title: Postgres
3 description: Provider-neutral Postgres schemas, Effect-native Drizzle queries, and connections for Workers, Lambda, and servers.
4 ---
5
6 import PackageManagers from "../../../../components/PackageManagers.astro";
7
8 export const deps = `@alchemy.run/drizzle drizzle-orm@1.0.0-rc.5 @effect/sql-pg`;
9 export const devDeps = `drizzle-kit@1.0.0-rc.5`;
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
13 Install the optional dependencies:
14
15 <PackageManagers add={deps} dev={devDeps} />
16
17 ## Define the schema
18
19 Drizzle schemas are plain TypeScript modules using the `pg-core`
20 column builders:
21
22 ```typescript
23 // src/schema.ts
24 import { defineRelations } from "drizzle-orm";
25 import { integer, pgTable, serial, text } from "drizzle-orm/pg-core";
26
27 export const Users = pgTable("users", {
28 id: serial("id").primaryKey(),
29 email: text("email").notNull().unique(),
30 name: text("name").notNull(),
31 });
32
33 export const Posts = pgTable("posts", {
34 id: serial("id").primaryKey(),
35 userId: integer("user_id")
36 .notNull()
37 .references(() => Users.id, { onDelete: "cascade" }),
38 title: text("title").notNull(),
39 });
40
41 export const relations = defineRelations({ Users, Posts }, (t) => ({
42 Users: { posts: t.many.Posts() },
43 Posts: {
44 user: t.one.Users({ from: t.Posts.userId, to: t.Users.id }),
45 },
46 }));
47 ```
48
49 ## Connect
50
51 ```typescript
52 // src/queries.ts
53 import * as Drizzle from "@alchemy.run/drizzle/Postgres";
54 import * as Effect from "effect/Effect";
55 import type * as Redacted from "effect/Redacted";
56 import { relations, Users } from "./schema.ts";
57
58 export const makeQueries = <E, R>(
59 connectionString: Effect.Effect<Redacted.Redacted<string>, E, R>,
60 ) =>
61 Effect.gen(function* () {
62 const db = yield* Drizzle.Postgres(connectionString, { relations });
63 return {
64 listUsers: () => db.select().from(Users),
65 };
66 });
67 ```
68
69 Pass 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).
70
71 ## Connect in a Worker
72
73 The [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).
74
75 ## Connect in a Fly Service
76
77 The [Fly deployment guide](/fly/data/drizzle-postgres) covers deployment and `ConnectPostgres`; its `connectionString` works with the same client above.
78
79 ## Queries are Effects
80
81 Every builder yields directly, with `SqlError` in the typed error
82 channel:
83
84 ```typescript
85 import { eq } from "drizzle-orm";
86
87 const [created] = yield* db
88 .insert(Users)
89 .values({ name, email })
90 .returning();
91
92 const [removed] = yield* db
93 .delete(Users)
94 .where(eq(Users.id, id))
95 .returning();
96 ```
97
98 Because `relations` was passed to `Drizzle.Postgres`, the typed
99 `db.query.*` API is available:
100
101 ```typescript
102 const user = yield* db.query.Users.findFirst({
103 where: { id },
104 with: { posts: true },
105 });
106 ```
107
108 ## Generate and review migrations
109
110 ```typescript
111 // drizzle.config.ts
112 import { defineConfig } from "drizzle-kit";
113
114 export default defineConfig({
115 schema: "./src/schema.ts",
116 out: "./migrations",
117 dialect: "postgresql",
118 });
119 ```
120
121 ```sh
122 bunx drizzle-kit generate
123 git add src/schema.ts drizzle.config.ts migrations
124 git diff --cached
125 git commit -m "Add database migration"
126 ```
127
128 Generate 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.
129
130 ## Where next
131
132 Choose 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).