1---
2title: Drizzle + Aurora DSQL
3description: 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
11git clone https://github.com/alchemy-run/alchemy.git
12cd alchemy
13pnpm install
14cd examples/aws-dsql-drizzle
15```
16
17Use 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, `@effect/sql-pg`, and `pg`; it is not an RDS Data API integration.
18
19:::caution
20These resources are billable. Deletion protection is disabled; destroy deletes the database data.
21:::
22
23## Create the cluster
24
25```typescript
26import * as AWS from "alchemy/AWS";
27
28export const Database = AWS.DSQL.Cluster("Database", {
29 deletionProtectionEnabled: false,
30});
31```
32
33DSQL 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
38import { boolean, pgSchema, text, timestamp, uuid } from "drizzle-orm/pg-core";
39
40export 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
50Use 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
55const schemaVersion = yield* BootstrapDatabase({
56 endpoint: cluster.endpoint,
57 roleArn: api.roleArn,
58 version: "1",
59});
60```
61
62The [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
67import * as Drizzle from "alchemy/Drizzle/Postgres";
68import * as Effect from "effect/Effect";
69import * as Redacted from "effect/Redacted";
70
71const cluster = yield* Database;
72const connect = yield* AWS.DSQL.Connect(cluster, { username: "app_user" });
73const 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
84Provide `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. The [complete handler](https://github.com/alchemy-run/alchemy/blob/main/examples/aws-dsql-drizzle/src/Api.ts) uses `build: { install: ["pg"] }` to package the CommonJS driver intact. [Connection lifecycle](/sql/effect-sql/lifecycle).
87
88## Query with Drizzle
89
90```typescript
91const rows = yield* db.select().from(Todos);
92
93const [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
104pnpm deploy --profile testing
105```
106
107The example's Action performs the initial bootstrap. Neither `AWS.DSQL.Cluster` nor the runtime `Drizzle.Postgres` wrapper applies generated migration files automatically.
108
109## Verify the connection
110
111```sh
112aws 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
118Use 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
121ALCHEMY_PROFILE=testing bun test test/integ.test.ts
122```
123
124## Evolve the schema
125
126Initial 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
131pnpm add -D drizzle-kit@1.0.0-rc.5-ab785fc
132```
133
134```typescript
135// drizzle.config.ts
136import { defineConfig } from "drizzle-kit";
137
138export default defineConfig({
139 dialect: "postgresql",
140 schema: "./src/schema.ts",
141 out: "./migrations",
142});
143```
144
145This 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
150pnpm exec drizzle-kit generate --config=drizzle.config.ts
151```
152
153Run whenever `src/schema.ts` changes, before deployment. Do not generate SQL in a deploy hook.
154
155### Check DSQL compatibility
156
157DSQL 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
162git add src/schema.ts drizzle.config.ts migrations/
163git diff --cached -- src/schema.ts drizzle.config.ts migrations/
164git commit -m "feat(db): update DSQL schema and migration"
165```
166
167Review 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}"
174export PGHOST
175export PGPORT=5432 PGDATABASE=postgres PGUSER=admin
176export PGSSLMODE=verify-full PGSSLROOTCERT=system
177export PGPASSWORD="$(aws dsql generate-db-connect-admin-auth-token \
178 --hostname "$PGHOST" --region "$AWS_REGION")"
179```
180
181This 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
185Use a validated DSQL-compatible migration runner in the release process before deploying dependent application code. It must record applied versions, handle partial failures, execute DDL as separate autocommit statements, and wait for asynchronous indexes. A plain `psql --file` has no migration history or safe replay; do not wrap the file in `BEGIN` or `--single-transaction`.
186
187Keep an existing validated DSQL migration workflow separate from `alchemy deploy`; the example's bootstrap Action is not its replacement. Do not expose schema administration through an HTTP `/setup` route.
188
189## Clean up
190
191```sh
192pnpm destroy --profile testing
193aws dsql get-cluster --region us-west-2 --identifier '<clusterId>'
194```
195
196Deletion is asynchronous. Wait for `DELETED` or `ResourceNotFoundException`.
197
198## Where next
199
200[Frontend + RPC](/aws/frontend/full-stack-tanstack-rpc-drizzle) · [Aurora PostgreSQL](/aws/data/drizzle-aurora) · [SQL databases](/sql/databases)