/aurora-dsql

Aurora DSQL Get Started Guide

Overview

This guide provides steps to help users get started with Aurora DSQL in their project. It sets up their DSQL cluster with IAM authentication and connects their database to their code by understanding the context within the codebase.

Use Case

These guidelines apply when users say "Get started with DSQL" or similar phrases. The user's codebase may be mature (with existing database connections) or have little to no code - the guidelines should apply to both cases.

Contents

• Overview
• Use Case
• Agent Communication Style
• Get Started with DSQL (Interactive Guide) — 10-step linear walkthrough
• DSQL Best Practices
• Additional Resources

Agent Communication Style

Keep all responses succinct:

• ALWAYS tell the user what you did.
  • Responses MUST be concise and concrete.
  • ALWAYS contain descriptions to necessary steps.
  • ALWAYS remove unnecessary verbiage.
  • Example:
    • "Created an inventory table with 4 columns"
    • "Updated the product column to be NOT NULL"
• Ask direct questions when needed:
  • ALWAYS ask clarifying questions to avoid inaccurate assumptions
  • User ambiguity SHOULD result in questions.
  • MUST clarify incompatible user decisions
  • Example:
    • "What column names would you like in this table?"
    • "What is the column name of the primary key?"
    • "JSON must be serialized. Would you like to stringify the JSON to serialize it as TEXT?"

Examples:

• Good: "Generated auth token. Ready to connect with psql?"
• Bad: "I'm going to generate an authentication token using the AWS CLI which will allow you to connect to your database. This token will be valid for..."

Get Started with DSQL (Interactive Guide)

TRIGGER PHRASE: When the user says "Get started with DSQL", "Get started with Aurora DSQL", or similar phrases, provide an interactive onboarding experience by following these steps:

Before starting: Let the user know they can pause and resume anytime by saying "Continue with DSQL setup" if they need to come back later.

RESUME TRIGGER: If the user says "Continue with DSQL setup" or similar, check what's already configured (AWS credentials, clusters, AWS MCP Server installation if applicable, connection tested) and resume from where they left off. Ask them which step they'd like to continue from or analyze their setup to determine automatically.

Step 1: Verify Prerequisites

Check AWS credentials:

aws sts get-caller-identity

If not configured:

• Guide them through aws configure

• MUST verify IAM permissions include dsql:CreateCluster, dsql:GetCluster, dsql:DbConnectAdmin

• For initial setup, use a scoped inline policy with only the minimum permissions needed. dsql:CreateCluster and dsql:ListClusters cannot target a specific cluster ARN (the cluster does not yet exist, and ListClusters is a list operation), so they go in a separate statement with Resource: "*". Cluster-scoped actions stay on the specific ARN:

  {
    "Version": "2012-10-17",
    "Statement": [
      {
        "Effect": "Allow",
        "Action": [
          "dsql:CreateCluster",
          "dsql:ListClusters"
        ],
        "Resource": "*"
      },
      {
        "Effect": "Allow",
        "Action": [
          "dsql:GetCluster",
          "dsql:DeleteCluster",
          "dsql:DbConnectAdmin"
        ],
        "Resource": "arn:aws:dsql:<region>:<account-id>:cluster/<cluster-id>"
      }
    ]
  }

  Replace <cluster-id> with the actual cluster ID returned from cluster creation. After Step 4, narrow the first statement further by adding an aws:RequestTag/<key> condition to dsql:CreateCluster (or remove dsql:CreateCluster entirely once the cluster exists). aws:RequestTag is required here because dsql:CreateCluster runs before any cluster (and any resource tag) exists. Revoke dsql:DbConnectAdmin after scoped database roles are established (Step 9).

Check PostgreSQL client:

psql --version

**If missing OR version <=14:** DSQL requires SNI support from psql >=14.

• macOS: brew install postgresql@17

• Linux (Debian/Ubuntu): sudo apt-get install postgresql-client

• Linux (RHEL/CentOS/Amazon Linux):

  sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  sudo yum install -y postgresql17

Step 2: Check for Existing Clusters

Set region (uses AWS_REGION or REGION if set, defaults to us-east-1):

REGION=${AWS_REGION:-${REGION:-us-east-1}}
echo $REGION

List clusters in the region:

aws dsql list-clusters --region $REGION

If they have NO clusters:

• Ask: "Would you like to create a new DSQL cluster in $REGION or a different region?"
  • If yes, proceed to create single-region cluster
  • If they want different region, ask which one and update REGION variable

If they have ANY clusters:

• List ALL cluster identifiers with creation dates and status
• Ask: "Would you like to use one of these clusters or create a new one?"
  • If using existing, proceed to Step 3.
  • If creating new:
    • "Which region would you like to create a enw cluster in?"
    • Immediately update REGION variable
• Confirm all selections before proceeding.

Create cluster command (if needed):

aws dsql create-cluster --region $REGION --tags '{"Name":"my-dsql-cluster","created_by":"<model-id>"}'

Wait for ACTIVE status (takes ~60 seconds):

aws dsql get-cluster --identifier CLUSTER_ID --region $REGION

Step 3: Get Cluster Connection Details

Construct cluster endpoint:

CLUSTER_ID="<selected-cluster-id>"
CLUSTER_ENDPOINT="${CLUSTER_ID}.dsql.${REGION}.on.aws"
echo $CLUSTER_ENDPOINT

Store endpoint for their project environment:

• Check for .env file or environment config
• Add or update: DSQL_ENDPOINT=<endpoint>
• Add region: AWS_REGION=$REGION
• ALWAYS try reading .env first before modifying
• If file is unreadable, use: echo "DSQL_ENDPOINT=$CLUSTER_ENDPOINT" >> .env

Step 4: Set Up the AWS MCP Server (Optional)

Would the user like AWS knowledge tools (documentation search/read, AWS API access) wired into their coding assistant?

If so, install the AWS MCP Server per the AWS docs. It provides:

• aws___search_documentation / aws___read_documentation / aws___recommend — DSQL docs lookup
• aws___call_aws — authenticated AWS API calls (for dsql: actions like cluster management)
• aws___run_script — sandboxed Python with AWS API access

A custom DSQL-specific MCP is optional. If the user has one configured already, it can stay alongside the AWS MCP Server. For ad-hoc DSQL queries, this skill PREFERS direct psql via scripts/psql-connect.sh over MCP-mediated execution.

Step 5: Test Connection

⚠️ Security Note: The admin connection (generate-db-connect-admin-auth-token + admin user) should only be used for the initial setup steps below (creating roles, granting permissions). Once scoped roles are established in Step 9, all subsequent operations should use the scoped role with generate-db-connect-auth-token. Consider revoking dsql:DbConnectAdmin from the setup IAM role after scoped roles are in place.

Generate authentication token and connect:

export PGPASSWORD=$(aws dsql generate-db-connect-admin-auth-token \
  --region $REGION \
  --hostname $CLUSTER_ENDPOINT \
  --expires-in 3600)

export PGSSLMODE=verify-full
export PGAPPNAME="<app-name>/<model-id>"

psql --quiet -h $CLUSTER_ENDPOINT -U admin -d postgres

Verify with test query:

SELECT current_database(), version();

If connection fails:

• Check token expiration (regenerate if needed)
• Verify SSL mode is set
• Confirm cluster is ACTIVE
• Check IAM permissions

Step 6: Understand the Project

First, check if this is an empty/new project:

• Look for existing source code, routes, or application logic
• Check if it's just minimal boilerplate

If empty or near-empty project:

• Ask briefly (1-2 questions): What are they building? Any specific tech preferences?
• Remember context for subsequent steps

If established project:

• Skip questions - infer from codebase
• Check for existing database code or ORMs
• Update relevant code to use DSQL

ALWAYS reference ./development-guide.md before making schema changes

Step 7: Install Database Driver

Based on their language, install appropriate driver (some examples):

JavaScript/TypeScript:

npm install @aws/aurora-dsql-node-postgres-connector tsx

Python:

pip install aurora-dsql-python-connector 'psycopg[binary]' psycopg-pool

Go:

go get github.com/awslabs/aurora-dsql-connectors/go/pgx

Rust:

cargo add aurora-dsql-sqlx-connector --features pool,occ
cargo add sqlx tokio --features postgres,runtime-tokio-native-tls,full

For implementation patterns, reference ./dsql-examples.md and ./language.md

Step 8: Schema Setup

Check for existing schema:

• Search for .sql files, migration folders, ORM schemas (Prisma, Drizzle, TypeORM)

If existing schema found:

• Show what you found
• Ask: "Found existing schema definitions. Want to migrate these to DSQL?"
• If yes, MUST verify DSQL compatibility:
  • No SERIAL types (use GENERATED AS IDENTITY with sequences, or UUID)
  • No foreign keys (implement in application)
  • No array/JSON column types (serialize as TEXT)
  • Reference ./development-guide.md for full constraints

If no schema found:

• Ask if they want to:
  1. Create simple example table
  2. Design custom schema together
  3. Skip for now

If creating example table:

Use scripts/psql-connect.sh --admin to execute:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX ASYNC idx_users_email ON users(email);

For custom schema:

• Ask about their app's needs
• Design tables following DSQL constraints
• Reference ./dsql-examples.md for patterns
• ALWAYS use CREATE INDEX ASYNC for all indexes

Step 9: Set Up Scoped Database Roles

Recommend creating scoped roles before application development begins.

• Ask: "Would you like to set up scoped database roles for your application? This is recommended over using admin directly."
• If yes, follow access-control.md for detailed guidance
• At minimum, guide creating one application role:

-- As admin
CREATE ROLE app_user WITH LOGIN;
AWS IAM GRANT app_user TO 'arn:aws:iam::<account-id>:role/<AppIAMRole>';
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

• If the application handles sensitive user data, recommend a separate schema:

CREATE SCHEMA users_schema;
GRANT USAGE ON SCHEMA users_schema TO app_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA users_schema TO app_user;
GRANT CREATE ON SCHEMA users_schema TO app_user;

• After setup, application connections should use generate-db-connect-auth-token (not the admin variant)

Step 10: What's Next

Let them know you're ready to help with more:

"You're all set! Here are some things I can help with - feel free to ask about any of these (or anything else):

• Schema design and migrations following DSQL best practices
• Writing queries with proper tenant isolation
• Connection pooling and token refresh strategies
• Multi-region cluster setup for high availability
• Performance optimization with indexes and query patterns
• Setting up additional scoped roles for different services"

Important Notes:

• ALWAYS be succinct - guide step-by-step without verbose explanations
• ALWAYS check ./development-guide.md before schema operations
• ALWAYS prefer scripts/psql-connect.sh for DSQL queries; reach for the AWS MCP Server when AWS knowledge or dsql: API calls are needed
• ALWAYS validate DSQL compatibility for existing schemas
• ALWAYS provide working, tested commands
• MUST handle token expiration gracefully (15-minute default, 1-hour recommended)

DSQL Best Practices

Critical Constraints

ALWAYS follow these rules:

1. Indexes: Use CREATE INDEX ASYNC - synchronous index creation not supported
2. Serialization: Store arrays/JSON as TEXT (comma-separated or JSON.stringify)
3. Referential Integrity: Implement foreign key validation in application code
4. DDL Operations: Execute one DDL per transaction, no mixing with DML
5. Transaction Limits: Maximum 3,000 row modifications, 10 MiB data size per transaction
6. Token Refresh: Regenerate auth tokens before 15-minute expiration
7. SSL Required: Always set PGSSLMODE=verify-full or sslmode=verify-full (use require as a fallback only when the CA bundle is unavailable)

DSQL-Specific Features

Leverage Aurora DSQL capabilities:

1. Serverless: True scale-to-zero with consumption-based pricing
2. Distributed: Active-active writes across multiple regions
3. Strong Consistency: Immediate read-your-writes across all regions
4. IAM Authentication: No password management, automatic token rotation
5. PostgreSQL Compatible: Supports many database drivers, ORMs, and adapters — see the AWS DSQL connectivity tools page for the current list.

For detailed patterns, see ./development-guide.md

Additional Resources

• Aurora DSQL Documentation
• Aurora DSQL Starter Kit
• Aurora DSQL Connectivity Tools (drivers, ORMs, samples)
• IAM Authentication Guide
• Getting Started Guide
• PostgreSQL Compatibility
• Incompatible PostgreSQL Features
• CloudFormation Resource