Building type-safe applications with Drizzle ORM in Aurora DSQL

Building type-safe applications with Drizzle ORM and Amazon Aurora DSQL means fewer production surprises, IAM-based authentication instead of stored passwords, and a database that handles scaling for you. Aurora DSQL gives you a PostgreSQL-compatible database that automatically scales based on traffic, requires no capacity planning, and authenticates through AWS Identity and Access Management (AWS IAM) instead of stored passwords. Drizzle ORM checks your queries against your schema at compile time, so you catch column typos and type errors while coding, not after deploying.

Getting Drizzle ORM to work with Aurora DSQL takes three adjustments. You’ll use UUIDs for primary keys (Aurora DSQL’s recommended approach for distributed workloads), define relationships in your application code using Drizzle’s relations() API, and set up a lightweight migration runner. Once those are in place, you get faster development cycles, fewer production errors, and zero infrastructure overhead.

In this post, you’ll build a working veterinary clinic CLI application that demonstrates production-ready patterns for connecting Drizzle ORM to Aurora DSQL. By the end, you’ll have a running app with one-to-many and many-to-many relationships, and the patterns you learn (UUID primary keys, application-level relationships, and a custom migration runner) work with other TypeScript ORMs on Aurora DSQL too. Here’s what we’ll cover:

Define a Drizzle ORM schema using UUID primary keys compatible with Aurora DSQL.
Manage table relationships using Drizzle’s relations() API instead of foreign key constraints.
Connect through AWS IAM authentication using the Aurora DSQL Connector.
Build a migration runner that replaces the SERIAL-based tracking table with UUIDs.
Run CRUD (create, read, update, delete) operations against Aurora DSQL.

Solution overview

You’ll create a Node.js CLI application that catches database errors before production by running type-safe CRUD operations against Aurora DSQL through Drizzle ORM. You’ll use a veterinary clinic domain model: owners and pets (one-to-many), veterinarians and specialties (many-to-many).

The following diagram shows the architecture.

Architecture diagram showing a Node.js CLI application connecting through the Aurora DSQL Connector with AWS IAM authentication to Amazon Aurora DSQL

Figure 1: Drizzle ORM application architecture with Amazon Aurora DSQL

When you run the app:

The Aurora DSQL Connector generates and refreshes short-lived IAM authentication tokens.
A custom migration runner applies Drizzle Kit-generated SQL using a UUID-based tracking table.
Drizzle ORM’s query builder runs CRUD operations. You use the relations() API to join tables at the application level.

Key considerations

Aurora DSQL is a distributed, serverless database. Its architecture affects schema design choices such as primary key selection. The migration guide covers additional patterns.

UUIDs are the recommended primary key type because they require no coordination across the distributed system, which means your inserts scale without bottlenecks. Aurora DSQL also supports sequences and identity columns (with CACHE specified) when you need integer identifiers.
Aurora DSQL does not support foreign key constraints. JOIN operations work as expected. You implement referential integrity in application code, which avoids performance bottlenecks from cascading operations. Drizzle ORM’s relations() API provides eager loading (fetching related records in a single query) and relational queries.
Drizzle ORM’s built-in migrate() function creates its tracking table using the SERIAL pseudo-type. This sample includes a migration runner that uses UUID primary keys instead.
Aurora DSQL uses IAM authentication with time-limited tokens. The Aurora DSQL Connector for node-postgres handles token generation and refresh. See Authentication and authorization for Aurora DSQL.

Prerequisites

To follow along with this tutorial, you’ll need:

An AWS account.
AWS Command Line Interface (AWS CLI) v2.
Node.js 20 or later.
Git.
An Aurora DSQL cluster.

IAM permissions configuration

Use the following IAM policy. ClusterSetup covers the one-time cluster creation step. DatabaseAccess is what your application uses at runtime.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ClusterSetup",
      "Effect": "Allow",
      "Action": [
        "dsql:CreateCluster",
        "dsql:GetCluster"
      ],
      "Resource": "arn:aws:dsql:<region>:<account-id>:cluster/*"
    },
    {
      "Sid": "DatabaseAccess",
      "Effect": "Allow",
      "Action": "dsql:DbConnectAdmin",
      "Resource": "arn:aws:dsql:<region>:<account-id>:cluster/<cluster-id>"
    }
  ]
}

Replace <region> and <account-id> with your values. Use dsql:DbConnect instead of dsql:DbConnectAdmin when connecting as a non-admin user.

Important: After creating your cluster, remove dsql:CreateCluster from your policy and scope the DatabaseAccess statement to your specific cluster ARN. The ClusterSetup statement uses a wildcard because the cluster identifier isn’t known before creation.

Estimated time and costs

This tutorial takes 15 to 20 minutes. Running the sample generates minimal database activity.

Aurora DSQL charges for Distributed Processing Units (DPUs) and storage (GB-month). See Amazon Aurora DSQL Pricing. Delete your cluster after testing.

Solution walkthrough

To create an Aurora DSQL cluster

Open a terminal and run:

aws dsql create-cluster --region us-east-1

Copy the identifier from the JSON response.

Retrieve the cluster endpoint:

aws dsql get-cluster --identifier <cluster-id> --region us-east-1

Wait for the status field to show Active. The endpoint field is the hostname your application connects to (for example, abc123def456.dsql.us-east-1.on.aws).

To clone the sample and install dependencies

Clone the repository:

git clone https://github.com/aws-samples/aurora-dsql-samples.git

Navigate to the Drizzle sample and install:

cd aurora-dsql-samples/typescript/drizzle
npm install

Note: If you want to run the sample immediately, skip ahead to “To build and run the application”. The following sections walk through the key parts of the code — schema design, connection setup, and migrations — to explain the Aurora DSQL-specific adaptations made in this sample.

To define the Drizzle schema

In src/schema.ts, you define five tables. Most use UUID primary keys (generated by gen_random_uuid()); the specialty table uses a VARCHAR primary key. None define foreign key constraints.

The owner table uses a UUID primary key. Aurora DSQL generates the ID on insert:

export const owner = pgTable("owner", {
  id: uuid()
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  name: varchar({ length: 30 }).notNull(),
  city: varchar({ length: 80 }).notNull(),
  telephone: varchar({ length: 20 }),
});

The pet table stores an owner reference as a UUID column without a REFERENCES clause. The explicit string argument in uuid(“owner_id”) sets the database column name. Omitting it, as in the owner table, derives the name from the TypeScript key:

export const pet = pgTable("pet", {
  id: uuid()
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  name: varchar({ length: 30 }).notNull(),
  birthDate: date({ mode: "date" }).notNull(),
  ownerId: uuid("owner_id"),
});

Because Aurora DSQL does not support foreign key constraints, relationships are declared in application code using Drizzle’s relations() API (see src/schema.ts). Drizzle uses these declarations for eager loading — fetching an owner and all their pets in a single query — without any REFERENCES clauses in the schema.

To connect to Aurora DSQL

In src/dsql-client.ts, you connect using the Aurora DSQL Connector (@aws/aurora-dsql-node-postgres-connector), which extends pg.Pool from node-postgres. Drizzle ORM accepts it without adapter code:

const pool = new AuroraDSQLPool({
  host: process.env.CLUSTER_ENDPOINT,
  user: process.env.CLUSTER_USER,
  options: `-c search_path=${searchPath}`,
});
const db = drizzle({ client: pool, schema });

Your connection pool generates IAM tokens and refreshes them before expiration. The searchPath value comes from a fixed comparison against two hardcoded constants — never interpolate user-supplied values into the options string, as it is passed directly to PostgreSQL as a startup parameter and is not parameterized.

To apply migrations

Drizzle ORM’s built-in migrate() tracks applied migrations in a table with a SERIAL primary key. Aurora DSQL does not support the SERIAL pseudo-type. The custom runner in src/migrate.ts replaces this with a UUID-based tracking table, reads drizzle/meta/_journal.json to find pending migrations, and records each applied migration with a SHA-256 hash.

To generate new migrations after schema changes:

npm run migrate:generate

To build and run the application

Set environment variables. Replace the endpoint with your cluster endpoint from the first step:
```
export CLUSTER_USER="admin"
export CLUSTER_ENDPOINT="your-cluster.dsql.us-east-1.on.aws"
```
Build and run:
```
npm run build
npm run sample
```

Verify the output:

Starting Drizzle DSQL Example...
Running migrations...
Applying migration: 0000_init
Creating owners...
Created owner: John Doe (ID: a1b2c3d4-...)
Created owner: Mary Major (ID: e5f6g7h8-...)
Creating pets...
Created pet: Pet1 (Owner: John Doe)
Created pet: Pet2 (Owner: John Doe)
Creating veterinary specialties...
Created specialties: Exotic, Dogs, Cats
Creating veterinarians...
Created vet: Akua Mansa (Specialty: Exotic)
Created vet: Carlos Salazar (Specialties: Cats, Dogs)
Querying pet information...
Querying owner information...
Querying veterinarians with specialties...
Cleaning up...
Cleanup complete.
Example completed successfully!

Note: If you see “Missing required environment variable CLUSTER_ENDPOINT”, check that your environment variables are set in the current shell session.

To validate with tests

Run the Jest integration test:

npm test

A successful run produces:

PASS dist/test/index.spec.ts
Drizzle DSQL example
  ✓ Runs all the example code (xxxxx ms)
Tests: 1 passed, 1 total

To handle write conflicts with retry logic

Aurora DSQL uses optimistic concurrency control (OCC) and returns SQLSTATE 40001 when two transactions conflict on the same row (OC000) or when a session’s cached schema is stale (OC001). Both cases are safe to retry. The recommended pattern is exponential backoff with jitter so that concurrent retries are less likely to conflict with each other.

Add src/utils/retry.ts to the project:

// src/utils/retry.ts
const OCC_SQLSTATE = "40001";
const BASE_DELAY_MS = 50;

interface DatabaseError extends Error {
  code?: string;
}

function isDatabaseError(err: unknown): err is DatabaseError {
  return err instanceof Error && "code" in err;
}

export async function withRetry<T>(
  fn: () => Promise<T>,
  maxRetries = 3,
): Promise<T> {
  for (let attempt = 0; attempt <= maxRetries; attempt++) {
    try {
      return await fn();
    } catch (err: unknown) {
      const isOCC = isDatabaseError(err) && err.code === OCC_SQLSTATE;
      if (isOCC && attempt < maxRetries) {
        const backoff = BASE_DELAY_MS * 2 ** attempt;
        const jitter = Math.random() * backoff;
        await new Promise((r) => setTimeout(r, backoff + jitter));
        continue;
      }
      throw err;
    }
  }
  throw new Error("unreachable");
}

Wrap any transaction that may conflict in withRetry():

import { withRetry } from "./utils/retry";
import { eq } from "drizzle-orm";
import { db } from "./dsql-client";
import { pet } from "./schema";

await withRetry(() =>
  db.transaction(async (tx) => {
    const [record] = await tx
      .select()
      .from(pet)
      .where(eq(pet.id, petId));
    await tx
      .update(pet)
      .set({ name: record.name + " (updated)" })
      .where(eq(pet.id, petId));
  })
);

withRetry() uses a type guard to safely narrow the caught error before checking the SQLSTATE code. It retries up to three times with exponential backoff and random jitter. If all retries are exhausted, the original error is re-thrown so the caller can decide whether to surface it or escalate to a dead-letter queue.

Clean up

To delete the Aurora DSQL cluster

Using the AWS CLI:

aws dsql delete-cluster --identifier <cluster-id> --region us-east-1

Or in the Aurora DSQL console: select your cluster, choose Actions, then Delete.

To remove database tables without deleting the cluster

DROP TABLE IF EXISTS "__drizzle_migrations";
DROP TABLE IF EXISTS "_SpecialtyToVet";
DROP TABLE IF EXISTS "pet";
DROP TABLE IF EXISTS "owner";
DROP TABLE IF EXISTS "specialty";
DROP TABLE IF EXISTS "vet";

Conclusion

In this post, you learned how to connect Drizzle ORM to Amazon Aurora DSQL by making three targeted adaptations: using UUID primary keys instead of the SERIAL pseudo-type, managing table relationships at the application level through Drizzle’s relations() API, and building a custom migration runner that replaces the SERIAL-based tracking table. You also added OCC retry logic with exponential backoff and jitter to handle write conflicts in production. These patterns provide a foundation for building type-safe applications on Aurora DSQL.

To take this further, connect as a non-admin user with a custom schema to test multi-tenant patterns. Explore the additional ORM samples available in the aurora-dsql-samples repository to find the implementation approach that fits your team.

Get started by cloning the aurora-dsql-samples repository and deploying the sample against your own Aurora DSQL cluster. For more on Aurora DSQL, see the Aurora DSQL User Guide. To learn more about Drizzle ORM’s PostgreSQL support, visit the Drizzle ORM PostgreSQL guide.

AWS Database Blog