Aurora Dsql

Amazon Aurora DSQL

Overview

Aurora DSQL is a serverless, PostgreSQL-compatible distributed SQL database. This skill provides direct database interaction via psql scripts and PostgreSQL drivers, schema management, migration support, multi-tenant patterns, and query-plan explainability.

Key capabilities:

• Direct query execution via psql with generated IAM auth tokens (see `scripts/psql-connect.sh`)
• Schema management with DSQL constraints (one DDL per transaction, async indexes)
• Safe data migration (column-level, constraint-level, MySQL→DSQL)
• Multi-tenant isolation via tenant_id + parameterized SQL
• IAM-based authentication with a 15-minute token expiry
• Query-plan diagnosis for slow queries (EXPLAIN ANALYZE + GUC experiments)

The recommended runtime is psql with aws dsql generate-db-connect-auth-token for IAM-authenticated sessions. Application code SHOULD use the language-specific DSQL Connectors and SDKs. For AWS knowledge lookups (service docs, AWS API calls), the AWS MCP Server is the preferred MCP integration.

Reference Files

Load these files as needed for detailed guidance:

development-guide.md

When: ALWAYS load before implementing schema changes or database operations Contains: Best Practices, DDL rules, connection patterns, transaction limits, data type serialization patterns, application-layer referential integrity instructions, security best practices

Query Execution:

database-tools.md

When: Load when you need detailed syntax and examples for ad-hoc query execution against DSQL. PREFER psql (via `scripts/psql-connect.sh`) for ad-hoc queries — execute directly rather than writing one-off scripts. Contains: psql-based read-only and write patterns, transaction semantics, input validation

MCP (AWS knowledge / API):

mcp-setup.md

When: Load when configuring or recommending the AWS MCP Server for AWS knowledge lookups, AWS API access, or per-assistant install. Contains: When to use psql vs the AWS MCP Server, pointer to the canonical AWS setup docs, credential reminders.

mcp-tools.md

When: Load when invoking AWS MCP Server tools to verify DSQL service limits, fetch docs, or drive AWS API calls. Contains: Tool surface — knowledge (aws___search_documentation, aws___read_documentation, aws___recommend, aws___retrieve_skill, aws___list_regions, aws___get_regional_availability) and API (aws___call_aws, aws___run_script, aws___get_tasks, aws___get_presigned_url); pointers to documentation-tools.md.

documentation-tools.md

When: Load when looking up DSQL service limits, fetching a specific AWS docs page, or polling long-running AWS API calls launched via the AWS MCP Server. Contains: Detailed parameters and example calls for the AWS knowledge tools.

platforms/ — per-assistant install notes

When: Load when installing the AWS MCP Server inside a specific coding assistant. Contains: Per-assistant entry-point details — claude-code.md, codex.md, gemini.md, kiro.md.

language.md

When: MUST load before writing DSQL connection code. Mirror the linked example_preferred.<ext> for the chosen driver — memory-authored connections drift from the canonical IAM-token-refresh pattern. Canonical entry-point examples (load language.md for the full driver list + pool/TLS/token-refresh details):

• Python: import aurora_dsql_psycopg as dsql → dsql.connect(host, region, user)
• JS (node-postgres): import { AuroraDSQLPool } from "@aws/aurora-dsql-node-postgres-connector" → new AuroraDSQLPool({ host, user })
• JS (postgres.js): import { auroraDSQLPostgres } from "@aws/aurora-dsql-postgresjs-connector" → auroraDSQLPostgres({ host, user })
• Go (pgx): import "github.com/awslabs/aurora-dsql-connectors/go/pgx/dsql"
• Java (JDBC): software.amazon.dsql:aurora-dsql-jdbc-connector:1.4.0 → jdbc:aws-dsql:postgresql://...

Contains: Canonical DSQL connector packages per language, driver selection, framework patterns, IAM auth token rotation and TLS configuration, and connection code examples for Python / JavaScript / TypeScript / Go / Java / Rust.

troubleshooting.md

When: Load when debugging errors or unexpected behavior. SHOULD always consult for OCC errors, connection failures, or unexpected query results. 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

access-control.md

When: MUST load when creating database roles, granting permissions, setting up schemas for applications, or handling sensitive data. ALWAYS use scoped roles for applications — create database roles with dsql:DbConnect. Contains: Scoped role setup, IAM-to-database role mapping, schema separation for sensitive data, role design patterns

Authentication & Operations:

auth/authentication-guide.md

When: MUST load when handling IAM auth tokens, secrets, SSL/TLS, connection pooling, or audit logging. Contains: Token lifecycle, secret storage patterns, SSL/TLS settings, connection-pool guidance, audit-log integration.

auth/connectivity-tools.md

When: Load when picking a driver/ORM/adapter or planning bulk-data loading. Contains: Pointer to the canonical AWS DSQL connectivity tools page (drivers, ORMs, adapters) and the bulk-loading docs page.

auth/scaling-guide.md

When: Load when designing for scale — connection pooling, batch optimization, hot-key avoidance, identifier choice. Contains: Horizontal scaling strategy, pool sizing, batch-size guidance, IDENTITY/UUID trade-offs, sequence cache rules.

Implementation Examples:

workflow-patterns.md

When: Load when looking for a worked example of a common multi-step DSQL workflow (schema explore, CREATE+INDEX, safe migration, batch insert, application-layer FK check). Contains: Five canonical patterns with psql / driver code.

dsql-examples.md

When: Load when looking for specific implementation examples. Contains: Index of examples/*.md (connection, schema, data-operations, migrations, patterns).

DDL Migrations (modular):

ddl-migrations/overview.md

When: MUST load when performing DROP COLUMN, RENAME COLUMN, ALTER COLUMN TYPE, or DROP CONSTRAINT Contains: Table recreation pattern overview, transaction rules, common verify & swap pattern

ddl-migrations/column-operations.md

When: Load for DROP COLUMN, ALTER COLUMN TYPE, SET/DROP NOT NULL, SET/DROP DEFAULT migrations Contains: Step-by-step migration patterns for column-level changes

ddl-migrations/constraint-operations.md

When: Load for ADD/DROP CONSTRAINT, MODIFY PRIMARY KEY, column split/merge migrations Contains: Step-by-step migration patterns for constraint and structural changes