SQL AST comparison: Red vs SQL::Abstract

Goal

The proposal is that we use a common SQL AST (Abstract Syntax Tree) that can be shared by:

• Red

• SQL::Abstract

• Qwiratry

The hope is that, instead of having 2 or 3 incomplete SQL AST representations, we can have one that combines the best of all of them.

Update

It's likely that the sensible thing to do is to layer Qwiratry on top of SQL::Abstract, then layer Red on top of Qwiratry. So I'm going to retire this for now, but reference it in Post-SQL-TaOP-Improvements.md.

Overview

Research note for a combined SQL AST. Compares the two Raku approaches that build a query AST and then render/translate it to SQL:

Release dates from raku.land badges pages (checked 2026-08-11).

Sources: Red docs, Red architecture, FCO/Red, SQL::Abstract / Leont/sql-abstract.

1. Verdict (feature range / options)

Most first-class DML / SELECT options: SQL::Abstract.It has a portable query AST plus CTEs (WITH), window definitions, DISTINCT, HAVING, compound queries (UNION-family), row locking clauses, upsert/ON CONFLICT, multi-row INSERT, INSERT…SELECT, UPDATE…FROM, DELETE…USING, NATURAL/CROSS joins, rich Conditions coercions (ranges → BETWEEN, junctions → IN, JSON ops, etc.), and separate Postgres / SQLite / MySQL renderers (including ? vs $n placeholders on Postgres). ~3500-line core module; designed to represent any SELECT/UPDATE/INSERT/DELETE valid in both PostgreSQL and SQLite.

Widest statement kinds including schema/DDL: Red.Beyond DML it builds CREATE TABLE/VIEW, column alter/FK AST, transactions, set ops, and ORM-tied prefetch/locking, with driver translate and an actively released codebase (newer of the two). It is not a free-form SQL builder: you go through models/ResultSeq. Notable DQL gap vs Abstract: no HAVING on the Select AST.

2. The Layers

Overview

In the Proposal column, a - means that each framework does their own thing (or nothing, if preferred).

Individual Layers

User-facing API

How application code asks for a query, before any SQL exists.

This layer is where the two projects feel most different. Red presents database work as Raku collections: Person.^all.grep({ .age > 18 }).sort(*.name). Models, traits (is column, relationships), and meta-methods (^create, ^load, ^create-table) are the product; SQL is incidental. SQL::Abstract is deliberately SQL-shaped: you either pass tables/columns/conditions as Raku data to select/insert/update/delete/upsert, or chain SQL::Abstract::Builder (on → where → select → …). No models required.

API -> AST

How that request becomes typed AST nodes.

Red grows the tree by interpreting ResultSeq operations and (for grep / map blocks) by evaluating callables with column proxies whose operators build Red::AST nodes instead of Bools. That is powerful and Rakuish, but historically heuristic; a RakuAST-based path is planned. SQL::Abstract uses explicit coercions: Hashes/Lists/Pairs/Range/Junction become Conditions, Column::List, joins, CTEs, etc. The Builder path constructs the same nodes method-by-method. Abstract’s strength here is predictable data→AST mapping (e.g. 1..10 → BETWEEN, 1|2|3 → IN) without running user blocks.

AST

The dialect-neutral representation of the query (or DDL).

Both keep a real node tree rather than a bag of SQL strings. Red’s Red::AST::* spans DML and schema/admin (CreateTable/View, column alter, FK, transactions, MultiSelect union/intersect/minus, Case, JSON ops, …). Select has filter/order/group/limit/offset/lock/prefetch, but no HAVING node. SQL::Abstract’s Expression/Query AST is DML-only but denser for modern SELECT: CTEs (Common), windows, DISTINCT, HAVING, compound queries, locking, upsert/ON CONFLICT, UPDATE…FROM, DELETE…USING. For a portable query IR checklist, Abstract’s node set is the richer reference; for schema IR, only Red has first-class nodes.

Rendering (AST -> Query Dialect)

Turn the AST into dialect-specific SQL text and placeholders.

Red centralizes common SQL in Red::Driver::CommonSQL.translate, with Pg / SQLite (and related) overrides for types, placeholders, RETURNING, EXCEPT vs MINUS, JSON, and so on. Emit is coupled to “the current driver.” SQL::Abstract picks a named renderer at construction (:renderer<postgres|sqlite|mysql>), including Postgres $n vs DBI ? placeholder styles. Abstract also lists MySQL as a first-class renderer; Red’s MySQL story has historically been more WIP. Neither is a full SQL standardizer — dialect quirks still live in this layer.

Query text (eg. SQL)

The value you can inspect or hand to a DB client: text + binds.

Red usually hides this: iteration triggers translate+execute; you see SQL mainly via $*RED-DEBUG, dry-run, or driver internals. SQL::Abstract stops at a SQL::Query (.sql, .arguments) — the natural hand-off for any executor. That makes Abstract easier to reuse as a pure encoder; Red’s product is “rows/objects,” with SQL as an internal side effect.

Execution call

Who triggers running the statement.

Red owns this: prepare, bind, execute, inflate model objects (and ResultSeq laziness). SQL::Abstract does not; callers invoke a DB client themselves (DBIish, Net::Postgres, or Net::Postgres::Abstract).

Actual Execution

The DB client that talks to the database.

Red uses DBIish under the hood. SQL::Abstract has no executor of its own; typical callers use DBIish or Net::Postgres::Abstract (Abstract

• async Postgres client).

Per-model Comments

Red

model / ResultSeq methods  →  Red::AST tree  →  Red::Driver.translate  →  SQL + binds  →  execute

• User-facing API is Rakuish (Person.^all.grep({ .age > 18 }).sort(*.name)).

• ~40 AST modules; CommonSQL alone ~80 translate multis (~1000 lines), plus Pg/SQLite overrides.

• SQL is an ORM implementation detail.

SQL::Abstract

Raku Hash/List/Pair helpers  →  Expression/Query AST  →  Renderer::*  →  SQL::Query(.sql, .arguments)
# or:
SQL::Abstract::Builder.new(...).on(...).select(...).where(...).build

• Three layers: AST, coercions from ordinary Raku data, dialect renderers.

• Functional API ($abstract.select(...)) and chainable Builder.

• No ORM; no built-in execution (pair with DBIish / Net::Postgres::Abstract, etc.).

3. AST object comparison

For each SQL construct, the concrete AST type (or role) each library uses. — = no dedicated node; behaviour may still exist via driver defaults, projection tricks, model metadata, or runtime Raku code.

Qwiratry / RakuAST / SQL mapping for the same rows lives in SQL-AST-Qwiratry-Mapping.md.

Red spreads SELECT clauses across Red::AST::Select and a linked Red::AST::Chained bag on Red::ResultSeq; Abstract keeps them as typed fields on SQL::Abstract::Select (and siblings). Red joins are not separate AST nodes — joined tables appear as Red::Model-shaped entries in @.table-list with join type/on supplied via model metaclass. Abstract models joins explicitly as SQL::Abstract::Join::* implementing SQL::Abstract::Source.

Type-name prefixes in the table: Red::* and SQL::Abstract::* (nested in the SQL::Abstract unit class).

4. Statement coverage matrix

Legend: Yes = first-class; Partial = limited / dialect-only / escape-hatch; No = not in API/AST without raw/custom SQL.

5. What Red can build (detail)

5.1 DQL / DML

• Filter/project/order/limit (grep/map/sort/head/skip), aggregates, classify → GROUP BY–style results, joins/join-model, prefetch, union/intersect/minus, ResultSeq delete/update, FOR UPDATE / SKIP LOCKED.

5.2 Expressions

Large Red::Operators + Red::ColumnMethods: comparisons, arithmetic, AND/OR/NOT, LIKE helpers, IN, BETWEEN, CASE, casts, string/datetime funcs, JSON -> / remove, generic ops, Red::AST::Function.

5.3 DDL / schema

CreateTable, CreateView, column create/change/drop, FK add, constraints, comments, transactions; schema readers on drivers.

5.4 Gaps / coupling

• Model-tied; no HAVING; coarse RETURNING; no CTEs/windows/upsert as AST; block→AST historically heuristic (RakuAST planned).

6. What SQL::Abstract can build (detail)

6.1 Functional + builder APIs

• $abstract.select($from, $columns, $where, :$common, :$distinct, :$group-by, :$having, :$windows, :$compound, :$order-by, :$limit, :$offset, :$locking)

• $abstract.insert (map, multi-row list, or select), update, delete, upsert.

• SQL::Abstract::Builder: on / with (CTEs) / join / select / insert / update / delete / upsert / clause methods including distinct, windows, compound, locking, returning.

Stated goal: any SELECT/UPDATE/INSERT/DELETE valid in both PostgreSQL and SQLite (portable core); MySQL renderer also present.

6.2 Conditions & expressions

Coercions from hashes/lists/pairs:

• Operators (=, comparisons, like/not-like, JSON ->/->>/#>/#>>, arithmetic, bitwise, IS DISTINCT FROM, …).

• Nil/undefined → IS NULL; Ranges → BETWEEN; Junctions → IN; nested and/or; subquery EXISTS/IN; CASE; CAST; functions; row lists.

6.3 Advanced SELECT / DML

• CTEs (Common), window frames/clauses, compound queries, locking clauses, DISTINCT ON–style column distinction where represented.

• Upsert / ON CONFLICT targets and update actions.

• Multi-row INSERT; INSERT…SELECT; UPDATE FROM; DELETE USING; RETURNING.

• Join types including NATURAL and CROSS; subquery / function sources (incl. WITH ORDINALITY rendering paths).

6.4 Gaps

• No DDL (CREATE/ALTER TABLE, etc.) — Red still unique here among AST builders.

• No ORM models or automatic schema mapping.

• Version still 0.0.x; last raku.land release April 2024 (older than Red). Large feature surface in one module; fewer public tutorials than Red.

7. Options count (rough)

Most DML options: SQL::Abstract.Most overall SQL kinds (incl. DDL): Red.

8. Excluded SQL builders

These generate or run SQL but are out of scope here because they do not use an AST → render/translate pipeline (the criterion for this note).

Also not AST builders (hand-written or non-generative SQL): Badger (.sql files → Raku subs), Slang::SQL (embed SQL in Raku), SQL::Lexer (lexical grammar only). Marrow reads schemas and generates Cro apps; it does not emit query SQL. Net::Postgres::Abstract wraps SQL::Abstract rather than defining its own AST.

9. Source pointers

Generated from upstream sources and raku.land release metadata checked 2026-08-11.