Amazon Aurora Dsql

Ocupación

• Execute SELECT queries (returns list of dicts) transact
• Execute DDL/DML statements in transaction (takes list of SQL statements) get_schema
• Get table structure for a specific table Documentation & Knowledge:

dsql_search_documentation

• Search Aurora DSQL documentation

dsql_read_documentation

• Read specific documentation pages

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. CLI Scripts Available Bash scripts for cluster management and direct psql connections. All scripts are located in scripts/ . Cluster Management: create-cluster.sh
• Create new DSQL cluster with optional tags delete-cluster.sh
• Delete cluster with confirmation prompt list-clusters.sh
• List all clusters in a region cluster-info.sh
• Get detailed cluster information Database Connection: psql-connect.sh
• Connect to DSQL using psql with automatic IAM auth token generation Quick example: ./scripts/create-cluster.sh --region us-east-1 export CLUSTER=abc123def456 ./scripts/psql-connect.sh See scripts/README.md for detailed usage. Quick Start

1. List tables and explore schema Use readonly_query with information_schema to list tables Use get_schema to understand table structure
2. Query data Use readonly_query for SELECT queries Always include tenant_id in WHERE clause for multi-tenant apps Validate inputs carefully (no parameterized queries available)
3. Execute schema changes Use transact tool with list of SQL statements Follow one-DDL-per-transaction rule Always use CREATE INDEX ASYNC in separate transaction Common Workflows Workflow 1: Create Multi-Tenant Schema Goal: Create a new table with proper tenant isolation Steps: Create main table with tenant_id column using transact Create async index on tenant_id in separate transact call Create composite indexes for common query patterns (separate transact calls) Verify schema with get_schema Critical rules: Include tenant_id in all tables Use CREATE INDEX ASYNC (never synchronous) Each DDL in its own transact call: transact(["CREATE TABLE ..."]) Store arrays/JSON as TEXT Workflow 2: Safe Data Migration Goal: Add a new column with defaults safely Steps: Add column using transact: transact(["ALTER TABLE ... ADD COLUMN ..."]) Populate existing rows with UPDATE in separate transact calls (batched under 3,000 rows) Verify migration with readonly_query using COUNT Create async index for new column using transact if needed Critical rules: Add column first, populate later Never add DEFAULT in ALTER TABLE Batch updates under 3,000 rows in separate transact calls Each ALTER TABLE in its own transaction Workflow 3: Application-Layer Referential Integrity Goal: Safely insert/delete records with parent-child relationships Steps for INSERT: Validate parent exists with readonly_query Throw error if parent not found Insert child record using transact with parent reference Steps for DELETE: Check for dependent records with readonly_query (COUNT) Return error if dependents exist Delete record using transact if safe Workflow 4: Query with Tenant Isolation Goal: Retrieve data scoped to a specific tenant Steps: Always include tenant_id in WHERE clause Validate and sanitize tenant_id input (no parameterized queries available!) Use readonly_query with validated tenant_id Never allow cross-tenant data access Critical rules: Validate ALL inputs before building SQL (SQL injection risk!) ALL queries include WHERE tenant_id = 'validated-value' Reject cross-tenant access at application layer Use allowlists or regex validation for tenant IDs Workflow 5: Set Up Scoped Database Roles Goal: Create application-specific database roles instead of using the admin role MUST load access-control.md for detailed guidance. Steps: Connect as admin (the only time admin should be used) Create database roles with CREATE ROLE <name> WITH LOGIN Create an IAM role with dsql:DbConnect for each database role Map database roles to IAM roles with AWS IAM GRANT Create dedicated schemas for sensitive data (e.g., users_schema ) Grant schema and table permissions per role Applications connect using generate-db-connect-auth-token (not the admin variant) Critical rules: ALWAYS use scoped database roles for application connections MUST place user PII and sensitive data in dedicated schemas, not public ALWAYS use dsql:DbConnect for application IAM roles SHOULD create separate roles per service component (read-only, read-write, user service, etc.) Workflow 6: Table Recreation DDL Migration Goal: Perform DROP COLUMN, RENAME COLUMN, ALTER COLUMN TYPE, or DROP CONSTRAINT using the table recreation pattern. MUST load ddl-migrations.md for detailed guidance. Steps: MUST validate table exists and get row count with readonly_query MUST get current schema with get_schema MUST create new table with desired structure using transact MUST migrate data (batched in 500-1,000 row chunks for tables > 3,000 rows) MUST verify row counts match before proceeding MUST swap tables: drop original, rename new MUST recreate indexes using CREATE INDEX ASYNC Rules: MUST use batching for tables exceeding 3,000 rows PREFER batches of 500-1,000 rows for optimal throughput MUST validate data compatibility before type changes (abort if incompatible) MUST NOT drop original table until new table is verified MUST recreate all indexes after table swap using ASYNC Workflow 6: MySQL to DSQL Schema Migration Goal: Migrate MySQL table schemas and DDL operations to DSQL-compatible equivalents, including data type mapping, ALTER TABLE ALTER COLUMN, and DROP COLUMN operations. MUST load mysql-to-dsql-migrations.md for detailed guidance. Steps: MUST map all MySQL data types to DSQL equivalents (e.g., AUTO_INCREMENT → UUID/IDENTITY/SEQUENCE, ENUM → VARCHAR with CHECK, JSON → TEXT) MUST remove MySQL-specific features (ENGINE, FOREIGN KEY, ON UPDATE CURRENT_TIMESTAMP, FULLTEXT INDEX) MUST implement application-layer replacements for removed features (referential integrity, timestamp updates) For ALTER TABLE ... ALTER COLUMN col datatype or MODIFY COLUMN : MUST use table recreation pattern For ALTER TABLE ... DROP COLUMN col : MUST use table recreation pattern MUST convert all index creation to CREATE INDEX ASYNC in separate transactions MUST validate data compatibility before type changes (abort if incompatible) Rules: MUST use table recreation pattern for ALTER COLUMN and DROP COLUMN (not directly supported) MUST replace FOREIGN KEY with application-layer referential integrity MUST replace ENUM with VARCHAR and CHECK constraint MUST replace SET with TEXT (comma-separated) MUST replace JSON columns with TEXT MUST convert AUTO_INCREMENT to UUID, IDENTITY column, or SEQUENCE (SERIAL not supported) MUST replace UNSIGNED integers with CHECK (col >= 0) MUST use batching for tables exceeding 3,000 rows MUST NOT drop original table until new table is verified Best Practices SHOULD read guidelines first

• Check development_guide.md before making schema changes SHOULD use preferred language patterns
• Check language.md SHOULD Execute queries directly
• PREFER MCP tools for ad-hoc queries REQUIRED: Follow DDL Guidelines
• Refer to DDL Rules SHALL repeatedly generate fresh tokens
• Refer to Connection Limits ALWAYS use ASYNC indexes

CREATE INDEX ASYNC is mandatory MUST Serialize arrays/JSON as TEXT

• Store arrays/JSON as TEXT (comma separated, JSON.stringify) ALWAYS Batch under 3,000 rows
• maintain transaction limits REQUIRED: Sanitize SQL inputs with allowlists, regex, and quote escaping
• See Input Validation MUST follow correct Application Layer Patterns
• when multi-tenant isolation or application referential itegrity are required; refer to Application Layer Patterns REQUIRED use DELETE for truncation
• DELETE is the only supported operation for truncation SHOULD test any migrations
• Verify DDL on dev clusters before production Plan for Horizontal Scale
• DSQL is designed to optimize for massive scales without latency drops; refer to Horizontal Scaling SHOULD use connection pooling in production applications
• Refer to Connection Pooling SHOULD debug with the troubleshooting guide:
• Always refer to the resources and guidelines in troubleshooting.md ALWAYS use scoped roles for applications
• Create database roles with dsql:DbConnect ; refer to Access Control Additional Resources Aurora DSQL Documentation Code Samples Repository PostgreSQL Compatibility IAM Authentication Guide CloudFormation Resource