Building a multi-Region API with Prisma ORM and Amazon Aurora DSQL

Global applications require databases that can serve users with low latency while maintaining consistency across AWS Regions. Traditional relational databases typically implement active-passive models. One Region accepts writes, the other serves read replicas. Traditional failover approaches can involve downtime, data replication gaps, and operational complexity.

Amazon Aurora DSQL removes the single-writer limitation of traditional active-passive models. Both peered Regions accept reads and writes with strong consistency, and there are no instances to manage, no Amazon Virtual Private Cloud (Amazon VPC) to configure, and no static passwords to rotate. But connecting an ORM like Prisma to a distributed SQL database with no foreign keys, no sequences, and async-only indexes requires deliberate adaptation.

In this post, we show how you can build a multi-Region active-active API using Prisma ORM and Amazon Aurora DSQL.

What is Amazon Aurora DSQL?

Amazon Aurora DSQL is a serverless, distributed relational database service designed for transactional workloads. You can deploy globally distributed applications with strong consistency across Regions, while AWS handles the underlying infrastructure. You get automatic scaling without managing infrastructure.

Why Amazon Aurora DSQL for multi-Region applications

Amazon Aurora DSQL supports active-active multi-Region deployments. Every peered Region accepts both reads and writes with strong consistency. There are no instances to size, no Amazon VPC to configure, and no static passwords to manage. Amazon Aurora DSQL handles authentication entirely through short-lived AWS Identity and Access Management (IAM) tokens.

Key features for Node.js developers:

• PostgreSQL-compatible: Use familiar SQL and existing drivers. Prisma connects by using @prisma/adapter-pg and the @aws/aurora-dsql-node-postgres-connector.
• Zero credential exposure: IAM token-based authentication removes the need for static passwords. The AuroraDSQLPool handles token generation and refresh automatically.
• Serverless: Scales to zero with no sharding or instance upgrades required.
• Multi-Region writes: Both peered Regions accept writes simultaneously. A witness Region (us-east-2 in this example) participates in quorum for durability without serving application traffic.

DSQL constraints to be aware of

DSQL makes deliberate trade-offs to support distributed writes. The following PostgreSQL features aren’t supported:

What is Prisma ORM?

Prisma is an open source ORM for Node.js and TypeScript that replaces traditional ORMs with a type-safe database client, a declarative schema language, and a migration system. The Prisma driver adapter system (@prisma/adapter-pg) works with custom connection pools, which is how we integrate it with the IAM-authenticated connections of Amazon Aurora DSQL.

Solution overview

The solution runs two application instances: one in us-east-1 and one in us-west-2. Each is connected to DSQL Regional endpoints simultaneously. When a Region becomes unreachable, the application switches to the peer endpoint in-process, without restarting or reconfiguring.

The key pieces that make this work together:

• Amazon Aurora DSQL multi-Region clusters: Two peered endpoints that both accept reads and writes with strong consistency, backed by a witness Region for quorum.
• AuroraDSQLPool: Manages AWS Identity and Access Management (IAM) token generation and connection pool refresh per Region, so credentials don’t go stale mid-request.
• MultiRegionDsqlClient: Establishes and health-checks connections to both Regions at startup, then exposes a failover() method that switches the active Prisma client to the secondary in-process.
• Prisma Object-Relational Mapping (ORM): Adapted for the constraints of DSQL (no foreign keys, UUID primary keys, async index creation) by relationMode = "prisma" and the aurora-dsql-prisma migration tool.

The full source code is available on GitHub.

The system uses two application instances communicating with a shared Amazon Aurora DSQL multi-Region cluster:

• App instances (us-east-1 and us-west-2): Each runs an Express API server. Both live and serve traffic simultaneously. Amazon Route 53 latency-based routing directs users to the nearest Region.
• MultiRegionDsqlClient: Runs inside each app instance. At startup, it establishes and verifies connections to both DSQL Regional endpoints. The local Region is treated as primary. The peer Region is secondary.
• Amazon Aurora DSQL multi-Region cluster: Two peered endpoints (us-east-1 and us-west-2) that both accept reads and writes. A witness Region (us-east-2) participates in quorum but has no application endpoint.

The failover flow works as follows:

1. Both app instances connect to both DSQL endpoints at startup and verify connectivity with SELECT 1.
2. Each instance routes requests through withFailover(), which calls the active Prisma client.
3. If the active client throws ECONNREFUSED, ETIMEDOUT, or an “unavailable” error, withFailover() calls dsql.failover(), which switches activePrisma to the secondary client in-process.
4. The retry executes against the secondary. No restart required.

Prerequisites

Before you get started, make sure you have the following:

• An AWS account with credentials configured.
• An IAM role with permissions to:
  • Create and manage Amazon Aurora DSQL clusters.
  • Generate DSQL authentication tokens (dsql:DbConnectAdmin or dsql:DbConnect)
  • Create IAM roles and policies.
• Node.js 20+.
• AWS Command Line Interface (AWS CLI) v2 installed and configured.

This post uses the us-east-1 and us-west-2 AWS Regions. For instructions on assigning permissions to the IAM role, refer to Amazon Aurora DSQL identity-based policies.

Clone the repository containing the source code. The repository README has the complete step-by-step setup commands for every section that follows.

git clone https://github.com/aws-samples/aurora-dsql-samples.git
cd aws-samples/aurora-dsql-samples/typescript/prisma-multi-region

Step 1: Provision the Amazon Aurora DSQL multi-Region cluster

Amazon Aurora DSQL is serverless. There are no instances, VPCs, or security groups to configure. IAM authentication secures the publicly accessible cluster endpoints.

Create a multi-Region cluster using the AWS CLI:

aws dsql create-multi-region-clusters \
  --linked-region-list us-east-1 us-west-2 \
  --witness-region us-east-2 \
  --cluster-properties '{
    "us-east-1": {"tags": {"Name": "dsql-primary"}},
    "us-west-2": {"tags": {"Name": "dsql-secondary"}}
  }'

This returns cluster identifiers for each Region. Your endpoints will be:

<cluster-id>.dsql.us-east-1.on.aws
<cluster-id>.dsql.us-west-2.on.aws

Poll until both clusters reach ACTIVE status:

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

Step 2: Set up the Prisma schema

The Prisma schema requires three adjustments from a standard PostgreSQL setup to be compatible with DSQL.

datasource db {
  provider     = "postgresql"
  relationMode = "prisma"   // Enforces relations in app code, not the database
}

generator client {
  provider = "prisma-client-js"
}

model Order {
  id        String      @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  product   String
  quantity  Int
  status    String      @default("pending")
  region    String      @map("region")
  createdAt DateTime    @default(now()) @map("created_at")
  updatedAt DateTime    @updatedAt @map("updated_at")
  items     OrderItem[]

  @@map("orders")
}

model OrderItem {
  id      String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  orderId String @map("order_id") @db.Uuid
  name    String
  price   Float
  order   Order  @relation(fields: [orderId], references: [id])

  @@index([orderId])
  @@map("order_items")
}

relationMode = "prisma" — Prisma enforces referential integrity in application code. DSQL doesn’t support database-level foreign key constraints.

gen_random_uuid() for IDs — DSQL doesn’t support sequences or autoincrement(). UUIDs generated by the database are globally unique across Regions by definition.

@@index at the model level — The aurora-dsql-prisma CLI tool transforms these into CREATE INDEX ASYNC statements that DSQL supports.

Validate and generate the migration

Install dependencies and validate the schema against DSQL constraints:

npm install
npm run validate

Generate a DSQL-compatible migration file:

npx aurora-dsql-prisma prisma/schema.prisma -o prisma/migrations/0_init/migration.sql

Step 3: Configure IAM authentication

Amazon Aurora DSQL uses IAM-based authentication, no static passwords. The prisma.config.ts file generates a short-lived signed token at startup for Prisma CLI tools (migrations, schema generation). The AuroraDSQLPool handles token refresh automatically for the runtime connection pool.

Two token types are available depending on the user:

Your AWS credentials (instance profile, ECS task role, etc.) need the appropriate permission scoped to the cluster Amazon Resource Names (ARNs).

Run migrations

Because DSQL doesn’t support advisory locks (used internally by Prisma Migrate), set the following environment variable before deploying migrations:

export CLUSTER_ENDPOINT="<cluster-id>.dsql.us-east-1.on.aws"
export CLUSTER_USER="admin"
export AWS_REGION="us-east-1"

PRISMA_SCHEMA_DISABLE_ADVISORY_LOCK=1 npx prisma migrate deploy

You only need to run migrations once. DSQL replicates schema changes to each peered Region automatically.

Step 4: Build the multi-Region client

The MultiRegionDsqlClient (src/dsql-client.ts) is the core of the failover strategy. It establishes connections to both Regions at startup and exposes a failover() method that switches the active Prisma client in-process.

A few design decisions worth noting:

Both connections are established and verified at startup. Failover is instant because there is no cold connection to establish under pressure. The client verifies the secondary endpoint with SELECT 1, so you know it is healthy before you need it.

AuroraDSQLPool handles token refresh. Amazon Aurora DSQL uses short-lived IAM tokens. The connector manages the refresh cycle automatically, so your application doesn’t need to handle token expiry.

Failover is centralized and explicit. The withFailover wrapper in Step 5 triggers failover() automatically, but only on a narrow allowlist of connectivity errors: ECONNREFUSED, ETIMEDOUT, and unavailable. Optimistic concurrency control (OCC) contention errors (40001) are a separate steady-state condition and aren’t treated as failover triggers. Handle those with a retry-with-backoff strategy on mutations.

Failover is one-shot with no automatic fail-back. Once activePrisma switches to the secondary, it stays there until the process restarts. If the secondary then fails, failover() has no further fallback. For production use, consider either restarting on failover or adding a circuit breaker that periodically retests the primary and switches back when it recovers.

Step 5: Build the API server

The Express server (src/server.ts) wraps every database operation in a withFailover helper that catches connectivity errors and retries against the secondary Region:

async function withFailover<T>(
  operation: (client: PrismaClient) => Promise<T>
): Promise<T> {
  try {
    return await operation(await dsql.getClient());
  } catch (err: any) {
    if (
      err.code === "ECONNREFUSED" ||
      err.code === "ETIMEDOUT" ||
      err.message?.includes("unavailable")
    ) {
      console.warn("Primary failed, attempting failover...");
      const fallback = await dsql.failover();
      return await operation(fallback);
    }
    throw err;
  }
}

Route handlers pass their Prisma operations directly to withFailover:

app.post("/orders", async (req, res) => {
  const { product, quantity, items } = req.body;

  const order = await withFailover((client) =>
    client.order.create({
      data: {
        product,
        quantity: quantity || 1,
        region: process.env.AWS_REGION || "unknown",
        items: items?.length
          ? { create: items.map((i) => ({ name: i.name, price: i.price })) }
          : undefined,
      },
      include: { items: true },
    })
  );

  res.status(201).json(order);
});

The withFailover helper retries failed operations against the secondary Region. This is safe for reads, but writes require care. ECONNREFUSED means the connection didn’t reach the primary, so a retry is safe. ETIMEDOUT and unavailable are ambiguous. The write might have already committed on the primary before the error was returned. Retrying in that case can create duplicate records. For production workloads, restrict failover retries to reads only, or add idempotency keys to your mutation endpoints so the database can detect and ignore duplicate retries.

Each order’s Region field records its originating Region, which is useful for debugging and auditing in multi-Region deployments.

The server exposes the following endpoints:

Step 6: Deploy to multiple Regions

Set environment variables for each instance, then build and start:

npm install
npm run build

us-east-1 instance:

export CLUSTER_ENDPOINT="<id>.dsql.us-east-1.on.aws"
export CLUSTER_ENDPOINT_SECONDARY="<id>.dsql.us-west-2.on.aws"
export AWS_REGION="us-east-1"
export PORT=3000
npm start

us-west-2 instance:

export CLUSTER_ENDPOINT="<id>.dsql.us-west-2.on.aws"
export CLUSTER_ENDPOINT_SECONDARY="<id>.dsql.us-east-1.on.aws"
export AWS_REGION="us-west-2"
export PORT=3000
npm start

Each instance treats its local Region as primary and the peer as secondary. Amazon Route 53 latency-based routing directs users to the Region that provides the best latency. If a Region goes down, Amazon Route 53 health checks redirect traffic, and the surviving instances are already connected to the peer DSQL endpoint.

Test the API

# Health check
curl http://EC2-INSTANCE-IP:3000/health

# Create an order
curl -X POST http://EC2-INSTANCE-IP:3000/orders \
  -H "Content-Type: application/json" \
  -d '{"product": "Widget", "quantity": 2}'

# Create an order with line items
curl -X POST http://EC2-INSTANCE-IP:3000/orders \
  -H "Content-Type: application/json" \
  -d '{"product": "Bundle", "quantity": 1, "items": [{"name": "Part A", "price": 9.99}]}'

# List orders
curl http://EC2-INSTANCE-IP:3000/orders

Step 7: Test failover

The project includes an integration test that validates the full failover path against live DSQL clusters. Both CLUSTER_ENDPOINT and CLUSTER_ENDPOINT_SECONDARY must be set.

npm run test:failover

The test connects to both Regions, writes an order by the primary, triggers application-level failover to the secondary, and verifies the data replicated. DSQL replication is near-instant, but the test includes a retry loop to account for propagation time.

Expected output:

Primary:   <cluster-id>.dsql.us-east-1.on.aws
Secondary: <cluster-id>.dsql.us-west-2.on.aws

Test 1: Primary Region serves queries
  ✓ SELECT 1 returns expected result

Test 2: Write and read on primary
  ✓ Order created: <uuid>
  ✓ Order readable on primary

Test 3: Failover to secondary Region
  ✓ Active client changed after failover
  ✓ Secondary responds to queries

Test 4: Data replicated to secondary
  ✓ Order replicated to secondary Region

Results: 6 passed, 0 failed

Security considerations

This architecture implements multiple overlapping security layers:

• IAM authentication everywhere: Amazon Aurora DSQL uses short-lived IAM tokens (no static passwords). The AuroraDSQLPool handles token generation and refresh automatically. Scope dsql:DbConnectAdmin to specific cluster ARNs, not *. Use separate IAM roles for admin (migrations) and application (runtime) access.
• No Amazon VPC required: DSQL endpoints are publicly accessible and secured entirely by using IAM. There are no security groups or VPC peering configurations to manage.
• Schema validation in CI: Run npm run validate in your continuous integration (CI) pipeline to catch DSQL-incompatible schema changes (foreign keys, synchronous indexes) before they reach production.
• Least-privilege roles: The application runtime only needs dsql:DbConnect. Only the migration role needs dsql:DbConnectAdmin.

Cost considerations

The serverless architecture means you pay only for what you use:

• Amazon Aurora DSQL: You pay for database activity (read/write/compute) through Distributed Processing Units (DPUs) and storage consumed. No idle instance costs. See Amazon Aurora DSQL pricing.
• Amazon Elastic Compute Cloud (Amazon EC2) / Amazon Elastic Container Service (Amazon ECS) / AWS Lambda: You pay based on your chosen compute model for the application layer.
• Amazon Route 53: Amazon Route 53 charges per hosted zone and DNS query for latency-based routing.

For development and testing, costs are minimal since DSQL scales to zero when idle.

Clean up

To avoid ongoing charges, delete the resources created in this walkthrough.

Remove the database tables:

PRISMA_SCHEMA_DISABLE_ADVISORY_LOCK=1 npx prisma db execute --file prisma/migrations/0_init/down.sql

Example:

DROP TABLE IF EXISTS "order_items"; DROP TABLE IF EXISTS "orders";

Delete the DSQL clusters (disable deletion protection first):

aws dsql update-cluster --identifier <cluster-id> --no-deletion-protection-enabled --region us-east-1
aws dsql delete-cluster --identifier <cluster-id> --region us-east-1

aws dsql update-cluster --identifier <cluster-id> --no-deletion-protection-enabled --region us-west-2
aws dsql delete-cluster --identifier <cluster-id> --region us-west-2

Conclusion

In this post, you built a multi-Region active-active API using Prisma ORM and Amazon Aurora DSQL. The MultiRegionDsqlClient pattern establishes verified connections to both Regions at startup and switches the active Prisma client on failure. This gives you in-process failover without restarts or external orchestration.

The adaptation work for Prisma is straightforward: relationMode = "prisma" for referential integrity, gen_random_uuid() for primary keys, and the aurora-dsql-prisma tool for DSQL-compatible migrations. After those are in place, your Prisma queries work unchanged across both Regions.

By combining the type-safe query layer of Prisma with the distributed write capability of Amazon Aurora DSQL, you get an architecture where both Regions are truly active for writes with strong consistency, not only for reads. You can extend this pattern to other Node.js applications that need to serve traffic globally: ecommerce order management, financial transactions, real-time inventory, or workloads where both Regions need to accept writes simultaneously.

The complete source code and configuration files are available in the GitHub repository. Try it out and adapt it to your specific use case.

About the authors