One query document. Twelve SQL dialects.
QQL is the structured definition behind every visual question in Qrly — columns, calculated fields, a nested AND/OR filter tree, joins, aggregations, breakouts, having, ordering. Around 35 filter operators, a 19-category type system, and a compiler that knows the difference between OFFSET…FETCH NEXT and TOP n.
What QQL is, what it compiles to, what it refuses
Three things worth knowing before the reference tables.
A query, described rather than typed
- Source table, or a reference to another question
- Columns with alias, raw expression or computed function
- A nested AND/OR filter tree, with negation
- Aggregations, breakouts, having, order-by, limit, offset
- Joins, calculated fields and a preferred IANA timezone
Twelve dialect strategies
- Identifier quoting:
", backtick or[ ] LIMIT/OFFSETvsOFFSET…FETCH NEXTvsTOP n- NULLS FIRST/LAST, with a workaround where it is missing
- Date bucketing per level, spelled the way the engine wants it
- Wire-compatible engines fold in; unknown types default to Postgres
It would rather say no than guess
- Native SQL must start with SELECT or WITH — nothing else runs
- Drill-down refuses CTEs and nested SELECTs instead of mangling them
- SQL → visual reports lossy features as warnings, not silence
- Question nesting stops at depth 5, with cycle detection
- Casts come from an allow-list; identifiers from a regex
The query document
Everything a question is, in a form a machine can validate, an agent can propose and a reviewer can read.
A table, or another question
The source is either a physical table or question:<id> — a saved question compiled as a subquery. Columns are selected explicitly, each optionally carrying an alias, a raw expression, or a computed function, so a query document is never a SELECT * that quietly changes meaning when someone adds a column upstream.
A nested AND/OR tree, with negation
Not a flat list of conditions joined by a single connective. A tree, so (A or B) and not (C and D) is expressible as itself rather than as an approximation. The same structure the visual builder edits is the one the compiler reads and the one an agent proposes.
Aggregations, breakouts, having, ordering
Five aggregations — COUNT (including COUNT(*)), SUM, AVG, MIN, MAX — as a strict whitelist. Breakouts produce the grouping, having filters the groups, and order-by, limit and offset finish the shape. The OLAP compiler adds COUNT(DISTINCT …) and case-conditional measures on top.
prefer_timezone, applied at the session
An IANA timezone travels with the query document and is applied as a session-level statement, or supplied per request through a Prefer: header. Date bucketing, day boundaries and "yesterday" then mean what the reader expects them to mean, rather than what the database server's clock happens to think.
Four join types, and NULL ordering that works everywhere
| JoinsTypeTable or question as target | Behaviour |
|---|---|
LEFT | The default — keep every row on the left, whether or not it matches. |
INNER | Matches only. What the Modeler prefers on a PK → FK relationship. |
RIGHT | Keep every row on the right. |
FULL | Keep both sides, matched or not. |
question:<id> | Any of the four, but against another saved question compiled as a subquery — join to a curated result set instead of re-deriving it. |
The filter operator reference
Around 35 operators. The filter interface only offers the ones the column's type actually supports — which is why the type system on the next section matters as much as this list does.
| ~35OperatorQQL filter tree | Meaning | Notes |
|---|---|---|
| Comparison | ||
= != > >= < <= | The six ordinary comparisons. | Every dialect. |
| Null & null-safe | ||
IS NULL · IS NOT NULL | Presence and absence. | Every dialect. |
IS DISTINCT FROM · IS NOT DISTINCT FROM | Comparison that treats NULL as a value rather than as unknown. | The correct answer to "why does != drop my NULL rows". |
| Sets | ||
IN · NOT IN | Membership in a supplied list. | Backs multi-select filter widgets. |
BETWEEN | Inclusive range. | Backs the DATE_RANGE widget. |
| Text | ||
CONTAINS · STARTS_WITH · ENDS_WITH | Substring, prefix and suffix matching. | The three a business user actually reaches for. |
LIKE · ILIKE | Pattern matching, case-sensitive and case-insensitive. | ILIKE compiles natively where it exists, otherwise via LOWER(), otherwise plain — per dialect. |
MATCH · IMATCH | Regular-expression match, case-sensitive and case-insensitive. | For the filter that CONTAINS cannot express. |
| Quantified | ||
ANY · ALL | The comparison holds for at least one, or for every, element. | Quantified comparison against a set. |
| JSON, array & range containment — PostgreSQL and Redshift | ||
CS @> | Contains. | The JSONB filter people leave BI tools to write by hand. |
CD <@ | Contained by. | The mirror of CS. |
OV && | Overlaps. | Arrays and ranges. |
SL << | Strictly left of. | Range operator. |
SR >> | Strictly right of. | Range operator. |
NXR &< | Does not extend to the right of. | Range operator. |
NXL &> | Does not extend to the left of. | Range operator. |
ADJ -|- | Adjacent to. | Touching ranges with no gap. |
| Array membership & JSON path | ||
HAS_ELEMENT | The array contains this element. | Membership without unnesting the column first. |
JSON_PATH · JSON_PATH_TEXT | Extract at a JSON path, as JSON or as text. | Filter on a nested document field directly. |
| Temporal | ||
AT_TIMEZONE | Evaluate the column in a given timezone. | Pairs with the document's prefer_timezone. |
PAST_N_DAYS | A rolling window ending now. | A saved question that stays correct tomorrow. |
START_OF_MONTH | Bucket to the first of the month. | Month-to-date without hand-written date maths. |
| Full-text search — PostgreSQL, Redshift and H2 | ||
FTS | to_tsquery — the full query syntax, operators and all. | Optional language argument, validated. |
PLFTS | plainto_tsquery — plain words, ANDed. | Optional language argument, validated. |
PHFTS | phraseto_tsquery — words in order. | Optional language argument, validated. |
WFTS | websearch_to_tsquery — quotes and minus signs, the way a search box behaves. | Optional language argument, validated. |
Seven special values, spelled once
today, tomorrow, yesterday, infinity, -infinity, epoch and now are understood as literals in a filter value. They compile into whatever each engine wants them to be, so a filter written against Postgres does not have to be re-authored when the same question is pointed at BigQuery.
raw, computed and cast
QQL does not pretend to cover every expression a database can evaluate. A filter can carry verbatim SQL through the raw flag, address Postgres row-type functions through computed, and coerce a value through a cast — but the cast comes from a fixed 30-entry allow-list rather than being a free string, and every alias and join reference is validated against an identifier regex.
Nineteen categories, and a classifier per dialect
The reason the filter interface offers the right operators on a MySQL tinyint(1) and on a Snowflake variant without the user knowing either detail.
| 19 categoriesTypeCategoryNormalised, not raw | Covers |
|---|---|
TEXT · NUMBER · BOOLEAN | The everyday three, including engines that model booleans as bits or tiny integers. |
DATE · TIMESTAMP · TIMESTAMPTZ · INTERVAL | Temporal types kept distinct, because offset-aware and offset-naive are not the same filter. |
JSON · JSONB · ARRAY · RANGE · COMPOSITE | The structured types that unlock the containment, adjacency and path operators. |
ENUM · UUID · BIT · BYTEA | Constrained and binary types. byte[] is returned Base64-encoded rather than mangled. |
XML · GEOMETRY · UNKNOWN | Including an honest bucket for what the classifier cannot place, rather than a wrong guess. |
A size normaliser that ignores nonsense
JDBC drivers report display sizes that are frequently meaningless — a column declared once and reported as two billion characters wide. The normaliser suppresses those rather than rendering them, so the schema browser shows what a person can use.
A result mapper with opinions
Arrays become lists. byte[] becomes Base64. Postgres json and jsonb become parsed structures rather than strings that happen to look like JSON. Range types become structured maps, XML becomes a string, and every date becomes ISO-8601 — one shape, whatever the driver felt like returning.
Reuse, drill, and the road back from SQL
Depth 5, with cycle detection
Reference a saved question with question:<id> as a source table or a join target and it compiles as a subquery — a curated, reviewed result set becomes a building block instead of a copy-paste. Qrly detects cycles and caps nesting at a depth of 5, because an unbounded chain of derived questions is a compile-time hazard, not a feature.
Two modes, and an honest refusal
ROWS strips the aggregations to expose the rows behind a number. FILTER keeps the aggregation and narrows to the group you clicked. On a visual definition this happens structurally on the query document; on native SQL it happens by clause surgery.
And where clause surgery is not safe — CTEs, nested SELECTs — it refuses. A drill-down that quietly rewrites a query it does not understand is worse than no drill-down, because the number it returns still looks plausible.
Reverse-engineering, with the losses declared
A flat SELECT converts into a visual definition — joins, filters, group by, having, order by with NULL ordering, limit and offset. Anything that cannot survive the trip is reported as a lossy-feature warning: CTEs, UNION, subqueries. You are told what was left behind before you save over the original.
Widgets bound to real lookups
Mark columns filterable, define lookup SQL to populate the dropdown — optionally against a different connection — and set runtime labels. Widgets are SELECT, INPUT, DATE, DATE_RANGE and NUMBER, with multi-select, required and default value.
On a visual question the filter becomes an extra QQL filter; on a native question it substitutes a {{param}}. AI can suggest both the filters and the lookup SQL — as a suggestion, which you accept or ignore.
Virtual columns, validated server-side
Define a virtual column from a SQL expression with a type hint, then use it in filters, breakouts, having, order-by and aggregations like any other column. Validation happens on the server with an explicit Valid/Invalid status, edited in a Monaco mini-editor with column completion, and there is an AI chat for authoring the expression when the syntax is not the interesting part of your day.
{{template_variables}} as prepared statements
Native SQL template variables are bound as prepared-statement parameters, typed from their declaration or from context. Not string interpolation with escaping bolted on — actual parameter binding, which is the only version of this feature that is safe to hand to a dashboard viewer.
Three layers between a query and your production database
Every layer is a whitelist or an allow-list. None of them is a blacklist of things we thought to ban.
What runs, and what never gets that far
- The SQL sanitizer. Native SQL must start with
SELECTorWITH. Beyond that opening rule it blocks DROP, DELETE, INSERT, UPDATE, TRUNCATE, ALTER, CREATE, GRANT, REVOKE, EXECUTE, EXEC, CALL, MERGE, REPLACE, LOAD, IMPORT, EXPORT, COPY, VACUUM, ANALYZE, LISTEN, NOTIFY, LOCK, SET, RESET, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, DO, DECLARE and REFRESH. Schema changes have their own deliberate, separate path — they do not arrive through a question. - The expression sanitizer, for calculated fields: no semicolons, no comments, no DDL, DML or subquery keywords, balanced parentheses and quotes, a 1,000-character limit, and identifier rules with a reserved-word list.
- Identifier and cast validation. Aliases and join references must match an identifier regex; casts must appear in the allow-list. The two places a string most easily becomes SQL are the two places a free string is not accepted.
- Execution bounds. A max-row cap and a per-statement timeout on every run, with parameterised execution throughout. A query that would return the whole table stops; a query that would hold a connection open indefinitely stops.
- Inspection without execution.
EXPLAINandEXPLAIN (FORMAT JSON), count queries, and compile-only preview — so "what would this do" is answerable before "do it". - Postgres response hints. Database-controlled GUCs are mapped to HTTP headers and status codes, letting the database itself influence the response your application sees.
Large results stream as NDJSON with meta, row, end, truncated and error frames, honouring the driver fetch size, with a live row counter, a progress bar, and a cancel that genuinely aborts the request. CSV export streams too, rather than assembling the whole file in memory first.
Monaco, wired to your actual schema
Autocomplete that knows your tables is the difference between an editor and a text box with syntax colouring.
| EditorFeatureMonaco · custom light and dark themes | Detail |
|---|---|
| Schema-aware autocomplete | alias.column dot completion, tables after FROM and JOIN, columns after SELECT and WHERE, plus a SQL function catalogue with signatures and documentation, and keywords. |
| Hover & signature help | Hover providers on identifiers and signature help on functions, from the same catalogue that drives completion. |
| Snippets & formatting | Snippets for common shapes, and Alt+Shift+F to format. |
| Keybindings | Ctrl/Cmd+Enter or F5 to run, Ctrl/Cmd+S to save, Ctrl/Cmd+/ to comment. |
| Inline error markers | Errors marked on the failing line, not appended to the bottom of the page. |
| Forbidden-keyword warning | Mirrors the server sanitizer, so you learn a statement will be rejected while typing it rather than on submit. |
| Textarea fallback | If Monaco fails to load, the editor degrades gracefully to a plain textarea. The page still works on a locked-down network. |
| Toolbar | Explain SQL as an EXPLAIN plan modal, AI Explain this SQL, convert SQL → visual, back to visual, unlock, and the AI assistant. |
Common questions
What is QQL?
QQL is Qrly's query document: a structured definition holding a source table (or a reference to another question), explicit column selections with alias, raw expression or computed function, calculated fields, a nested AND/OR filter tree with negation, aggregations, breakouts, having, order-by, limit, offset, joins and a preferred IANA timezone.
The compiler turns that document into SQL for whichever of the 12 dialect strategies the connection uses, so the same question runs on PostgreSQL, BigQuery, Snowflake or SQL Server without being rewritten.
Can Qrly write to my database?
Not through a question. The SQL sanitizer requires native SQL to start with SELECT or WITH, and blocks DROP, DELETE, INSERT, UPDATE, TRUNCATE, ALTER, CREATE, GRANT, REVOKE, EXECUTE, EXEC, CALL, MERGE, REPLACE, LOAD, IMPORT, EXPORT, COPY, VACUUM, ANALYZE, LISTEN, NOTIFY, LOCK, SET, RESET, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, DO, DECLARE and REFRESH.
Calculated-field expressions go through a separate sanitizer, and aliases and join references are validated against an identifier regex with a cast allow-list. Schema authoring has its own deliberate, separate path.
Which filter operators does QQL support?
Around 35, grouped as comparison, null, null-safe, sets, text (including LIKE, ILIKE, and the regex operators MATCH and IMATCH), quantified ANY and ALL, JSON/array/range containment and adjacency on Postgres and Redshift, array membership, JSON path, temporal helpers, and four full-text search operators — FTS, PLFTS, PHFTS and WFTS — each taking a validated optional language argument.
Each filter may also carry a cast drawn from a 30-entry allow-list, a raw flag for verbatim SQL, or a computed flag for Postgres row-type functions.
Can I turn existing SQL into a visual question?
Yes, for a flat SELECT. Qrly reverse-engineers it into a visual definition covering joins, filters, group by, having, order by with NULL ordering, and limit/offset. Features that cannot survive the conversion — CTEs, UNION, subqueries — are reported as explicit lossy-feature warnings rather than silently dropped.
The reverse direction is always available too: any visual question compiles to SQL you can read, and the editor toolbar converts between the two.
Does drill-down work on hand-written SQL?
Yes, within limits it states honestly. Drill-down has two modes: ROWS, which strips the aggregations to expose the underlying rows, and FILTER, which keeps the aggregation and narrows to the group you clicked. On visual definitions it operates structurally on the query document; on native SQL it works by clause surgery.
Where clause surgery would be unsafe — CTEs, nested SELECTs — it refuses rather than mangling a query it cannot rewrite correctly.
What happens on engines that do not support NULLS FIRST or NULLS LAST?
The compiler emulates it. Order-by supports ASC/DESC with NULLS FIRST, NULLS LAST or the database default, including ordering on computed fields, and on MySQL, MariaDB, SQLite and Sybase it automatically emits a leading (field IS NULL) expression to produce the same ordering.
That is one instance of a general pattern: per dialect the compiler also handles identifier quoting, LIMIT/OFFSET versus OFFSET…FETCH NEXT versus TOP n, and the right date-bucketing spelling.