# Authentication
Source: https://docs.dynamosql.com/api-reference/authentication
Obtain and refresh bearer tokens for API access.
DynamoSQL API clients authenticate by exchanging their credentials for a bearer token via `POST /v1/auth/token`. When the access token expires, use `POST /v1/auth/refresh` to get a new one.
* [Get Access Token](/api-reference/authentication/get-token) -- exchange `clientId` and `clientSecret` for tokens
* [Refresh Access Token](/api-reference/authentication/refresh-token) -- exchange a refresh token for a new access token
See the [Authentication guide](/guides/authentication) for token caching patterns, scope details, and tenant scoping.
# Get Access Token
Source: https://docs.dynamosql.com/api-reference/authentication/get-token
POST /v1/auth/token
Exchange API client credentials for a bearer token.
# Refresh Access Token
Source: https://docs.dynamosql.com/api-reference/authentication/refresh-token
POST /v1/auth/refresh
Exchange a refresh token for a new access token.
# Query
Source: https://docs.dynamosql.com/api-reference/query
POST /v1/query
Run or plan SQL queries against DynamoDB.
Required scope: `query`
## Before You Start
* See [Query Modes](/guides/query-modes) for when to use `execute` vs `plan` and what each response field means.
* See [Pagination](/guides/pagination) for how to page through large result sets using `maxRows` and `resumeIdx`.
* See [Response Formats](/guides/response-formats) to choose between row arrays and key-value objects.
* See the [SQL Reference](/sql-reference/overview) for the full list of supported SQL features and current limitations.
## Authentication
All requests require a bearer token obtained from `POST /v1/auth/token`. Pass it in the `Authorization` header:
```
Authorization: Bearer YOUR_ACCESS_TOKEN
```
Both modes require the `query` scope.
# Create Schema
Source: https://docs.dynamosql.com/api-reference/schemas/create
POST /v1/schemas
Connect DynamoSQL to DynamoDB tables in your AWS account.
Required scope: `schemas:write`
Before creating a schema, follow the [IAM Setup](/guides/iam-setup) guide to configure the trust policy and permissions DynamoSQL needs to assume your role. After creating a schema, use [Validate Role](/api-reference/schemas/validate-role) to confirm the configuration is correct.
# Delete Schema
Source: https://docs.dynamosql.com/api-reference/schemas/delete
DELETE /v1/schemas/{schemaName}
Permanently delete a schema and its cached metadata.
Required scope: `schemas:write`
# Get Schema
Source: https://docs.dynamosql.com/api-reference/schemas/get
GET /v1/schemas/{schemaName}
Retrieve a schema by name.
Required scope: `schemas:read`
# List Schemas
Source: https://docs.dynamosql.com/api-reference/schemas/list
GET /v1/schemas
List all schemas for your tenant.
Required scope: `schemas:read`
# Refresh Metadata
Source: https://docs.dynamosql.com/api-reference/schemas/refresh-metadata
POST /v1/schemas/{schemaName}/refresh-metadata
Refresh the cached DynamoDB table metadata for a schema.
Required scope: `schemas:write`
# Update Schema
Source: https://docs.dynamosql.com/api-reference/schemas/update
PATCH /v1/schemas/{schemaName}
Update a schema's connection details, table allowlist, or status.
Required scope: `schemas:write`
# Validate Role
Source: https://docs.dynamosql.com/api-reference/schemas/validate-role
POST /v1/schemas/{schemaName}/validate-role
Verify that the schema's IAM role can be assumed and has DynamoDB access.
Required scope: `schemas:write`
# Usage Summary
Source: https://docs.dynamosql.com/api-reference/usage
GET /v1/usage/summary
Query metered usage — requests, rows returned, and DynamoDB read units.
Required scope: `usage:read`
# 2026 Changelog
Source: https://docs.dynamosql.com/changelog/2026
DynamoSQL release history for 2026.
## v0.1 — 2026-03-11 — Initial Public Release
DynamoSQL is now publicly available.
**API**
* `POST /v1/query` endpoint with `execute` and `plan` modes
* Stateless offset pagination via `maxRows` / `resumeIdx` options
* Row and object response formats via `options.responseType`
* `x-request-id` response header on all Lambda responses for support tracing
**Authentication**
* Cognito JWT authentication using the OAuth 2.0 client credentials grant
* Scoped access: `query:execute` and `query:plan`
* Multi-tenant request scoping via per-tenant IAM role assumption with ExternalId
**SQL Support**
* `SELECT` with column aliases, `SELECT *`, `table.*`, `SELECT DISTINCT`
* `ORDER BY` (ascending), `LIMIT`, `OFFSET`, `FETCH FIRST n ROWS ONLY`
* `INNER JOIN`, `LEFT OUTER JOIN`, `RIGHT OUTER JOIN`, `FULL OUTER JOIN`
* `WHERE` with comparisons, `BETWEEN`, `IN`, `EXISTS`, `LIKE`, `IS NULL`
* `GROUP BY`, `HAVING`, aggregate functions (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `STDDEV`)
* `WITH` CTEs (single and multiple), scalar subqueries, correlated subqueries, derived tables
* `UNION` and `UNION ALL`
* Arithmetic, bitwise, string operators; `CASE`, `COALESCE`, `NULLIF`
* Built-in numeric, string, and conditional functions
**Docs**
* Interactive API playground and full documentation at [docs.dynamosql.com](https://docs.dynamosql.com)
# Authentication
Source: https://docs.dynamosql.com/guides/authentication
Principal types, scopes, and token lifecycle.
See the [Authentication API reference](/api-reference/authentication) for the interactive playground.
## Principal types
DynamoSQL recognizes two principal types:
| Type | Description | How to authenticate |
| --------------- | -------------------------------------------------------- | ------------------------------------------------------ |
| **Portal user** | Human user who logged in via the DynamoSQL portal | Passkey or password + TOTP; portal issues a session |
| **API client** | Machine-to-machine service account created in the portal | `POST /v1/auth/token` with `clientId` + `clientSecret` |
The `/v1/query` endpoint is intended for API clients. Portal users typically interact through the portal's SQL editor.
## Getting a token
API clients authenticate by posting their credentials to the token endpoint:
```bash curl theme={null}
curl -X POST https://api.dynamosql.com/v1/auth/token \
-H "Content-Type: application/json" \
-d '{
"clientId": "YOUR_CLIENT_ID",
"clientSecret": "YOUR_CLIENT_SECRET"
}'
```
```python Python theme={null}
import requests
resp = requests.post(
"https://api.dynamosql.com/v1/auth/token",
json={
"clientId": "YOUR_CLIENT_ID",
"clientSecret": "YOUR_CLIENT_SECRET",
},
)
data = resp.json()["data"]
access_token = data["accessToken"]
refresh_token = data["refreshToken"]
```
```javascript Node.js theme={null}
const resp = await fetch("https://api.dynamosql.com/v1/auth/token", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({
clientId: "YOUR_CLIENT_ID",
clientSecret: "YOUR_CLIENT_SECRET",
}),
});
const { data } = await resp.json();
const { accessToken, refreshToken } = data;
```
```typescript TypeScript theme={null}
const resp = await fetch("https://api.dynamosql.com/v1/auth/token", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({
clientId: "YOUR_CLIENT_ID",
clientSecret: "YOUR_CLIENT_SECRET",
}),
});
const { data } = (await resp.json()) as {
data: { accessToken: string; refreshToken: string; expiresIn: number };
};
```
The response contains:
```json theme={null}
{
"success": true,
"data": {
"accessToken": "eyJhbGciOiJSUzI1NiIs...",
"refreshToken": "eyJjdHkiOiJKV1QiLCJl...",
"expiresIn": 3600,
"tokenType": "Bearer"
}
}
```
* **`accessToken`** -- JWT valid for one hour. Pass as `Authorization: Bearer `.
* **`refreshToken`** -- use to obtain a new access token without re-sending credentials.
* **`expiresIn`** -- token lifetime in seconds.
## Scopes
Scopes control which endpoints an API client can access. They are assigned when the client is created in the portal.
| Scope | Required for |
| --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `query` | `POST /v1/query` (both `execute` and `plan` modes) |
| `schemas:read` | `GET /v1/schemas`, `GET /v1/schemas/{name}` |
| `schemas:write` | `POST /v1/schemas`, `PATCH /v1/schemas/{name}`, `DELETE /v1/schemas/{name}`, `POST /v1/schemas/{name}/refresh-metadata`, `POST /v1/schemas/{name}/validate-role` |
| `usage:read` | `GET /v1/usage/summary` |
New API clients receive `query` and `schemas:read` by default.
## Passing the bearer token
Include the token in the `Authorization` header on every request:
```
Authorization: Bearer YOUR_ACCESS_TOKEN
```
## Token refresh
When your access token expires, exchange the refresh token for a new access token instead of re-authenticating with credentials:
```bash curl theme={null}
curl -X POST https://api.dynamosql.com/v1/auth/refresh \
-H "Content-Type: application/json" \
-d '{
"refreshToken": "YOUR_REFRESH_TOKEN"
}'
```
```python Python theme={null}
resp = requests.post(
"https://api.dynamosql.com/v1/auth/refresh",
json={"refreshToken": refresh_token},
)
data = resp.json()["data"]
access_token = data["accessToken"]
```
```javascript Node.js theme={null}
const resp = await fetch("https://api.dynamosql.com/v1/auth/refresh", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({ refreshToken }),
});
const { data } = await resp.json();
const { accessToken } = data;
```
```typescript TypeScript theme={null}
const resp = await fetch("https://api.dynamosql.com/v1/auth/refresh", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({ refreshToken }),
});
const { data } = (await resp.json()) as {
data: { accessToken: string; expiresIn: number };
};
```
The refresh response contains a new `accessToken` and `expiresIn`. No new refresh token is issued.
## Token caching example
```python theme={null}
import requests
import time
class TokenCache:
def __init__(self, client_id, client_secret):
self.client_id = client_id
self.client_secret = client_secret
self._access_token = None
self._refresh_token = None
self._expires_at = 0
def get(self):
if time.time() < self._expires_at - 30: # 30s buffer
return self._access_token
# Try refresh first if we have a refresh token
if self._refresh_token:
try:
return self._refresh()
except Exception:
pass # Fall through to full auth
return self._authenticate()
def _authenticate(self):
resp = requests.post(
"https://api.dynamosql.com/v1/auth/token",
json={
"clientId": self.client_id,
"clientSecret": self.client_secret,
},
)
resp.raise_for_status()
data = resp.json()["data"]
self._access_token = data["accessToken"]
self._refresh_token = data.get("refreshToken")
self._expires_at = time.time() + data["expiresIn"]
return self._access_token
def _refresh(self):
resp = requests.post(
"https://api.dynamosql.com/v1/auth/refresh",
json={"refreshToken": self._refresh_token},
)
resp.raise_for_status()
data = resp.json()["data"]
self._access_token = data["accessToken"]
self._expires_at = time.time() + data["expiresIn"]
return self._access_token
```
## Tenant scoping
Every JWT carries a `tenantId` claim. The server reads this claim and scopes all DynamoDB access to that tenant's tables. You cannot query another tenant's data.
The `tenantId` field in the request body is optional. If provided, it must match the `tenantId` in the JWT -- a mismatch returns `403`.
# Error Handling
Source: https://docs.dynamosql.com/guides/error-handling
Interpret error responses and handle failures gracefully.
## The `success` Field
Every response from `POST /v1/query` includes a `success` boolean. Always check it first.
When `success` is `false`:
* `error` — a short, human-readable error message.
* `detailedError` — extended detail about the failure. May be absent for some error types. For parse errors, it includes the line and column position of the problem.
## HTTP Status Codes
| Status | Meaning |
| ------ | -------------------------------------------------------------------------------------------------------- |
| `200` | Request was processed. Check `success` — SQL errors return 200 with `success: false`. |
| `400` | Malformed request body, SQL parse error, or unsupported SQL feature. |
| `401` | Missing or expired JWT, or token issued by the wrong Cognito pool. |
| `403` | Missing required scope (`query`) or tenantId mismatch. |
| `429` | API rate limit exceeded. Retry with exponential backoff. |
| `500` | Internal server error. Retry with exponential backoff. Include `x-request-id` when reporting to support. |
SQL errors (parse failures, unknown tables, unsupported features) are returned as **200 with `success: false`**, not as 400. A 400 indicates the request body itself was malformed — for example, missing the `sql` field or sending invalid JSON.
## Parse Error Example
**Request (SQL typo):**
```json theme={null}
{
"sql": "SELEKT id, name FROM myschema.users",
"mode": "execute"
}
```
**Response:**
```json theme={null}
{
"success": false,
"error": "Parse error: unexpected token 'SELEKT'",
"detailedError": "Parse error at line 1, column 1: unexpected token 'SELEKT'. Expected SELECT, WITH, or a set operation."
}
```
HTTP status: `400`
## Execution Error Example (200 with `success: false`)
**Request:**
```json theme={null}
{
"sql": "SELECT * FROM myschema.nonexistent_table",
"mode": "execute"
}
```
**Response:**
```json theme={null}
{
"success": false,
"error": "Table not found: myschema.nonexistent_table",
"detailedError": "No table 'nonexistent_table' exists in schema 'myschema' for this tenant. Check that the table is registered in the portal under Settings > Data Sources."
}
```
HTTP status: `200`
## The `x-request-id` Header
Every response from the DynamoSQL Lambda includes an `x-request-id` response header. Save this value whenever you encounter a 500 error — include it when filing a support request so the DynamoSQL team can locate the relevant logs.
```
x-request-id: req_01HZ8KXMR3F9VW2BN6PQYT7GDE
```
## Retry Guidance
* **500s:** Retry with exponential backoff (e.g., 1s, 2s, 4s). These indicate a transient server-side failure.
* **429s:** Retry with exponential backoff. You have exceeded the per-tenant API call limit.
* **400 / 401 / 403:** Do not retry. These indicate a client-side error — fix the request, credentials, or token scopes before trying again.
* **200 with `success: false`:** Do not retry unless the error message suggests a transient condition. Most SQL errors are deterministic and will fail the same way on retry.
When building a wrapper around DynamoSQL, distinguish between HTTP-level errors (non-200 status) and application-level errors (200 with `success: false`). They require different handling: one is a transport or auth issue, the other is a SQL or schema issue.
# IAM Setup
Source: https://docs.dynamosql.com/guides/iam-setup
Grant DynamoSQL read access to your DynamoDB tables by creating a schema in the portal.
DynamoSQL queries DynamoDB in your own AWS account. Instead of storing long-lived credentials, DynamoSQL's data plane assumes an IAM role you create and control. This means your data never leaves your account — DynamoSQL receives only the query results it fetches on your behalf.
The portal walks you through this end-to-end when you create a schema. It generates the exact trust policy and permissions policy JSON for you to copy into AWS.
## Step 1: Create a schema in the portal
In the portal, navigate to **Schemas** and click **Create schema**. Fill in the following fields:
| Field | Description |
| ------------------- | ------------------------------------------------------------------------------------------------------------------------------------------- |
| **Schema name** | Logical name used in SQL (`schema.table`). Cannot be changed after creation. |
| **Region** | AWS region where your DynamoDB tables live (e.g., `us-east-1`). |
| **Account ID** | Your 12-digit AWS account number. |
| **Role name** | The IAM role name you will create in Step 2 (e.g., `DynamoSqlReadRole`). |
| **External ID** | Auto-generated. Used in the trust policy to prevent confused-deputy attacks. You can regenerate it, but do so before creating the IAM role. |
| **Table allowlist** | Table names DynamoSQL is allowed to query, one per line — or check **Allow all tables (\*)** for wildcard access. |
The portal derives the full role ARN from your account ID and role name and shows it to you before you submit.
## Step 2: Copy the generated IAM policies
Expand **Role setup instructions (AWS Console + CLI)** on the schema creation form. The portal generates two ready-to-use JSON documents based on your inputs:
**Trust policy** — allows DynamoSQL's data plane to assume the role, scoped to your ExternalId:
```json theme={null}
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AllowDynamoSqlAssumeRole",
"Effect": "Allow",
"Principal": {
"AWS": ["arn:aws:iam:::root"]
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId": ""
}
}
}
]
}
```
**DynamoDB read policy** — grants the read permissions DynamoSQL needs, scoped to your allowlisted tables:
```json theme={null}
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AllowDynamoSqlReadQueries",
"Effect": "Allow",
"Action": [
"dynamodb:BatchGetItem",
"dynamodb:DescribeTable",
"dynamodb:GetItem",
"dynamodb:Query",
"dynamodb:Scan"
],
"Resource": [
"arn:aws:dynamodb:*::table/"
]
}
]
}
```
When **Allow all tables (\*)** is enabled, the portal adds a `dynamodb:ListTables` statement on `Resource: "*"` so DynamoSQL can enumerate your tables.
Use the **Copy** buttons in the portal to copy each JSON block exactly — the values are pre-filled with your account ID, role name, and ExternalId.
## Step 3: Create the IAM role in AWS
The portal also shows a ready-to-run **AWS CLI script**. The fastest path is to copy it, save the two JSON files (`trust-policy.json` and `dsql-read-policy.json`), then run:
```bash theme={null}
aws iam create-role \
--role-name "DynamoSqlReadRole" \
--assume-role-policy-document file://trust-policy.json
aws iam put-role-policy \
--role-name "DynamoSqlReadRole" \
--policy-name DynamoSqlReadPolicy \
--policy-document file://dsql-read-policy.json
```
Alternatively, use the AWS Console:
1. IAM → Roles → **Create role**
2. Choose **Custom trust policy** and paste the trust policy JSON from the portal
3. Name the role exactly as entered in the schema form
4. Add an inline policy and paste the DynamoDB read policy JSON
5. Save the role
## Step 4: Save the schema and validate
Back in the portal, click **Create schema**. The portal saves the schema configuration and redirects you to the schema detail page, where it automatically attempts to assume the role and validate access.
If validation passes, the schema status shows **Active** and you're ready to query. If it fails, the portal shows a **Failed — Please Review** status. Common causes:
* Role name or account ID typo (the derived ARN won't match the role you created)
* Trust policy principal ARN does not match DynamoSQL's data plane ARN exactly
* ExternalId in the trust policy doesn't match the one generated by the portal
* IAM propagation delay — wait 10–15 seconds and re-validate from the schema detail page
## Step 5: Run a test query
Open the **SQL** tab in the portal and run a plan-mode query to confirm the role assumption succeeds without consuming DynamoDB capacity:
```sql theme={null}
SELECT * FROM myschema.orders LIMIT 10
```
Set the mode to **Plan** before running. A successful plan response confirms DynamoSQL can resolve your schema and table metadata. Switch to **Execute** to verify full read access.
# Pagination
Source: https://docs.dynamosql.com/guides/pagination
Loop through large result sets using stateless offset pagination.
DynamoSQL uses stateless offset pagination. There is no server-side cursor — each page is a fresh query execution that skips past previously-returned rows.
## How It Works
* `options.maxRows` — maximum rows to return per request. Defaults to `100`.
* `options.resumeIdx` — the row offset to start from. Omit (or pass `0`) for the first page.
When the response contains exactly `maxRows` rows, the engine sets `resumeIdx` in the response to `firstRowIdx + maxRows`. Pass that value as `options.resumeIdx` on the next request to get the next page.
When `resumeIdx` is **absent** from the response, the result set is exhausted.
Check for `resumeIdx` with `"resumeIdx" in data` (presence check), not `data.resumeIdx !== null` or `data.resumeIdx !== undefined`. The field is omitted entirely when there are no more rows — it is never set to `null`.
## Statefulness Caveat
This is **offset pagination** — the engine re-executes the full query and skips rows on each page. If DynamoDB data changes between page requests, rows may be duplicated or skipped at page boundaries. This approach is appropriate for analytics and reporting workloads, but is not suitable for transactional reads where consistency across pages is required.
## Pagination Loop Examples
```python Python theme={null}
import requests
def paginate(token, sql, max_rows=100):
resume_idx = 0
while True:
resp = requests.post(
"https://api.dynamosql.com/v1/query",
headers={"Authorization": f"Bearer {token}"},
json={
"sql": sql,
"mode": "execute",
"options": {"maxRows": max_rows, "resumeIdx": resume_idx},
},
)
body = resp.json()
if not body["success"]:
raise RuntimeError(body["error"])
data = body["data"]
yield from data["data"]
if "resumeIdx" not in data:
break
resume_idx = data["resumeIdx"]
# Usage
for row in paginate(token, "SELECT * FROM myschema.orders WHERE status = 'shipped'"):
print(row)
```
```javascript Node.js theme={null}
async function* paginate(token, sql, maxRows = 100) {
let resumeIdx = 0;
while (true) {
const resp = await fetch("https://api.dynamosql.com/v1/query", {
method: "POST",
headers: {
"Authorization": `Bearer ${token}`,
"Content-Type": "application/json",
},
body: JSON.stringify({
sql,
mode: "execute",
options: { maxRows, resumeIdx },
}),
});
const body = await resp.json();
if (!body.success) {
throw new Error(body.error);
}
const { data } = body;
yield* data.data;
if (!("resumeIdx" in data)) {
break;
}
resumeIdx = data.resumeIdx;
}
}
// Usage
for await (const row of paginate(token, "SELECT * FROM myschema.orders WHERE status = 'shipped'")) {
console.log(row);
}
```
# Postman Collection
Source: https://docs.dynamosql.com/guides/postman
Import the DynamoSQL API into Postman
Download Postman Collection
## Importing into Postman
1. Download the collection using the link above.
2. Open Postman and click **Import** (top left).
3. Drag the downloaded file into the import dialog, or click **Upload Files** and select it.
4. The collection will appear in your sidebar with folders for Authentication, Query, Schemas, and Usage.
## Getting started
After importing, call **Authentication > Get access token** with your `clientId` and `clientSecret`. Copy the `accessToken` from the response and set it as a Bearer token on the collection or individual requests.
# Query Modes
Source: https://docs.dynamosql.com/guides/query-modes
Understand the difference between execute and plan mode.
Every request to `POST /v1/query` runs in one of two modes, controlled by the `mode` field in the request body.
## Execute Mode
`"mode": "execute"` is the default. The engine parses, plans, optimizes, and then runs the query — fetching rows from DynamoDB and returning them in the response.
* **Consumes DynamoDB RCUs** charged to your AWS account.
* **Required scope:** `query`
* Returns a `data` payload with rows, column metadata, and timing fields.
## Plan Mode
`"mode": "plan"` parses and optimizes the query without fetching any data.
* **Does NOT consume DynamoDB RCUs.**
* **Required scope:** `query`
* Returns a human-readable plan tree, an optimizer weight (cost estimate), and the normalized SQL string.
Plan mode is useful for:
* **Debugging slow queries** — check whether the optimizer selected an index or fell back to a scan.
* **Validating SQL in CI/CD** — catch syntax errors and unsupported features without consuming capacity.
* **Previewing query structure** — understand how the engine will execute a query before running it against production tables.
## Understanding the Response Fields
### `weight`
The optimizer's relative cost estimate. Lower is cheaper. A weight of `0` means no cost estimate was computed — for example, when the query has no index candidates and the optimizer produces a simple scan plan.
### `planTime`
Present in both modes. Covers parse + plan + optimize only. It does **not** include any DynamoDB I/O.
### `execTime`
Present in execute mode only. Total elapsed time from request receipt to response, including all DynamoDB I/O. Always `>= planTime`.
## Example
**Plan mode request:**
```json theme={null}
{
"sql": "SELECT id, total FROM myschema.orders WHERE status = 'pending' LIMIT 10",
"mode": "plan"
}
```
**Plan mode response:**
```json theme={null}
{
"success": true,
"data": {
"plan": "Limit(10)\n Scan(myschema.orders)\n Filter(status = 'pending')",
"weight": 0,
"normalizedSql": "SELECT id, total FROM myschema.orders WHERE status = 'pending' LIMIT 10",
"planTime": 3
}
}
```
The plan tree shows the engine will perform a full `Scan` with a `Filter` applied — no index was matched. If you have a GSI on `status`, check that the schema metadata is registered correctly in the portal so the optimizer can see it.
If you see `Scan(...)` in the plan for a query that should use an index, run the query in plan mode first and inspect the tree before paying DynamoDB RCU costs for a scan.
**Execute mode request (same query):**
```json theme={null}
{
"sql": "SELECT id, total FROM myschema.orders WHERE status = 'pending' LIMIT 10",
"mode": "execute"
}
```
**Execute mode response:**
```json theme={null}
{
"success": true,
"data": {
"columns": ["id", "total"],
"data": [
["ord-001", 59.99],
["ord-002", 120.00]
],
"firstRowIdx": 0,
"planTime": 3,
"execTime": 47
}
}
```
# Quickstart
Source: https://docs.dynamosql.com/guides/quickstart
Get your first query running in under 5 minutes.
## Overview
DynamoSQL lets you query your DynamoDB tables with SQL. This guide walks through obtaining an access token and running your first query against the API.
## Prerequisites
* A DynamoSQL account -- sign up at [dynamosql.com](https://dynamosql.com)
* Your `clientId` and `clientSecret` from the portal
## Step 1 -- Obtain an access token
Exchange your API client credentials for a bearer token:
```bash curl theme={null}
curl -X POST https://api.dynamosql.com/v1/auth/token \
-H "Content-Type: application/json" \
-d '{
"clientId": "YOUR_CLIENT_ID",
"clientSecret": "YOUR_CLIENT_SECRET"
}'
```
```python Python theme={null}
import requests
resp = requests.post(
"https://api.dynamosql.com/v1/auth/token",
json={
"clientId": "YOUR_CLIENT_ID",
"clientSecret": "YOUR_CLIENT_SECRET",
},
)
token = resp.json()["data"]["accessToken"]
```
```javascript Node.js theme={null}
const resp = await fetch("https://api.dynamosql.com/v1/auth/token", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({
clientId: "YOUR_CLIENT_ID",
clientSecret: "YOUR_CLIENT_SECRET",
}),
});
const { accessToken } = (await resp.json()).data;
```
```typescript TypeScript theme={null}
const resp = await fetch("https://api.dynamosql.com/v1/auth/token", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({
clientId: "YOUR_CLIENT_ID",
clientSecret: "YOUR_CLIENT_SECRET",
}),
});
const { accessToken }: { accessToken: string } = (await resp.json()).data;
```
The token is valid for one hour. Cache and reuse it; refresh it with `POST /v1/auth/refresh` when it expires.
## Step 2 -- Run a query
Call `POST /v1/query` with your bearer token:
```bash curl theme={null}
curl -X POST https://api.dynamosql.com/v1/query \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
-H "Content-Type: application/json" \
-d '{
"sql": "SELECT * FROM myschema.orders LIMIT 10",
"mode": "execute"
}'
```
```python Python theme={null}
import requests
resp = requests.post(
"https://api.dynamosql.com/v1/query",
headers={"Authorization": f"Bearer {token}"},
json={
"sql": "SELECT * FROM myschema.orders LIMIT 10",
"mode": "execute",
},
)
result = resp.json()
```
```javascript Node.js theme={null}
const resp = await fetch("https://api.dynamosql.com/v1/query", {
method: "POST",
headers: {
Authorization: `Bearer ${accessToken}`,
"Content-Type": "application/json",
},
body: JSON.stringify({
sql: "SELECT * FROM myschema.orders LIMIT 10",
mode: "execute",
}),
});
const result = await resp.json();
```
```typescript TypeScript theme={null}
const resp = await fetch("https://api.dynamosql.com/v1/query", {
method: "POST",
headers: {
Authorization: `Bearer ${accessToken}`,
"Content-Type": "application/json",
},
body: JSON.stringify({
sql: "SELECT * FROM myschema.orders LIMIT 10",
mode: "execute",
}),
});
const result = await resp.json();
```
## Step 3 -- Interpret the response
A successful response looks like this:
```json theme={null}
{
"success": true,
"data": {
"columns": ["order_id", "customer_id", "total"],
"data": [
[1001, "cust-42", 99.99],
[1002, "cust-07", 14.50]
],
"firstRowIdx": 0,
"resumeIdx": 10,
"planTime": 3,
"execTime": 42
}
}
```
* **`success`** -- always check this first. If `false`, read `error` and `detailedError`.
* **`data.columns`** -- column names in the same order as each row array.
* **`data.data`** -- array of rows (each row is an array of values).
* **`data.resumeIdx`** -- present when there are more rows; pass it back to fetch the next page.
* **`data.planTime`** / **`data.execTime`** -- parse+plan time and total elapsed time in milliseconds.
`resumeIdx` is **absent** (not `null`) when the result set is exhausted. Always use `"resumeIdx" in data` (not `data.resumeIdx !== null`) to test for more pages.
## Next steps
* [Authentication](/guides/authentication) -- principal types, scopes, and token refresh
* [Pagination](/guides/pagination) -- looping through large result sets
* [SQL Reference](/sql-reference/overview) -- what SQL is supported
# Rate Limits and Quotas
Source: https://docs.dynamosql.com/guides/rate-limits-and-quotas
Per-tenant metering, DynamoDB capacity, and usage tracking.
## DynamoDB Capacity
DynamoSQL queries DynamoDB in your own AWS account using the IAM role you configure. **DynamoDB RCU consumption is charged directly to your AWS account** — DynamoSQL does not meter or bill for DynamoDB read capacity.
Scan-heavy queries — those where the optimizer cannot match a suitable index — consume significantly more RCUs than index-backed queries. Use [plan mode](/guides/query-modes) to verify which execution strategy the optimizer selected before running expensive queries against production tables.
If the plan tree shows `Scan(...)` for a query you expect to use an index, check that your GSI metadata is registered in the portal so the optimizer can see it.
## API Call Volume
DynamoSQL meters the number of API calls per tenant. Current limits and your rolling usage are visible in the portal under **Settings > Usage**.
For programmatic access to usage data, use the `/v1/usage/summary` endpoint.
If you exceed your API call limit, the API returns a `429` response. Retry with exponential backoff. If you consistently hit the limit, contact support to discuss a higher quota.
## Row Limits
There are no artificial row limits beyond the `maxRows` pagination parameter, which defaults to `100` rows per request. You can increase it per-request or page through the full result set using `resumeIdx`. See [Pagination](/guides/pagination) for details.
## Reducing Costs
| Practice | Effect |
| ------------------------------------------------ | --------------------------------------------- |
| Use plan mode during development | Zero RCU cost for SQL validation |
| Add GSIs for common WHERE predicates | Converts scans to index queries |
| Use `LIMIT` to cap row counts | Reduces RCUs for exploratory queries |
| Prefer key-based lookups over scans | DynamoDB key reads are the cheapest operation |
| Filter at the SQL layer, not in application code | Fewer rows fetched from DynamoDB |
# Response Formats
Source: https://docs.dynamosql.com/guides/response-formats
Choose between row arrays and key-value objects.
DynamoSQL supports two shapes for the `data` field in an execute-mode response. Set `options.responseType` in the request body to control which you receive.
## Row Format (default)
`"responseType": "row"` — `data` is an array of arrays. Each inner array is one row; values are ordered to match the `columns` array.
This format is compact and well-suited for tabular display, streaming to a spreadsheet, or passing into a table-rendering component.
## Object Format
`"responseType": "object"` — `data` is an array of objects. Each object maps column names to their values.
This format is more ergonomic in dynamic languages where you want to access fields by name without tracking column positions manually.
## Both Formats Include
* `columns` — ordered array of column name strings
* `firstRowIdx` — absolute offset of the first row in this page (always `0` on the first page)
* `resumeIdx` — present when more rows exist; pass as `options.resumeIdx` on the next request
* `planTime` — parse + plan + optimize time in milliseconds
* `execTime` — total elapsed time including DynamoDB I/O in milliseconds
## Example
**Request:**
```json theme={null}
{
"sql": "SELECT id, name FROM myschema.users LIMIT 2",
"mode": "execute",
"options": {
"responseType": "row"
}
}
```
```json Row format (default) theme={null}
{
"success": true,
"data": {
"columns": ["id", "name"],
"data": [
["u-001", "Alice"],
["u-002", "Bob"]
],
"firstRowIdx": 0,
"planTime": 2,
"execTime": 18
}
}
```
```json Object format theme={null}
{
"success": true,
"data": {
"columns": ["id", "name"],
"data": [
{ "id": "u-001", "name": "Alice" },
{ "id": "u-002", "name": "Bob" }
],
"firstRowIdx": 0,
"planTime": 2,
"execTime": 18
}
}
```
Response format has no effect in plan mode. Plan responses always use the plan result shape (`plan`, `weight`, `normalizedSql`, `planTime`) regardless of `responseType`.
# MCP Authentication
Source: https://docs.dynamosql.com/mcp/authentication
OAuth flows for human users and API clients on the DynamoSQL MCP server.
The MCP server uses a separate OAuth 2.0 authorization server at `https://mcp.dynamosql.com`. This is distinct from the REST API token endpoint at `https://api.dynamosql.com/v1/auth/token`.
Use the same API client **credentials**, but not the same bearer token. The REST API `accessToken` from `https://api.dynamosql.com/v1/auth/token` is not accepted on `POST /mcp`. The MCP server only accepts bearer tokens minted by `https://mcp.dynamosql.com/token`.
## Supported flows
| Flow | Caller type | Notes |
| ------------------------------------ | ---------------------------------------- | ----------------------------------------------------------------------------------------------- |
| `authorization_code` + PKCE (`S256`) | Human users in OAuth-capable MCP clients | Browser login through `auth.dynamosql.com`, explicit DynamoSQL consent, rotating refresh tokens |
| `refresh_token` | Human users in OAuth-capable MCP clients | Used to renew MCP access tokens after interactive sign-in |
| `client_credentials` | API clients and automation | Uses API client ID/secret from the DynamoSQL portal |
Interactive browser login supports pre-registered clients, Client ID Metadata Documents (CIMD), and Dynamic Client Registration (DCR) for public interactive clients. DynamoSQL always validates the final `redirect_uri` and shows a consent screen before issuing the MCP auth code.
## OAuth 2.0 discovery
MCP clients that support OAuth discovery can find the authorization and token endpoints automatically:
| Endpoint | URL |
| ----------------------------- | ------------------------------------------------------------------ |
| Protected resource metadata | `https://mcp.dynamosql.com/.well-known/oauth-protected-resource` |
| Authorization server metadata | `https://mcp.dynamosql.com/.well-known/oauth-authorization-server` |
The protected resource metadata response:
```json theme={null}
{
"resource": "https://mcp.dynamosql.com/mcp",
"authorization_servers": ["https://mcp.dynamosql.com"],
"bearer_methods_supported": ["header"],
"scopes_supported": ["query", "schemas:read"]
}
```
The authorization server metadata response:
```json theme={null}
{
"issuer": "https://mcp.dynamosql.com",
"authorization_endpoint": "https://mcp.dynamosql.com/authorize",
"token_endpoint": "https://mcp.dynamosql.com/token",
"grant_types_supported": [
"authorization_code",
"refresh_token",
"client_credentials"
],
"response_types_supported": ["code"],
"code_challenge_methods_supported": ["S256"],
"token_endpoint_auth_methods_supported": [
"none",
"client_secret_basic",
"client_secret_post"
],
"client_id_metadata_document_supported": true,
"registration_endpoint": "https://mcp.dynamosql.com/register",
"scopes_supported": ["query", "schemas:read"]
}
```
## Human-user browser login
OAuth-capable MCP clients use a standard OAuth authorization-code flow with PKCE:
1. The MCP client sends the user to `https://mcp.dynamosql.com/authorize`.
Clients can include `prompt=login` to force a fresh Hosted UI sign-in and
avoid silently reusing an existing browser session.
2. DynamoSQL resolves the client from pre-registration, CIMD, or DCR, then redirects the browser to the DynamoSQL Hosted UI at `auth.dynamosql.com`.
3. The user signs in with their DynamoSQL portal account.
4. DynamoSQL exchanges the Cognito code server-side and shows a first-party consent page.
5. After approval, DynamoSQL returns a one-time MCP authorization code to the MCP client.
6. The MCP client exchanges that code at `POST /token`.
7. DynamoSQL returns:
* an MCP `access_token`
* a rotating `refresh_token`
The resulting bearer token is an MCP token minted by DynamoSQL. It is not a raw Cognito access token or ID token.
### Example token response after interactive login
```json theme={null}
{
"access_token": "eyJhbGciOiJIUzI1NiIs...",
"token_type": "Bearer",
"expires_in": 600,
"refresh_token": "kQ7JYf...",
"scope": "query schemas:read"
}
```
## API client authentication
Use this flow for automation, service accounts, or MCP clients that only support static bearer tokens.
### Using HTTP Basic authentication (recommended)
```bash theme={null}
curl -X POST https://mcp.dynamosql.com/token \
-u "YOUR_CLIENT_ID:YOUR_CLIENT_SECRET" \
-d "grant_type=client_credentials&scope=query%20schemas:read"
```
### Using form body credentials
```bash theme={null}
curl -X POST https://mcp.dynamosql.com/token \
-d "grant_type=client_credentials" \
-d "client_id=YOUR_CLIENT_ID" \
-d "client_secret=YOUR_CLIENT_SECRET" \
-d "scope=query%20schemas:read"
```
### Token response
```json theme={null}
{
"access_token": "eyJhbGciOiJIUzI1NiIs...",
"token_type": "Bearer",
"expires_in": 600,
"scope": "query schemas:read"
}
```
| Field | Description |
| --------------- | ------------------------------------------------ |
| `access_token` | Bearer token for MCP requests |
| `token_type` | Always `Bearer` |
| `expires_in` | Token lifetime in seconds (600 = 10 minutes) |
| `scope` | Granted scopes (space-delimited) |
| `refresh_token` | Present only for interactive browser-login flows |
## Scopes
The `scope` parameter is optional. If omitted, the issued token includes all MCP-supported scopes allowed for that principal.
| Scope | Enables |
| -------------- | ---------------------------------------- |
| `query` | `run_sql` tool |
| `schemas:read` | `list_tables` and `describe_table` tools |
The MCP surface only recognizes `query` and `schemas:read`. Other portal or API-client scopes are ignored for MCP token issuance.
## Using the bearer token
Include the token in the `Authorization` header on `POST /mcp` requests:
```
Authorization: Bearer YOUR_ACCESS_TOKEN
```
When the token is missing or invalid, the server returns `401` with a `WWW-Authenticate` challenge header pointing to the protected resource metadata URL.
## Token errors
| HTTP status | `error` code | Description |
| ----------- | ------------------------- | ------------------------------------------------------------------------------------------------------------------------------ |
| 400 | `invalid_grant` | Authorization code or refresh token is invalid, expired, replayed, or does not match the client / redirect URI / PKCE verifier |
| 400 | `invalid_client_metadata` | CIMD document or DCR payload is invalid |
| 400 | `invalid_request` | Required OAuth parameters are missing or malformed |
| 400 | `invalid_scope` | Requested scope exceeds the principal's allowed MCP scopes |
| 400 | `unsupported_grant_type` | Grant type is not supported by DynamoSQL |
| 401 | `invalid_client` | Wrong API client credentials or inactive API client |
| 429 | `temporarily_unavailable` | Authentication service rate limited |
| 500 | `server_error` | Internal failure |
## Token lifecycle
* MCP access tokens expire after **10 minutes** (`expires_in: 600`)
* Interactive browser-login sessions receive a rotating refresh token with a **12-hour** default lifetime
* API-client `client_credentials` tokens do **not** receive refresh tokens
* MCP clients that support OAuth discovery and refresh-token handling can renew access tokens automatically
* For static bearer-token configurations, request a new token when the current one expires
## Dynamic Client Registration
Public interactive clients that do not use CIMD can register dynamically at `POST /register`.
Requirements:
* `client_name`
* `redirect_uris`
* `grant_types` including `authorization_code`
* `response_types` including `code`
* `token_endpoint_auth_method = none`
Redirect URI rules:
* `https://...` is always allowed
* loopback HTTP is allowed only for `http://127.0.0.1/...` and `http://localhost/...`
* custom URI schemes and non-loopback plain HTTP are rejected
DCR is public-client-only in this release. DynamoSQL does not issue client secrets for DCR clients and does not support RFC 7592 registration-management APIs yet.
# Overview
Source: https://docs.dynamosql.com/mcp/overview
Connect AI assistants to your DynamoDB data with the DynamoSQL MCP server.
## What is MCP?
The [Model Context Protocol](https://modelcontextprotocol.io) (MCP) is an open standard that lets AI assistants connect to external data sources and tools. DynamoSQL provides an MCP server that gives AI assistants direct access to your DynamoDB data through schema discovery and read-only SQL execution.
## What the DynamoSQL MCP server provides
The MCP server exposes three capabilities:
| Capability | Description |
| ------------- | ---------------------------------------------------------------------------------------------------------------------------------- |
| **Tools** | `list_tables`, `describe_table`, and `run_sql` -- let the AI discover schemas, inspect table structures, and execute read-only SQL |
| **Resources** | Bundled documentation covering supported SQL syntax and known limitations |
| **Prompts** | Guided workflows for exploring data and writing queries |
## When to use MCP vs. the REST API
| Use case | Recommended |
| -------------------------------------------------------------- | ----------------------------------------------- |
| AI assistant exploring and querying your data conversationally | **MCP server** |
| Application or script executing SQL programmatically | **REST API** ([Quickstart](/guides/quickstart)) |
Both use the same API client credentials created in the [DynamoSQL portal](https://portal.dynamosql.com/api-clients).
## Server URL
```
https://mcp.dynamosql.com/mcp
```
## Authentication
The MCP server supports two authentication modes:
| Caller type | Flow | Best for |
| ----------------------------------------- | ------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------- |
| Human user in an OAuth-capable MCP client | OAuth 2.0 `authorization_code` + PKCE (`S256`) + rotating `refresh_token` | Codex and other MCP clients that can open a browser for sign-in and support pre-registration, CIMD, or DCR |
| API client / automation | OAuth 2.0 `client_credentials` | Static MCP configurations, service accounts, and automation |
In both cases, the MCP bearer token is separate from the REST API bearer token. See [MCP Authentication](/mcp/authentication) for details.
Interactive browser login supports three client-registration paths: exact pre-registration for known clients, Client ID Metadata Documents (CIMD), and Dynamic Client Registration (DCR) for public interactive clients. Every interactive login shows a DynamoSQL consent screen before the auth code is issued.
## Scopes
Scopes control which tools are available to the AI assistant:
| Scope | Tools enabled |
| -------------- | ------------------------------- |
| `query` | `run_sql` |
| `schemas:read` | `list_tables`, `describe_table` |
Grant both scopes for full access. If a scope is not granted, the corresponding tools are not advertised to the AI assistant.
## Behavioral notes
* The MCP server is **read-only**. Only `SELECT` and `WITH` statements are accepted.
* Query results are capped at **1000 rows** (default 100).
* Table listings are capped at **200 tables**.
* MCP request handling is **stateless**, but interactive OAuth uses short-lived server-side state for consent, authorization codes, refresh-token rotation, CIMD caching, and DCR registration.
* Access tokens expire after **10 minutes**.
* Interactive browser login issues rotating refresh tokens with a **12-hour** default session lifetime.
## Next steps
* [MCP Quickstart](/mcp/quickstart) -- connect your AI assistant in 5 minutes
* [MCP Authentication](/mcp/authentication) -- OAuth flow details
* [Tools and Resources](/mcp/tools-and-resources) -- complete reference for all tools, resources, and prompts
# MCP Quickstart
Source: https://docs.dynamosql.com/mcp/quickstart
Connect your AI assistant to DynamoDB data in under 5 minutes.
## Prerequisites
* A DynamoSQL account with at least one [schema configured](/guides/iam-setup)
* One of the following:
* a DynamoSQL portal user account for browser-based sign-in
* API client credentials from the [DynamoSQL portal](https://portal.dynamosql.com/api-clients)
For full MCP access, the resulting token should include both `query` and `schemas:read`.
## Step 1 -- Choose an authentication mode
### Option A -- Browser login for human users
Use this when your MCP client supports remote OAuth discovery and can identify itself through pre-registration, a Client ID Metadata Document (CIMD), or Dynamic Client Registration (DCR).
* Recommended for Codex and other MCP clients that can open a browser for sign-in
* Uses your DynamoSQL portal user account
* Shows a DynamoSQL consent screen before the connection is approved
* Automatically handles access-token refresh with rotating refresh tokens
### Option B -- API client credentials
Use this for automation, service accounts, or MCP clients that only support static bearer tokens.
1. Open the [API clients](https://portal.dynamosql.com/api-clients) page in the DynamoSQL portal
2. Click **Create API client**
3. Enter a label (for example, `Claude Desktop`)
4. Select the **query** and **schemas:read** scopes
5. Click **Create API client**
6. Copy the **Client ID** and **Client Secret** -- the secret is shown only once
## Step 2 -- Configure your MCP client
### Recommended for OAuth-capable clients
#### Codex
If your Codex build supports remote MCP OAuth discovery:
1. Add the remote server URL `https://mcp.dynamosql.com/mcp`
2. Let Codex discover the OAuth metadata automatically
3. When Codex prompts for authentication, choose the browser sign-in flow
4. Sign in with your DynamoSQL portal account on `auth.dynamosql.com`
5. Review the DynamoSQL consent screen and approve the requested scopes
6. Return to Codex and complete the connection
If your Codex build only supports static bearer tokens, use the fallback configuration in the next section.
#### Claude Desktop / Claude Code / Cursor / other MCP clients with OAuth discovery
For any other MCP client that supports remote OAuth discovery:
1. Use `https://mcp.dynamosql.com/mcp` as the server URL
2. Let the client discover:
* `https://mcp.dynamosql.com/.well-known/oauth-protected-resource`
* `https://mcp.dynamosql.com/.well-known/oauth-authorization-server`
3. Complete browser sign-in when prompted
4. Review the DynamoSQL consent screen and approve the requested scopes
Interactive browser login requires one of these registration paths:
* exact pre-registration in DynamoSQL
* a valid HTTPS Client ID Metadata Document (CIMD)
* successful Dynamic Client Registration (DCR)
If your MCP client supports none of those, use the static bearer-token flow below.
### Fallback for static bearer-token clients
Choose the configuration for your AI assistant:
```bash Codex theme={null}
# Add the remote MCP server once
codex mcp add dynamosql --url https://mcp.dynamosql.com/mcp \
--bearer-token-env-var DYNAMOSQL_MCP_TOKEN
# Before launching Codex, export a fresh MCP bearer token
export DYNAMOSQL_MCP_TOKEN="YOUR_MCP_TOKEN"
```
```json Claude Desktop theme={null}
// Add to ~/Library/Application Support/Claude/claude_desktop_config.json (macOS)
// or %APPDATA%\\Claude\\claude_desktop_config.json (Windows)
{
"mcpServers": {
"dynamosql": {
"url": "https://mcp.dynamosql.com/mcp",
"headers": {
"Authorization": "Bearer YOUR_MCP_TOKEN"
}
}
}
}
```
```json Claude Code (.mcp.json) theme={null}
// Add to .mcp.json in your project root or ~/.claude/.mcp.json globally
{
"mcpServers": {
"dynamosql": {
"type": "url",
"url": "https://mcp.dynamosql.com/mcp",
"headers": {
"Authorization": "Bearer YOUR_MCP_TOKEN"
}
}
}
}
```
```json Cursor theme={null}
// Add to Cursor MCP settings (Settings > MCP Servers > Add)
{
"mcpServers": {
"dynamosql": {
"url": "https://mcp.dynamosql.com/mcp",
"headers": {
"Authorization": "Bearer YOUR_MCP_TOKEN"
}
}
}
}
```
### Obtaining a static bearer token
If you are using API client credentials, exchange them for an MCP bearer token:
```bash theme={null}
curl -s -X POST https://mcp.dynamosql.com/token \
-u "YOUR_CLIENT_ID:YOUR_CLIENT_SECRET" \
-d "grant_type=client_credentials&scope=query%20schemas:read"
```
The response contains your bearer token:
```json theme={null}
{
"access_token": "eyJhbGciOiJIUzI1NiIs...",
"token_type": "Bearer",
"expires_in": 600,
"scope": "query schemas:read"
}
```
Copy the `access_token` value and use it as `YOUR_MCP_TOKEN` in the configuration above.
Do not use the REST API `accessToken` from `https://api.dynamosql.com/v1/auth/token` as `YOUR_MCP_TOKEN`. The MCP server only accepts bearer tokens minted by `https://mcp.dynamosql.com/token`.
Static MCP bearer tokens expire after **10 minutes**. Clients using browser-based OAuth can refresh automatically. Clients using a static token must request a new one when the current token expires.
## Step 3 -- Verify the connection
Once configured, ask your AI assistant to explore your data:
> "List the tables in my DynamoDB schema"
The assistant should call `list_tables` and return your table names. Then try:
> "Describe the orders table and show me the first 10 rows"
## Troubleshooting
| Symptom | Likely cause | Fix |
| --------------------------------------- | --------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------- |
| `invalid_client` error on token request | Wrong client ID or secret | Verify credentials in the portal; rotate the secret if unsure |
| `invalid_scope` error on token request | Requested scope not granted to client | Check that the API client has `query` and `schemas:read` scopes in the portal |
| `invalid_grant` during browser login | Authorization code, redirect URI, PKCE verifier, or refresh token is invalid or expired | Restart the login flow and ensure the MCP client uses the exact registered redirect URI |
| Browser login does not start | MCP client is not approved for interactive login | Use API client credentials instead or contact DynamoSQL support |
| 401 on MCP requests | Expired or missing bearer token | Sign in again or obtain a fresh token from `/token` |
| No tools listed | Token missing required scopes | Ensure the token was issued with both `query` and `schemas:read` |
| "Table not found" error | Wrong schema or table name | Call `list_tables` first to discover available tables |
## Next steps
* [MCP Authentication](/mcp/authentication) -- full OAuth flow details and token lifecycle
* [Tools and Resources](/mcp/tools-and-resources) -- complete reference for all MCP tools, resources, and prompts
* [SQL Reference](/sql-reference/overview) -- supported SQL syntax
* [SQL Limitations](/sql-reference/limitations) -- unsupported features to be aware of
# Tools and Resources
Source: https://docs.dynamosql.com/mcp/tools-and-resources
Complete reference for DynamoSQL MCP tools, resources, and prompts.
## Tools
Tools are the primary way AI assistants interact with your DynamoDB data. Each tool is only available when the bearer token includes the required scope.
Bearer tokens may come from either:
* interactive browser login (`authorization_code` + PKCE + consent + refresh token)
* API client credentials (`client_credentials`)
### list\_tables
Lists tables available in one schema for the authenticated tenant.
**Required scope:** `schemas:read`
**Arguments:**
| Name | Type | Required | Description |
| ------------- | ------ | -------- | ---------------------------------------------------------------- |
| `schema_name` | string | No | Schema to list. Defaults to the tenant's default schema. |
| `refresh` | string | No | Metadata refresh mode: `if_stale` (default), `force`, or `skip`. |
**Response fields:**
| Field | Description |
| --------------------- | ------------------------------------------------------------------------------------------------------------- |
| `schema_name` | Resolved schema name |
| `tables` | Array of table objects with `name`, `qualified_name`, `physical_table_name`, `item_count`, and `refreshed_at` |
| `truncated` | `true` when more than 200 tables exist (results capped at 200) |
| `refreshed` | Whether metadata was refreshed on this call |
| `refreshed_at` | ISO timestamp of the last metadata refresh |
| `stale_after_seconds` | Seconds until metadata is considered stale |
**Example interaction:**
> **User:** "What tables do I have?"
>
> **Assistant calls:** `list_tables` with `{}`
>
> **Result:** 3 tables -- `orders`, `customers`, `products` in schema `east`
***
### describe\_table
Returns column, index, and type metadata for a single table.
**Required scope:** `schemas:read`
**Arguments:**
| Name | Type | Required | Description |
| ------------- | ------ | -------- | --------------------------------------------------------------------- |
| `table_name` | string | Yes | Table name to describe. |
| `schema_name` | string | No | Schema containing the table. Defaults to the tenant's default schema. |
| `refresh` | string | No | Metadata refresh mode: `if_stale` (default), `force`, or `skip`. |
**Response fields:**
| Field | Description |
| --------------------- | ---------------------------------------------------------------------------------------------------------- |
| `schema_name` | Resolved schema name |
| `table_name` | Resolved table name |
| `qualified_name` | `schema_name.table_name` |
| `physical_table_name` | Physical DynamoDB table name used for execution |
| `item_count` | Approximate row count |
| `refreshed` | Whether metadata was refreshed on this call |
| `refreshed_at` | ISO timestamp of the last metadata refresh for this table |
| `stale_after_seconds` | Seconds until metadata is considered stale |
| `columns` | Array of column objects with `name`, `type`, and `nullable` |
| `indexes` | Array of index objects with `name`, `type`, `hashKey`, `hashKeyType`, and optional `sortKey`/`sortKeyType` |
| `attribute_types` | DynamoDB attribute type mappings |
**Example interaction:**
> **User:** "What columns does the orders table have?"
>
> **Assistant calls:** `describe_table` with `{ "table_name": "orders" }`
>
> **Result:** Columns `order_id` (S, primary key), `customer_id` (S), `total` (N), `status` (S), `order_date` (S)
***
### run\_sql
Executes a read-only SQL query against the authenticated tenant's data.
**Required scope:** `query`
**Arguments:**
| Name | Type | Required | Description |
| ---------- | ------- | -------- | --------------------------------------------------------- |
| `sql` | string | Yes | SQL query to execute. Must begin with `SELECT` or `WITH`. |
| `max_rows` | integer | No | Maximum rows to return (1--1000, default 100). |
**Response fields:**
| Field | Description |
| ------------- | ----------------------------------------------------- |
| `columns` | Array of column names |
| `rows` | Array of row objects |
| `firstRowIdx` | Index of the first row in the result set |
| `resumeIdx` | Next row index for pagination (absent when exhausted) |
| `planTime` | Parse and plan time in milliseconds |
| `execTime` | Total execution time in milliseconds |
**Error cases:**
| Condition | Behavior |
| ------------------------------------------ | -------------------------------------------------------------------- |
| SQL does not start with `SELECT` or `WITH` | Returns tool error: "Only read-only SELECT statements are supported" |
| SQL syntax error | Returns tool error with parse error details |
| Table not found | Returns tool error with table resolution failure |
**Example interaction:**
> **User:** "Show me the top 5 customers by total spend"
>
> **Assistant calls:** `run_sql` with:
>
> ```json theme={null}
> {
> "sql": "SELECT customer_id, SUM(total) AS total_spend FROM east.orders GROUP BY customer_id ORDER BY total_spend DESC LIMIT 5",
> "max_rows": 5
> }
> ```
>
> **Result:** 5 rows with `customer_id` and `total_spend` columns
***
## Resources
Resources provide static documentation that AI assistants can read for context about DynamoSQL's SQL capabilities.
### docs\://sql-overview
High-level summary of supported SQL features including SELECT, JOINs, CTEs, aggregations, subqueries, set operations, and functions. Includes an example query.
### docs\://sql-limitations
List of unsupported features including write operations, DDL, window functions, recursive CTEs, CAST/CONVERT, and behavioral notes about pagination and string escaping.
AI assistants can read these resources to understand what SQL syntax is available before writing queries, reducing errors from unsupported features.
***
## Prompts
Prompts are guided workflows that help AI assistants follow best practices when exploring data or writing queries.
### explore-data
Guides the AI through schema discovery before querying.
**Arguments:**
| Name | Type | Required | Description |
| ------------- | ------ | -------- | ------------------------------------------------------- |
| `goal` | string | No | What the user wants to explore or learn about the data. |
| `schema_name` | string | No | Preferred schema to start with. |
**Behavior:** Instructs the AI to call `list_tables` first, then `describe_table` on relevant tables, and keep queries bounded with `LIMIT`.
### write-query
Helps the AI write a read-only query that respects DynamoSQL limitations.
**Arguments:**
| Name | Type | Required | Description |
| ------------- | ------ | -------- | -------------------------------------------------- |
| `request` | string | Yes | Natural language description of the desired query. |
| `schema_name` | string | No | Schema to target. |
| `table_name` | string | No | Preferred table. |
**Behavior:** Instructs the AI to use only `SELECT` syntax, avoid unsupported features (INSERT, UPDATE, DELETE, DDL, window functions, CAST), and reference the SQL limitations documentation when needed.
***
## Metadata refresh modes
The `refresh` parameter on `list_tables` and `describe_table` controls how table metadata is loaded:
| Mode | Behavior |
| ---------- | ---------------------------------------------------------------------------------- |
| `if_stale` | Refreshes metadata only if older than the configured staleness threshold (default) |
| `force` | Always refreshes metadata from DynamoDB, even if recently cached |
| `skip` | Uses cached metadata without checking freshness |
Use `force` when you know table structure has recently changed. Use `skip` to avoid refresh latency when metadata accuracy is not critical.
## Interactive registration notes
For browser-based login, the MCP server can recognize interactive clients through:
* exact pre-registration in DynamoSQL
* Client ID Metadata Documents (CIMD)
* Dynamic Client Registration (DCR) for public interactive clients
CIMD notes:
* `client_id` must be an HTTPS URL with a path
* DynamoSQL fetches the metadata document server-side
* the metadata document must allow `authorization_code`, `code`, and `token_endpoint_auth_method = none`
DCR notes:
* `POST /register` returns an opaque `client_id`
* DCR clients are public clients only in this release
* loopback HTTP redirect URIs are allowed only for `127.0.0.1` and `localhost`
# Expressions and Operators
Source: https://docs.dynamosql.com/sql-reference/expressions-and-operators
Arithmetic, bitwise, string, CASE, and conditional expressions.
## Arithmetic
Standard arithmetic with conventional precedence (`*`, `/`, `%` before `+`, `-`):
```sql theme={null}
SELECT price * quantity AS line_total FROM myschema.order_items
SELECT price * quantity - discount AS net_total FROM myschema.order_items
SELECT total % 100 AS cents_portion FROM myschema.orders
```
Unary plus and minus:
```sql theme={null}
SELECT -price AS negative_price FROM myschema.products
SELECT +total AS total FROM myschema.orders -- unary plus is a no-op
```
## Bitwise Operators
```sql theme={null}
SELECT flags & 3 AS lower_two_bits FROM myschema.permissions -- AND
SELECT flags | 4 AS with_flag_set FROM myschema.permissions -- OR
SELECT flags ^ 1 AS toggled FROM myschema.permissions -- XOR
SELECT flags << 2 AS shifted_left FROM myschema.permissions -- left shift
SELECT flags >> 1 AS shifted_right FROM myschema.permissions -- right shift
SELECT ~flags AS inverted FROM myschema.permissions -- bitwise NOT
```
## String Concatenation
Use `||` to concatenate strings:
```sql theme={null}
SELECT first_name || ' ' || last_name AS full_name FROM myschema.users
SELECT 'Order #' || CAST(id AS VARCHAR) AS label FROM myschema.orders
```
## CASE
**Searched form** — each `WHEN` is a full condition:
```sql theme={null}
SELECT
id,
CASE
WHEN total >= 1000 THEN 'large'
WHEN total >= 100 THEN 'medium'
ELSE 'small'
END AS order_size
FROM myschema.orders
```
**Simple form** — compares a single expression against values:
```sql theme={null}
SELECT
id,
CASE status
WHEN 'pending' THEN 'Awaiting payment'
WHEN 'shipped' THEN 'On the way'
WHEN 'delivered' THEN 'Complete'
ELSE 'Unknown'
END AS status_label
FROM myschema.orders
```
## COALESCE
Returns the first non-NULL argument:
```sql theme={null}
SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM myschema.users
```
## NULLIF
Returns NULL if both arguments are equal; otherwise returns the first argument:
```sql theme={null}
SELECT NULLIF(discount, 0) AS discount FROM myschema.orders
```
## ISNULL
Returns `true` if the argument is NULL, `false` otherwise. Equivalent to `IS NULL` in a predicate context:
```sql theme={null}
SELECT id, ISNULL(shipped_at) AS not_yet_shipped FROM myschema.orders
```
## Expression Aliases in ORDER BY
Aliases defined in `SELECT` can be used in `ORDER BY`:
```sql theme={null}
SELECT id, price * quantity AS line_total
FROM myschema.order_items
ORDER BY line_total
```
## Literals
| Type | Example |
| ------- | ------------------------------------------- |
| Integer | `42`, `-7` |
| Decimal | `3.14`, `-0.5` |
| String | `'hello'`, `'it''s'` (doubled single quote) |
| Boolean | `TRUE`, `FALSE` |
| NULL | `NULL` |
## Not Supported
* `CAST` / `CONVERT`
* `DATE`, `TIME`, `TIMESTAMP` literals
* Scientific notation literals (e.g., `1e3`)
* Hex string literals (`X'0A'`)
* Bit string literals (`B'0101'`)
# FROM and JOINs
Source: https://docs.dynamosql.com/sql-reference/from-and-joins
Table references, aliases, subqueries in FROM, and all join types.
## Single Table with Alias
```sql theme={null}
SELECT o.id, o.total FROM myschema.orders AS o
```
Quoted table names and aliases are supported when DynamoDB identifiers include special characters or exact-case names:
```sql theme={null}
SELECT "o"."order-id", "o"."Safety.Warning"
FROM east."prod-orders" AS "o"
```
## Multiple Tables (Implicit Cross Join)
Comma-separated tables produce a cartesian product. Use a `WHERE` predicate to filter to matching rows:
```sql theme={null}
SELECT o.id, c.name
FROM myschema.orders AS o, myschema.customers AS c
WHERE o.customer_id = c.id
```
## Derived Table (Subquery in FROM)
A subquery in the `FROM` clause must have an alias:
```sql theme={null}
SELECT sub.status, COUNT(*) AS cnt
FROM (
SELECT status FROM myschema.orders WHERE total > 100
) AS sub
GROUP BY sub.status
```
## CTE References
Tables defined in a `WITH` clause can be referenced in `FROM` like any other table:
```sql theme={null}
WITH big_orders AS (
SELECT * FROM myschema.orders WHERE total > 500
)
SELECT customer_id, COUNT(*) FROM big_orders GROUP BY customer_id
```
See [Subqueries and CTEs](/sql-reference/subqueries-and-ctes) for full CTE syntax.
## VALUES Constructor
Use `VALUES` to produce an inline table:
```sql theme={null}
SELECT v.status_code, v.label
FROM (VALUES ('pending', 'Awaiting'), ('shipped', 'On the way')) AS v(status_code, label)
```
## INNER JOIN
Join on an equality or arbitrary condition:
```sql theme={null}
-- ON syntax
SELECT o.id, c.name
FROM myschema.orders AS o
INNER JOIN myschema.customers AS c ON o.customer_id = c.id
-- USING syntax (columns must share the same name)
SELECT o.id, c.name
FROM myschema.orders AS o
INNER JOIN myschema.customers AS c USING (customer_id)
-- USING also accepts quoted identifiers
SELECT l.value, r.value
FROM left_data AS l
INNER JOIN right_data AS r USING ("order-id")
```
## Outer Joins
```sql theme={null}
-- LEFT OUTER JOIN: all rows from the left table; NULL for unmatched right columns
SELECT c.name, o.id
FROM myschema.customers AS c
LEFT OUTER JOIN myschema.orders AS o ON c.id = o.customer_id
-- RIGHT OUTER JOIN
SELECT c.name, o.id
FROM myschema.orders AS o
RIGHT OUTER JOIN myschema.customers AS c ON o.customer_id = c.id
-- FULL OUTER JOIN
SELECT c.name, o.id
FROM myschema.customers AS c
FULL OUTER JOIN myschema.orders AS o ON c.id = o.customer_id
```
## Join Chains (3+ Tables)
```sql theme={null}
SELECT o.id, c.name, p.title
FROM myschema.orders AS o
INNER JOIN myschema.customers AS c ON o.customer_id = c.id
INNER JOIN myschema.products AS p ON o.product_id = p.id
```
## Non-Equi Join Predicates
Join conditions are not limited to equality:
```sql theme={null}
SELECT e.name, s.tier
FROM myschema.employees AS e
INNER JOIN myschema.salary_bands AS s ON e.salary >= s.min_salary AND e.salary < s.max_salary
```
## Join with Derived Table
```sql theme={null}
SELECT c.name, recent.total
FROM myschema.customers AS c
INNER JOIN (
SELECT customer_id, MAX(total) AS total
FROM myschema.orders
GROUP BY customer_id
) AS recent ON c.id = recent.customer_id
```
## Not Supported
* `CROSS JOIN` keyword syntax (use comma syntax instead)
* `NATURAL JOIN`
* `LATERAL` table references
# Functions
Source: https://docs.dynamosql.com/sql-reference/functions
Built-in numeric, string, conditional, and aggregate functions.
## Numeric Functions
| Function | Description |
| ---------------- | ------------------------------ |
| `ABS(n)` | Absolute value |
| `CEILING(n)` | Smallest integer >= n |
| `FLOOR(n)` | Largest integer \<= n |
| `ROUND(n, d)` | Round n to d decimal places |
| `TRUNCATE(n, d)` | Truncate n to d decimal places |
```sql theme={null}
SELECT ABS(-42) -- 42
SELECT CEILING(4.1) -- 5
SELECT FLOOR(4.9) -- 4
SELECT ROUND(3.14159, 2) -- 3.14
SELECT TRUNCATE(3.99, 1) -- 3.9
```
## String Functions
| Function | Description |
| ----------------------------- | -------------------------------------------------- |
| `ASCII(s)` | ASCII code of the first character |
| `CHAR(n)` | Character for ASCII code n |
| `CONCAT(s1, s2, ...)` | Concatenate strings (NULLs treated as empty) |
| `CONCAT_WS(sep, s1, s2, ...)` | Concatenate with separator, skipping NULLs |
| `INSTR(s, sub)` | Position of first occurrence of sub in s (1-based) |
| `LEFT(s, n)` | First n characters |
| `LENGTH(s)` | Length in characters |
| `LOWER(s)` | Convert to lowercase |
| `LPAD(s, n, pad)` | Left-pad s to length n with pad string |
| `LTRIM(s)` | Remove leading whitespace |
| `RIGHT(s, n)` | Last n characters |
| `RPAD(s, n, pad)` | Right-pad s to length n with pad string |
| `RTRIM(s)` | Remove trailing whitespace |
| `SUBSTRING(s, pos, len)` | Extract substring (1-based position) |
| `TRIM(s)` | Remove leading and trailing whitespace |
| `UPPER(s)` | Convert to uppercase |
```sql theme={null}
SELECT UPPER(name) AS name_upper FROM myschema.users
SELECT CONCAT_WS(', ', last_name, first_name) AS full_name FROM myschema.contacts
SELECT SUBSTRING(sku, 1, 3) AS sku_prefix FROM myschema.products
SELECT LPAD(order_number, 8, '0') AS padded FROM myschema.orders
SELECT LENGTH(description) AS desc_length FROM myschema.products
```
## Conditional Functions
`IF(condition, true_value, false_value)` — returns `true_value` when condition is truthy, otherwise `false_value`:
```sql theme={null}
SELECT
id,
IF(total > 500, 'high_value', 'standard') AS tier
FROM myschema.orders
```
## UUID
Generate a random UUID v4:
```sql theme={null}
SELECT UUID() AS request_id
```
## Date/Time Functions
| Function | Description |
| --------------------- | --------------------------------- |
| `CURRENT_DATE()` | Current date as `YYYY-MM-DD` |
| `CURRENT_TIMESTAMP()` | Current date and time as a string |
```sql theme={null}
SELECT CURRENT_DATE() -- e.g. '2026-03-11'
SELECT CURRENT_TIMESTAMP() -- e.g. 'Tue Mar 11 2026 17:00:00 GMT+0000'
```
These return strings, not native date objects. To compare against stored date strings, use standard string comparison operators.
## Aggregate Functions
| Function | Description |
| ------------- | ----------------------------- |
| `COUNT(*)` | Count all rows |
| `COUNT(col)` | Count non-NULL values |
| `SUM(col)` | Sum of non-NULL values |
| `AVG(col)` | Average of non-NULL values |
| `MIN(col)` | Minimum value |
| `MAX(col)` | Maximum value |
| `STDDEV(col)` | Population standard deviation |
See [Grouping and Aggregation](/sql-reference/grouping-and-aggregation) for usage with `GROUP BY` and `HAVING`.
## Not Supported
* `EXTRACT`, `DATE_ADD`, `DATE_DIFF`, `DATEDIFF`, `NOW()`
* `CONVERT` / `CAST`
# Grouping and Aggregation
Source: https://docs.dynamosql.com/sql-reference/grouping-and-aggregation
GROUP BY, HAVING, and aggregate functions.
## GROUP BY
Group rows by one or more columns and apply aggregate functions to each group:
```sql theme={null}
SELECT status, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY status
```
Multiple grouping columns:
```sql theme={null}
SELECT customer_id, status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY customer_id, status
```
Positional ordinals are also supported. `GROUP BY 1, 2` groups by the first and second
select-list expressions:
```sql theme={null}
SELECT customer_id, status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY 1, 2
```
Grouping expressions are also supported directly:
```sql theme={null}
SELECT region || ':' || status AS region_status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY region || ':' || status
```
## HAVING
Filter groups after aggregation. You can use both aggregate and non-aggregate predicates:
```sql theme={null}
-- Keep only customers with more than 5 orders
SELECT customer_id, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY customer_id
HAVING COUNT(*) > 5
-- Combine aggregate and non-aggregate conditions
SELECT customer_id, SUM(total) AS total_spent
FROM myschema.orders
GROUP BY customer_id
HAVING SUM(total) > 1000 AND customer_id IS NOT NULL
```
## Aggregate Functions
| Function | Description |
| ------------- | ------------------------------ |
| `COUNT(*)` | Count all rows in the group |
| `COUNT(col)` | Count non-NULL values in `col` |
| `SUM(col)` | Sum of non-NULL values |
| `AVG(col)` | Average of non-NULL values |
| `MIN(col)` | Minimum value |
| `MAX(col)` | Maximum value |
| `STDDEV(col)` | Population standard deviation |
```sql theme={null}
SELECT
category,
COUNT(*) AS total_products,
COUNT(description) AS with_description,
AVG(price) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price,
STDDEV(price) AS price_stddev
FROM myschema.products
GROUP BY category
```
## DISTINCT Aggregates
Deduplicate values before aggregating:
```sql theme={null}
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM myschema.orders
SELECT SUM(DISTINCT total) AS sum_unique_totals
FROM myschema.orders
```
## Using Aliases in ORDER BY
Aliases defined in the `SELECT` list are available in `ORDER BY`. They cannot be used in `GROUP BY` or `HAVING` — repeat the expression there instead.
```sql theme={null}
SELECT customer_id, SUM(total) AS lifetime_value
FROM myschema.orders
GROUP BY customer_id
HAVING SUM(total) > 500
ORDER BY lifetime_value -- alias is valid here
```
## GROUPING SETS
`GROUPING SETS` lets you compute aggregates for multiple grouping combinations in a single query. Columns not part of a given grouping set are returned as `NULL`:
```sql theme={null}
SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY GROUPING SETS((region), (status))
```
## ROLLUP
`ROLLUP` creates subtotals that roll up from the most detailed level to a grand total. `ROLLUP(a, b)` is equivalent to `GROUPING SETS((a, b), (a), ())`:
```sql theme={null}
SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY ROLLUP(region, status)
```
## CUBE
`CUBE` generates subtotals for all possible combinations of the grouping columns. `CUBE(a, b)` is equivalent to `GROUPING SETS((a, b), (a), (b), ())`:
```sql theme={null}
SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY CUBE(region, status)
```
## Not Supported
* Window functions (`ROW_NUMBER`, `RANK`, `SUM OVER`, etc.)
# Limitations
Source: https://docs.dynamosql.com/sql-reference/limitations
Current hard limits and unsupported SQL features.
## Not Implemented
These features are not currently supported. Some are planned for future releases.
**Write operations:**
* `INSERT`, `UPDATE`, `DELETE`, `MERGE` — the engine is read-only (`SELECT` only)
**DDL:**
* `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`
* `CREATE VIEW`, `CREATE SCHEMA`, `CREATE INDEX`
**Transactions:**
* `BEGIN`, `COMMIT`, `ROLLBACK`
**Window functions:**
* `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`
* `SUM() OVER (...)`, `AVG() OVER (...)`, and other analytic aggregates
**Set operations:**
* Parenthesized set operations (e.g., `(SELECT ... UNION SELECT ...) UNION SELECT ...`)
**CTEs:**
* `WITH RECURSIVE` (recursive CTEs)
**Sorting:**
* `NULLS FIRST` / `NULLS LAST` — `NULL` values sort before non-nulls ascending and after non-nulls descending; the order cannot be overridden
**Type operations:**
* `CAST` / `CONVERT`
**Date/time:**
* `EXTRACT`, `DATE_ADD`, `DATE_DIFF`, `DATEDIFF`
* `DATE`, `TIME`, `TIMESTAMP` literals
* `NOW()` (use `CURRENT_DATE()` or `CURRENT_TIMESTAMP()` instead — see [Functions](/sql-reference/functions))
**Join syntax:**
* `LATERAL` table references
* `NATURAL JOIN`
* `CROSS JOIN` keyword syntax (use comma-separated table list instead)
**Predicates:**
* `IS DISTINCT FROM` / `IS NOT DISTINCT FROM`
* `ANY` / `ALL` quantifiers
**Identifiers and literals:**
* Scientific notation literals (e.g., `1e3`, `2.5e-4`)
* Hex string literals (`X'0A'`)
* Bit string literals (`B'0101'`)
***
## Behavioral Notes
**Pagination is stateless.** The engine re-executes the full query on each page request and skips rows using an offset. If DynamoDB data changes between page requests, rows may be skipped or duplicated at page boundaries. This is appropriate for analytics and reporting, but not for transactional reads where consistency across pages is required. See [Pagination](/guides/pagination).
**String escaping.** Doubled single quote (`''`) is the supported escape for a literal single quote inside a string literal. Other escape sequences (e.g., backslash-based) may not be handled correctly.
**Quoted identifiers.** ANSI double quotes delimit identifiers. Use double quotes for table names, column names, aliases, or CTE names that include punctuation, spaces, reserved words, leading digits, or exact DynamoDB casing. Single quotes remain string literals.
**Keyword case sensitivity.** SQL keywords (`SELECT`, `FROM`, `WHERE`, etc.) are treated case-insensitively in most contexts. Full case-insensitive coverage of all keywords is partial — if you encounter a parse error on a valid keyword, try uppercasing it.
# SQL Reference Overview
Source: https://docs.dynamosql.com/sql-reference/overview
What SQL DynamoSQL supports today.
DynamoSQL implements a substantial subset of ANSI SQL targeting DynamoDB and CSV backends. The engine is **read-only** — it supports `SELECT` queries only. There is no `INSERT`, `UPDATE`, `DELETE`, or DDL.
## Supported Feature Categories
| Category | Reference |
| ---------------------------------------------------------------------------- | --------------------------------------------------------------------- |
| SELECT, double-quoted identifiers, column aliases, DISTINCT, ORDER BY, LIMIT | [SELECT](/sql-reference/select) |
| Single tables, JOINs, derived tables, CTEs as sources | [FROM and JOINs](/sql-reference/from-and-joins) |
| Comparisons, BETWEEN, IN, EXISTS, LIKE, IS NULL | [WHERE Predicates](/sql-reference/where-predicates) |
| GROUP BY, HAVING, GROUPING SETS, ROLLUP, CUBE, aggregates | [Grouping and Aggregation](/sql-reference/grouping-and-aggregation) |
| WITH clauses, scalar subqueries, correlated subqueries | [Subqueries and CTEs](/sql-reference/subqueries-and-ctes) |
| UNION, UNION ALL, INTERSECT, EXCEPT | [Set Operations](/sql-reference/set-operations) |
| Arithmetic, bitwise, string, CASE, COALESCE | [Expressions and Operators](/sql-reference/expressions-and-operators) |
| ABS, CONCAT, SUBSTRING, IF, UUID, aggregates | [Functions](/sql-reference/functions) |
Unsupported features — including window functions, INSERT/UPDATE/DELETE, recursive CTEs, and date/time functions — are listed on the [Limitations](/sql-reference/limitations) page.
## Quick Example
```sql theme={null}
WITH recent_orders AS (
SELECT
o.customer_id,
o.total,
o.status,
c.name AS customer_name
FROM myschema.orders AS o
INNER JOIN myschema.customers AS c ON o.customer_id = c.id
WHERE o.status = 'shipped'
)
SELECT
customer_name,
COUNT(*) AS order_count,
SUM(total) AS total_spent
FROM recent_orders
GROUP BY customer_name
HAVING SUM(total) > 500
ORDER BY total_spent
LIMIT 20
```
This query demonstrates a CTE, an INNER JOIN, WHERE filtering, GROUP BY with an aggregate, HAVING, ORDER BY, and LIMIT — all supported today.
# SELECT
Source: https://docs.dynamosql.com/sql-reference/select
Column selection, aliases, DISTINCT, ORDER BY, and LIMIT.
## Basic SELECT
Select one or more expressions from a table:
```sql theme={null}
SELECT id, name, email FROM myschema.users
```
## Column Aliases
Use `AS` to name an output column, or omit `AS` and place the alias directly after the expression:
```sql theme={null}
SELECT id, total * 1.1 AS total_with_tax FROM myschema.orders
SELECT id, total * 1.1 total_with_tax FROM myschema.orders -- bare alias
```
Aliases defined in the `SELECT` list can be referenced in `ORDER BY`.
## Delimited Identifiers
Use ANSI double quotes when a table name, column name, or alias includes punctuation, spaces, reserved words, leading digits, or exact-case DynamoDB names:
```sql theme={null}
SELECT
"order-id",
"Safety.Warning",
total AS "Total Amount"
FROM east."prod-orders"
ORDER BY "order-id"
```
Double quotes delimit identifiers only. Single quotes still create string literals:
```sql theme={null}
SELECT 'order-id' AS label, "order-id" FROM east."prod-orders"
```
## SELECT \*
Expand all columns from all referenced tables:
```sql theme={null}
SELECT * FROM myschema.products
```
Expand all columns from a specific table when multiple tables are in scope:
```sql theme={null}
SELECT o.*, c.name FROM myschema.orders AS o
INNER JOIN myschema.customers AS c ON o.customer_id = c.id
```
## SELECT DISTINCT
Deduplicate rows in the result set:
```sql theme={null}
SELECT DISTINCT status FROM myschema.orders
```
## ORDER BY
Sort results by one or more columns. Each sort key can be `ASC` (default) or `DESC`. Multiple sort keys are separated by commas:
```sql theme={null}
SELECT id, name FROM myschema.products ORDER BY name
SELECT id, name FROM myschema.products ORDER BY name DESC
SELECT id, created_at, total FROM myschema.orders ORDER BY created_at ASC, total DESC
```
Aliases from the `SELECT` list are usable in `ORDER BY`:
```sql theme={null}
SELECT customer_id, SUM(total) AS lifetime_value
FROM myschema.orders
GROUP BY customer_id
ORDER BY lifetime_value DESC
```
**NULL ordering:** `NULL` values sort before non-null values when ascending, and after non-null values when descending. `NULLS FIRST` and `NULLS LAST` are not supported.
## LIMIT
Cap the number of rows returned:
```sql theme={null}
SELECT * FROM myschema.events LIMIT 100
```
ANSI `FETCH FIRST n ROWS ONLY` is equivalent:
```sql theme={null}
SELECT * FROM myschema.events FETCH FIRST 100 ROWS ONLY
```
`OFFSET` skips a fixed number of rows before returning results:
```sql theme={null}
SELECT * FROM myschema.products LIMIT 10 OFFSET 20
```
For paginating through a full result set, prefer the `maxRows` / `resumeIdx` API options over SQL `OFFSET`. See [Pagination](/guides/pagination).
# Set Operations
Source: https://docs.dynamosql.com/sql-reference/set-operations
UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT, and INTERSECT ALL.
Set operations combine the results of two or more `SELECT` statements. Both queries must produce the same number of columns, and corresponding columns must have compatible types.
## UNION
Combines two result sets and removes duplicate rows:
```sql theme={null}
SELECT id, name FROM myschema.active_customers
UNION
SELECT id, name FROM myschema.archived_customers
```
Because deduplication requires comparing every row, `UNION` is slower than `UNION ALL`. Use it only when you actually need distinct results.
## UNION ALL
Combines two result sets and keeps all rows, including duplicates:
```sql theme={null}
SELECT customer_id, total FROM myschema.orders_2024
UNION ALL
SELECT customer_id, total FROM myschema.orders_2025
```
`UNION ALL` is faster than `UNION` because no deduplication step is needed. Prefer it when duplicates are acceptable or when you know the two result sets are already disjoint.
## EXCEPT
Returns rows from the left query that do not appear in the right query, with duplicates removed:
```sql theme={null}
SELECT id FROM myschema.all_customers
EXCEPT
SELECT id FROM myschema.opted_out_customers
```
## EXCEPT ALL
Like `EXCEPT`, but preserves multiplicity. If a row appears *m* times on the left and *n* times on the right, the result contains `max(m - n, 0)` copies of that row:
```sql theme={null}
SELECT product_id FROM myschema.cart_items
EXCEPT ALL
SELECT product_id FROM myschema.backordered_items
```
## INTERSECT
Returns rows that appear in both the left and right query, with duplicates removed:
```sql theme={null}
SELECT id FROM myschema.premium_customers
INTERSECT
SELECT id FROM myschema.active_customers
```
## INTERSECT ALL
Like `INTERSECT`, but preserves multiplicity. If a row appears *m* times on the left and *n* times on the right, the result contains `min(m, n)` copies of that row:
```sql theme={null}
SELECT product_id FROM myschema.wishlist_items
INTERSECT ALL
SELECT product_id FROM myschema.in_stock_items
```
## ORDER BY after set operations
`ORDER BY` is supported after any set operation. However, you must reference columns by **position** (1-based) or by **alias** — not by the original column name from the base tables:
```sql theme={null}
-- Valid: column position
SELECT id, name FROM myschema.active_customers
UNION
SELECT id, name FROM myschema.archived_customers
ORDER BY 1, 2
-- Valid: alias defined in the SELECT list
SELECT id, name AS customer_name FROM myschema.active_customers
UNION
SELECT id, name AS customer_name FROM myschema.archived_customers
ORDER BY customer_name
```
`LIMIT` and `OFFSET` / `FETCH` may follow `ORDER BY` as usual:
```sql theme={null}
SELECT id FROM myschema.new_orders
UNION ALL
SELECT id FROM myschema.backfill_orders
ORDER BY 1 DESC
LIMIT 100
```
## Chaining Multiple Set Operations
```sql theme={null}
SELECT id, status FROM myschema.orders WHERE region = 'us-east'
UNION ALL
SELECT id, status FROM myschema.orders WHERE region = 'us-west'
UNION ALL
SELECT id, status FROM myschema.orders WHERE region = 'eu-central'
```
Set operations associate left-to-right. `UNION ALL` followed by `EXCEPT` first unions the two queries, then removes rows from the third:
```sql theme={null}
SELECT id FROM myschema.base_set
UNION
SELECT id FROM myschema.additions
EXCEPT
SELECT id FROM myschema.exclusions
```
## Not Supported
* Parenthesized set operations (e.g., `(SELECT ... UNION SELECT ...) UNION SELECT ...`)
# Subqueries and CTEs
Source: https://docs.dynamosql.com/sql-reference/subqueries-and-ctes
WITH clauses, scalar subqueries, correlated subqueries, and derived tables.
## WITH (CTE)
Common Table Expressions give a name to a subquery that can be referenced multiple times in the main query.
**CTE using SELECT:**
```sql theme={null}
WITH top_customers AS (
SELECT customer_id, SUM(total) AS lifetime_value
FROM myschema.orders
GROUP BY customer_id
HAVING SUM(total) > 1000
)
SELECT c.name, tc.lifetime_value
FROM myschema.customers AS c
INNER JOIN top_customers AS tc ON c.id = tc.customer_id
ORDER BY tc.lifetime_value
```
**CTE using VALUES:**
```sql theme={null}
WITH status_labels AS (
SELECT * FROM (VALUES
('pending', 'Awaiting payment'),
('shipped', 'On the way'),
('delivered', 'Complete')
) AS t(code, label)
)
SELECT o.id, sl.label
FROM myschema.orders AS o
INNER JOIN status_labels AS sl ON o.status = sl.code
```
Quoted CTE names and quoted CTE column lists are also supported:
```sql theme={null}
WITH "sales-data" ("order-id", "Safety.Warning") AS VALUES (
(1, 'green'),
(2, 'yellow')
)
SELECT "order-id", "Safety.Warning"
FROM "sales-data"
ORDER BY "order-id"
```
## Multiple CTEs
Define multiple CTEs in a single `WITH` block. Later CTEs may reference earlier ones:
```sql theme={null}
WITH
active_orders AS (
SELECT * FROM myschema.orders WHERE status != 'cancelled'
),
order_totals AS (
SELECT customer_id, COUNT(*) AS cnt, SUM(total) AS total
FROM active_orders
GROUP BY customer_id
)
SELECT c.name, ot.cnt, ot.total
FROM myschema.customers AS c
INNER JOIN order_totals AS ot ON c.id = ot.customer_id
WHERE ot.cnt >= 3
```
## Scalar Subquery in WHERE
A subquery that returns a single value can be used in a comparison:
```sql theme={null}
SELECT * FROM myschema.orders
WHERE total > (SELECT AVG(total) FROM myschema.orders)
```
## Correlated Subquery in WHERE
The subquery references a column from the outer query:
```sql theme={null}
-- Orders where the total exceeds the customer's average order total
SELECT o.id, o.customer_id, o.total
FROM myschema.orders AS o
WHERE o.total > (
SELECT AVG(o2.total)
FROM myschema.orders AS o2
WHERE o2.customer_id = o.customer_id
)
```
## Subquery in FROM (Derived Table)
```sql theme={null}
SELECT sub.customer_id, sub.order_count
FROM (
SELECT customer_id, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY customer_id
) AS sub
WHERE sub.order_count > 10
```
## Scalar Subquery in SELECT List
A subquery that returns a single value can appear as a column expression:
```sql theme={null}
SELECT
o.id,
o.total,
(SELECT AVG(total) FROM myschema.orders) AS avg_total
FROM myschema.orders AS o
```
Correlated scalar subqueries in the SELECT list are also supported:
```sql theme={null}
SELECT
c.name,
(SELECT COUNT(*) FROM myschema.orders AS o WHERE o.customer_id = c.id) AS order_count
FROM myschema.customers AS c
```
## Subquery in HAVING
Scalar subqueries can be used in HAVING predicates:
```sql theme={null}
SELECT customer_id, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY customer_id
HAVING COUNT(*) > (SELECT AVG(cnt) FROM (
SELECT COUNT(*) AS cnt FROM myschema.orders GROUP BY customer_id
) AS t)
```
## Subquery in ORDER BY
Scalar subqueries can be used in ORDER BY expressions:
```sql theme={null}
SELECT name FROM myschema.customers AS c
ORDER BY (SELECT COUNT(*) FROM myschema.orders AS o WHERE o.customer_id = c.id) DESC
```
## Not Supported
* `WITH RECURSIVE` (recursive CTEs)
# WHERE Predicates
Source: https://docs.dynamosql.com/sql-reference/where-predicates
Filter rows with comparisons, BETWEEN, IN, EXISTS, LIKE, IS NULL, and more.
## Comparison Operators
Standard equality and inequality operators:
```sql theme={null}
SELECT * FROM myschema.orders WHERE status = 'pending'
SELECT * FROM myschema.orders WHERE status != 'cancelled'
SELECT * FROM myschema.orders WHERE status <> 'cancelled' -- synonym for !=
SELECT * FROM myschema.orders WHERE total > 100
SELECT * FROM myschema.orders WHERE total >= 50
SELECT * FROM myschema.orders WHERE total < 1000
SELECT * FROM myschema.orders WHERE total <= 999.99
```
## BETWEEN / NOT BETWEEN
```sql theme={null}
SELECT * FROM myschema.orders WHERE total BETWEEN 50 AND 200
SELECT * FROM myschema.orders WHERE total NOT BETWEEN 50 AND 200
```
`BETWEEN a AND b` is inclusive on both bounds.
## IN / NOT IN with Value List
```sql theme={null}
SELECT * FROM myschema.orders WHERE status IN ('pending', 'processing', 'shipped')
SELECT * FROM myschema.orders WHERE status NOT IN ('cancelled', 'refunded')
```
## IN / NOT IN with Subquery
```sql theme={null}
SELECT * FROM myschema.customers
WHERE id IN (SELECT customer_id FROM myschema.orders WHERE total > 500)
```
## IN / NOT IN with Row Value Constructor
```sql theme={null}
SELECT * FROM myschema.shipments
WHERE (origin, destination) IN (('NYC', 'LAX'), ('SFO', 'ORD'))
```
## EXISTS / NOT EXISTS (Correlated Subquery)
```sql theme={null}
-- Customers who have placed at least one order
SELECT c.id, c.name
FROM myschema.customers AS c
WHERE EXISTS (
SELECT 1 FROM myschema.orders AS o WHERE o.customer_id = c.id
)
-- Customers with no orders
SELECT c.id, c.name
FROM myschema.customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM myschema.orders AS o WHERE o.customer_id = c.id
)
```
## LIKE / NOT LIKE
Two wildcard characters are supported:
| Wildcard | Matches |
| -------- | --------------------------------------- |
| `%` | Any sequence of zero or more characters |
| `_` | Exactly one character |
```sql theme={null}
SELECT * FROM myschema.products WHERE name LIKE 'Widget%' -- starts with "Widget"
SELECT * FROM myschema.products WHERE name LIKE '%Pro%' -- contains "Pro"
SELECT * FROM myschema.products WHERE sku LIKE 'A__-%%' -- "A" + 2 chars + "-" + anything
SELECT * FROM myschema.products WHERE sku NOT LIKE 'DISC-%'
SELECT * FROM myschema.products WHERE sku LIKE 'A#_%' ESCAPE '#' -- starts with literal "A_"
SELECT * FROM myschema.products WHERE sku LIKE 'A#_#%' ESCAPE '#' -- exactly literal "A_%"
```
Use `ESCAPE` when you need literal `%`, `_`, or the escape character itself inside the pattern. Escaping is explicit only: there is no implicit backslash escape mode.
## IS NULL / IS NOT NULL
```sql theme={null}
SELECT * FROM myschema.orders WHERE shipped_at IS NULL
SELECT * FROM myschema.orders WHERE shipped_at IS NOT NULL
```
## Not Supported
* `IS DISTINCT FROM` / `IS NOT DISTINCT FROM`
* `ANY` / `ALL` quantifiers (e.g., `WHERE x > ANY (SELECT ...)`)