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

main
mdx 139 lines 4.49 KB
Raw
1 ---
2 title: Drizzle Postgres on Railway
3 description: Query Railway Postgres from an Effect-native Service and apply reviewed Drizzle migrations separately.
4 ---
5
6 Run Drizzle in a Railway container beside [Railway Postgres](/railway/data/postgres). Start with [Railway setup](/railway/setup), the [Service tutorial](/railway/tutorial/part-2), 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 migrations
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` here belongs to your migration runner; the deployed Service receives its own private connection through a binding.
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 rather than generating migrations during deployment.
53
54 ## Declare Postgres
55
56 ```typescript
57 // src/db.ts
58 import * as Railway from "@alchemy.run/railway";
59 import { Site } from "./project.ts";
60
61 export const Db = Railway.Postgres("Db", { project: Site });
62 ```
63
64 Use the same Project and environment as the Service; omitting `environment` on both selects the Project's primary environment.
65
66 ## Bind Postgres
67
68 ```diff lang="typescript"
69 +import { Db } from "./db.ts";
70
71 Effect.gen(function* () {
72 + const conn = yield* Railway.ConnectPostgres(Db);
73 return {
74 fetch: Effect.succeed(HttpServerResponse.text("Hello from Railway!")),
75 };
76 -}),
77 +}).pipe(Effect.provide(Railway.ConnectPostgresHttp)),
78 ```
79
80 The binding injects the private URI into the Service environment; `conn.connectionString` is an Effect yielding a redacted URL, not a query client. These excerpts use the tutorial's minimal text handler until the database is migrated.
81
82 ## Provision before the first migration
83
84 ```sh
85 pnpm alchemy deploy
86 ```
87
88 Keep `yield* Api` in the tutorial's Stack with `Railway.providers()`. This initial deployment provisions Postgres while the handler still avoids application tables.
89
90 ## Apply the committed SQL
91
92 ```sh
93 pnpm exec drizzle-kit migrate
94 ```
95
96 For an external CI runner, supply the database's `publicConnectionUri` as secret `DATABASE_URL`; the default `public: true` creates that TCP proxy. With `public: false`, run migrations inside the same Railway environment using `connectionUri` instead.
97
98 ## Review public TLS
99
100 The generated Railway Postgres URLs currently include `sslmode=no-verify`; a public TCP proxy does not provide certificate verification. For production, configure a verifiable server certificate and migration-client trust, or run migrations within the private environment.
101
102 ## Open the runtime client
103
104 ```diff lang="typescript"
105 +import * as Drizzle from "@alchemy.run/drizzle/Postgres";
106
107 const conn = yield* Railway.ConnectPostgres(Db);
108 +const db = yield* Drizzle.Postgres(conn.connectionString);
109 ```
110
111 Runtime traffic stays on Railway's private network, regardless of whether a public migration endpoint exists.
112
113 ## Query from the Service
114
115 ```diff lang="typescript"
116 +import { Users } from "./schema.ts";
117
118 return {
119 - fetch: Effect.succeed(HttpServerResponse.text("Hello from Railway!")),
120 + fetch: Effect.gen(function* () {
121 + const users = yield* db.select().from(Users).pipe(Effect.orDie);
122 + return yield* HttpServerResponse.json({ users });
123 + }),
124 };
125 ```
126
127 The request opens the pool lazily and closes it when its scope ends.
128
129 ## Deploy the application
130
131 ```sh
132 pnpm alchemy deploy
133 ```
134
135 For later changes, generate, review, and commit the schema, SQL, and snapshots before running migrations and deploying compatible code. `Railway.Postgres` has no `migrations` prop, so deployment does not replace `drizzle-kit migrate`.
136
137 ## Continue
138
139 See the [SQL database comparison](/sql/databases), [Postgres client guide](/sql/drizzle/postgres), and [migration workflow](/sql/drizzle/migrations). The [railway-service example](https://github.com/alchemy-run/alchemy/tree/main/examples/railway-service) contains the Service and Postgres binding used here.