Dsql

Amazon Aurora DSQL Skill

Aurora DSQL is a serverless, PostgreSQL-compatible distributed SQL database. This skill covers direct query execution via MCP tools, schema management, migrations, multi-tenant isolation, IAM auth, and bulk data loading via aurora-dsql-loader.

Reference Files

Load these files as needed for detailed guidance:

Core:

MCP:

DDL Migrations:

MySQL Migrations:

PostgreSQL Migrations:

ORM Guides:

Data Loading:

System Diagnostics:

Query Plan Explainability:

Choosing How to Connect: MCP vs CLI/psql

The aurora-dsql MCP server binds a single cluster at startup (--cluster_endpoint), so using it for another cluster means editing .mcp.json and restarting the session.

• Use the aurora-dsql MCP tools (readonly_query, transact, get_schema) ONLY when the server already targets the cluster you need.

• Otherwise — unconfigured, disabled, or bound to a different cluster — do NOT reconfigure it. Use the CLI + psql path instead: scripts/psql-connect.sh <cluster-id> --region <region> --command "SELECT ..." (mints an IAM token and runs via psql).

• If you cannot confirm which cluster the MCP targets, confirm first or use the CLI/psql path — running against the wrong cluster is worse than the check.

The doc-only MCP tools (dsql_lint, dsql_*_documentation, dsql_recommend) need no cluster. The CloudWatch MCP (Workflow 12) takes region/cluster_id per call, so one running server can query clusters in any PromQL-enabled region (pass each cluster's region on the call). Details: connectivity-tools.md.

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

SQL Validation:

1. dsql_lint - Validate SQL for DSQL compatibility and optionally auto-fix issues. Use before executing externally-sourced SQL.

Documentation & Knowledge:

1. dsql_search_documentation - Search Aurora DSQL documentation

2. dsql_read_documentation - Read specific documentation pages

3. dsql_recommend - Get DSQL best practice recommendations

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

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

AWS Knowledge MCP (awsknowledge)

Consult for verifying 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; any time the exact number can affect user decision. For the full limit set with each SQLSTATE and its exact error message, see limits-and-error-codes.md; where it marks a value observed and the docs differ, use the observed value.

Fallback: If awsknowledge is unavailable, use the defaults above and flag that limits should be verified against DSQL documentation.

CLI Scripts Available

Bash scripts in scripts/ for cluster management (create, delete, list, cluster info), psql connection, and bulk data loading from local/s3 csv/tsv/parquet files. See scripts/README.md for usage and hook configuration.

Quick Start

0. Pick a connection path: confirm the aurora-dsql MCP targets your cluster; if not, use the CLI/psql path instead — see Choosing How to Connect. The steps below name MCP tools; the equivalent SQL runs the same way through psql-connect.sh --command "...".

1. Explore: Use readonly_query with information_schema to list tables. Use get_schema for table structure.

2. Query: Use readonly_query for SELECT queries. MUST include tenant_id in WHERE for multi-tenant apps. MUST build SQL with safe_query.build().

3. Schema changes: Use transact with one DDL per transaction. MUST batch DML under 3,000 rows. MUST use CREATE INDEX ASYNC in a separate call. Use dsql_lint to validate first.

4. Bulk load data: Use aurora-dsql-loader for CSV/TSV/Parquet. Load data-loading.md for details. Use --dry-run first.

Performance Routing

When the user reports a performance problem, use this table to select the correct workflow:

Rule: When in doubt, start with Workflow 12. It identifies specific queries to investigate and routes to Workflow 9 with context.

Common Workflows

Workflow 1: Create Multi-Tenant Schema

1. Create tenant-owned tables with tenant_id using separate transact calls

2. Use composite tenant FKs for tenant-owned parents and ordinary FKs for shared parents

3. Create tenant and query-pattern indexes with separate CREATE INDEX ASYNC calls

4. Verify with get_schema

• MUST issue each DDL in its own transact call: transact(["CREATE TABLE ..."])

• MUST serialize arrays into a single-column representation — DSQL has no array column type; PREFER JSONB (operators work directly); MAY use TEXT when the column is opaque to the database; ASK the user. For JSONB arrays, expand at query time with jsonb_array_elements_text(data)

Workflow 2: Safe Data Migration

MUST validate every DDL with dsql_lint(fix=true) before executing. DML does not require linting.

1. Validate DDL with dsql_lint(sql=..., fix=true) — handle diagnostics per dsql-lint.md

2. Execute the reviewed fixed_sql when present, otherwise the reviewed source statement

3. Add column in its own transact call

4. Populate existing rows with UPDATE (batched under 3,000 rows)

5. Verify with readonly_query COUNT

6. Create an index if needed: validate then execute the reviewed DDL in its own transact call

• MUST issue each ALTER TABLE in its own transact call — DSQL rejects multi-DDL transactions with multiple ddl statements not supported in a transaction

• MUST add column with only name and type; apply DEFAULT via separate UPDATE

• MUST batch updates under 3,000 rows in separate transact calls

Recovery: Resume failed batches by filtering WHERE new_column IS NULL.

Workflow 3: Bulk Data Loading

Use aurora-dsql-loader for CSV, TSV, or Parquet loads. MUST load data-loading.md before advising on throughput or diagnosing slow loads.

1. Validate with --dry-run first

2. Run with --manifest-dir on persistent storage (not /tmp — tmpfs on AL2023, lost on crash) and --header if file has a header row

3. On failure: resume with --resume-job-id; for duplicates use --on-conflict do-nothing

4. For large tables: create secondary indexes after load using CREATE INDEX ASYNC

Workflow 4: Foreign Key Constraints

MUST load and follow foreign-keys.md before creating, altering, dropping, or migrating foreign keys.

Workflow 5: 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 6: Set Up Scoped Database Roles

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

Workflow 7: Table Recreation DDL Migration

For ALTER COLUMN TYPE, SET NOT NULL, or MODIFY PRIMARY KEY, MUST load ddl-migrations/overview.md. Use a direct ALTER form when supported; otherwise, present a user-approved table-recreation plan.

Workflow 8: Validate and Migrate to DSQL

MUST load dsql-lint.md before running dsql_lint. Run dsql_lint(sql=source_sql, fix=true) to validate and auto-convert. For MySQL-origin SQL, MUST cross-check against mysql-migrations/type-mapping.md even when lint returns clean. On parse_error, fall back to manual conversion then re-lint.

Workflow 9: Query Plan Explainability

Explains why the DSQL optimizer chose a particular plan. Triggered by slow queries, high DPU, unexpected Full Scans, or plans the user doesn't understand. REQUIRES a structured Markdown diagnostic report as the deliverable.

MUST load query-plan/workflow.md at entry — it defines trigger criteria, context disambiguation, routing, and the full phased workflow (Phase 0–4). Workflow.md specifies which reference files to load at each phase.

Safety. Plan capture uses readonly_query exclusively. Rewrite DML to SELECT for plan capture. MUST NOT use transact --allow-writes for plan capture.

Workflow 10: Full PostgreSQL → DSQL Schema Migration

MUST load pg-migrations/type-mapping.md, pg-migrations/index-conversion.md, pg-migrations/schema-objects.md, and foreign-keys.md. Run dsql_lint(fix=true) first for mechanical fixes, preserve foreign-key relationships, translate unsupported source syntax or options, then apply semantic conversions from the pg-migrations references for unfixable diagnostics and patterns the linter cannot handle. Re-lint the final output before deploying.

Workflow 11: ORM Migration and Locking (Django/EF Core/Hibernate/Rails/SQLAlchemy)

Load orm-guides/overview.md for adapter names, locking guidance, and framework-specific gotchas.

Workflow 12: System Diagnostics (CloudWatch AAS)

Diagnose cluster performance by querying db.active_sessions.avg via PromQL. Detects temporal anomalies in wait event distribution, identifies regressed queries, and routes to Workflow 9 for per-query investigation.

Requires: CloudWatch MCP server (awslabs.cloudwatch-mcp-server) enabled and configured with PromQL access in the same region as the cluster — see mcp/mcp-setup.md for enabling it, region requirements, and the session restart needed for its tools to register.

MUST load system-diagnostics/workflow.md at entry — it defines prerequisites, 5 diagnostic phases, temporal baselines, and the routing to Workflow 9 for identified queries.

Error Scenarios

• awsknowledge returns no results: Use the default limits in the table above and note that limits should be verified against DSQL documentation.

• dsql_lint unavailable or timing out: See the Error Handling section of dsql-lint.md. Do not silently skip validation — inform the user and require explicit confirmation before proceeding with manual rules from development-guide.md.

• OCC serialization error (40001): Retry with backoff, and keep (OC001) in the loop too — see occ-retry-patterns.md.

• Foreign key violation (23503): Correct the relationship or referential action; MUST NOT send it through the 40001 retry loop — see troubleshooting.md.

• Transaction exceeds limits: Split batches under 3,000 rows and 10 MiB; size is checked at COMMIT — see limits-and-error-codes.md to size them and batched-migration.md for the pattern.

• Token expiration mid-operation: Generate a fresh IAM token — see authentication-guide.md. See troubleshooting.md for other issues.

Additional Resources

• Aurora DSQL Documentation

• Code Samples Repository