Skip to main content
POST/query

Query with SQL

Executes a parameterised raw SQL statement against the project's user-data schema (k9o_<proj_id>).

The request body carries sql (string) and an optional params array of positional bind values.

Params are bound via Postgres positional placeholders ($1, $2, …) and never interpolated into the SQL text.

POST is used for all raw SQL regardless of read/write intent because the SQL cannot be safely carried in a URL parameter.

RLS policies apply to all returned rows.

DDL statements (CREATE, DROP, ALTER) are rejected because the _query credential lacks DDL grants.

Returns K9O_INJECTION_DETECTED (400) at the SDK layer when bare-string SQL contains unparameterised dynamic content.

Parameters
projectSlug *path

The project's slug: the path segment after @.

It is unique within the org.

Request body
sql *string

One read-only statement starting with SELECT or WITH, at most 100,000 characters.

Bare table names are the project's collections.

A query that names dataset.<slug> runs against those analytics datasets instead (address a collection there as pg.<collection>).

Writes (INSERT, UPDATE, DELETE), DDL, SET/RESET, transaction control and more than one statement are refused.

Use $1, $2, ... placeholders for values.

paramsany[]

Values bound to the placeholders in order: strings, numbers, booleans or null; at most 1,000.

Default empty.

One of: string
or: number
or: boolean
or: null
limitinteger

The most rows to return, 1 to 10,000; default 1,000.

The statement runs as written; reading stops after limit rows, and meta.truncated says whether there were more.

Applies to a collection query.

Example: 1000
timeoutSecondsinteger

How long the statement may run, in seconds; default 60.

The maximum is set by the deployment and is never above 300; a larger value is refused with 400 K9O_INVALID_FIELD_VALUE.

A statement that runs longer is cancelled with 504 K9O_QUERY_TIMEOUT.

Applies to a collection query.

Example: 60
Responses
HTTPK9O CodeDescription
200Success
400K9O_INVALID_FILTERThe query contains a malformed or unsupported filter expression.
400K9O_INVALID_VECTOR_DIMENSIONThe embedding vector supplied for a vector search has a dimension that does not match the column's declared dimension.
401K9O_SESSION_EXPIREDYour session has expired; sign in again to execute a query.
401K9O_INVALID_CREDENTIALSThe credentials provided are not valid; the query request was rejected.
403K9O_ACCESS_DENIEDYour role does not permit executing queries against this project.
404K9O_COLLECTION_NOT_FOUNDA collection referenced in the query does not exist in this project.
409K9O_MULTIPLE_ROWS_RETURNEDA single-row assertion in the query matched more than one row.
422K9O_CROSS_SHARD_TRANSACTION_UNSUPPORTEDThe query spans collections on multiple shards, which is not supported in a single transaction.
Response body (200)
data *object[]

The rows the statement returned, in the order it returned them.

For a collection query, at most limit rows and at most the deployment's response-size cap of JSON; meta.truncated says whether rows were left out and meta.truncatedBy which cap stopped the read.

meta *object

Request metadata (cost, timing, request and trace ids), plus limit, truncated, truncatedBy and rawQuery.

limitinteger

The row limit applied.

Present for a collection query.

truncatedboolean

Whether the statement had more rows than data holds.

Present for a collection query.

truncatedBy"rows" | "bytes"

Present only when truncated is true: rows when the read stopped at limit rows, bytes when it stopped at the response-size cap (the UTF-8 JSON size of the rows), whichever came first.

Add a LIMIT or select fewer columns.

rawQueryobject

Present for a collection query; absent for a dataset query.

intent *"read"

The statement's classification.

Only reads run here.

rowCount *integer

How many rows data holds.

TypeScript
// const k9o = new K9o({ orgSlug: 'playground', projectSlug: 'storefront', apiKey: 'k9o_…' })
const { data } = await k9o.query(k9o.sql`SELECT id, title FROM products WHERE id = ${'prod_demo00000001'}`)

Showing example in TypeScript

Request example
{
  "sql": "string",
  "params": [
    "string"
  ],
  "limit": 1000,
  "timeoutSeconds": 60
}
Response example (200)
{
  "data": [],
  "meta": {
    "limit": 0,
    "truncated": false,
    "truncatedBy": "rows",
    "rawQuery": {
      "intent": "read",
      "rowCount": 0
    }
  }
}

More in Data, Queries & Vectors