@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, 06:00 UTC

main
mdx 206 lines 8.33 KB
Raw
1 ---
2 title: Drizzle + Aurora DSQL
3 description: Drizzle on Lambda with Aurora DSQL, a non-admin database role, verified TLS, and an explicit DSQL migration boundary.
4 ---
5
6 [Runnable example](https://github.com/alchemy-run/alchemy/tree/main/examples/aws-dsql-drizzle) · [AWS setup](/aws/setup) · [Aurora PostgreSQL instead](/aws/data/drizzle-aurora)
7
8 ## Start from the example
9
10 ```sh
11 git clone https://github.com/alchemy-run/alchemy.git
12 cd alchemy
13 pnpm install
14 cd examples/aws-dsql-drizzle
15 ```
16
17 Use a DSQL-supported region such as `us-west-2`. Deployment needs `dsql:DbConnectAdmin` and access to the cluster on port 5432. The example includes Effect 4, Effect-native Drizzle, and `@effect/sql-pg`; it is not an RDS Data API integration.
18
19 :::caution
20 These resources are billable. Deletion protection is disabled; destroy deletes the database data.
21 :::
22
23 ## Create the cluster
24
25 ```typescript
26 import * as AWS from "@alchemy.run/aws";
27
28 export const Database = AWS.DSQL.Cluster("Database", {
29 deletionProtectionEnabled: false,
30 });
31 ```
32
33 DSQL provides the `postgres` database and a public PostgreSQL-wire endpoint authenticated through IAM; no VPC attachment or password secret is needed. It is not the Aurora cluster-and-writer topology. [DSQL cluster reference](/providers/aws/dsql/cluster) · [SQL databases](/sql/databases).
34
35 ## Define the table
36
37 ```typescript
38 import { boolean, pgSchema, text, timestamp, uuid } from "drizzle-orm/pg-core";
39
40 export const Todos = pgSchema("app").table("todos", {
41 id: uuid("id").primaryKey(),
42 text: text("text").notNull(),
43 done: boolean("done").notNull().default(false),
44 createdAt: timestamp("created_at", { withTimezone: true })
45 .notNull()
46 .defaultNow(),
47 });
48 ```
49
50 Use application-generated UUIDs for this table rather than PostgreSQL sequence defaults. Check [DSQL's PostgreSQL compatibility](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-migration-guide.html) before extending the schema; PostgreSQL SQL generation is not a portability guarantee.
51
52 ## Separate schema setup from application access
53
54 ```typescript
55 const schemaVersion = yield* BootstrapDatabase({
56 endpoint: cluster.endpoint,
57 roleArn: api.roleArn,
58 version: "1",
59 });
60 ```
61
62 The [deployment Action](https://github.com/alchemy-run/alchemy/blob/main/examples/aws-dsql-drizzle/src/bootstrap.ts) creates `app.todos` and maps the Lambda role to `app_user` using `AWS IAM GRANT`. It grants schema usage and table CRUD, not schema administration. Its privileged connection verifies TLS and executes catalog changes as separate autocommit statements; this is initial setup, not a versioned migration runner.
63
64 ## Connect from Lambda
65
66 ```typescript
67 import * as Drizzle from "@alchemy.run/drizzle/Postgres";
68 import * as Effect from "effect/Effect";
69 import * as Redacted from "effect/Redacted";
70
71 const cluster = yield* Database;
72 const connect = yield* AWS.DSQL.Connect(cluster, { username: "app_user" });
73 const db = yield* Drizzle.Postgres(
74 connect.pipe(
75 Effect.map((connection) => {
76 const url = new URL(Redacted.value(connection.url));
77 url.searchParams.set("sslmode", "verify-full");
78 return Redacted.make(url.toString());
79 }),
80 ),
81 );
82 ```
83
84 Provide `AWS.DSQL.ConnectHttp` on the Lambda Effect. It grants cluster-scoped `dsql:DbConnect`, not `dsql:DbConnectAdmin`, and signs a fresh IAM token locally when the connection effect runs. The binding does not create the database role or SQL grants.
85
86 `Drizzle.Postgres` resolves the redacted URL on the first query and scopes its pool to the invocation, rather than retaining a pool with an old token across warm invocations. See the [complete handler](https://github.com/alchemy-run/alchemy/blob/main/examples/aws-dsql-drizzle/src/Api.ts). [Connection lifecycle](/sql/effect-sql/lifecycle).
87
88 ## Query with Drizzle
89
90 ```typescript
91 const rows = yield* db.select().from(Todos);
92
93 const [created] = yield* db.insert(Todos).values({
94 id: "aaaaaaaa-0000-4000-8000-000000000001",
95 text: "Ship the DSQL guide",
96 }).returning();
97 ```
98
99 [Drizzle query API](/sql/drizzle/postgres)
100
101 ## Deploy
102
103 ```sh
104 pnpm deploy --profile testing
105 ```
106
107 The example's Action performs the initial bootstrap. To apply versioned migrations on deploy instead, pass them to the cluster's `migrations` prop; see [Apply the committed SQL](#apply-the-committed-sql).
108
109 ## Verify the connection
110
111 ```sh
112 aws lambda invoke --region us-west-2 \
113 --function-name '<functionName>' \
114 --cli-binary-format raw-in-base64-out \
115 --payload file://health-event.json response.json
116 ```
117
118 Use the printed function name. The Function URL requires [SigV4-signed requests](https://github.com/alchemy-run/alchemy/blob/main/examples/aws-dsql-drizzle/test/integ.test.ts).
119
120 ```sh
121 ALCHEMY_PROFILE=testing bun test test/integ.test.ts
122 ```
123
124 ## Evolve the schema
125
126 Initial setup does not alter existing tables. The Action's `version` input is not a migration history: before adopting generated migrations for this bootstrapped database, establish a reviewed baseline matching `app.todos` rather than replaying its initial creation.
127
128 ### Configure SQL generation
129
130 ```sh
131 pnpm add -D drizzle-kit@1.0.0-rc.5
132 ```
133
134 ```typescript
135 // drizzle.config.ts
136 import { defineConfig } from "drizzle-kit";
137
138 export default defineConfig({
139 dialect: "postgresql",
140 schema: "./src/schema.ts",
141 out: "./migrations",
142 });
143 ```
144
145 This generates PostgreSQL SQL from local snapshots without connecting to DSQL; it is not a DSQL-specific dialect or migration runner.
146
147 ### Generate after a schema change
148
149 ```sh
150 pnpm exec drizzle-kit generate --config=drizzle.config.ts
151 ```
152
153 Run whenever `src/schema.ts` changes, before deployment. Do not generate SQL in a deploy hook.
154
155 ### Check DSQL compatibility
156
157 DSQL allows [one DDL statement per transaction and separates DDL from DML](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-migration-guide.html#working-with-postgresql-compatibility-unsupported-limitations). Do not assume the ordinary PostgreSQL `drizzle-kit migrate` transaction, which also writes migration history, works on DSQL. Review generated SQL for supported syntax, including [`CREATE INDEX ASYNC`](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/create-index-syntax-support.html) and its completion checks.
158
159 ### Review and commit the migration
160
161 ```sh
162 git add src/schema.ts drizzle.config.ts migrations/
163 git diff --cached -- src/schema.ts drizzle.config.ts migrations/
164 git commit -m "feat(db): update DSQL schema and migration"
165 ```
166
167 Review the staged SQL and snapshots before committing, including any DSQL-specific adjustments or destructive operations. Commit the entire generated migration directory with the schema. The release process consumes committed SQL; deployment must not generate or silently rewrite it.
168
169 ### Authenticate the migration operator
170
171 ```sh
172 : "${PGHOST:?Set PGHOST to the deployed DSQL endpoint}"
173 : "${AWS_REGION:?Set AWS_REGION to the cluster region}"
174 export PGHOST
175 export PGPORT=5432 PGDATABASE=postgres PGUSER=admin
176 export PGSSLMODE=verify-full PGSSLROOTCERT=system
177 export PGPASSWORD="$(aws dsql generate-db-connect-admin-auth-token \
178 --hostname "$PGHOST" --region "$AWS_REGION")"
179 ```
180
181 This operator connection uses AWS CLI credentials authorized for `dsql:DbConnectAdmin` and a libpq 16-or-newer client for system certificate trust. It is separate from the Lambda's `app_user` connection. Keep the token out of source control and logs; mint a fresh one before reconnecting and unset `PGPASSWORD` when finished.
182
183 ### Apply the committed SQL
184
185 Pass the committed directory to the cluster. `alchemy deploy` applies pending migrations before anything that depends on the cluster:
186
187 ```typescript
188 const database = yield* AWS.DSQL.Cluster("Database", {
189 migrations: "./migrations",
190 });
191 ```
192
193 Each statement runs separately, `CREATE INDEX` becomes `CREATE INDEX ASYNC` and is awaited, and applied migrations are recorded in `__alchemy_migrations`. Editing or deleting an applied migration fails the deploy; add a new migration instead. Don't wrap migration files in `BEGIN`/`COMMIT`, and don't expose schema administration through an HTTP `/setup` route.
194
195 ## Clean up
196
197 ```sh
198 pnpm destroy --profile testing
199 aws dsql get-cluster --region us-west-2 --identifier '<clusterId>'
200 ```
201
202 Deletion is asynchronous. Wait for `DELETED` or `ResourceNotFoundException`.
203
204 ## Where next
205
206 [Frontend + RPC](/aws/frontend/full-stack-tanstack-rpc-drizzle) · [Aurora PostgreSQL](/aws/data/drizzle-aurora) · [SQL databases](/sql/databases)