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

main
mdx 213 lines 7.73 KB
Raw
1 ---
2 title: Drizzle + Aurora PostgreSQL
3 description: Drizzle on Lambda with private Aurora PostgreSQL, IAM authentication, verified TLS, and reviewed SQL migrations.
4 ---
5
6 [Runnable example](https://github.com/alchemy-run/alchemy/tree/main/examples/aws-aurora-drizzle) · [AWS setup](/aws/setup) · [Aurora DSQL instead](/aws/data/drizzle-dsql)
7
8 ## Prerequisites
9
10 ```sh
11 git clone https://github.com/alchemy-run/alchemy.git
12 cd alchemy
13 pnpm install
14 cd examples/aws-aurora-drizzle
15 ```
16
17 The example uses `us-west-2`, Aurora PostgreSQL 17.5, and Node.js 22. Change both subnet availability zones if you change region. Application queries use Effect-native Drizzle over `@effect/sql-pg`, not the RDS Data API driver; [SQL databases](/sql/databases) compares the integrations.
18
19 :::caution
20 Aurora is billable until destroyed. This example is disposable: destroy deletes its database data.
21 :::
22
23 ## Provision a private database
24
25 ```typescript
26 const aurora = yield* AWS.RDS.Aurora("Database", {
27 databaseName: "app",
28 engine: "aurora-postgresql",
29 engineVersion: "17.5",
30 subnetIds,
31 securityGroupIds: [dbSecurityGroup.groupId],
32 secret: { username: "dbadmin" },
33 dataApi: true,
34 cluster: {
35 enableIAMDatabaseAuthentication: true,
36 serverlessV2ScalingConfiguration: { MinCapacity: 0.5, MaxCapacity: 1 },
37 },
38 instance: { dbInstanceClass: "db.serverless", publiclyAccessible: false },
39 });
40 ```
41
42 [Network setup](https://github.com/alchemy-run/alchemy/blob/main/examples/aws-aurora-drizzle/src/database.ts) allows port 5432 only from the Lambda's security group. [Aurora options](/aws/data/rds).
43
44 ## Initialize the schema during deployment
45
46 ```typescript
47 import { bootstrap } from "./bootstrap.ts";
48
49 const schemaVersion = yield* bootstrap;
50 ```
51
52 The [deployment Action](https://github.com/alchemy-run/alchemy/blob/main/examples/aws-aurora-drizzle/src/bootstrap.ts) creates `todos` and the restricted `app_iam` login through the Data API. This is initial setup only, not a versioned migration runner; to apply versioned migrations on deploy, pass them to Aurora's `migrations` prop (see [Apply the committed SQL](#apply-the-committed-sql)). The application role gets table CRUD but cannot create objects in `public`.
53
54 ## Bind the application identity
55
56 ```typescript
57 const connect = yield* AWS.RDS.Connect(aurora.cluster, {
58 auth: "iam",
59 username: "app_iam",
60 database: "app",
61 subnetIds,
62 securityGroupIds: [lambdaSecurityGroup.groupId],
63 });
64 ```
65
66 Provide `AWS.RDS.ConnectHttp` on the Lambda Effect. The binding attaches Lambda to the VPC and grants `rds-db:connect` for `app_iam`; runtime authentication signs an IAM token rather than reading the administrator secret. [Complete handler](https://github.com/alchemy-run/alchemy/blob/main/examples/aws-aurora-drizzle/src/Api.ts).
67
68 ## Verify the database certificate
69
70 Set the Lambda environment:
71
72 ```typescript
73 env: { NODE_EXTRA_CA_CERTS: "/var/runtime/ca-cert.pem" }
74 ```
75
76 The RDS binding currently emits `sslmode=no-verify`. Override it to require certificate and hostname verification:
77
78 ```typescript
79 import * as Drizzle from "@alchemy.run/drizzle/Postgres";
80 import * as Effect from "effect/Effect";
81 import * as Redacted from "effect/Redacted";
82
83 const db = yield* Drizzle.Postgres(
84 connect.pipe(
85 Effect.map((connection) => {
86 const url = new URL(Redacted.value(connection.url));
87 url.searchParams.set("sslmode", "verify-full");
88 return Redacted.make(url.toString());
89 }),
90 ),
91 );
92 ```
93
94 The CA path is specific to [managed Lambda Node.js runtimes](https://docs.aws.amazon.com/lambda/latest/dg/lambda-nodejs.html#nodejs-certificate-loading). The connection stays lazy: the first query resolves the redacted URL and opens a pool scoped to that invocation, which closes when its scope ends.
95
96 ## Install the driver
97
98 ```sh
99 pnpm add alchemy @alchemy.run/aws @alchemy.run/drizzle effect @effect/sql-pg \
100 drizzle-orm@1.0.0-rc.5
101 ```
102
103 `@effect/sql-pg` speaks the Postgres wire protocol itself, so the Lambda bundle needs no native or CommonJS driver.
104
105 ## Query with Drizzle
106
107 ```typescript
108 const rows = yield* db.select().from(todos);
109 ```
110
111 [Schema and queries](/sql/drizzle/postgres) · [Connection lifecycle](/sql/effect-sql/lifecycle)
112
113 ## Deploy and invoke
114
115 ```sh
116 pnpm deploy --profile testing --stage aurora-example
117 aws lambda invoke --region us-west-2 \
118 --function-name '<functionName>' \
119 --cli-binary-format raw-in-base64-out \
120 --payload file://health-event.json response.json
121 ```
122
123 Use the printed function name. The Function URL requires [SigV4-signed requests](https://github.com/alchemy-run/alchemy/blob/main/examples/aws-aurora-drizzle/test/integ.test.ts).
124
125 ## Evolve the schema
126
127 The example's bootstrap does not alter existing tables or maintain Drizzle migration history. Before adopting generated migrations for an already-bootstrapped database, establish a reviewed baseline matching the existing schema; do not replay an initial `CREATE TABLE` migration over it.
128
129 ### Configure SQL generation
130
131 ```sh
132 pnpm add -D drizzle-kit@1.0.0-rc.5
133 ```
134
135 ```typescript
136 // drizzle.config.ts
137 import { defineConfig } from "drizzle-kit";
138
139 export default defineConfig({
140 dialect: "postgresql",
141 schema: "./src/schema.ts",
142 out: "./migrations",
143 });
144 ```
145
146 Generation compares the [schema module](https://github.com/alchemy-run/alchemy/blob/main/examples/aws-aurora-drizzle/src/schema.ts) with local snapshots and does not need a database connection.
147
148 ### Generate after a schema change
149
150 ```sh
151 pnpm exec drizzle-kit generate --config=drizzle.config.ts
152 ```
153
154 Run this whenever `src/schema.ts` changes, before deployment. Do not put SQL generation in a deploy hook.
155
156 ### Review and commit the migration
157
158 ```sh
159 git add src/schema.ts drizzle.config.ts migrations/
160 git diff --cached -- src/schema.ts drizzle.config.ts migrations/
161 git commit -m "feat(db): update Aurora schema and migration"
162 ```
163
164 Review the staged SQL, snapshots, destructive operations, and rename decisions before committing. Commit the entire generated migration directory with the schema. The release process consumes committed SQL; deployment must not generate or silently rewrite it.
165
166 ### Apply the committed SQL
167
168 Pass the committed directory to Aurora. `alchemy deploy` applies pending migrations over the Data API once the writer is available, so the deploying machine needs no network route to the private cluster:
169
170 ```typescript
171 const aurora = yield* AWS.RDS.Aurora("Database", {
172 // ...
173 dataApi: true,
174 migrations: "./migrations",
175 });
176 ```
177
178 Each migration and its row in `__alchemy_migrations` commit in one transaction, authenticated with the cluster's master secret. The Data API runs one statement per call, so keep drizzle-kit's default `--> statement-breakpoint` separators. A database previously migrated by drizzle-kit is adopted on first deploy. Editing or deleting an applied migration fails the deploy; add a new migration instead.
179
180 To keep the Data API off, deploy an `AWS.RDS.MigrationRunner` into the cluster's private subnets and pass it as `migrationsTransport`. Alchemy then invokes that Lambda, and it connects to the writer from inside the VPC:
181
182 ```typescript
183 const runner = yield* AWS.RDS.MigrationRunner("MigrationRunner", {
184 subnetIds: network.privateSubnetIds,
185 securityGroupIds: [runnerSecurityGroup.groupId],
186 });
187 const aurora = yield* AWS.RDS.Aurora("Database", {
188 // ...
189 dataApi: false,
190 migrations: "./migrations",
191 migrationsTransport: runner,
192 });
193 ```
194
195 ## Run the integration test
196
197 ```sh
198 ALCHEMY_PROFILE=testing bun test test/integ.test.ts
199 ```
200
201 This deploys real AWS resources and destroys them afterward; provisioning and cleanup take several minutes.
202
203 ## Clean up
204
205 ```sh
206 pnpm destroy --profile testing --stage aurora-example
207 ```
208
209 Wait for deletion to finish. Reuse the same profile and stage if cleanup is interrupted.
210
211 ## Next steps
212
213 [SQL databases](/sql/databases) · [Aurora configuration](/aws/data/rds) · [Lambda](/aws/compute/lambda)