Skip to main content
The Query API provides REST endpoints for querying every structured destination — PostgreSQL, MongoDB, object store, Snowflake, Databricks, and SQL Server — plus natural language queries and AI-powered answers. These endpoints mirror the MCP tools, making the same retrieval capabilities available via HTTP. In the web UI, the Search tab’s Traditional mode can also query Databricks, Snowflake and SQL Server pipelines (under Warehouse): pick the pipeline and run read-only SQL against its destination table through the pipeline’s own warehouse or server and credentials. The two AI endpoints are documented with the Search API, next to the vector search they are usually paired with: POST /api/v1/query/natural turns a plain-English question into SQL and runs it against PostgreSQL, and POST /api/v1/ai/answer generates an answer from context you pass in (typically retrieved chunks). This page covers the direct, read-only SQL and document queries against each destination.

Query PostgreSQL

Execute a read-only SQL SELECT query against PostgreSQL.

Request

Example

Response

Safety

The endpoint enforces multiple layers of protection:
  • SELECT-only — only queries starting with SELECT are allowed
  • Read-only connection — the JDBC connection is set to read-only mode
  • Statement blocklist — queries containing INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, GRANT, REVOKE, COPY, CALL, EXECUTE, or EXEC are rejected
  • No stacked queries — semicolons are not allowed
  • No SQL comments — -- and /* */ are rejected to prevent obfuscation
  • LIMIT enforcement — if no LIMIT is present in the SQL, one is appended automatically using the request’s limit value (or the default 100). Pass limit: -1 to bypass the automatic append entirely — the caller’s SQL runs as-is.

Query MongoDB

Query a MongoDB collection with optional filter and projection.

Request

Example

Response

Safety

  • Blocked operators — filters containing $where, $function, or $accumulator are rejected, as these allow arbitrary JavaScript execution
  • Standard MongoDB query operators ($eq, $gt, $in, $regex, etc.) are safe and fully supported

Query Object Store

Read rows from a pipeline’s object store destination (MinIO or S3). The pipeline name selects the bucket, prefix, and credentials — the caller never supplies connection details.

Request

Example

Response

snapshotId and snapshotTimestamp identify the Iceberg snapshot that was read; both are null for Parquet and ORC pipelines. If the pipeline exists but no runs have written data yet, the endpoint returns an empty result ("count": 0) rather than an error.

Error Cases

Returns 400 Bad Request with {"error": "..."} when:
  • The pipeline is not found
  • The pipeline does not have an object store destination
  • The pipeline’s object store configuration is missing a prefix key
  • The read times out (default 90 seconds, tunable via the DATRIS_OBJECTSTORE_QUERY_TIMEOUT_SEC environment variable)

Query Snowflake

Execute a read-only query against the Snowflake account a pipeline loads into. The pipeline name selects the credentials, warehouse, database, and schema — credentials never leave the server.

Request

Example

Response

The sql field echoes the query that actually ran, including any automatically appended LIMIT.

Safety

  • Read-only allowlist — queries must start with SELECT, WITH, SHOW, DESCRIBE, or DESC. SHOW/DESCRIBE are allowed so agents can discover databases, schemas, tables, and columns.
  • Statement blocklist — queries containing INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, GRANT, REVOKE, CALL, EXECUTE, EXEC, PUT, REMOVE, COPY, MERGE, or USE are rejected (USE is blocked so a query cannot re-point the session at another database or role)
  • No stacked queries — semicolons are not allowed
  • No SQL comments — -- and /* */ are rejected to prevent obfuscation
  • LIMIT enforcement — if a SELECT/WITH query has no LIMIT, one is appended using the request’s limit value. SHOW/DESCRIBE output is row-capped at the same value.
  • Query timeout — 30 seconds, bounding both hung requests and warehouse spend. Not applied when limit: -1.

Error Cases

Returns 400 Bad Request with {"error": "..."} when:
  • The pipeline is not found
  • The pipeline does not have a Snowflake destination
  • The SQL fails validation (allowlist, blocklist, semicolons, comments)

Query Databricks

Execute a read-only query against the Databricks workspace a pipeline loads into. The pipeline name selects the credentials, SQL warehouse, catalog, and schema — credentials never leave the server.

Request

Example

Response

The sql field echoes the query that actually ran, including any automatically appended LIMIT.

Safety

Same protections as Query Snowflake, with the blocklist extended for Databricks-specific data-movement and maintenance verbs: OPTIMIZE, VACUUM, RESTORE, CLONE, REFRESH, MSCK, and DECLARE are also rejected.
If the SQL warehouse is stopped, opening the connection blocks while it auto-starts — this happens before the 30-second query timeout applies, so the first query after idle time can take longer.

Error Cases

Returns 400 Bad Request with {"error": "..."} when:
  • The pipeline is not found
  • The pipeline does not have a Databricks destination
  • The SQL fails validation (allowlist, blocklist, semicolons, comments)

Query SQL Server

Execute a read-only T-SQL query against the SQL Server or Azure SQL database a pipeline loads into. The pipeline name selects the credentials secret, host, database, and schema — credentials never leave the server, and every authMethod the destination supports works here. Requires the query:sqlserver capability. The read-only and reporting key templates and the editor and viewer roles include it; a key issued before this capability existed keeps its explicit list, so add query:sqlserver to it or issue a new key.

Request

Example

Response

The sql field echoes the query that actually ran, including the row cap.

Row cap

T-SQL has no LIMIT, so the server caps rows one of two ways:
  • Top-level ORDER BY, no TOP — OFFSET 0 ROWS FETCH NEXT <limit> ROWS ONLY is appended, keeping the order. An ORDER BY inside parentheses (a subquery or OVER (...)) does not count.
  • Anything else (including SELECT TOP n ... ORDER BY, since SQL Server does not allow TOP and OFFSET together) — the statement is wrapped: SELECT TOP (<limit>) * FROM (<sql>) q. For a WITH statement the CTEs stay in front and only the final statement is wrapped.
  • A statement that already has OFFSET ... FETCH runs as written.
When SQL Server rejects the wrapper itself (a result column without a name such as COUNT(*), two columns with the same name from a join, or FOR JSON / FOR XML), the query runs unwrapped with the same row cap applied by the driver. Give computed columns an alias (COUNT(*) AS n) to see the wrapped form.

Safety

  • Read-only allowlist — queries must start with SELECT or WITH. For metadata, select from INFORMATION_SCHEMA views, for example SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES or SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'orders'.
  • Statement blocklist — queries containing INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, GRANT, REVOKE, DENY, EXEC, EXECUTE, MERGE, INTO (so SELECT ... INTO cannot create a table), USE, BULK, OPENROWSET, OPENDATASOURCE, OPENQUERY, DBCC, BACKUP, RESTORE, SHUTDOWN, KILL, RECONFIGURE, WAITFOR, DECLARE, TRIGGER, SETUSER, REVERT, WRITETEXT or UPDATETEXT are rejected. The check covers the whole statement, string literals included, so WHERE note = 'please delete' is rejected too
  • No stacked queries — semicolons are not allowed
  • No SQL comments — -- and /* */ are rejected to prevent obfuscation
  • Query timeout — 30 seconds. Not applied when limit: -1.

Error Cases

Returns 400 Bad Request with {"error": "..."} when:
  • The pipeline is not found
  • The pipeline does not have a SQL Server destination
  • The SQL fails validation (allowlist, blocklist, semicolons, comments)
  • SQL Server rejects the query (unknown table or column, ambiguous column, syntax error, permission denied, a value that will not convert)
  • The connection is refused (the message names the auth method and the fix, as for loads)
Returns 502 Bad Gateway when SQL Server fails for a reason outside the query.

Kill Job

Kill a running pipeline job by its pipeline token. The job thread is interrupted and the job is marked as cancelled.

Request

Example

Response

Error Cases

  • Job not found for the given pipeline token
  • Job is not in PROCESSING state (already completed or cancelled)
  • Job thread is no longer alive