Execute a DQL query

Error

A valid request URL is required to generate request examples

{
  "transactionId": 100,
  "queryType": "select",
  "items": [
    {
      "_id": 1,
      "name": "Francis",
      "favoriteBook": {
        "title": "The Great Gatsby",
        "published": 1925
      }
    }
  ],
  "mutatedDocumentIds": [],
  "error": {},
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

POST

store

execute

Error

A valid request URL is required to generate request examples

{
  "transactionId": 100,
  "queryType": "select",
  "items": [
    {
      "_id": 1,
      "name": "Francis",
      "favoriteBook": {
        "title": "The Great Gatsby",
        "published": 1925
      }
    }
  ],
  "mutatedDocumentIds": [],
  "error": {},
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

{
  "queryType": "unknown",
  "items": [],
  "mutatedDocumentIds": [],
  "error": {
    "description": "..."
  },
  "warnings": [],
  "totalWarningsCount": 0
}

Authorization

All requests to this endpoint require authentication using an API key or JWT token in the Authorization: Bearer header. For detailed instructions on creating and managing API keys, see Auth and Parameters.

API versions

This endpoint is available at both /api/v4/store/execute and /api/v5/store/execute. The v5 API defaults to DQL_STRICT_MODE=false, which treats nested objects as MAPs instead of REGISTERs. For more information, see API v4 vs v5.

Examples

The following examples demonstrate common DQL operations using curl. Replace {YOUR_CLOUD_URL_ENDPOINT} with your Cloud URL Endpoint from the portal (see Getting your Cloud URL Endpoint) and {YOUR_API_KEY} with your API key.

Insert a document

Insert a new document into a collection:

cURL

curl -X POST 'https://{YOUR_CLOUD_URL_ENDPOINT}/api/v5/store/execute' \
  -H 'Authorization: Bearer {YOUR_API_KEY}' \
  -H 'Content-Type: application/json' \
  -d '{
    "statement": "INSERT INTO cars DOCUMENTS (:newCar)",
    "args": {
      "newCar": {
        "_id": "car-123",
        "make": "Toyota",
        "model": "Camry",
        "year": 2024,
        "color": "blue"
      }
    }
  }'

Select documents

Query documents from a collection:

cURL

curl -X POST 'https://{YOUR_CLOUD_URL_ENDPOINT}/api/v5/store/execute' \
  -H 'Authorization: Bearer {YOUR_API_KEY}' \
  -H 'Content-Type: application/json' \
  -d '{
    "statement": "SELECT * FROM cars WHERE color = :color",
    "args": {
      "color": "blue"
    }
  }'

Update a document

Update an existing document:

cURL

curl -X POST 'https://{YOUR_CLOUD_URL_ENDPOINT}/api/v5/store/execute' \
  -H 'Authorization: Bearer {YOUR_API_KEY}' \
  -H 'Content-Type: application/json' \
  -d '{
    "statement": "UPDATE cars SET color = :newColor WHERE _id = :id",
    "args": {
      "newColor": "red",
      "id": "car-123"
    }
  }'

Delete a document

Important: The DELETE operation permanently removes documents from the Ditto system. Deleted documents cannot be recovered. For detailed guidance on delete behavior, tombstones, and best practices, see Removing Documents.Key considerations:

Deleting large numbers of documents (50,000+) at once can impact system performance. Use LIMIT to batch deletions.
Concurrent delete and update operations on the same document can result in unexpected “husked documents.”
Consider using the soft-delete pattern or EVICT for local-only removal instead of permanent deletion.

Delete a document from a collection:

cURL

curl -X POST 'https://{YOUR_CLOUD_URL_ENDPOINT}/api/v5/store/execute' \
  -H 'Authorization: Bearer {YOUR_API_KEY}' \
  -H 'Content-Type: application/json' \
  -d '{
    "statement": "DELETE FROM cars WHERE _id = :id",
    "args": {
      "id": "car-123"
    }
  }'

To delete documents in batches (recommended for large datasets):

cURL

curl -X POST 'https://{YOUR_CLOUD_URL_ENDPOINT}/api/v5/store/execute' \
  -H 'Authorization: Bearer {YOUR_API_KEY}' \
  -H 'Content-Type: application/json' \
  -d '{
    "statement": "DELETE FROM cars WHERE year < :year LIMIT 30000",
    "args": {
      "year": 2020
    }
  }'

Upsert a document

Insert a document or update it if it already exists:

cURL

curl -X POST 'https://{YOUR_CLOUD_URL_ENDPOINT}/api/v5/store/execute' \
  -H 'Authorization: Bearer {YOUR_API_KEY}' \
  -H 'Content-Type: application/json' \
  -d '{
    "statement": "INSERT INTO cars DOCUMENTS (:car) ON ID CONFLICT DO UPDATE",
    "args": {
      "car": {
        "_id": "car-123",
        "make": "Toyota",
        "model": "Camry",
        "year": 2024,
        "color": "green",
        "mileage": 15000
      }
    }
  }'

Response

A successful response includes:

transactionId: The transaction ID for this operation
queryType: The type of query executed (e.g., “select”, “insert”, “update”, “delete”)
items: For SELECT queries, the array of matching documents
mutatedDocumentIds: For mutations, the IDs of affected documents

API reference

Authorizations

Authorization

string

header

required

Authentication using either an API key or JWT token in the Authorization header

Headers

X-DITTO-TXN-ID

integer<int64>

Optional transaction ID that ensures consistency across operations. When provided, the operation will only execute if the Big Peer's current transaction ID meets or exceeds this value, preventing stale reads and ensuring causal consistency in your application.

Body

application/json

The DQL statement to execute, along with any parameterized arguments. Using parameterized queries with the args field helps prevent injection attacks and improves query performance through statement caching.

Request parameters for executing a DQL statement. DQL is Ditto's powerful query language that supports complex queries and data modifications.

statement

string

required

The DQL statement to execute. See https://docs.ditto.live/dql-guide for comprehensive documentation on DQL syntax and features.

args

any

Named parameters to use in the DQL statement, providing safe value substitution and better query performance

Response

The DQL statement executed successfully. The response includes the results of the query, any mutated document IDs, the transaction ID, and any warnings that occurred during execution. For SELECT queries, results appear in the items array. For mutations, affected IDs appear in mutatedDocumentIds.

Response from executing a DQL statement. Contains query results, affected document IDs, and any warnings or errors that occurred.

items

any[]

required

mutatedDocumentIds

any[]

required

queryType

string

required

Indicates the type of query that was executed

totalWarningsCount

integer<int64>

required

Total number of warnings generated during query execution

warnings

object[]

required

Show child attributes

error

object

An error occurred that prevented the query from executing or completing successfully

Show child attributes

transactionId

integer<int64>

Overview

Public Cloud (BYOC)

Cloud Portal

HTTP Data API

Data Integration

Data Authentication

Reference

Execute a DQL query

Authorization

API versions

Examples

Insert a document

Select documents

Update a document

Delete a document

Upsert a document

Response

API reference

Authorizations

Headers

Body

Response

​Authorization

​API versions

​Examples

​Insert a document

​Select documents

​Update a document

​Delete a document

​Upsert a document

​Response

​API reference

Authorizations

Headers

Body

Response

Authorization

API versions

Examples

Insert a document

Select documents

Update a document

Delete a document

Upsert a document

Response

API reference