Drizzle + Aurora PostgreSQL

Runnable example · AWS setup · Aurora DSQL instead

Prerequisites

git clone https://github.com/alchemy-run/alchemy.git
cd alchemy
pnpm install
cd examples/aws-aurora-drizzle

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 compares the integrations.

Caution

Aurora is billable until destroyed. This example is disposable: destroy deletes its database data.

Provision a private database

const aurora = yield* AWS.RDS.Aurora("Database", {
  databaseName: "app",
  engine: "aurora-postgresql",
  engineVersion: "17.5",
  subnetIds,
  securityGroupIds: [dbSecurityGroup.groupId],
  secret: { username: "dbadmin" },
  dataApi: true,
  cluster: {
    enableIAMDatabaseAuthentication: true,
    serverlessV2ScalingConfiguration: { MinCapacity: 0.5, MaxCapacity: 1 },
  },
  instance: { dbInstanceClass: "db.serverless", publiclyAccessible: false },
});

Network setup allows port 5432 only from the Lambda’s security group. Aurora options.

Initialize the schema during deployment

import { bootstrap } from "./bootstrap.ts";

const schemaVersion = yield* bootstrap;

The deployment Action 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). The application role gets table CRUD but cannot create objects in public.

Bind the application identity

const connect = yield* AWS.RDS.Connect(aurora.cluster, {
  auth: "iam",
  username: "app_iam",
  database: "app",
  subnetIds,
  securityGroupIds: [lambdaSecurityGroup.groupId],
});

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.

Verify the database certificate

Set the Lambda environment:

env: { NODE_EXTRA_CA_CERTS: "/var/runtime/ca-cert.pem" }

The RDS binding currently emits sslmode=no-verify. Override it to require certificate and hostname verification:

import * as Drizzle from "@alchemy.run/drizzle/Postgres";
import * as Effect from "effect/Effect";
import * as Redacted from "effect/Redacted";

const db = yield* Drizzle.Postgres(
  connect.pipe(
    Effect.map((connection) => {
      const url = new URL(Redacted.value(connection.url));
      url.searchParams.set("sslmode", "verify-full");
      return Redacted.make(url.toString());
    }),
  ),
);

The CA path is specific to managed Lambda Node.js runtimes. 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.

Install the driver

pnpm add https://pkg.alchemy.run/alchemy/pr:2930:a10573e https://pkg.alchemy.run/@alchemy.run/aws/pr:2930:a10573e https://pkg.alchemy.run/@alchemy.run/drizzle/pr:2930:a10573e effect @effect/sql-pg \
  drizzle-orm@1.0.0-rc.5

@effect/sql-pg speaks the Postgres wire protocol itself, so the Lambda bundle needs no native or CommonJS driver.

Query with Drizzle

const rows = yield* db.select().from(todos);

Schema and queries · Connection lifecycle

Deploy and invoke

pnpm deploy --profile testing --stage aurora-example
aws lambda invoke --region us-west-2 \
  --function-name '<functionName>' \
  --cli-binary-format raw-in-base64-out \
  --payload file://health-event.json response.json

Use the printed function name. The Function URL requires SigV4-signed requests.

Evolve the schema

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.

Configure SQL generation

pnpm add -D drizzle-kit@1.0.0-rc.5

drizzle.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema.ts",
  out: "./migrations",
});

Generation compares the schema module with local snapshots and does not need a database connection.

Generate after a schema change

pnpm exec drizzle-kit generate --config=drizzle.config.ts

Run this whenever src/schema.ts changes, before deployment. Do not put SQL generation in a deploy hook.

Review and commit the migration

git add src/schema.ts drizzle.config.ts migrations/
git diff --cached -- src/schema.ts drizzle.config.ts migrations/
git commit -m "feat(db): update Aurora schema and migration"

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.

Apply the committed SQL

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:

const aurora = yield* AWS.RDS.Aurora("Database", {
  // ...
  dataApi: true,
  migrations: "./migrations",
});

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.

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:

const runner = yield* AWS.RDS.MigrationRunner("MigrationRunner", {
  subnetIds: network.privateSubnetIds,
  securityGroupIds: [runnerSecurityGroup.groupId],
});
const aurora = yield* AWS.RDS.Aurora("Database", {
  // ...
  dataApi: false,
  migrations: "./migrations",
  migrationsTransport: runner,
});

Run the integration test

ALCHEMY_PROFILE=testing bun test test/integ.test.ts

This deploys real AWS resources and destroys them afterward; provisioning and cleanup take several minutes.

Clean up

pnpm destroy --profile testing --stage aurora-example

Wait for deletion to finish. Reuse the same profile and stage if cleanup is interrupted.

Next steps

SQL databases · Aurora configuration · Lambda