Two Kinds of SQL Query Builders, and Only One Knows What Your Query Means

Swap two adjacent calls in a query builder chain. One library silently returns different data. Another returns byte-identical SQL. That divergence is not a bug in either library — it is the most useful axis for classifying query builders, and almost nobody names it.

The FunSQL documentation draws the line explicitly: there are data-oriented query builders, which incrementally refine the query output, and syntax-oriented query builders, which incrementally assemble the SQL syntax tree. Both are compositional. Both expose chained methods with SQL names. Only one of them has an opinion about what your query means.

The Chain Syntax Is a Coincidence, Not a Contract

Here is a FunSQL pipeline that computes order counts per customer:

using FunSQL: From, Where, Group, Select, Agg, Get, render

q = From(:orders) |>
    Where(Get.status == "shipped") |>
    Group(Get.customer_id) |>
    Select(Get.customer_id, Agg.count() => :order_count)

render(q)

Now compare the same intent in a syntax-oriented builder:

Order.where(status: "shipped")
     .group(:customer_id)
     .select("customer_id, count(*) AS order_count")

The shapes are identical. Move Group above Where in the FunSQL version and you get a different query — or a compile error, because Get.status no longer resolves after grouping. Move group above where in the Ruby version and you get the exact same SQL, because where and group write into separate slots of an abstract syntax tree.

That is the whole distinction. A data-oriented builder is a pipeline of functions from dataset to dataset — the same mental model as Unix pipes or dplyr. Each node consumes the previous node's output, so node order is part of the semantics. A syntax-oriented builder is the builder pattern: each call mutates one slot of a composite structure named SELECT, FROM, WHERE, JOIN. Order is irrelevant because slots are addressed by name.

You can classify any builder in about thirty seconds. Permute two adjacent nodes in a chain and re-render. Data-oriented builders change the result or refuse to compile. Syntax-oriented builders emit identical SQL. FunSQL, EF/LINQ, and dbplyr are order-sensitive. Active Record, Eloquent, Knex, and GORM are not.

Data-Oriented Builders Are Translators, and Translators Have Coverage Gaps

The data-oriented family has an implementation problem: a pipeline that operates on in-memory datasets is useless against a database with indexes. Loading orders into the application to filter it there is not a query plan, it is a full table scan followed by a memory leak.

So data-oriented builders do not execute the pipeline. They treat it as a specification and rewrite the whole thing into SQL — a transformation called SQL pushdown. EF/LINQ is a LINQ query framework adapted to SQL, dbplyr is dplyr adapted to SQL, and FunSQL was designed against SQL's feature set from the start. The pipeline never runs; it is compiled away.

Pushdown is where the class of bugs unique to this family lives. The pipeline is expressed in the host language, but only a subset of the host language maps to SQL. Anything outside that subset has no server-side representation:

// Translates: everything maps to SQL
var recent = await db.Orders
    .Where(o => o.CreatedAt > cutoff)
    .Where(o => o.Items.Any(i => i.Sku.StartsWith(prefix)))
    .ToListAsync();

// Throws InvalidOperationException: could not be translated
var bad = await db.Orders
    .Where(o => IsEligible(o))   // local method, opaque to the translator
    .ToListAsync();

Before EF Core 3.0, that second query did not throw. EF Core executed the untranslatable fragment client-side, which meant it pulled the filtered-on rows out of the database and finished the predicate in .NET. A Where chained after a Select could materialize an entire table over the wire and discard 99.9% of it in the process. Removing silent client evaluation was the single highest-impact change in EF Core's history, and it converted a silent performance disaster into a loud exception.

That is the trade-off of the data-oriented family, stated precisely: your expressiveness ceiling is not SQL's feature set, it is the translator's coverage. Every SQL capability you actually need — lateral joins, recursive CTEs, window frame clauses — has to be individually mapped, released, and adopted before you can express it.

Syntax-Oriented Builders Are Cheap Until They Are Not

A syntax-oriented builder has two structural advantages. It is trivial to implement — the API is a thin set of setters over a tree, and you can generate the setters mechanically from the grammar. And it is complete by construction, because anything SQL can express, the tree can hold as a raw fragment.

That second property is a trap. Completeness is achieved by allowing escape hatches, and escape hatches are where the guarantees end.

// GORM: the type system knows nothing about this string
db.Model(&Order{}).
    Select("customer_id, count(*) FILTER (WHERE status = 'shipped') AS shipped").
    Group("customer_id").
    Having("count(*) > ?", threshold).
    Find(&rows)

Nothing here is checked. Rename customer_id in a migration and this compiles, passes review, and fails at runtime against production. The builder's job was to assemble a string, and it assembled a string. Compare that with FunSQL, where Get.customer_id resolves against a known schema and a renamed column becomes a build failure.

The honest framing is that the two families fail in different directions:

• Data-oriented fails loudly at compile or translate time when it cannot express your intent, but its coverage is bounded by the translator.
• Syntax-oriented always accepts your intent, including the wrong intent, and defers every error to the database.

Neither failure mode is strictly better. What matters is which one your team can detect. A compile error costs minutes. A production query that returns plausible-but-wrong aggregates costs an incident.

Pushdown Is Not Free: The Cartesian Explosion

Data-oriented builders buy you a semantic model, and the semantic model buys you a class of performance bug that syntax-oriented builders structurally cannot produce. It comes from the gap between what you asked for and what the SQL engine has to do to satisfy it.

Consider two collection includes in EF Core:

var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.History)
    .ToListAsync();

The intent is unambiguous: 10 items plus 5 history rows means 15 rows. The generated SQL is a single statement with two sibling LEFT JOINs, and a relational engine has no way to emit two independent one-to-many relationships in one flat result set. It emits their cross product. One order with 10 items and 5 history rows returns 50 rows, with the order header duplicated 50 times.

In a reported production case, a single entity with ~150 section changes and ~880 question changes produced a result set of over 133,000 rows to load one object graph. The fix is one method call:

var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.History)
    .AsSplitQuery()
    .AsNoTracking()
    .ToListAsync();

AsSplitQuery() abandons the single-statement strategy. EF Core issues one query for the root plus reference navigations, then one query per collection filtered by the loaded parent keys, and stitches the graph in memory. Row count drops from ~133,000 to ~1,000 — a 99% reduction. Published measurements put the win at roughly 5–6x on wall-clock time and 5x on allocated memory when an explosion is actually present.

Read that qualifier carefully, because it is the whole point. With no collection navigations, the single query is faster: ~16.6 ms / 4.0 MB versus ~19.0 ms / 4.3 MB for the split form. Splitting adds round-trips, and round-trips cost when there is nothing to deduplicate. Blanket-applying AsSplitQuery() is a regression on your cheap endpoints.

The data-oriented builder gave you a semantically clean way to express "load this graph." The SQL engine cannot execute that expression directly, so the translator guesses a strategy, and the guess is wrong for exactly the case where the graph has two or more sibling collections.

Choosing, and the Escape Hatch You Must Budget For

Pick data-oriented when the query is a data transformation you will compose, reuse, and test in pieces. Analytical pipelines, reporting layers, and query-building code that assembles filters from user input all benefit, because the pipeline can be validated as a value before it ever touches a database. FunSQL's node-per-SQL-verb design means Bind covers correlated subqueries, Group/Partition cover aggregates and window functions, and Iterate covers recursive queries — the whole surface area, not a happy path.

Pick syntax-oriented when the query is fixed, the team already knows SQL, and you would rather read the generated statement than trust a translator. Active Record and Eloquent earn their keep on CRUD-shaped workloads where the query is short and the escape hatch is rarely needed.

The decision that actually costs money is the migration between them, so budget for it up front:

1. Count your raw fragments. Every string literal passed to Select, Where, or Having is a query the data-oriented family must be able to express natively, or it becomes a blocker. Grep for them before committing to a migration.
2. Log the generated SQL in staging. A translator's output is not the query you wrote. Row counts that exceed your domain cardinality are the signature of a pushdown mistake, and they are invisible in unit tests that run against small fixtures.
3. Gate the strategy per endpoint. Apply AsSplitQuery() where a profiler shows an explosion, not globally. The same discipline applies to projection: shape a flat DTO with Select and the explosion never forms, because there are no collections to multiply.

Order-Insensitivity Is a Silent Contract

The most expensive property of a syntax-oriented builder is not the missing type checking. It is that order-insensitivity is assumed rather than enforced, and the assumption breaks the moment two slots interact.

ORDER BY combined with LIMIT is the canonical case. In a tree, they are two slots, so a builder is free to let you set them in either order:

-- What you meant: the 10 most recent, in recency order
SELECT id, total FROM orders
ORDER BY created_at DESC
LIMIT 10;

-- What a misordered builder chain can emit: 10 arbitrary rows, sorted
SELECT id, total FROM (
  SELECT id, total FROM orders LIMIT 10
) t
ORDER BY created_at DESC;

Both statements parse. Both return 10 rows. The second returns a random subset of the table, sorted — which looks correct in a development fixture with 12 rows and is wrong against a production table with 4 million. Nothing about the builder API signalled the difference, because at the tree level nothing was different.

LINQ has the same hazard from the opposite direction. The classic query below moves ordering to the outer statement to satisfy SQL's grammar:

var page = await db.Customers
    .Where(c => c.City == "London")
    .OrderBy(c => c.CustomerId)
    .Skip(1)
    .Take(1)
    .ToListAsync();

The generated SQL wraps the ordered, limited projection in a correlated subquery and re-applies ORDER BY at the outer level. It works, but only because Skip and Take are chained consistently with OrderBy. Chain them inconsistently and the result is undefined — the database is permitted to return any row, and different query plans will return different ones.

This is the practical argument for treating the classification as an architectural decision rather than a style preference. A data-oriented builder makes ordering semantics explicit because the pipeline is the semantics. A syntax-oriented builder hides them behind slot assignment, which means correctness depends on a developer remembering a rule the type system never encoded.

The mitigation is unglamorous and effective: pin the generated SQL in a test. Run ToQueryString() or the equivalent, assert on the exact statement, and diff it in review. Query builders are compilers, and compilers deserve golden-file tests.