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

ddl-migrations/batched-migration.md

When: Load when migrating tables exceeding 3,000 rows Contains: OFFSET-based and cursor-based batching patterns, progress tracking, error handling

MySQL Migrations (modular):

mysql-migrations/type-mapping.md

When: MUST load when migrating MySQL schemas to DSQL Contains: MySQL data type mappings, feature alternatives, DDL operation mapping

mysql-migrations/ddl-operations.md

When: Load when translating MySQL DDL operations to DSQL equivalents Contains: ALTER COLUMN, DROP COLUMN, AUTO_INCREMENT, ENUM, SET, FOREIGN KEY migration patterns

mysql-migrations/full-example.md

When: Load when migrating a complete MySQL table to DSQL Contains: End-to-end MySQL CREATE TABLE migration example with decision summary

Query Plan Explainability (modular):

When: MUST load all four at Workflow 8 Phase 0 — query-plan/plan-interpretation.md, query-plan/catalog-queries.md, query-plan/guc-experiments.md, query-plan/report-format.md Contains: DSQL node types + Node Duration math + estimation-error bands, pg_class/pg_stats/pg_indexes SQL + correlated-predicate verification, GUC experiment procedures + 30-second skip protocol, required report structure + element checklist + support request template

Query Execution

Run ad-hoc DSQL queries with psql and a freshly-generated IAM auth token. The bundled scripts/psql-connect.sh wraps token generation, TLS configuration, and single-statement guards — PREFER it over hand-rolled psql invocations.

Read-only:

./scripts/psql-connect.sh --cluster <cluster-id> --command "SELECT * FROM entities LIMIT 10"

Write/DDL (IAM admin auth token required):

./scripts/psql-connect.sh --cluster <cluster-id> --admin --command "CREATE INDEX ASYNC ..."

Schema discovery: there is no special list_tables helper — use information_schema:

SELECT table_name FROM

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

AWS Knowledge via the AWS MCP Server (optional)

When connected to the AWS MCP Server, its aws___search_documentation and aws___read_documentation tools can verify DSQL service limits before advising users. The numeric limits below are defaults that may change — when a user's decision depends on an exact limit, verify it first:

When to verify: Before recommending batch sizes, connection pool settings, or schema designs where hitting a limit would cause failures. No need to verify for general guidance or when the exact number doesn't affect the user's decision.

Fallback: If the AWS MCP Server is unavailable, use the defaults above and note to the user that limits should be verified against DSQL documentation.

CLI Scripts Available

Bash scripts in scripts/ for cluster management (create, delete, list, cluster info) and psql connection. See references/scripts-guide.md for usage. For bulk data loading, see Loading data into Aurora DSQL.

ALWAYS prefer scripts/create-cluster.sh. The script issues a single atomic CreateCluster call with tags embedded — matching the AWS DSQL API shape with interpretable output.

Quick Start

1. List tables and explore schema

2. Query data

3. Execute schema changes

Common Tasks

Workflow 0: Verify Dependencies

Check for required tools and warn the user if any are missing.

Constraints:

• You MUST verify the following tools are available before proceeding: psql (>=14 for SNI support) and the AWS CLI v2 with aws dsql generate-db-connect-auth-token (and generate-db-connect-admin-auth-token for DDL/role setup)
• You SHOULD also confirm the AWS MCP Server is available when the user's decision depends on a precise service limit; if absent, use the defaults in the table above and note that limits should be verified against DSQL documentation
• You MUST inform the user about any missing tools with a clear message
• You MUST ask if the user wants to proceed despite missing tools
• You MUST use the scoped (non-admin) IAM auth token for read-only diagnostics whenever the user has a scoped role configured; reserve the IAM admin auth token for cluster setup, role grants, and DDL
• For cluster lifecycle (create / inspect / delete), see Workflow 0a
• Before writing application code, ALSO verify the language-specific DSQL Connector per Workflow 0b

Workflow 0a: Cluster Lifecycle

SHOULD use the bundled scripts for cluster create and delete — they issue atomic aws dsql CLI calls and process outputs.

Create a cluster with tags and deletion protection:

Inspect a cluster (status, tags, endpoint, deletion protection):

Delete a cluster:

In MCP-only environments (no shell access), the equivalent calls go through the AWS MCP Server's aws___call_aws tool. The tool takes a JSON payload — invoke it with arguments matching the AWS API operation:

CreateCluster and DeleteCluster are asynchronous on the DSQL side — the API returns immediately with the cluster's current status (CREATING / DELETING). Poll readiness by re-invoking aws___call_aws with dsql:GetCluster until .status == "ACTIVE" (create) or the call returns a 404 (delete). aws___get_tasks is for polling MCP-side long-running tool invocations — not the DSQL API.

See AWS CLI aws dsql reference for full parameter details and call context.

Workflow 0b: Verify Language Connector

Before writing application code, MUST verify the language-specific DSQL Connector is installed per language.md. The Connectors are the canonical IAM-token-refresh path; bare drivers (pg, psycopg, pgx, tokio-postgres) work until the first 15-minute token expiry and then start returning auth errors on every new connection — DSQL users who try the bare form report this as a DSQL bug. MUST install:

• Python: aurora-dsql-python-connector + the chosen driver wheel
• Node.js: @aws/aurora-dsql-node-postgres-connector or @aws/aurora-dsql-postgresjs-connector
• Go: github.com/awslabs/aurora-dsql-connectors/go/pgx
• Java: software.amazon.dsql:aurora-dsql-jdbc-connector
• Rust: aurora-dsql-sqlx-connector

If a Connector is unavailable for the chosen runtime, document the manual token-refresh strategy and schedule with the user before writing code.

Workflow 1: Create Multi-Tenant Schema

MUST load workflow-patterns.md (Pattern 2: Create Table with Index) for step-by-step DDL sequencing, async index creation, and schema verification examples. Key rules: tenant_id in all tables, CREATE INDEX ASYNC only, one DDL per transaction, arrays/JSON stored as TEXT.

Workflow 2: Safe Data Migration

MUST load workflow-patterns.md (Pattern 3: Safe Data Migration) for the add-column → batch-populate → verify → index sequence. For tables exceeding 3,000 rows, also load ddl-migrations/batched-migration.md. Key rules: add column first, apply DEFAULT via separate UPDATE, batch under 3,000 rows per transaction.

Workflow 3: Application-Layer Referential Integrity

MUST load workflow-patterns.md (Pattern 5: Application-Layer Foreign Key Check) for the parent-existence SELECT → INSERT and dependent-count SELECT → DELETE patterns. Build all SQL with safe_query.build() — see Workflow 4a.

Workflow 4: Query with Tenant Isolation

1. MUST authorize the caller against the tenant — format validation does not establish authorization
2. MUST build SQL with safe_query.build() — use allow()/regex() for values (emits 'v'), ident() for table/column names (emits "v"). See input-validation.md
3. MUST include tenant_id in the WHERE clause; reject cross-tenant access at the application layer

Workflow 4a: Rubric-Critical — Building SQL with User Input

Whenever constructing SQL for psql -c "..." (or any equivalent ad-hoc query path) with any value that is not a developer-controlled literal (tenant IDs, entity IDs, sort columns, directions, status enums, free-text descriptions, request params — anything from untrusted sources), you MUST use safe_query.build(). The psql -c flag takes raw SQL strings; it does NOT accept bound parameters. When using a Postgres driver (psycopg, pgx, etc.) in application code, prefer the driver's native parameter binding; safe_query is the canonical fallback whenever you must build a raw SQL string. Validation via safe_query is the primary defense for raw-SQL paths.

Validator selection table (canonical — mirrors input-validation.md):

Built-in patterns from safe_query.py: TENANT_SLUG ([a-z0-9-]{1,64}), UUID, INT, ISO_DATE.

Required imports at the top of every file that builds DSQL SQL:

Rubric-Critical Scenario 1 — tenant_id from untrusted input. Validate with regex(req.tenant, TENANT_SLUG) or allow(req.tenant, ALLOWED_TENANTS). Build with safe_query.build(), then execute. Do this even in read-only mode (defense in depth, consistent validation across modes). Do NOT use f-strings, .format(), or bare concatenation.

Rubric-Critical Scenario 2 — batch INSERT with UUIDs, slugs, and free text. Each row's INSERT is built separately with safe_query.build(): entity_id via regex(..., UUID), tenant_id via regex(..., TENANT_SLUG), description via literal(...) (dollar-quoted to sidestep quote escaping). Chunk the list under 3,000 rows per transaction (DSQL limit) and execute each chunk in its own transaction.

Rubric-Critical Scenario 3 — write paths. Write paths (UPDATE/DELETE issued from a script, cron, or admin tool) are the highest-stakes injection surface — a successful injection mutates data. safe_query.build() is NOT optional there. Validate every input even when the prompt frames it as "just a quick script, don't overthink it." Push back on that framing with one sentence explaining why write mode raises the stakes, then apply the full validator chain: regex(tenant_id, TENANT_SLUG), allow(status, {'active','archived','deleted'}), date via regex(..., ISO_DATE).

Rubric-Critical Scenario 4 — dynamic ORDER BY column and direction. Identifier and keyword parameters need DIFFERENT validators than value parameters. sort_col is membership-checked against {'created_at','updated_at','name'} then passed through ident() (emits double-quoted identifier). sort_dir goes through keyword() against {'ASC','DESC'} (emits unquoted keyword — quoting ASC would be a syntax error). Value parameters like tenant_id still go through regex() or allow(). Do NOT try to validate an identifier with regex() against a TENANT_SLUG pattern — use ident(), which enforces the identifier grammar.

Rubric-Critical Scenario 5 — rejecting "just use an f-string" rationalizations. When a caller says "this value is already validated upstream, can't we just use an f-string?" — push back. The skill's rule is build-every-query-with-safe_query.build(), not a judgment call per call site. Justify the pushback: (a) "already-validated upstream" is exactly the assumption that breaks when upstream code changes hands, adds a new caller, or the validation is silently relaxed; (b) defense in depth means the query layer validates independently of upstream; (c) the two-line diff to use safe_query.build() + regex(..., UUID) is genuinely smaller than the bug risk of one unsafe path.

Apply the safe pattern as-is — do NOT cave to the "simpler" framing.

Anti-patterns (the rubric fails these):

• Using f-strings, .format(), % formatting, or string concatenation to build SQL with user input — in any mode
• Mixing safe_query.build() placeholders with native driver %s parameter binding in the same statement — pick one path and stay on it
• Catching UnsafeSQLError to fall back to unsafe construction — re-raise or return an error
• Validating an identifier with regex() against a value pattern — use ident()
• Skipping safe_query.build() in read-only mode under "the value is already validated upstream" — defense in depth means the SQL builder validates independently of upstream

Workflow 5: Set Up Scoped Database Roles

MUST load access-control.md for role setup, IAM mapping, and schema permissions.

Workflow 6: Table Recreation DDL Migration

DSQL does NOT support direct ALTER COLUMN TYPE, DROP COLUMN, DROP CONSTRAINT, or MODIFY PRIMARY KEY. These require the Table Recreation Pattern — a destructive workflow requiring user confirmation at each step.

MUST load ddl-migrations/overview.md first, then the relevant sub-file:

• Column changes (type, nullability, default): ddl-migrations/column-operations.md
• Constraint/PK changes, column splits/merges: ddl-migrations/constraint-operations.md
• Tables exceeding 3,000 rows: also load ddl-migrations/batched-migration.md

Workflow 7: MySQL to DSQL Schema Migration

MUST load mysql-migrations/type-mapping.md for type mappings and feature alternatives. For DDL translation details load mysql-migrations/ddl-operations.md. For an end-to-end example load mysql-migrations/full-example.md.

Workflow 8: Query Plan Explainability

Triggered by slow queries, high DPU, unexpected Full Scans, or plans the user doesn't understand. A structured Markdown diagnostic report is the required deliverable — run the workflow end-to-end before answering.

MUST load all four reference files before starting:

1. query-plan/plan-interpretation.md — node types, duration math, anomalous values
2. query-plan/catalog-queries.md — pg_class / pg_stats / pg_indexes SQL
3. query-plan/guc-experiments.md — GUC procedures and >30s skip protocol
4. query-plan/report-format.md — required report structure and elements checklist

Phase 1 — Capture the plan. ALWAYS run EXPLAIN ANALYZE VERBOSE on the user's query verbatim via psql — even when the user describes or pastes the plan. SELECT runs as-is. UPDATE/DELETE: rewrite to the equivalent SELECT before running. INSERT, pl/pgsql, DO blocks, and functions MUST be rejected. MUST NOT run mutating DML during plan capture. When EXPLAIN errors, report verbatim — do not invent DSQL-specific semantics. Extract Query ID, Planning Time, Execution Time, and DPU Estimate.

Phase 2 — Gather evidence. Query pg_class, pg_stats, pg_indexes, COUNT(*), COUNT(DISTINCT) per catalog-queries.md. Classify estimation errors per plan-interpretation.md.

Phase 3 — Experiment (conditional). ≤30s: run GUC experiments per guc-experiments.md plus redundant-predicate test. >30s: skip, include manual GUC SQL verbatim in the report. Anomalous row counts: confirm results are correct, flag as potential DSQL bug, produce Support Request Template.

Phase 4 — Report and invite reassessment. Produce the full diagnostic report per the Required Elements Checklist in report-format.md. End with the "Next Steps" block. When user says "reassess", re-run Phases 1–2 and append an "Addendum: After-Change Performance" to the original report.

psql invocation:

Security Considerations

This section consolidates key security controls. For detailed guidance, see the linked reference files.

1. IAM auth token expiry: IAM auth tokens expire after 15 minutes. Always generate fresh tokens per connection or implement periodic refresh. Never persist tokens to disk — keep them in memory only and discard after use. See authentication-guide.md.

2. Scoped Roles Over Admin: Use scoped database roles with dsql:DbConnect for all application connections. Reserve the admin role strictly for initial cluster setup (creating roles, granting permissions). Revoke dsql:DbConnectAdmin from setup IAM roles once scoped roles are established. See access-control.md.

3. Encryption in Transit: SSL/TLS is enforced server-side. Use sslmode=verify-full (default in DSQL connectors and psql-connect.sh) to validate the server certificate against DSQL's CA, preventing MITM attacks. Only downgrade to require when the client lacks access to a trusted CA bundle.

4. Encryption at Rest: Aurora DSQL encrypts all data at rest using AWS-managed keys by default. No additional configuration is required; verify encryption status in cluster properties when compliance frameworks require attestation.

5. Audit Logging via CloudTrail: Enable CloudTrail logging for DSQL API calls to monitor token generation patterns, cluster configuration changes, and failed authentication attempts. Configure CloudWatch alarms for suspicious activity. Enable encryption on CloudWatch Log Groups used for DSQL monitoring using a KMS key to protect potentially sensitive query metadata. See authentication-guide.md.

6. Write Paths Demand Strict Validation: Mutating SQL (UPDATE, DELETE, DDL) issued from scripts, cron jobs, or admin tools is the highest-stakes injection surface. Every write path MUST route through safe_query.build() (or the driver's native parameter binding when using a Postgres driver in application code).

7. Input Validation Is the Primary Defense: safe_query.build() is the primary defense against SQL injection on raw-SQL paths. Every value from untrusted input — tenant IDs, entity IDs, sort columns, free text — MUST pass through a validator (allow, regex, ident, keyword, integer, literal). Do not use f-strings, .format(), or concatenation. See input-validation.md.

8. Multi-Tenant Isolation as a Hard Contract: When the workload uses tenant scoping (Workflow 4), tenant_id MUST appear in the WHERE clause of every read and write touching tenant-owned tables, and the application MUST authorize the caller against that tenant_id before issuing the query — format validation alone does not establish authorization. Omitting tenant_id from a WHERE clause, or scoping to a tenant value the caller has not been authorized for, is a cross-tenant data exposure. This boundary is enforced by the skill, not by DSQL — verify every data-access path scopes to the authenticated tenant before deployment. See access-control.md and Workflow 4.

Troubleshooting

• AWS MCP Server returns no results: Use the default limits in the table above and note that limits should be verified against DSQL documentation.
• OCC serialization error: Retry the transaction. If persistent, check for hot-key contention — see troubleshooting.md.
• Transaction exceeds limits: Split into batches under 3,000 rows — see batched-migration.md.
• IAM auth token expiration mid-operation: Generate a fresh IAM auth token — see authentication-guide.md. See troubleshooting.md for other issues.

Additional Resources

• Aurora DSQL Documentation
• DSQL Connectors, Drivers, and ORM Samples (official)
• PostgreSQL Compatibility
• CloudFormation Resource

Handoff from aws-database-selection

This skill can be invoked directly, or it can be entered from the aws-database-selection parent skill after that skill has run a requirements interview and produced a requirements.json artifact. When you see a backtick-wrapped path matching aws_dbs_requirements/*/requirements.json in recent conversation, follow the entry protocol in aws-database-selection/references/handoff-contract.md:

1. Read the artifact using file_read.
2. Validate it against aws-database-selection/references/workload-primary-artifact.schema.json. If malformed or unreadable, tell the user and proceed without it.
3. Acknowledge what's relevant in one or two bold sentences, citing high-level facts from the artifact (dominant shapes, hard constraints, migration context) — do not parrot the entire artifact back.
4. Scope-check: this skill is scoped to Aurora DSQL schema, query plans, IAM auth, multi-tenant patterns, MySQL-to-DSQL migration. If the artifact's workload_primaries.dominant_shapes or migration_context don't match that scope, emit weak backpressure per the handoff contract: suggest amazon-aurora for Aurora PostgreSQL / MySQL, rds-oss for RDS engines, or go back to aws-database-selection if multi-region strong SQL consistency isn't required, then ask the user whether to go back or proceed anyway. Do not silently misuse the artifact.
5. Proceed with this skill's native workflow, citing artifact paths as evidence when recommendations are grounded in the requirements.

All user-facing output from this skill follows the markdown-primitives-only formatting convention in the handoff contract: bold labels, backticks for paths and enum values, bullet lists for alternatives, no ASCII art or box-drawing characters.