/aurora-dsql

DSQL Database Operations Reference

Part of the Aurora DSQL Skill. The PREFERRED execution path is psql via scripts/psql-connect.sh; application code should use the language-specific DSQL Connector.

1. Read-Only Queries (SELECT)

Use for: SELECT queries, data exploration, ad-hoc analysis.

Connect with the scoped (non-admin) auth token:

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

The wrapper rejects multi-statement input, dollar-quoted strings, and SQL comment markers in --command (a single trailing semicolon is accepted). For multi-statement scripts (BEGIN/COMMIT blocks, migration files, GUC experiments), use --script PATH instead — it runs a SQL file through psql -f with ON_ERROR_STOP=1 and no semicolon guard. In application code, build SQL with safe_query.build() and execute via your driver.

Examples:

from safe_query import build, regex, ident, TENANT_SLUG

# Simple SELECT — user-supplied tenant_id goes through a validator
sql = build(
    "SELECT * FROM {tbl} WHERE tenant_id = {tid} LIMIT 10",
    tbl=ident("entities"),
    tid=regex(tenant_id, TENANT_SLUG),
)
# Pass `sql` to your driver: psycopg `cur.execute(sql)`, pgx `conn.Query(ctx, sql)`, etc.

# Aggregate query (no user-supplied values)
sql = build(
    "SELECT tenant_id, COUNT(*) as count FROM objectives GROUP BY tenant_id",
)

# Join query — declare e/o as table aliases after each ident() expansion
sql = build(
    "SELECT e.entity_id, e.name, o.title "
    "FROM {e} e INNER JOIN {o} o ON e.entity_id = o.entity_id "
    "WHERE e.tenant_id = {tid}",
    e=ident("entities"),
    o=ident("objectives"),
    tid=regex(tenant_id, TENANT_SLUG),
)

Building queries: MUST build SQL with safe_query.build() for any value that originates outside the developer's source code. F-string interpolation is the primary SQL-injection vector. See input-validation.md for the required pattern.

2. Write Operations (INSERT / UPDATE / DELETE / DDL)

Use for: INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE.

Connect with the admin auth token (or a scoped role with the appropriate grants):

./scripts/psql-connect.sh --cluster <cluster-id> --admin --command "ALTER TABLE entities ADD COLUMN status VARCHAR(50)"

DSQL transaction rules (always apply):

• One DDL statement per transaction. Each psql -c invocation already opens its own implicit transaction, so chaining DDL across one invocation is forbidden.
• CREATE INDEX ASYNC is required; synchronous index creation is not supported.
• Atomic commit/rollback. Multi-statement DML inside a single transaction commits or rolls back as a unit — open the transaction explicitly with BEGIN;/COMMIT; when feeding multiple statements through a driver.

Examples (driver-side, using safe_query to compose the statements):

# Create table with index — TWO transactions, in order
conn.execute("CREATE TABLE IF NOT EXISTS entities (...)")           # tx 1: DDL
conn.execute("CREATE INDEX ASYNC idx_entities_tenant ON entities(tenant_id)")  # tx 2: DDL

# Insert rows — build each statement with safe_query.
from safe_query import build, allow, regex, literal, UUID, TENANT_SLUG

with conn.transaction():
    for row in rows:  # keep each transaction under 3,000 rows
        sql = build(
            "INSERT INTO entities (entity_id, tenant_id, name) "
            "VALUES ({eid}, {tid}, {name})",
            eid=regex(row["entity_id"], UUID),
            tid=regex(row["tenant_id"], TENANT_SLUG),
            name=literal(row["name"]),
        )
        conn.execute(sql)

# Two-step column migration
STATUSES = {"active", "archived", "pending"}
conn.execute("ALTER TABLE entities ADD COLUMN status VARCHAR(50)")  # tx 1
sql = build(
    "UPDATE entities SET status = {s} "
    "WHERE status IS NULL AND tenant_id = {tid}",
    s=allow("active", STATUSES),
    tid=regex(tenant_id, TENANT_SLUG),
)
conn.execute(sql)                                                   # tx 2

Important Notes:

• Each ALTER TABLE must be in its own transaction (DSQL limitation)
• Keep transactions under 3,000 rows and 10 MiB
• For large batch operations, split across multiple transactions
• MUST build every statement with safe_query.build() when any value is not a developer-controlled literal.

3. Schema Discovery

Use for: understanding table structure, planning migrations, exploring the database.

DSQL supports the standard PostgreSQL information_schema and pg_catalog views — no DSQL-specific helper is needed.

List tables in the public schema:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Inspect a specific table's columns:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'entities'
ORDER BY ordinal_position;

Inspect indexes on a table:

SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'entities';

Run any of these via scripts/psql-connect.sh or your driver of choice.