AWS Aurora PostgreSQL Integration

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

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

Guidelines

•Use the standard pg (node-postgres) package to interact with an AWS Aurora PostgreSQL database.
•Use AWS IAM authentication with Aurora PostgreSQL via the @aws-sdk/rds-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 PostgreSQL:
- •Use Aurora PostgreSQL variables: PGHOST, AWS_REGION, AWS_ROLE_ARN, PGUSER, and project-based AWS credentials using awsCredentialsProvider.
•Use parameterized queries to prevent SQL injection attacks.
•Aurora PostgreSQL is fully PostgreSQL-compatible, so you can use standard PostgreSQL syntax and features including SERIAL types, triggers, sequences, and all standard PostgreSQL functionality.
•Always use awsCredentialsProvider from @vercel/functions/oidc to get AWS credentials.
•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-rds
•@aws-sdk/rds-signer
•pg

Optional dependencies:

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

Directory Structure

text

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

sql

-- Create todos table with auto-incrementing ID
CREATE TABLE IF NOT EXISTS todos (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  completed BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create index for efficient querying
CREATE INDEX IF NOT EXISTS idx_todos_created_at ON todos(created_at DESC);

lib/db.ts

typescript

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

const signer = new Signer({
  credentials: awsCredentialsProvider({
    roleArn: process.env.AWS_ROLE_ARN,
    clientConfig: { region: process.env.AWS_REGION },
  }),
  region: process.env.AWS_REGION,
  hostname: process.env.PGHOST,
  username: process.env.PGUSER || 'postgres',
  port: 5432,
})

const pool = new Pool({
  host: process.env.PGHOST,
  database: process.env.PGDATABASE || 'postgres',
  port: 5432,
  user: process.env.PGUSER || 'postgres',
  // The auth token value can be cached for up to 15 minutes (900 seconds) if desired.
  password: () => signer.getAuthToken(),
  // Recommended to switch to `true` in production.
  // See https://docs.aws.amazon.com/lambda/latest/dg/services-rds.html#rds-lambda-certificates
  ssl: { rejectUnauthorized: false },
  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

typescript

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

Reference Files

File	Description
`references/examples/scripts/001-setup-todos-schema.sql`	SQL schema for creating the todos table with auto-incrementing ID
`references/examples/lib/db.ts`	Database connection pool using RDS Signer with IAM authentication
`references/examples/lib/types.ts`	TypeScript interface for Todo (id is number)
`references/examples/app/api/todos/route.ts`	API route handlers for GET (list) and POST (create) todos
`references/examples/app/api/todos/[id]/route.ts`	API route handlers for PATCH (update) and DELETE todos (uses parseInt for id)
`references/examples/app/page.tsx`	Home page component that renders the TodoApp
`references/examples/components/todo-app.tsx`	Client-side Todo list component with CRUD operations (id: number in functions)