Skip to content
DaloyJS

Search docs

Jump between documentation pages.

Browse docs

Use AWS Aurora DSQL with DaloyJS

Aurora DSQL is AWS's serverless, distributed PostgreSQL-compatible service. It speaks the Postgres wire protocol, so you use the standard pg driver, but the password is a short-lived IAM auth token instead of a static secret. Pair it with the Lambda adapter for an AWS-native stack.

1. Provision a cluster

Create a cluster in the AWS console or via the CLI. Note the endpoint hostname (<cluster-id>.dsql.<region>.on.aws) and the cluster ARN.

bash
aws dsql create-cluster --region eu-west-1

2. Create a least-privilege database role

DSQL has a built-in admin role. Use it to set up the schema and run migrations, not for application traffic. Connect once as admin (for example from CloudShell with psql) and create a login role for the app, mapped to the IAM role your Lambda function or server runs as:

sql
CREATE ROLE app WITH LOGIN;
AWS IAM GRANT app TO 'arn:aws:iam::123456789012:role/my-api-runtime';

CREATE SCHEMA app;
GRANT USAGE ON SCHEMA app TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app;

Create your tables in the app schema. ON ALL TABLES covers only tables that exist when it runs, so repeat the GRANT in the migration that adds a table. List the IAM mappings with SELECT * FROM sys.iam_pg_role_mappings; and remove one with AWS IAM REVOKE.

Then give the runtime IAM role only dsql:DbConnect on this cluster. dsql:DbConnectAdmin (which signs admin tokens) belongs to the migration pipeline only.

iam-policy.jsonjson
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "dsql:DbConnect",
      "Resource": "arn:aws:dsql:eu-west-1:123456789012:cluster/<cluster-id>"
    }
  ]
}

3. Install

bash
pnpm add pg @aws-sdk/dsql-signer
bash
pnpm add -D @types/pg

4. How the token works

IAM token auth flow
  1. 01DsqlSignernew DsqlSigner({ hostname, region })
  2. 02Sign a tokensigner.getDbConnectAuthToken() · role app
  3. 03Open a connectiontoken checked once, at connect
  4. 04Reuse itvalid until the 60-minute connection limit
  5. 05Pool recyclesmaxLifetimeSeconds < 3600 · new token per connection
The token only authenticates a new connection. An open connection stays valid after the token expires, until DSQL closes it at 60 minutes, so recycle pooled connections before that.

Signing is a local operation; it does not call AWS. The token (15 minutes by default) is checked only when a connection opens, and an established connection stays valid after it expires. The limits that matter are server-side: DSQL closes every connection after 60 minutes and aborts any transaction older than 5 minutes. So you do not need a token per request; you need a fresh token for every new connection, and a pool that retires connections before the 60-minute mark.

5. A module-scope pool

src/db/dsql.tsts
import { Pool } from "pg";
import { DsqlSigner } from "@aws-sdk/dsql-signer";

const hostname = process.env.DSQL_ENDPOINT;
if (!hostname) throw new Error("DSQL_ENDPOINT is not set");

// <cluster-id>.dsql.<region>.on.aws: derive the region instead of guessing.
const region = /^[a-z0-9]+\.dsql\.([a-z0-9-]+)\.on\.aws$/.exec(hostname)?.[1];
if (!region) throw new Error(`Cannot derive the AWS region from DSQL_ENDPOINT "${hostname}"`);

const signer = new DsqlSigner({ hostname, region });

export const pool = new Pool({
  host: hostname,
  port: 5432,
  user: "app",
  database: "postgres",
  // Called for every new connection, so each one gets a fresh token.
  password: () => signer.getDbConnectAuthToken(),
  ssl: { rejectUnauthorized: true },
  max: 5,
  idleTimeoutMillis: 60_000,
  // Retire connections well before DSQL's 60-minute connection limit.
  maxLifetimeSeconds: 50 * 60,
});

declare module "@daloyjs/core" {
  interface AppState {
    db: Pool;
  }
}

Keep the declare module block in the module that creates the pool, not in a separate .d.ts file. Declaration files are exempt from type-checking when skipLibCheck is on (the scaffolded default), so a broken import inside a .d.ts fails silently and state.db degrades to any.

Or use the AWS connector

AWS recommends its Aurora DSQL Connector for node-postgres (@aws/aurora-dsql-node-postgres-connector, pre-1.0 on npm). It wraps pg Client and Pool, generates a token per connection, reads the region from the hostname, and has a transaction() helper that retries optimistic-concurrency conflicts:

bash
pnpm add @aws/aurora-dsql-node-postgres-connector pg pg-connection-string
src/db/dsql.tsts
import { AuroraDSQLPool } from "@aws/aurora-dsql-node-postgres-connector";

export const pool = new AuroraDSQLPool({
  host: process.env.DSQL_ENDPOINT!,
  user: "app",
  max: 5,
  idleTimeoutMillis: 60_000,
});

6. Wire it into the app

On Node, Bun, or Deno, decorate the pool and drain it on shutdown:

src/db/plugin.tsts
import type { App } from "@daloyjs/core";
import { pool } from "./dsql.ts";

// Call with the root app, not through app.register(): a decoration made
// inside a registered plugin is visible only to that plugin's own routes.
export function dsqlPlugin(app: App) {
  app.decorate("db", pool);
  // Runs on the Node, Bun, and Deno adapters after in-flight requests drain.
  app.onClose(async () => {
    await pool.end();
  });
}

On the Lambda adapter, use the same module-scope pool: warm invocations reuse its connections, password() signs a new token whenever the pool opens one, and maxLifetimeSeconds keeps every connection under the 60-minute limit. Lambda has no shutdown hook, so onClose() never runs there; the small max and the idle timeout keep a frozen container from holding many connections.

src/handler.tsts
import { App } from "@daloyjs/core";
import { toLambdaHandler } from "@daloyjs/core/lambda";
import { pool } from "./db/dsql.ts";

const app = new App();
app.decorate("db", pool);

// ...register routes that read state.db

export const handler = toLambdaHandler(app);

7. Retry optimistic concurrency conflicts

DSQL uses optimistic concurrency control: transactions never wait on locks, and a conflicting transaction fails at COMMIT with SQLSTATE 40001 (the AWS connector also retries OC000 and OC001). Keep transactions short and idempotent, and retry them:

src/db/retry.tsts
import type { Pool, PoolClient } from "pg";

const RETRYABLE = new Set(["40001", "OC000", "OC001"]);

export async function withTransaction<T>(
  pool: Pool,
  work: (client: PoolClient) => Promise<T>,
  maxRetries = 3,
): Promise<T> {
  for (let attempt = 0; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query("begin");
      const result = await work(client);
      await client.query("commit");
      return result;
    } catch (err) {
      await client.query("rollback").catch(() => {});
      const code = (err as { code?: string }).code;
      if (!code || !RETRYABLE.has(code) || attempt >= maxRetries) throw err;
      // Exponential backoff with jitter: 50 ms, 100 ms, 200 ms, ...
      await new Promise((r) => setTimeout(r, 50 * 2 ** attempt * (0.5 + Math.random())));
    } finally {
      client.release();
    }
  }
}

Let unexpected errors throw: DaloyJS redacts 5xx detail in production, so the driver error is not echoed to clients.

8. Limits that affect ORM migrations

DSQL is PostgreSQL-compatible, not identical. These rules (from the AWS migration guide and quotas) break most migration tools' defaults:

  • A transaction can hold only one DDL statement, and DDL and DML need separate transactions. Run each migration statement on its own, and turn off any "wrap the whole migration in one transaction" behavior.
  • Use CREATE INDEX ASYNC instead of CREATE INDEX, and DELETE FROM instead of TRUNCATE.
  • No temporary tables, triggers, or procedural languages such as PL/pgSQL (SQL functions are supported).
  • A write transaction can modify at most 3,000 rows and 10 MiB, and lives at most 5 minutes, so batch backfills.
  • The isolation level is fixed at Repeatable Read, there is one database (postgres) per cluster, and up to 10 schemas.
  • Prefer UUID primary keys over sequential ids to spread writes; see sequences and identity columns if you need them.

Run migrations from CI with an admin token (signer.getDbConnectAdminAuthToken(), which needs dsql:DbConnectAdmin), then re-grant the app role on any new tables.

With Drizzle ORM

bash
pnpm add drizzle-orm
src/db/drizzle.tsts
import { drizzle } from "drizzle-orm/node-postgres";
import { pool } from "./dsql.ts";

export const db = drizzle({ client: pool });

With Prisma

Use the pg driver adapter and pass it the token-refreshing pool: new PrismaPg(pool) from @prisma/adapter-pg, then new PrismaClient({ adapter }) from your generated client. See Prisma. Check the limits above before running prisma migrate against DSQL.

Things to remember

  • Cloudflare Workers are not covered by this guide. Hyperdrive stores static credentials, and DSQL only accepts short-lived IAM tokens. On Workers, use Neon, PlanetScale, or another Postgres host through Hyperdrive.
  • Tokens only gate new connections; the 60-minute connection limit and 5-minute transaction limit are what your pool and transactions must respect.
  • Check the known issues before relying on niche extensions or features.

See also the database hosting overview or jump back to Prisma / Drizzle.

Edit this page on GitHub

Last updated