AWS Aurora DSQL Integration

Explore these example files to see a minimal implementation of AWS Aurora DSQL in a Next.js application.

This is a simple TODO app that demonstrates how to connect to an AWS Aurora DSQL database using awsCredentialsProvider authentication and perform basic CRUD operations.

Guidelines

• •Use the standard pg (node-postgres) package to interact with an AWS Aurora DSQL database.
• •Use AWS IAM authentication with Aurora DSQL via the @aws-sdk/dsql-signer package.
• •Create a connection pool using new Pool() from the pg package.
• •Use attachDatabasePool from @vercel/functions when using Pool.
• •When connecting to Aurora DSQL:
  • •Use DSQL variables: PGHOST, AWS_REGION, AWS_ROLE_ARN, and project-based AWS credentials using awsCredentialsProvider.
• •Use parameterized queries to prevent SQL injection attacks.
• •Aurora DSQL is partially PostgreSQL-compatible, so you can use standard PostgreSQL syntax and features.
• •DO NOT use the following non-supported Postgres features: "Temporary Tables", "Triggers", "Tablespaces", "Sequences", "Partitions".
• •DO NOT use the ON UPDATE clause in create table statements.
• •DO NOT use SERIAL, BIGSERIAL, SMALLSERIAL types in create and alter table statements.
• •ALWAYS use ASYNC when creating indexes.
• •DO NOT use ASC or DESC for index ordering.
• •ALWAYS use awsCredentialsProvider from @vercel/functions/oidc to get AWS credentials.
• •Use COMMIT after each DDL statement in scripts because Aurora DSQL does not support multiple DDL statements in the same transaction.
• •Be careful when dealing with decimal types, which may be returned as strings by default.

Dependencies

Required dependencies:

• •@vercel/functions/oidc
• •@vercel/functions
• •@aws-sdk/client-dsql
• •@aws-sdk/dsql-signer
• •pg

Optional dependencies:

• •nanoid: for generating unique IDs
• •lucide-react: for UI components and icons
• •swr: for client-side data fetching

Directory Structure

app/
  api/
    todos/
      [id]/
        route.ts
      route.ts
  page.tsx
components/
  todo-app.tsx
lib/
  db.ts
  types.ts
scripts/
  001-setup-todos-schema.sql

Core Files

scripts/001-setup-todos-schema.sql

CREATE TABLE IF NOT EXISTS todos (
  id VARCHAR(100) PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  completed BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);
COMMIT;

-- IMPORTANT: Must always use ASYNC when creating indexes
-- IMPORTANT: Must not use ASC/DESC for index ordering
CREATE INDEX ASYNC IF NOT EXISTS idx_todos_created_at ON todos(created_at);
COMMIT;

lib/db.ts

import { Pool, ClientBase } from 'pg'
import { DsqlSigner } from '@aws-sdk/dsql-signer'
import { awsCredentialsProvider } from '@vercel/functions/oidc'
import { attachDatabasePool } from '@vercel/functions'

const signer = new DsqlSigner({
  credentials: awsCredentialsProvider({
    roleArn: process.env.AWS_ROLE_ARN,
    clientConfig: { region: process.env.AWS_REGION },
  }),
  region: process.env.AWS_REGION,
  hostname: process.env.PGHOST,
  expiresIn: 900,
})

const pool = new Pool({
  host: process.env.PGHOST,
  user: process.env.PGUSER || 'admin',
  database: process.env.PGDATABASE || 'postgres',
  // The auth token value can be cached for up to 15 minutes (900 seconds) if desired.
  password: () => signer.getDbConnectAdminAuthToken(),
  port: 5432,
  ssl: true,
  max: 20,
})
attachDatabasePool(pool)

// Single query transactions.
export async function query(text: string, params?: unknown[]) {
  return pool.query(text, params)
}

// Use for multi-query transactions.
export async function withConnection<T>(
  fn: (client: ClientBase) => Promise<T>,
): Promise<T> {
  const client = await pool.connect()
  try {
    return await fn(client)
  } finally {
    client.release()
  }
}

lib/types.ts

export interface Todo {
  id: string
  title: string
  completed: boolean
  created_at: string
}

Reference Files