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:

Query Plan Explainability:

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. dsqlsearchdocumentation - Search Aurora DSQL documentation
2. dsqlreaddocumentation - Read specific documentation pages
3. dsql_recommend - Get DSQL best practice recommendations

Note: There is no listtables tool. Use readonlyquery with information_schema.

See [mcp-setup.md](mcp/mcp-setup.md) for detailed setup instructions. See [mcp-tools.md](mcp/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.

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

CLI Tools

Use the AWS CLI, psql, and aurora-dsql-loader directly for cluster management, SQL connections, and bulk data loading from local or S3 CSV/TSV/Parquet files. Load [data-loading.md](references/data-loading.md) for loader guidance.

Quick Start

1. Explore: Use readonlyquery with informationschema to list tables. Use get_schema for table structure.
2. Query: Use readonlyquery for SELECT queries. MUST include tenantid 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](references/data-loading.md) for details. Use --dry-run first.

Common Workflows

Workflow 1: Create Multi-Tenant Schema

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

• MUST include tenant_id in all tables
• MUST use CREATE INDEX ASYNC exclusively
• MUST issue each DDL in its own transact call: transact(["CREATE TABLE ..."])
• MUST serialize arrays as JSONB; expand at query time with jsonbarrayelements_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](references/dsql-lint.md)
2. Add column: transact(["ALTER TABLE ... ADD COLUMN ..."])
3. Populate existing rows with UPDATE (batched under 3,000 rows)
4. Verify with readonly_query COUNT
5. Create index if needed: validate then transact(["CREATE INDEX ASYNC ..."])

• 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](references/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: Application-Layer Referential Integrity

INSERT: MUST validate parent exists with readonly_query → throw error if not found → insert child with transact.

DELETE: MUST check dependents with readonly_query COUNT → return error if dependents exist → delete with transact if safe.

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 [safequery.build()](mcp/tools/safequery.py) — use allow()/regex() for

values (emits 'v'), ident() for table/column names (emits "v"). See [input-validation.md](mcp/tools/input-validation.md)

1. 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](references/access-control.md) for role setup, IAM mapping, and schema permissions.

Workflow 7: Table Recreation DDL Migration

Use the Table Recreation Pattern for ALTER COLUMN TYPE, DROP COLUMN, DROP CONSTRAINT, or MODIFY PRIMARY KEY. This is a destructive workflow that requires user confirmation at each step. Every generated DDL in the pattern (CREATE new, INSERT ... SELECT, DROP old, RENAME) MUST be validated with dsql_lint(sql=..., fix=true) before execution.

MUST load [ddl-migrations/overview.md](references/ddl-migrations/overview.md) before attempting any of these operations.

Workflow 8: Validate and Migrate to DSQL

MUST load [dsql-lint.md](references/dsql-lint.md) before running dsqllint. Run dsqllint(sql=sourcesql, fix=true) to validate and auto-convert. For MySQL-origin SQL, MUST cross-check against [mysql-migrations/type-mapping.md](references/mysql-migrations/type-mapping.md) even when lint returns clean. On parseerror, 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 all four reference files at Phase 0: [query-plan/plan-interpretation.md](references/query-plan/plan-interpretation.md), [query-plan/catalog-queries.md](references/query-plan/catalog-queries.md), [query-plan/guc-experiments.md](references/query-plan/guc-experiments.md), [query-plan/report-format.md](references/query-plan/report-format.md). The phase procedures (capture plan, gather evidence, experiment, produce report) are defined in those files.

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](references/pg-migrations/type-mapping.md) and [pg-migrations/schema-objects.md](references/pg-migrations/schema-objects.md). Run dsql_lint(fix=true) first for mechanical fixes, 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 (Django/Hibernate/Rails)

Load [orm-guides/overview.md](references/orm-guides/overview.md) for adapter names and framework-specific gotchas.

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](references/dsql-lint.md). Do not silently skip validation — inform the user and require explicit confirmation before proceeding with manual rules from [development-guide.md](references/development-guide.md).
• OCC serialization error: Retry the transaction. If persistent, check for hot-key contention — see [troubleshooting.md](references/troubleshooting.md).
• Transaction exceeds limits: Split into batches under 3,000 rows — see [batched-migration.md](references/ddl-migrations/batched-migration.md).
• Token expiration mid-operation: Generate a fresh IAM token — see [authentication-guide.md](references/auth/authentication-guide.md). See [troubleshooting.md](references/troubleshooting.md) for other issues.

Additional Resources

• Aurora DSQL Documentation
• Code Samples Repository