The best way to control Claude Code, Codex and more from any computer or phone
| 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) |