GEP-26

The backend is a dual offering: a built-in native provider with no external dependencies and an optional JOOQ-backed provider, both sitting behind one user-facing configuration surface and sharing one intermediate model.

Both providers reuse stages 1 and 2 of the existing pipeline unchanged. The SQL-specific work begins at the visitor: instead of emitting Queryable call chains, the SQL visitor walks the GinqExpression into a provider-independent SqlQuery model (an intermediate representation, or IR). A renderer then turns that IR into something executable. There are two renderers, one per provider, and they are the only provider-specific code of any size.

Both renderers run at compile time and produce the same artefact: a constant SQL string plus an ordered list of parameter expressions. Execution is therefore identical for both providers — a thin runtime wrapper around groovy.sql.Sql returning Queryable<GroovyRowResult> — and JOOQ never needs to be on the runtime classpath.

The IR is the central design asset. By understanding GINQ semantics once and recording the result in a neutral shape, each renderer is reduced to a mechanical tree-to-text (native) or tree-to-tree (JOOQ) mapping. A sketch:

A SqlParam never carries a literal value into rendered SQL; it carries the source expression (literal or captured variable) whose value is bound at runtime. Positional order is assigned by the renderer as it emits each ? placeholder, guaranteeing the parameter list always matches the emitted text — essential because dialects may reorder placeholders (for example, one dialect’s LIMIT ? OFFSET ? versus ANSI OFFSET ? ROWS FETCH NEXT ? ROWS ONLY swap the two values). This is what makes both renderers injection-safe for free.

The one deliberate exception is SqlLiteral, which inlines a value into the SQL text. It is only ever constructed from compile-time constants in the query source — never from runtime values — and exists because databases cannot infer a type for constructs such as CASE WHEN …​ THEN ? ELSE ? END where every branch is a placeholder. The parameter-safety guarantee is unchanged.

A new high-level provider configuration key selects the backend. It is sugar over the existing low-level astWalker key, which remains available as an escape hatch for custom visitors.

The JOOQ provider additionally accepts a dialect key naming one of jOOQ’s SQLDialect values (for example 'POSTGRES', 'MYSQL', 'H2'); with no dialect, jOOQ’s DEFAULT dialect is used. Rather than growing the core option list for every provider, the provider registry SPI lets each provider declare the extra option names it accepts (GinqProvider.getAdditionalOptionNames()); option validation consults registered providers, so provider-specific options remain out of core GINQ.

The annotation gains one new optional member, provider, alongside the existing value, optimize, parallel, and astWalker members. Adding an annotation member with a default is binary compatible (see Compatibility and impact). Because a dataSource is a runtime value, it cannot be expressed as an annotation member; database-backed queries therefore use the macro form. (Ergonomic @GQ support — for example an annotation member naming a field or getter in scope — is a possible future refinement.)

A dataSource configuration key supplies the connection. Because both providers execute through the same runtime, both accept the same types: groovy.sql.Sql (used as-is; the caller owns its lifecycle), javax.sql.DataSource (a connection is borrowed and released per query), or java.sql.Connection (used without closing).

In the collection provider, from e in employees binds employees to a runtime collection variable. In the SQL providers, the data source of a from/join is a table name, written as a string literal:

String literals are the initial, simplest form and match how dynamic SQL builders work. Schema-qualified names in dotted form pass through unquoted; a dedicated table(…​) helper for quoting-aware qualification is deferred. Type-driven table inference (from e in Employee) is explicitly deferred (see Excluded and deferred features). A nested GINQ query may also appear as a from/join source, becoming a SQL derived table (see Set operations and subqueries).

Each GINQ clause maps to its SQL counterpart:

The core of the work is translating Groovy expressions used in where, on, having, and select into SqlExpr nodes. The translator handles a defined vocabulary and rejects the rest:

The translator starts with comparisons, boolean logic, null checks, in lists, arithmetic, and the standard aggregate functions. Method-call mapping is a lookup table extended incrementally; the initial vocabulary covers toUpperCase/toLowerCase (UPPER/LOWER), trim (TRIM), length (CHAR_LENGTH), substring (SUBSTRING, converting Groovy’s 0-based/exclusive indices), replace (REPLACE), string concatenation with + (||), and contains/startsWith/endsWith, which map to LIKE with wildcard characters in the runtime value escaped so the match is always literal. Ternary expressions become searched CASE WHEN expressions (the Elvis operator is rejected: its truthiness semantics do not map to SQL null handling). Anything outside the recognised vocabulary triggers a compile-time error in the default strict mode, or is deferred to in-memory evaluation in mixed mode (see Mixed-mode execution).

Every literal and every captured (enclosing-scope) variable becomes a positional bound parameter; only column/table identifiers and structural keywords are ever emitted into the SQL text. Property access on an alias variable (e.salary) renders as a column reference; a literal or free variable (50000, threshold) renders as ? with the value collected into a runtime parameter list.

Because user values never enter the SQL text, SQL injection is not merely discouraged but structurally prevented — the same guarantee groovy.sql.Sql provides for parameterised queries.

The SQL providers support the groupby …​ into g form, which maps directly to GROUP BY plus aggregate functions:

The translation is direct pattern matching: g.key or g.dept becomes the GROUP BY column; g.count() becomes COUNT(*); g.sum(e → e.amount) becomes SUM(e.amount); and so on. The standard aggregates (count, sum, avg, min, max) are supported by both providers. Projected group keys automatically keep their GINQ name as the SQL column label (for example groupby e.deptId as dept …​ select dept renders e.deptId AS dept), so result rows read the same as with the collection provider.

The classic groupby form (without into) also translates — in SQL there is no implicit-variable rewriting to replicate, since grouped columns and implicit-this aggregates (select e.dept, count(), sum(e.salary)) map directly onto GROUP BY with aggregate functions. (Version 1 deferred the classic form; the reference implementation showed it to be a modest addition.)

The DSL set operations combine complete from…​select queries and map to their SQL equivalents, including left-associative chaining (Q1 union Q2 minus Q3): union to UNION, unionall to UNION ALL, intersect to INTERSECT, minus to EXCEPT.

A trailing orderby/limit on the final operand applies to the combined result, matching SQL’s reading of a trailing ORDER BY; the order keys must match columns of the final select clause and are rendered as select-list ordinals, since alias-qualified references are not visible after a set operation. orderby/limit within earlier operands are rejected. Note that the collection provider instead applies a trailing orderby to the final operand before combining; the SQL reading is proposed here as the more useful and less surprising semantics for database execution, but the divergence is called out for review.

Nested GINQ queries render as SQL subqueries in both providers:

Subqueries in expression positions may be correlated, i.e. reference the aliases of enclosing queries — in SQL translation this is pure name resolution, requiring none of the execution machinery correlation demands of the collection provider. Derived tables cannot be correlated, matching standard SQL.

Ships inside groovy-ginq-sql with no external dependencies and is licensed under ASL2. It renders the IR to an ANSI SQL string with positional parameters and executes via groovy.sql.Sql, wrapping the resulting GroovyRowResult rows as a Queryable.

Scope:

Limitations (by design):

An optional provider that uses JOOQ as a compile-time, dialect-aware SQL renderer. Users supply their own JOOQ dependency on the compile classpath; it is not needed at runtime. The provider maps the SqlQuery IR onto JOOQ’s dynamic query model (the imperative SelectQuery API — no jOOQ code generation required, which suits GINQ’s string-named tables and columns), has JOOQ render SQL for the dialect option’s SQLDialect with named parameters, and converts those back to positional ? placeholders paired with the parameter expressions in placeholder order. The result is the same artefact the native renderer produces — a constant SQL string plus ordered parameters — so execution, results, and downstream composition are identical across providers.

What JOOQ adds over native: SQL rendering and function spellings for 30+ database dialects, including dialect-aware LIMIT/OFFSET, without growing the native Dialect SPI. It also provides the natural implementation path for window functions, which remain deferred (see Excluded and deferred features). If a query names the jooq-sql provider without JOOQ on the compile classpath, compilation fails with a precise error saying so.

Some GINQ constructs have no portable SQL equivalent (for example list-collecting aggregates on many databases, window functions, or an arbitrary Groovy expression in a select). Mixed mode provides a graceful boundary, modelled on LINQ-to-SQL’s AsEnumerable():

Mixed mode is opt-in and not yet part of the reference implementation. The default for the SQL providers is strict: an untranslatable construct is a compile-time error, so users never get a silent, surprising performance cliff. Enabling mixed mode trades that strictness for completeness.

The original estimates are retained for the record; the delivered implementation landed within them.

For reference, the existing collection visitor is on the order of 1,600 lines; the shared visitor is the hardest part here, and each individual renderer is comparatively modest.

Phase 1 already delivers something useful with zero external dependencies. Phases 2 and 3 both depend only on Phase 1 (the shared IR) and not on each other. Window functions, originally listed under phase 3, are deferred (see below).

The feature is purely additive and opt-in:

GINQ’s provider/astWalker selection and the result Queryable surface are the forward-evolution points exercised by this proposal. Any effort to mark GINQ stable should keep these configuration keys explicitly open to additive growth, so that this backend (and any future provider) can be introduced compatibly. Introducing the SQL providers requires no breaking change to the GINQ surface language.

All literals and captured variables are bound as statement parameters; user values never enter SQL text. SQL injection is structurally prevented for both providers.

Push-down moves filtering, joining, grouping, and limiting into the database, typically reducing both data transfer and client memory versus materialising tables and querying them in memory. Mixed mode, when enabled, can reintroduce in-memory work for the untranslatable tail of a query; strict mode (the default) makes that trade-off explicit by refusing to compile such queries.