SQL dialect

Reads use a restricted, read-only SELECT over one collection or view per query. Queries are validated, limited to your account's data and never executed as raw SQL.

SELECT analysis_status, COUNT(*) AS total
FROM projects
WHERE created_at >= now() - interval '30 days'
GROUP BY analysis_status
ORDER BY total DESC
LIMIT 10

SELECT

• direct registered fields with optional AS aliases
• SELECT DISTINCT explicit fields (rewritten to GROUP BY)
• * as the sole projection
• COUNT/SUM/AVG/MIN/MAX(field) AS alias
• COUNT(*) AS alias
• COUNT(DISTINCT field) AS alias (PostgreSQL/sqlView only)
• DATE_BUCKET('day'|'week'|'month', field[, 'IANA timezone']) AS alias (PostgreSQL/sqlView only)
• RANK(field, search_text) AS alias

WHERE

Use IS NULL or IS NOT NULL. Ordinary comparisons with NULL produce unknown, which WHERE does not select.

LIKE uses % for any sequence and _ for one character; ILIKE ignores case. Prisma preserves internal wildcards when the pattern has a leading or trailing %. Fully anchored wildcard patterns require a view. Other adapters may reject patterns or operators they cannot preserve.

ORDER BY

ORDER BY registered scalar fields, aliases or aggregate calls with ASC or DESC and optional NULLS FIRST/LAST. ASC defaults to NULLS LAST and DESC to NULLS FIRST. Enum ordering follows declared value order; an adapter that cannot sort a field in that order rejects it with UNSUPPORTED_ORDER. Geometry, JSON and array fields cannot be sorted. Grouped results end with their group keys in ascending order. Prisma grouped ordering uses default NULL placement. On large collections, ordering by RANK(field, ...) requires AND SEARCH(field, ...) in WHERE.

GROUP BY and HAVING

GROUP BY supports registered fields and selected DATE_BUCKET expressions or aliases. HAVING requires GROUP BY and filters grouped fields or aggregate expressions/aliases. Prisma COUNT(*) ordering/HAVING requires a declared nonnullable scalar field.

Relative time

now(), CURRENT_DATE and now() - interval 'N days' are folded into bound values using one request clock; cursor pages and streams keep the first page's clock. CURRENT_DATE uses UTC; intervals are whole nonnegative days (at most 365000).

Arrays

String arrays support scalar = ANY(field), @> ARRAY[...] and && ARRAY[...]; array literals may contain scalar positional parameters. Array parameters, NULL elements and array sorting/grouping are unsupported.

Enumerated values

Enum values are exact and case-sensitive. Use declared values; NULL comparisons retain SQL three-valued semantics. Pattern/range/search predicates and MIN/MAX are unsupported. Ordering follows the declared enum value order; adapters that cannot honor it reject the sort. COUNT and GROUP BY are available when aggregation is enabled. Filterability and field permissions still apply.

Paging

LIMIT and OFFSET accept nonnegative integer literals or positional parameters. For collections declaring pagination, cursor:null starts signed keyset paging and meta.nextCursor resumes it. Use no OFFSET or aggregates with cursors; declared unique key fields break ties. Every query returns meta.hasMore; meta.limitApplied indicates an implicit default LIMIT. Registered defaultOrderBy and unique pagination keys stabilize row ordering. Limits remain authoritative. Streaming yields bounded cursor pages with backpressure and fresh policy checks.

Parameters

$1, $2, … bind JSON scalar values: string, finite number, boolean or null.

Limits

Max Sql Length

4096

Max Depth

32

Max Nodes

1000

Max Parameters

100

Max Offset

10000

Default Limit

25

Default Max Limit

100

Max Order By Fields

10

Max In Values

100

Not supported

Need something the dialect cannot express, such as a join? Use a view or a report.