Aurora DSQL

Connection keys

Each Aurora DSQL operation connects to one cluster as one database role. Set the connection keys on the operation, or one time in [defaults].

Aurora DSQL operations do not accept endpointUrl, database or tableArn. In the app, set the defaults in Request settings.

This part of inspect-orders.dnml gets the cluster ID from an input:

[defaults]profileName = "commerce-dev"clusterArn = "arn:aws:dsql:us-east-1:111122223333:cluster/${CLUSTER_ID}"databaseRole = "app_reader"

Connections in a run

• A run opens one connection for each profile, cluster ARN and database role. Operations with the same three values share the connection, so a SET stays for the later operations.
• Each connection starts with TimeZone=UTC, DateStyle=ISO and IntervalStyle=iso_8601.
• The connection uses the saved network settings of the cluster ARN.
• After a cancellation, a deadline, a lost connection or a failed rollback, Dynomate closes the connection. Later operations on it fail in the same run.
• Aurora DSQL operations need no confirmation or approval, also as admin. See Aurora DSQL connections.

Required permissions

A request needs dsql:DbConnect on the cluster, or dsql:DbConnectAdmin to connect as admin. It makes no Aurora DSQL API calls, because Dynomate signs the token on your computer. For an example policy, see connection permissions. A request does not need the dsql:GetCluster action of that policy.

Parameters

parameters gives the values for $1, $2 and so on, in order. Each value binds as a PostgreSQL parameter:

• With parameters, the SQL must be one statement. Without parameters, the SQL can hold more than one statement.
• For exact money values, use { N = "12.50" }. A TOML float binds as float8.
• To reuse a decimal from an earlier result, use a whole-value template. { N = "${…}" } fails with DNML_TYPED_VALUE.

In this part of inspect-orders.dnml, the SQL casts the string $1 to interval, and $2 binds as int8:

[[dsql.query]]name = "Recent orders"sql = '''SELECT id, status, total, created_atFROM ordersWHERE created_at >= now() - $1::intervalORDER BY created_at DESCLIMIT $2'''parameters = ["7 days", 100]maxRows = 25

Dollar quotes

In DNML, $$ is the escape for one $, so a $$ … $$ body reaches Aurora DSQL as $ … $. Use a tagged dollar quote, for example $fn$ … $fn$. See Escaping in DNML.

Results

dsql.query and dsql.transaction return this result:

{ statements: [ { columns: [ { name, key, type } ], rows: [ { <key>: value } ],                  rowCount, truncated, rowsAffected, commandTag } ],  attempts, elapsedMs }

• statements has one entry for each statement that the server runs. The BEGIN and COMMIT that Dynomate sends have no entry.
• The rows use key, which is the column name. Repeated column names get _2, _3 and so on.
• With maxRows, truncated is true if the server sent more rows than Dynomate kept.

Each value in rows gets a type from its PostgreSQL type:

To use a value in a later operation, refer to the result by the normalized name of the operation, for example ${load_order.statements[0].rows[0].id}. For the CLI output, see Aurora DSQL results in the CLI.

Commit status and retries

When a dsql.query or dsql.transaction fails, details.commitStatus shows what happened to its changes, if Dynomate knows it:

An unknown outcome comes from an XX000 error, a cancellation, a deadline or a lost connection during a commit or an autocommit write. See Unknown commit outcomes.

Retry on conflict

By default, an optimistic concurrency conflict fails the operation. With retryOnConflict = true, Dynomate runs the full operation again after OC000 or OC001, up to 3 attempts in total. It does not retry after an unknown outcome or another error. timeoutMs includes all attempts.

In dsql-ledger.dnml, a retry is safe. The UPDATE reads the balance that it changes, and no value comes from an earlier operation:

[[dsql.transaction]]name = "Record payment"dependsOn = "Account"statements = [  {    sql = "UPDATE ledger.accounts SET balance = balance - $2, updated_at = now() WHERE id = $1",    parameters = ["${ACCOUNT_ID}", { N = "12.50" }],  },  {    sql = "INSERT INTO ledger.payments (account_id, amount) VALUES ($1, $2)",    parameters = ["${ACCOUNT_ID}", { N = "12.50" }],  },]retryOnConflict = true

Aurora DSQL query

Runs SQL on an Aurora DSQL cluster in autocommit mode and returns the rows and command tag of each statement.

[defaults]profileName = "commerce-dev"clusterArn = "arn:aws:dsql:us-east-1:111122223333:cluster/exampleclusterid0123456789"databaseRole = "app_writer"[[dsql.query]]name = "Load order"sql = '''SELECT id, status, total, currencyFROM ordersWHERE id = $1 AND status = 'PENDING''''parameters = ["${ORDER_ID}"]

• Each dsql.query is destructive, also a SELECT, but it needs no confirmation or approval.
• Dynomate sends the SQL as written and adds no LIMIT. maxRows only limits the rows that Dynomate keeps.
• If the SQL leaves a transaction open, the result has the warning DNML_DSQL_TRANSACTION_OPEN. A later operation on the same connection must run COMMIT. If not, the transaction rolls back when the run ends.

DSQL transaction

Runs statements in one transaction, between BEGIN and COMMIT. If a statement fails, Dynomate rolls back the transaction.

[[dsql.transaction]]name = "Settle"dependsOn = "Load order"statements = [  {    sql = "UPDATE orders SET status = 'SETTLED', settled_at = now() WHERE id = $1 AND status = 'PENDING'",    parameters = ["${load_order.statements[0].rows[0].id}"],  },  {    sql = "INSERT INTO order_events (order_id, event, amount, currency) VALUES ($1, 'settled', $2, $3)",    parameters = ["${load_order.statements[0].rows[0].id}", "${load_order.statements[0].rows[0].total}", "${load_order.statements[0].rows[0].currency}"],  },]

• If a statement fails, Dynomate sends ROLLBACK. details.statementIndex names the statement.
• A statement that runs COMMIT or ROLLBACK ends the transaction early. The result then has the warning DNML_DSQL_TRANSACTION_ENDED.
• The Aurora DSQL limits apply. For example, a transaction cannot mix DDL and DML. The 5-minute limit gives DNML_AWS, not DNML_TIMEOUT.
• Here, retryOnConflict stays false, because the values come from an earlier operation.

List schemas

Lists the schemas in an Aurora DSQL cluster, without the system schemas.

[[dsql.listSchemas]]name = "Schemas"

The result has schemas and count. Each schema has name.

List DSQL tables

Lists the tables and views in one schema, with an optional name pattern.

[[dsql.listTables]]name = "Order tables"schema = "public"expression = "order%"

Here, order% matches orders and order_events. The result has tables and count. Each table has schema, name, kind, estimatedRows and comment.

Describe DSQL table

Returns the columns, primary key, indexes and foreign keys of one table or view.

[[dsql.describeTable]]name = "Orders table"dependsOn = "Wait for index"schema = "public"tableName = "orders"

• The result has table, with columns, primaryKey, indexes, foreignKeys, estimatedRows and comment.
• The state of each index is valid, invalid or building.
• If the table does not exist, the operation fails with DNML_AWS and the SQLSTATE 42P01.

Wait for job

Waits until an asynchronous job, for example an index build, finishes.

[defaults]profileName = "commerce-admin"clusterArn = "arn:aws:dsql:us-east-1:111122223333:cluster/exampleclusterid0123456789"databaseRole = "admin"[[dsql.query]]name = "Create index"sql = "CREATE INDEX ASYNC orders_by_status ON orders (status, created_at)"[[dsql.waitForJob]]name = "Wait for index"dependsOn = "Create index"timeoutMs = 900000jobId = "${create_index.statements[0].rows[0].job_id}"pollIntervalMs = 2000

• Wait for index reads the job ID from the job_id column that CREATE INDEX ASYNC returns.
• The default deadline is 30 minutes. Here, timeoutMs sets 15 minutes.
• state in the result is completed, or unknown if Aurora DSQL removed the job record. For an index job, indexValid then shows if the index is valid.
• A failed job fails the operation with DNML_AWS.