Designing a query language where clauses read in execution order — what trade-offs have you hit?

[absterjr]

🏷️ Discussion Type

Question

Body

I'm building UNSeQueL, a query language where a program is a pipeline of stages in the order they execute: from, filter, join, group by + aggregate, having, sort, take. It compiles to DuckDB SQL. The motivation is that SQL's keyword order (SELECT before FROM, HAVING after GROUP BY) doesn't match how you reason about a query.

from orders
filter status = 'paid'
group by customer_id
aggregate total = SUM(amount)
having total > 100
sort -total
take 10

I'd like the community's perspective on the design trade-offs:

1. Discoverability vs familiarity — is a stage-per-line grammar easier to learn than SQL, or just different?
2. Which constructs are worth adding next: window syntax, nested subqueries, write statements?
3. For tool builders: what makes a new query language worth trying rather than a SQL generator you already have?

Background and grammar: https://github.com/absterjr/UNSeQueL (docs at https://absterjr.github.io/UNSeQueL/).

Guidelines

• I have read and understood this category's guidelines before making this post.

[danilablagorodniy37]

Nice project. A few thoughts from someone who writes a lot of SQL and Python against DuckDB.

You are in good company, and it is worth saying so in the README. "Stages in execution order" is exactly what PRQL does (it also compiles to SQL, DuckDB dialect included), what KQL does in Azure Data Explorer (T | where ... | summarize ... | top 10 by ...), what dplyr does in R, and what LINQ, Polars and pandas method chains do inside general-purpose languages. Google published "pipe syntax" for GoogleSQL in 2024 (FROM orders |> WHERE ... |> AGGREGATE ...), and DuckDB itself now accepts FROM-first queries with an optional SELECT. So the industry has mostly voted "yes" on pipeline order already; the open question for a reader is "why this one". I would put a short comparison with PRQL and pipe-syntax SQL near the top of the docs, because that is the first thing an experienced user will look for.

Discoverability vs familiarity. Pipeline order wins in two places. First, teaching: it matches SQL's logical evaluation order (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT), which is the thing beginners trip over. Second, tooling: once from comes first, the editor knows the schema at every later line, so autocomplete and type errors become cheap to implement. Where it loses: everything else in the stack (ORMs, BI tools, dbt, colleagues) speaks SQL, so the language lives or dies on the quality of the escape hatch. Keep the generated SQL readable and stable, and give people a one-command way to "eject" to plain SQL.

A concrete grammar suggestion: you may not need having as a keyword. In a pipeline, a filter placed after aggregate is a HAVING; the position already disambiguates. PRQL does exactly this and it removes one concept to learn.

What I would add next, in this order:

1. Named intermediate pipelines (let paid = from orders | filter status = 'paid') compiled to CTEs. This covers most "nested subquery" needs without nested syntax, and it is where the pipeline model shines.
2. Window functions. This is the hardest part for pipeline languages, because a window is "a group that keeps its rows". Look at how PRQL scopes window inside group before designing it; the simple case is easy, rows between is where it gets painful.
3. Joins with an explicit side (join side:left) and a join-key syntax that cannot be confused with a filter.
4. Write statements last. Read-only is a feature early on: it keeps the compiler small and makes the tool safe to point at production data.

What makes me try a new query language instead of a SQL generator I already have:

• Error messages that point at my source line and column, not at the generated SQL.
• A --show-sql flag so I can learn from and audit what it produces.
• Zero-friction embedding: pip install unsequel, a to_sql(text) function that returns a string, and a Jupyter cell magic. Since you already target DuckDB, a Python API is the shortest path to real users.
• A formatter and an LSP with autocomplete, which the pipeline design makes feasible.
• Semantics documented as "this stage maps to this SQL", so I never have to guess.

I would hold off on anything that does not compile to a plain SQL string, because that is what keeps the language a thin layer rather than a new database.

[NexzaDev]

Hi @absterjr,

Interesting project, and the earlier reply covers the landscape (PRQL, KQL, pipe syntax) and roadmap well, so I'll focus on semantic details that tend to bite pipeline languages once real users arrive.

1. Is it easier to learn than SQL?

For reading, mostly yes: each line is one transformation and you can mentally "run" the pipeline top to bottom. For beginners the bigger win is that the order you write is the order the data flows. The cost is that anything that feels familiar from SQL but behaves differently becomes a trap, so the docs should call out the differences explicitly (for example, where a filter ends up in the generated SQL depending on its position).

2. Details worth deciding early

• Stage position defines meaning. A filter before group by becomes a WHERE and one after aggregate becomes a HAVING. Define this rule precisely and test it, including a filter after join, since filtering before vs after a LEFT JOIN changes results.
• Scoping after each stage. After aggregate, which columns still exist? Make it an error to reference amount after the group-by instead of letting SQL's rules leak through. This is also the point where good error messages matter most.
• NULL semantics. filter status = 'paid' inherits SQL's three-valued logic, so rows with NULL silently disappear. Decide whether = null is an error, and give a clear is null / is not null form.
• Sorting and take. sort -total is compact, but a prefix - is easy to miss on a long expression, and it doesn't say where NULLs go. Specify NULL ordering and tie behaviour. A take 10 after a sort with ties is non-deterministic unless you add a tie-breaker, so consider documenting or warning about it.
• Mixing languages. Your example uses SQL function syntax (SUM(amount)) inside a non-SQL stage. That's pragmatic, but state clearly which expression language you support, since users will try anything DuckDB accepts.

3. Testing the compiler

Because the target is DuckDB, you can do differential testing: for every example, run the generated SQL and a hand-written equivalent over the same small tables and compare results. That catches semantic drift faster than snapshot-testing SQL strings.

4. What makes a new language worth trying (a user's view)

Predictable generated SQL, error messages at the source line/column, and being able to see the SQL and copy it out. Beyond that, a clear explanation of what you can express more easily than in SQL. One or two realistic examples, such as a multi-step analytics query, would convince people faster than the grammar.

Hope this is useful, and good luck with the project!