The best way to control Claude Code, Codex and more from any computer or phone

main
mdx 138 lines 4.45 KB
Raw
1 ---
2 title: Drizzle Postgres on Fly
3 description: Connect a Fly Service to Managed Postgres and deploy reviewed Drizzle migrations.
4 ---
5
6 Run Drizzle in a `Fly.Service`, with [Fly Managed Postgres](/fly/data/postgres) on the same organization's private network. Start with [Fly setup](/fly/setup), the [Service tutorial](/fly/tutorial/part-1), and a `src/schema.ts` exporting `Users` from the [Postgres client guide](/sql/drizzle/postgres).
7
8 ## Install the database dependencies
9
10 ```sh
11 pnpm add @alchemy.run/drizzle drizzle-orm@1.0.0-rc.5 @effect/sql-pg
12 pnpm add -D drizzle-kit@1.0.0-rc.5
13 ```
14
15 The Drizzle versions match the repository's Effect integration.
16
17 ## Configure migration generation
18
19 ```typescript
20 // drizzle.config.ts
21 import { defineConfig } from "drizzle-kit";
22
23 export default defineConfig({
24 dialect: "postgresql",
25 schema: "./src/schema.ts",
26 out: "./drizzle",
27 dbCredentials: { url: process.env.DATABASE_URL! },
28 });
29 ```
30
31 `DATABASE_URL` is the direct Postgres URL used by the migration runner, not the pooled runtime URL.
32
33 ## Generate after changing the schema
34
35 ```sh
36 pnpm exec drizzle-kit generate
37 ```
38
39 Review the generated SQL and snapshots, including any rename decisions, before applying them.
40
41 ## Commit the migration
42
43 ```sh
44 git add src/schema.ts drizzle.config.ts drizzle
45 git diff --cached
46 ```
47
48 ```sh
49 git commit -m "Add users migration"
50 ```
51
52 Commit the schema, SQL, and snapshots together; CI deploys this reviewed history rather than generating it.
53
54 ## Declare the cluster
55
56 ```typescript
57 // src/db.ts
58 import * as Fly from "@alchemy.run/fly";
59
60 export const Db = Fly.Postgres("Db", { region: "iad" });
61 ```
62
63 This provisions a billed Managed Postgres cluster, not an unmanaged Postgres Machine.
64
65 ## Bind the cluster
66
67 ```diff lang="typescript"
68 +import { Db } from "./db.ts";
69
70 Effect.gen(function* () {
71 + const conn = yield* Fly.ConnectPostgres(Db);
72 return {
73 fetch: Effect.succeed(HttpServerResponse.text("Hello from Fly!")),
74 };
75 -}),
76 +}).pipe(Effect.provide(Fly.ConnectPostgresHttp)),
77 ```
78
79 The binding attaches the cluster to the Service's App and supplies pooled and direct connection Effects. These excerpts use the tutorial's minimal text handler until the database is migrated.
80
81 ## Provision before the first migration
82
83 ```sh
84 pnpm alchemy deploy
85 ```
86
87 Keep the tutorial's `yield* Api` in the Stack, using `Fly.providers()`. This first deployment creates the database and a handler that does not yet query application tables.
88
89 ## Apply the committed SQL
90
91 ```sh
92 pnpm exec drizzle-kit migrate
93 ```
94
95 Supply the cluster's `connectionUri` as the migration runner's secret `DATABASE_URL`, and run where the direct hostname resolves and is reachable on the Fly organization network, such as a correctly configured WireGuard peer or a Machine on 6PN. A pooled URI is not the migration endpoint.
96
97 ## Open the runtime client
98
99 ```diff lang="typescript"
100 +import * as Drizzle from "@alchemy.run/drizzle/Postgres";
101
102 const conn = yield* Fly.ConnectPostgres(Db);
103 +const db = yield* Drizzle.Postgres(conn.connectionString);
104 ```
105
106 The pooled connection opens lazily when a request first queries the database.
107
108 ## Query from the Service
109
110 ```diff lang="typescript"
111 +import { Users } from "./schema.ts";
112
113 return {
114 - fetch: Effect.succeed(HttpServerResponse.text("Hello from Fly!")),
115 + fetch: Effect.gen(function* () {
116 + const users = yield* db.select().from(Users).pipe(Effect.orDie);
117 + return yield* HttpServerResponse.json({ users });
118 + }),
119 };
120 ```
121
122 Queries run inside the request scope, rather than during infrastructure construction.
123
124 ## Deploy the application
125
126 ```sh
127 pnpm alchemy deploy
128 ```
129
130 For subsequent schema changes, generate, review, and commit first, then apply the committed migrations before deploying compatible application code. `alchemy deploy` does not replace `drizzle-kit migrate` in this guide.
131
132 ## Choose one migration owner
133
134 `Fly.Postgres` also supports a `migrations` directory, but this guide deliberately leaves it unset and keeps Drizzle as the migration owner. Switching an existing database to Alchemy's migration history is a separate [migration adoption decision](/sql/drizzle/migrations#existing-migration-history), not a command substitution.
135
136 ## Continue
137
138 See the [Managed Postgres guide](/fly/data/postgres), [portable Postgres queries](/sql/drizzle/postgres), and [migration workflow](/sql/drizzle/migrations). The [fly-postgres example](https://github.com/alchemy-run/alchemy/tree/main/examples/fly-postgres) demonstrates the Service binding, but its test-only HTTP migration endpoint is not used here.