Query API

Each running ReflexDB instance exposes a single query endpoint. Queries are plain-text selections sent as a POST body.

Endpoint

POST https://<instance-id>.reflexdb.cloud/query

Authentication

Pass your API key as a Bearer token:

Authorization: Bearer rxk_<keyId>.<hmac>

API keys are created in the dashboard (Database → API Keys) or via the control plane API.

---

Request format

The request body is plain text (not JSON). The Content-Type header is not required.

<table> { <fields> }

Select fields

users { id name email }

Wildcards

Use * to select all scalar fields, or ... to select all scalars plus one level of relations:

users { * }
users { ... }

Wildcards can be combined with explicit fields — explicit fields override wildcard-generated ones:

users { * posts { id title } }

Filter

Filter by any column using a SQL-style predicate in parentheses:

users(plan = "pro") { id name email }

Supported operators: =, !=, <, <=, >, >=, LIKE, ILIKE, IN (...), IS NULL, IS NOT NULL, BETWEEN x AND y.

Boolean literals true and false are supported:

posts(published = true) { id title }

Multiple conditions can be combined with AND and OR. Parentheses override precedence:

posts(published = true AND user_id = 42) { id title }
users(plan = "pro" OR (plan = "free" AND verified = true)) { id name }

Relation filters

Filter parent rows by conditions on related tables using dot notation:

users(posts.published = true) { id name }

Dot chains can traverse multiple levels:

users(posts.comments.approved = true) { id name }

For to-many relations, ANY semantics apply — the parent matches if at least one related row satisfies the predicate.

Include

Embed related rows in a single request by selecting fields from a relation:

posts { id title author { id name } comments { id body } }

Forward relations (FK on this table) return an object; reverse relations return an array.

[
  {
    "id": 1,
    "title": "Hello world",
    "author": { "id": 42, "name": "Alice" },
    "comments": [
      { "id": 100, "body": "Great post!" }
    ]
  }
]

Relations can be nested to any depth and filtered independently:

users { id posts(published = true) { id title } }

Sort

Add an ORDER BY clause after the closing brace:

posts { id title } ORDER BY created_at DESC

Sort order is ASC (default) or DESC. Multiple sort fields are separated by commas:

posts { id title } ORDER BY published_at DESC, id ASC

Nested relations support their own ORDER BY:

users { id posts { id title } ORDER BY title ASC }

Pagination

Add LIMIT and/or OFFSET after the closing brace. Clauses must appear in this order: ORDER BY → LIMIT → OFFSET.

users { id name } ORDER BY id LIMIT 20 OFFSET 40

Clause	Default	Notes
LIMIT	1000	Prevents accidental full-table dumps
OFFSET	0	—

Note

When no LIMIT is specified, a default limit of 1000 is applied to prevent accidental full-table dumps on large datasets. Use meta.pagination.has_more to detect when additional rows exist beyond the limit. To retrieve more rows, specify a larger explicit LIMIT (e.g. LIMIT 10000). The default is configurable per instance via the query.default_limit setting.

Nested relations also support LIMIT and OFFSET:

users { id posts { id title } ORDER BY title ASC LIMIT 5 }

---

Aggregations

ReflexDB supports COUNT, SUM, and AVG aggregation functions. Aggregates are specified inline in the field list.

Supported functions

Function	Output key	Notes
COUNT(*)	count	Count of all rows
COUNT(field)	count	Count of non-NULL values
SUM(field)	sum_<field>	Sum of a numeric column
AVG(field)	avg_<field>	Average of a numeric column

Basic aggregation

orders { COUNT(*) }

[{"count": 1482}]

GROUP BY (implicit)

Any non-aggregate field in the selection is an implicit GROUP BY key:

orders { status COUNT(*) }

[
  {"status": "pending",  "count": 42},
  {"status": "shipped",  "count": 1440}
]

Multiple aggregates

orders { SUM(total_cents) AVG(total_cents) }

[{"sum_total_cents": 9840200, "avg_total_cents": 6640}]

With filter and sort

orders(status = "shipped") { user_id SUM(total_cents) } ORDER BY total_cents DESC LIMIT 10

Nested aggregation

Aggregate functions also work inside embedded relations — useful for counting child rows per parent:

users { id name posts { COUNT(*) } }

[
  {"id": 1, "name": "Alice", "posts": [{"count": 12}]},
  {"id": 2, "name": "Bob",   "posts": [{"count": 3}]}
]

Note

SUM and AVG require a field name — SUM(*) is not valid. Aggregates cannot be used inside to-one (FK) relations, only to-many (reverse) relations.

---

Response format

A successful response is a JSON envelope with data and meta keys:

{
  "data": [
    { "id": 1, "name": "Alice", "email": "alice@example.com" },
    { "id": 2, "name": "Bob",   "email": "bob@example.com" }
  ],
  "meta": {
    "table": "users",
    "query": {
      "fields": ["id", "name", "email"]
    },
    "pagination": {
      "count": 2,
      "total_matched": 2,
      "has_more": false,
      "limit": 1000
    },
    "timing_ms": 0.42
  }
}

• data — array of matching rows
• meta.table — the queried table name
• meta.query — echo of the parsed query parameters (fields, aggregates, filter, order_by, limit, offset — only present when specified)
• meta.pagination.count — number of rows in this page
• meta.pagination.total_matched — total rows matching the filter before limit/offset
• meta.pagination.has_more — true if more rows exist beyond this page
• meta.timing_ms — server-side query execution time in milliseconds

---

Data types

ReflexDB maps source database columns to JSON types as follows:

SQL type	JSON type	Example
INT, BIGINT, SMALLINT, TINYINT	number	42
FLOAT, DOUBLE, REAL	number	3.14
DECIMAL, NUMERIC	number	99.99
BOOLEAN, BIT(1), TINYINT(1)	boolean	true
CHAR, VARCHAR, TEXT	string	"hello"
JSON, JSONB	string	"{\"key\":\"value\"}"
UUID, INET, MACADDR	string	"550e8400-..."
ENUM	string	"active"
DATE	string	"2024-06-15"
DATETIME, TIMESTAMP, TIMESTAMPTZ	string	"2024-06-15T12:30:00Z"
TIME, TIMETZ	number (seconds)	45000
INTERVAL	string	"1 year 2 months"
BINARY, VARBINARY, BLOB, BYTEA	string (hex)	"deadbeef"
Nullable columns	null when NULL	null

Note

MySQL TINYINT(1) columns are treated as booleans and returned as true/false. If you use TINYINT(1) to store small integers (not just 0/1), add the comment REFLEX_TYPE=INTEGER to the column in your source database to preserve integer serialization.

Note

All dates and times are stored and returned in UTC. See Dates and times for details on timezone handling and filter formats.

---

OpenAPI spec

Each instance also serves its own OpenAPI spec describing the exact tables and fields in its compiled schema:

GET https://<instance-id>.reflexdb.cloud/openapi.json

This is also proxied through the control plane at:

GET https://api.reflexdb.cloud/v1/databases/<id>/openapi.json

---

Error responses

All errors return a JSON envelope with a nested error object:

HTTP	error.code	Meaning
400	parse_error	Malformed query syntax
400	executor_error	Valid syntax but unknown field or invalid query structure
404	unknown_table	Table name not found in the schema
401	unauthorized	Missing or invalid API key
503	—	Instance is starting up or unhealthy

{ "error": { "code": "unknown_table", "message": "unknown table: 'foobar'" } }

Tip

The OpenAPI spec for your instance documents the exact tables and fields available — use it to avoid unknown_table and executor_error responses.