@aws-blocks/bb-distributed-data

Serverless SQL database backed by Amazon Aurora DSQL. Zero-ops, instant provisioning, scale-to-zero, and optionally multi-region active-active writes.

When to use: Serverless apps that need to scale without ops overhead, workloads with zero idle cost requirements, multi-region active-active writes, or any SQL app where you don't need Row Level Security, triggers, procedures, or PL/pgSQL functions.

When NOT to use: If you need Row Level Security, triggers, procedures, or PL/pgSQL functions — use Database (Aurora). Aurora DSQL supports SQL-language functions. If you need transactions that must not fail at commit under contention — use Database. If you're connecting to Supabase — use Database with fromExisting().

Design & mock parity details: DESIGN.md

Quick Start

import { DistributedDatabase, sql } from '@aws-blocks/bb-distributed-data';

const db = new DistributedDatabase(scope, 'main', {
  migrationsPath: './aws-blocks/dsql-migrations',
});

// Same query API as Database — parameterized via sql tagged template
const users = await db.query<{ id: string; name: string }>(
  sql`SELECT * FROM users WHERE active = ${true}`
);

const user = await db.queryOne<{ id: string; name: string }>(
  sql`SELECT * FROM users WHERE id = ${userId}`
);

const { rowCount } = await db.execute(
  sql`INSERT INTO users (id, name, email) VALUES (${id}, ${name}, ${email})`
);

Transactions (Optimistic Concurrency Control)

DSQL uses OCC — transactions may fail at commit if another transaction modified the same rows. The callback executes exactly once unless you opt into retry.

// Default: no retry. Throws SerializationFailureException (HTTP 409 Conflict, retriable) on conflict.
await db.transaction(async (tx) => {
  await tx.execute(sql`UPDATE accounts SET balance = balance - ${100} WHERE id = ${fromId}`);
  await tx.execute(sql`UPDATE accounts SET balance = balance + ${100} WHERE id = ${toId}`);
});

// Opt-in retry: callback may execute multiple times.
// ⚠️ Do NOT include external side effects (HTTP calls, emails) inside.
await db.transaction(async (tx) => {
  await tx.execute(sql`UPDATE accounts SET balance = balance - ${100} WHERE id = ${fromId}`);
  await tx.execute(sql`UPDATE accounts SET balance = balance + ${100} WHERE id = ${toId}`);
}, { retryOnConflict: true, maxRetries: 3 });

The second argument to transaction() accepts:

interface TransactionOptions {
  /** Retry on OCC conflict. Callback may execute multiple times. @default false */
  retryOnConflict?: boolean;
  /** Max retry attempts. Only applies when retryOnConflict is true. @default 3 */
  maxRetries?: number;
}

Migrations

One DDL statement per file. DML in separate files. This matches DSQL's transaction constraints.

aws-blocks/dsql-migrations/
  001_create_users.sql       ← single DDL
  002_create_posts.sql       ← single DDL
  003_create_index.sql       ← single DDL (CREATE INDEX ASYNC)
  004_seed_admin.sql         ← DML only

-- 001_create_users.sql
CREATE TABLE users (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 003_create_index.sql
CREATE INDEX ASYNC idx_users_email ON users(email);

Migrations are checked with dsql-lint at synth time.

Set migrationsPath to a path relative to your project root (e.g. './aws-blocks/dsql-migrations'); it's resolved at synth from the directory you run cdk / npm run deploy in. That's the simplest reliable pattern.

You don't need fileURLToPath(import.meta.url) for this. Your backend module runs as ESM locally but is bundled to CommonJS in Lambda, where import.meta is empty. AWS Blocks shims import.meta.url / import.meta.dirname in the bundle so it won't crash at load — but at runtime those resolve to the bundled output location, not your source tree. So don't use import.meta.url to read a file relative to your source at request time; inline the data or ship it as a Lambda asset instead.

PostgreSQL Compatibility

Aurora DSQL is PostgreSQL wire-protocol compatible. SQL feature compatibility differs from PostgreSQL; see the Aurora DSQL SQL compatibility documentation for full details.

Transaction Constraints

Kysely Query Builder

import { createKyselyAdapter } from '@aws-blocks/bb-distributed-data';

interface Schema {
  users: { id: string; email: string; name: string };
}

const kysely = createKyselyAdapter<Schema>(db);

const users = await kysely
  .selectFrom('users')
  .where('email', '=', '[email protected]')
  .selectAll()
  .execute();

Do not use Kysely's .addForeignKeyConstraint() — it will fail on DSQL.

Error Handling

import { DistributedDatabaseErrors } from '@aws-blocks/bb-distributed-data';
import { isBlocksError } from '@aws-blocks/core';

try {
  await db.transaction(async (tx) => { /* ... */ });
} catch (e: unknown) {
  if (isBlocksError(e, DistributedDatabaseErrors.SerializationFailure)) {
    // OCC conflict — transaction was NOT committed. Serialized as HTTP 409
    // (Conflict), retriable — safe to retry.
  }
  if (isBlocksError(e, DistributedDatabaseErrors.UniqueConstraintViolation)) {
    // Duplicate key — serialized as HTTP 409 (Conflict), not retriable
    // (a blind retry of the same insert fails identically).
  }
  if (isBlocksError(e, DistributedDatabaseErrors.QueryFailed)) {
    // General query failure
  }
  if (isBlocksError(e, DistributedDatabaseErrors.TransactionRowLimitExceeded)) {
    // More than 3,000 rows mutated in one transaction
  }
  if (isBlocksError(e, DistributedDatabaseErrors.ConnectionFailed)) {
    // Cannot reach the DSQL cluster
  }
  if (isBlocksError(e, DistributedDatabaseErrors.TransactionFailed)) {
    // Transaction could not commit (general failure distinct from serialization/row-limit)
  }
}

Testing OCC Conflicts

The mock provides a simulateConflict() helper for unit testing. It only works against the local mock: in the deployed AWS runtime it throws, and during cdk synth it throws the usual synth-time error.

db.simulateConflict(); // next commit will fail with SerializationFailureException

await expect(db.transaction(fn)).rejects.toThrow('SerializationFailureException');

db.simulateConflict(); // first attempt fails, second succeeds
const result = await db.transaction(fn, { retryOnConflict: true });

What It Provisions (AWS)

• Aurora DSQL Cluster — Serverless, no VPC required, public endpoint
• Migration Lambda — Runs .sql files on deploy via CustomResource with retry
• IAM — dsql:DbConnect (app Lambda, DML only), dsql:DbConnectAdmin (migration Lambda, DDL)
• Environment variables — BLOCKS_{name}_ENDPOINT, BLOCKS_{name}_REGION

No VPC, no secrets, no security groups, no proxy. DSQL uses IAM token authentication.

Local Development

• Engine: PGlite (WASM PostgreSQL) wrapped with a DSQL compatibility layer
• Storage: .bb-data/{fullId}/ — persists across restarts
• Validation: dsql-lint checks SQL before PGlite executes it
• Transaction tracking: DDL/DML mixing and 3,000-row limit enforced locally

Migration SQL is also checked with dsql-lint during CDK synthesis, before it is bundled for deployment.

Configuration

interface DistributedDatabaseOptions {
  /** Path to directory containing numbered .sql migration files. */
  migrationsPath?: string;
  /** Removal policy. @default 'retain' */
  removalPolicy?: 'destroy' | 'retain';
  /** Optional logger for internal operations. When omitted, a default Logger at error level is created. */
  logger?: ChildLogger;
}

Package Export Conditions

{
  "exports": {
    ".": {
      "cdk": "./dist/index.cdk.js",
      "aws-runtime": "./dist/index.aws.js",
      "default": "./dist/index.mock.js"
    }
  }
}

Differences from Database (Aurora)