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
| 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) |