name	dsql
description	Build with Aurora DSQL - manage schemas, execute queries, and handle migrations with DSQL-specific requirements. Use when developing a scalable or distributed database/application or user requests DSQL.

Amazon Aurora DSQL Skill

Aurora DSQL is a serverless, PostgreSQL-compatible distributed SQL database with specific constraints. This skill provides direct database interaction via MCP tools, schema management, migration support, and multi-tenant patterns.

Key capabilities: - Direct query execution via MCP tools - Schema management with DSQL constraints - Migration support and safe schema evolution - Multi-tenant isolation patterns - IAM-based authentication

Reference Files

Load these files as needed for detailed guidance:

development-guide.md

When: ALWAYS load before implementing schema changes or database operations Contains: DDL rules, connection patterns, transaction limits, security best practices

MCP:

mcp-setup.md

When: Load for guidance adding to the DSQL MCP server Requires: An existing cluster endpoint Contains: Instructions for setting up the DSQL MCP server

mcp-tools.md

When: Load when you need detailed MCP tool syntax and examples Contains: Tool parameters, detailed examples, usage patterns

language.md

When: MUST load when making language-specific implementation choices Contains: Driver selection, framework patterns, connection code for Python/JS/Go/Java/Rust

dsql-examples.md

When: Load when looking for specific implementation examples Contains: Code examples, repository patterns, multi-tenant implementations

troubleshooting.md

When: Load when debugging errors or unexpected behavior Contains: Common pitfalls, error messages, solutions

onboarding.md

When: User explicitly requests to "Get started with DSQL" or similar phrase Contains: Interactive step-by-step guide for new users

MCP Tools Available

The aurora-dsql MCP server provides these tools:

Database Operations: 1. readonly_query - Execute SELECT queries (returns list of dicts) 2. transact - Execute DDL/DML statements in transaction (takes list of SQL statements) 3. get_schema - Get table structure for a specific table

Documentation & Knowledge: 4. dsql_search_documentation - Search Aurora DSQL documentation 5. dsql_read_documentation - Read specific documentation pages 6. dsql_recommend - Get DSQL best practice recommendations

Note: There is no list_tables tool. Use readonly_query with information_schema.

See mcp-tools.md for detailed usage and examples.

CLI Scripts Available

Bash scripts for cluster management and direct psql connections. All scripts are located in scripts/.

Cluster Management: - create-cluster.sh - Create new DSQL cluster with optional tags - delete-cluster.sh - Delete cluster with confirmation prompt - list-clusters.sh - List all clusters in a region - cluster-info.sh - Get detailed cluster information

Database Connection: - psql-connect.sh - Connect to DSQL using psql with automatic IAM auth token generation

Quick example:

./scripts/create-cluster.sh --region us-east-1
export CLUSTER=abc123def456
./scripts/psql-connect.sh

See scripts/README.md for detailed usage.

Quick Start

1. List tables and explore schema

Use readonly_query with information_schema to list tables
Use get_schema to understand table structure

2. Query data

Use readonly_query for SELECT queries
Always include tenant_id in WHERE clause for multi-tenant apps
Validate inputs carefully (no parameterized queries available)

3. Execute schema changes

Use transact tool with list of SQL statements
Follow one-DDL-per-transaction rule
Always use CREATE INDEX ASYNC in separate transaction

Common Workflows

Workflow 1: Create Multi-Tenant Schema

Goal: Create a new table with proper tenant isolation

Steps: 1. Create main table with tenant_id column using transact 2. Create async index on tenant_id in separate transact call 3. Create composite indexes for common query patterns (separate transact calls) 4. Verify schema with get_schema

Critical rules: - Include tenant_id in all tables - Use CREATE INDEX ASYNC (never synchronous) - Each DDL in its own transact call: transact(["CREATE TABLE ..."]) - Store arrays/JSON as TEXT

Workflow 2: Safe Data Migration

Goal: Add a new column with defaults safely

Steps: 1. Add column using transact: transact(["ALTER TABLE ... ADD COLUMN ..."]) 2. Populate existing rows with UPDATE in separate transact calls (batched under 3,000 rows) 3. Verify migration with readonly_query using COUNT 4. Create async index for new column using transact if needed

Critical rules: - Add column first, populate later - Never add DEFAULT in ALTER TABLE - Batch updates under 3,000 rows in separate transact calls - Each ALTER TABLE in its own transaction

Workflow 3: Application-Layer Referential Integrity

Goal: Safely insert/delete records with parent-child relationships

Steps for INSERT: 1. Validate parent exists with readonly_query 2. Throw error if parent not found 3. Insert child record using transact with parent reference

Steps for DELETE: 1. Check for dependent records with readonly_query (COUNT) 2. Return error if dependents exist 3. Delete record using transact if safe

Workflow 4: Query with Tenant Isolation

Goal: Retrieve data scoped to a specific tenant

Steps: 1. Always include tenant_id in WHERE clause 2. Validate and sanitize tenant_id input (no parameterized queries available!) 3. Use readonly_query with validated tenant_id 4. Never allow cross-tenant data access

Critical rules: - Validate ALL inputs before building SQL (SQL injection risk!) - ALL queries include WHERE tenant_id = 'validated-value' - Reject cross-tenant access at application layer - Use allowlists or regex validation for tenant IDs

Best Practices

SHOULD read guidelines first - Check development_guide.md before making schema changes
SHOULD use preferred language patterns - Check language.md
SHOULD Execute queries directly - PREFER MCP tools for ad-hoc queries
REQUIRED: Follow DDL Guidelines - Refer to DDL Rules
SHALL repeatedly generate fresh tokens - Refer to Connection Limits
ALWAYS use ASYNC indexes - CREATE INDEX ASYNC is mandatory
MUST Serialize arrays/JSON as TEXT - Store arrays/JSON as TEXT (comma separated, JSON.stringify)
ALWAYS Batch under 3,000 rows - maintain transaction limits
REQUIRED: Use parameterized queries - Prevent SQL injection with $1, $2 placeholders
MUST follow correct Application Layer Patterns - when multi-tenant isolation or application referential itegrity are required; refer to Application Layer Patterns
REQUIRED use DELETE for truncation - DELETE is the only supported operation for truncation
SHOULD test any migrations - Verify DDL on dev clusters before production
Plan for Horizontal Scale - DSQL is designed to optimize for massive scales without latency drops; refer to Horizontal Scaling
SHOULD use connection pooling in production applications - Refer to Connection Pooling
SHOULD debug with the troubleshooting guide: - Always refer to the resources and guidelines in troubleshooting.md

dsql-skill

SKILL.md