--- name: Aurora DSQL slug: aurora-dsql category: Data description: Aurora DSQL helps you build with Amazon Aurora DSQL by managing schemas, running queries, handling migrations, and diagnosing performance. Use it for IAM auth, multi-tenant patterns, bulk loads, and SQL compatibility checks. github: "https://github.com/awslabs/agent-plugins/tree/main/plugins/databases-on-aws/skills/dsql" language: Python stars: 867 forks: 145 install: "npx degit https://github.com/awslabs/agent-plugins/tree/main/plugins/databases-on-aws/skills/dsql ~/.claude/skills/dsql" installs_to: ~/.claude/skills/dsql source_path: plugins/databases-on-aws/skills/dsql/SKILL.md collection_size: 25 category_size: 668 collection_url: "https://dirskills.com/collections/awslabs/agent-plugins" added: 2026-08-22T05:21:34.915Z last_synced: 2026-08-22T05:21:34.915Z canonical_url: "https://dirskills.com/skills/aurora-dsql" --- # Aurora DSQL Aurora DSQL helps you build with Amazon Aurora DSQL by managing schemas, running queries, handling migrations, and diagnosing performance. Use it for IAM auth, multi-tenant patterns, bulk loads, and SQL compatibility checks. **Install:** ```bash npx degit https://github.com/awslabs/agent-plugins/tree/main/plugins/databases-on-aws/skills/dsql ~/.claude/skills/dsql ``` ## README # 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: | Reference | When to Load | Contains | | --------------------------------------------------------- | --------------------------------------------------- | ------------------------------------------------------------------------------------------ | | [development-guide.md](references/development-guide.md) | ALWAYS before schema changes or DB operations | Best practices, DDL rules, transaction limits, app-layer referential integrity | | [language.md](references/language.md) | MUST load for language-specific choices | Driver selection, DSQL Connectors, connection code | | [access-control.md](references/access-control.md) | MUST load for roles, grants, or sensitive data | Scoped role setup, IAM-to-database role mapping | | [troubleshooting.md](references/troubleshooting.md) | SHOULD load for errors or unexpected behavior | OCC errors, connection failures, cluster state errors, token expiry, DDL rejection causes | | [dsql-examples.md](references/dsql-examples.md) | Load for implementation examples | Multi-tenant schema examples, batch operations, FK validation patterns, connection pooling | | [onboarding.md](references/onboarding.md) | User requests "Get started with DSQL" | Interactive step-by-step guide | | [occ-retry-patterns.md](references/occ-retry-patterns.md) | MUST load for OCC retry code or conflict mitigation | DSQL Connectors, manual retry pattern, idempotent design | ### MCP: | Reference | When to Load | Contains | | --------------------------------------- | --------------------------------------------------------------- | ------------------------------------------------------------------ | | [mcp-setup.md](mcp/mcp-setup.md) | Always for MCP server guidance | Setup instructions, 2 configuration options | | [mcp-tools.md](mcp/mcp-tools.md) | For MCP tool syntax and examples | Tool parameters, [input validation](mcp/tools/input-validation.md) | | [dsql-lint.md](references/dsql-lint.md) | MUST load before running `dsql_lint` or processing external SQL | Tool reference, fix statuses, unfixable error resolution | ### DDL Migrations: | Reference | When to Load | Contains | | --------------------------------------------------------------------------------------------- | ------------------------------------------------------------ | --------------------------------------- | | [ddl-migrations/overview.md](references/ddl-migrations/overview.md) | MUST load for DROP COLUMN, ALTER TYPE, DROP CONSTRAINT | Table recreation pattern, verify & swap | | [ddl-migrations/column-operations.md](references/ddl-migrations/column-operations.md) | DROP COLUMN, ALTER TYPE, SET/DROP NOT NULL/DEFAULT | Column-level migration patterns | | [ddl-migrations/constraint-operations.md](references/ddl-migrations/constraint-operations.md) | ADD/DROP CONSTRAINT, VALIDATE CONSTRAINT, MODIFY PRIMARY KEY | Constraint and structural changes | | [ddl-migrations/batched-migration.md](references/ddl-migrations/batched-migration.md) | Tables exceeding 3,000 rows | Batching patterns, progress tracking | ### MySQL Migrations: | Reference | When to Load | Contains | | ----------------------------------------------------------------------------------- | ------------------------------------ | ---------------------------------------- | | [mysql-migrations/type-mapping.md](references/mysql-migrations/type-mapping.md) | MUST load for MySQL → DSQL migration | Data type mappings, feature alternatives | | [mysql-migrations/ddl-operations.md](references/mysql-migrations/ddl-operations.md) | Translating MySQL DDL to DSQL | AUTO_INCREMENT, ENUM, SET, FK patterns | | [mysql-migrations/full-example.md](references/mysql-migrations/full-example.md) | Complete MySQL table migration | End-to-end example with decision summary | ### PostgreSQL Migrations: | Reference | When to Load | Contains | | --------------------------------------------------------------------------------- | ---------------------------------------------------------------- | -------------------------------------------------- | | [pg-migrations/type-mapping.md](references/pg-migrations/type-mapping.md) | MUST load for PG → DSQL type questions | C collation rules, NUMERIC precision, JSON/JSONB | | [pg-migrations/fk-replacement.md](references/pg-migrations/fk-replacement.md) | MUST load for FK validation code generation | Tenant-scoped validate_fk_*() template, cascade | | [pg-migrations/index-conversion.md](references/pg-migrations/index-conversion.md) | MUST load for unfixable index diagnostics | GIN/GiST/BRIN → btree, partial, expression indexes | | [pg-migrations/schema-objects.md](references/pg-migrations/schema-objects.md) | MUST load for ENUM, materialized views, extensions, multi-schema | ENUM → CHECK, views, role/IAM mapping | | [pg-migrations/multi-region.md](references/pg-migrations/multi-region.md) | Multi-region, active-active, or HA questions | Architecture, geographic partitioning | ### ORM Guides: | Reference | When to Load | Contains | | ----------------------------------------------------------- | ------------------------- | ------------------------------------------------------------------------ | | [orm-guides/overview.md](references/orm-guides/overview.md) | Migrating any ORM to DSQL | Adapter names, key gotchas for Django/EF Core/Hibernate/Rails/SQLAlchemy | ### Data Loading: | Reference | When to Load | Contains | | --------------------------------------------- | -------------------------------------------------------- | ----------------------------------------------------------------------------------------- | | [data-loading.md](references/data-loading.md) | Planning or running bulk loads with `aurora-dsql-loader` | Fresh-vs-warm partitions, resume/retry, `--on-conflict` semantics, throughput diagnostics | ### System Diagnostics: | Reference | When to Load | Contains | | ----------------------------------------------------------------------------------------- | ---------------------------------------------------------------- | --------------------------------------------------------------------- | | [system-diagnostics/workflow.md](references/system-diagnostics/workflow.md) | MUST load at Workflow 12 entry — cluster performance diagnostics | Prerequisites, 5 diagnostic phases, temporal comparison, handoff | | [system-diagnostics/wait-events.md](references/system-diagnostics/wait-events.md) | ALWAYS load when interpreting AAS results | Canonical DSQL wait event descriptions and investigation guidance | | [system-diagnostics/promql-patterns.md](references/system-diagnostics/promql-patterns.md) | Load when constructing PromQL queries | Reusable query templates for AAS breakdown, top-SQL, temporal compare | ### Query Plan Explainability: | Reference | When to Load | Contains | | --------------------------------------------------------------------------------------------------- | ----------------------------------------------------- | ------------------------------------------------------------------------- | | [query-plan/workflow.md](references/query-plan/workflow.md) | MUST load at Workflow 9 entry — gates all other files | Trigger criteria, context disambiguation, routing, phased workflow | | [query-plan/plan-interpretation.md](references/query-plan/plan-interpretation.md) | MUST load at Workflow 9 Phase 0 | DSQL node types, Node Duration math, estimation-error bands | | [query-plan/catalog-queries.md](references/query-plan/catalog-queries.md) | MUST load at Workflow 9 Phase 0 | `pg_class`/`pg_stats`/`pg_indexes` SQL, correlated-predicate verification | | [query-plan/guc-experiments.md](references/query-plan/guc-experiments.md) | MUST load at Workflow 9 Phase 0 | GUC experiment procedures, 30-second skip protocol | | [query-plan/report-format.md](references/query-plan/report-format.md) | MUST load at Workflow 9 Phase 0 | Required report structure, element checklist, support request template | | [query-plan/query-rewrites-generic.md](references/query-plan/query-rewrites-generic.md) | SHOULD load at Phase 0; sub-files on-demand | Index of 10 generic rewrite patterns | | [query-plan/query-rewrites-dsql-specific.md](references/query-plan/query-rewrites-dsql-specific.md) | SHOULD load at Phase 0; sub-files on-demand | Index of DSQL-specific rewrite patterns | --- ## 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`](../../scripts/psql-connect.sh) ` --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](references/auth/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](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: | Limit | Default | Verify query | | ------------------------------ | ------------- | ---------------------------------- | | Max rows per transaction | 3,000 | `aurora dsql transaction limits` | | Max data size per transaction | 10 MiB | `aurora dsql transaction limits` | | Max transaction duration | 5 minutes | `aurora dsql transaction limits` | | Max connections per cluster | 10,000 | `aurora dsql connection limits` | | Auth token expiry | 15 minutes | `aurora dsql authentication token` | | Max connection duration | 60 minutes | `aurora dsql connection limits` | | Max indexes per table | 24 | `aurora dsql index limits` | | Max columns per index | 8 | `aurora dsql index limits` | | IDENTITY/SEQUENCE CACHE values | 1 or >= 65536 | `aurora dsql sequence cache` | | Supported column data types | See docs | `aurora dsql supported data types` | **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](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/). ## CLI Scripts Available Bash scripts in [scripts/](../../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](../../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](#choosing-how-to-connect-mcp-vs-clipsql). 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](references/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: | User signal | Route to | | ----------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------- | | General performance complaint, "cluster is slow", "something changed", latency regression, no specific query identified | **Workflow 12** (System Diagnostics) — observe via CloudWatch first | | Specific query or query_id to investigate, "explain this plan", "why is this query slow" | **Workflow 9** (Query Plan Explainability) — direct EXPLAIN analysis | | OCC conflicts, commit errors, retry storms | **Workflow 12** (System Diagnostics) — confirm via CW metrics before investigating | | Cost optimization, "where is compute time spent" | **Workflow 12** (System Diagnostics) — identify top contributors first | **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 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 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](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: Applic