Modern abstraction over SQL

Reusable abstractions

Names, relation types, extension methods, extension columns, and macros for reuse.

Typed column lists

A table-shaped type can stand in for a repeated column list.

query.btrql 
type PublicUser = [id: BIGINT, name: TEXT]​users  .select[PublicUser]​archived_users  .select[PublicUser]
generated/query.sql 
SELECT  id,  nameFROM users;SELECT  id,  nameFROM archived_users;

Symbolic relation results in Outline

Extension-method details bind open relation variables explicitly. T0 is always the receiver, while T1, T2, and later variables follow table-kind argument order. Tn is never a scalar type. Documentation uses ScalarType0, ScalarType1, and so on only when a scalar is intentionally schematic; when inference knows the type, Outline prints the concrete name such as INT, DECIMAL, or TEXT. Both table-kind and scalar-kind argument groups are always shown, even when one group is empty.

T0=[id: INT, amount: DECIMAL].()() => T0T0=[id: INT].(other: T1=[id: INT])() => T0 ++ T1T0=[id: INT].(left: T1=[id: INT], right: T2=[id: INT])() => T0 ++ T1 ++ T2​

Set operations keep their left symbolic operand: self.union(other) returns T0, while other.union(self) returns T1. ++ T1 means joined row shapes, never SQL UNION. A join whose sides are reshaped independently can render as T0 -- [bla: ScalarType1] ++ T1 -- [foo: ScalarType2] ++ [bar: ScalarType3].

Projection and grouping changes remain attached to the originating variable. Omitted fields use --, computed fields and aggregate aliases use ++, and a rename is always paired: T0 -- [old_name: TEXT] ++ [new_name: TEXT]. A type replacement is likewise T0 -- [value: INT] ++ [value: DECIMAL]. Simple grouping keys keep their original identity; non-grouped fields are removed and aggregate outputs are added. Extra call-site columns remain part of the instantiated Tn until an operation explicitly omits them.

Extension-column symbols use the restricted form [receiver] ++ [columnName: ConcreteScalarType]; their scalar dependencies are substituted into expressions but are not listed as output columns. Optional written method and column annotations are checked against normalized inference and mismatches are errors. This symbolic Outline surface is currently implemented in the PostgreSQL and ClickHouse C# prototypes.

Extension columns

column members are receiver-bound scalar expressions. Any relation satisfying the receiver contract can use them in projections, filters, joins, grouping, ordering, windows, and DML expressions. Requesting a bare extension column from select or addColumn gives it the member name. Dependencies are substituted into the requested expression but are not added as output columns automatically. The PostgreSQL and ClickHouse C# compilers implement this surface.

Relation methods and scalar columns share one receiver contract; one extension column can depend on another without projecting the dependency.

query.btrql 
using analytics​type EngagementInput = [  posts_viewed: INT,  comments_written: INT]​extension EngagementInput {  method activeOnly =    self      .where(is_engaged)​  method withMinimumPosts()(minPosts: INT) =    self      .where(posts_viewed >= minPosts)​  column engagement_score: INT =    posts_viewed + comments_written * 5​  column is_engaged: BOOLEAN =    posts_viewed > 10 and comments_written > 0​  column engagement_label =    case when is_engaged then 'active' else 'inactive' end}​view engaged_users =  users    .activeOnly()    .withMinimumPosts()(5)    .addColumn(engagement_score, engagement_label)
generated/postgresql.sql 
CREATE VIEW "engaged_users" ASSELECT  *,  "_q3"."posts_viewed" + "_q3"."comments_written" * 5 AS "engagement_score",  CASE    WHEN "_q3"."posts_viewed" > 10 AND "_q3"."comments_written" > 0    THEN 'active'    ELSE 'inactive'  END AS "engagement_label"FROM (  SELECT *  FROM (    SELECT      "users"."user_id",      "users"."posts_viewed",      "users"."comments_written"    FROM "analytics"."users" AS "users"  ) AS "_q1"  WHERE "_q1"."posts_viewed" > 10 AND "_q1"."comments_written" > 0) AS "_q3"WHERE "posts_viewed" >= 5;

Macros

A macro expands a checked source template before normal compilation.

query.btrql 
macro activeView(src: UserShape)(out: identifier) =  <{ view $out = $src.where(active == TRUE) }>​activeView(users)(active_users)
generated/query.sql 
CREATE VIEW active_users ASSELECT *FROM usersWHERE active = TRUE;

val

val names a scalar expression for reuse in later BtrQL source.

query.btrql 
val activeOnly = active == TRUE​users  .where(activeOnly)  .select(id)​archived_users  .where(activeOnly)  .select(id)
generated/query.sql 
SELECT idFROM usersWHERE active = TRUE;​SELECT idFROM archived_usersWHERE active = TRUE;

type

A relation type names a reusable row contract. The same row shape can drive extension-method receivers and repeated projections.

query.btrql 
type CustomerCard = [id: BIGINT, name: TEXT, region: VARCHAR]​extension CustomerCard {  method inRegion()(targetRegion: VARCHAR) =    self      .where(region == targetRegion)}​users  .select[CustomerCard]​archived_users  .where(active == TRUE)  .select[CustomerCard]​customers  .inRegion()('EMEA')  .select[CustomerCard]
generated/query.sql 
SELECT  id,  name,  regionFROM users;​SELECT  id,  name,  regionFROM archived_usersWHERE active = TRUE;​SELECT  id,  name,  regionFROM customersWHERE region = 'EMEA';

extension

An extension adds checked relation methods to a row shape. Extension methods and extension columns use a receiver contract: a compatible relation can reuse the member, and a method expands inline before SQL is emitted.

query.btrql 
type LeaderboardRow = [  customer_id: BIGINT,  customer_name: TEXT,  region: VARCHAR,  sales_rep_name: TEXT,  order_id: BIGINT,  amount: DECIMAL,  regional_paid_amount: DECIMAL,  regional_rank: INT]​extension OrderShape {  method regionalLeaderboard()(minAmount: DECIMAL) =    self      .as(o)      .innerJoin(customers.as(c) on o.customer_id == c.id)      .leftJoin(sales_reps.as(rep) on c.sales_rep_id == rep.id)      .where(o.status == 'paid' and o.amount >= minAmount)      .select[LeaderboardRow](        c.id -> customer_id,        c.name -> customer_name,        c.region,        rep.name -> sales_rep_name,        o.order_id,        o.amount,        sum(o.amount)          .over {          partitionBy(c.region)        } -> regional_paid_amount,        row_number()          .over {          partitionBy(c.region)          orderBy(o.amount.desc, o.ordered_at.desc)        } -> regional_rank      )}​val enterpriseOrders =  orders    .where(order_kind == 'enterprise')    .regionalLeaderboard()(500)    .where(regional_rank <= 3)​val renewalOrders =  orders    .where(order_kind == 'renewal')    .regionalLeaderboard()(250)    .where(regional_paid_amount >= 2500)​val expansionOrders =  orders    .where(order_kind == 'expansion')    .regionalLeaderboard()(100)    .where(regional_rank <= 5)​enterpriseOrders  .unionAll(renewalOrders)  .unionAll(expansionOrders)  .orderBy(region.asc, regional_rank.asc)
generated/query.sql 
SELECT  leaderboard.customer_id,  leaderboard.customer_name,  leaderboard.region,  leaderboard.sales_rep_name,  leaderboard.order_id,  leaderboard.amount,  leaderboard.regional_paid_amount,  leaderboard.regional_rankFROM (  SELECT *  FROM (    SELECT      c.id AS customer_id,      c.name AS customer_name,      c.region,      rep.name AS sales_rep_name,      o.order_id,      o.amount,      SUM(o.amount) OVER (        PARTITION BY c.region      ) AS regional_paid_amount,      ROW_NUMBER() OVER (        PARTITION BY c.region        ORDER BY o.amount DESC, o.ordered_at DESC      ) AS regional_rank    FROM orders AS o    INNER JOIN customers AS c      ON o.customer_id = c.id    LEFT JOIN sales_reps AS rep      ON c.sales_rep_id = rep.id    WHERE      o.order_kind = 'enterprise'      AND o.status = 'paid'      AND o.amount >= 500  ) AS enterprise_ranked  WHERE enterprise_ranked.regional_rank <= 3​  UNION ALL​  SELECT *  FROM (    SELECT      c.id AS customer_id,      c.name AS customer_name,      c.region,      rep.name AS sales_rep_name,      o.order_id,      o.amount,      SUM(o.amount) OVER (        PARTITION BY c.region      ) AS regional_paid_amount,      ROW_NUMBER() OVER (        PARTITION BY c.region        ORDER BY o.amount DESC, o.ordered_at DESC      ) AS regional_rank    FROM orders AS o    INNER JOIN customers AS c      ON o.customer_id = c.id    LEFT JOIN sales_reps AS rep      ON c.sales_rep_id = rep.id    WHERE      o.order_kind = 'renewal'      AND o.status = 'paid'      AND o.amount >= 250  ) AS renewal_ranked  WHERE renewal_ranked.regional_paid_amount >= 2500​  UNION ALL​  SELECT *  FROM (    SELECT      c.id AS customer_id,      c.name AS customer_name,      c.region,      rep.name AS sales_rep_name,      o.order_id,      o.amount,      SUM(o.amount) OVER (        PARTITION BY c.region      ) AS regional_paid_amount,      ROW_NUMBER() OVER (        PARTITION BY c.region        ORDER BY o.amount DESC, o.ordered_at DESC      ) AS regional_rank    FROM orders AS o    INNER JOIN customers AS c      ON o.customer_id = c.id    LEFT JOIN sales_reps AS rep      ON c.sales_rep_id = rep.id    WHERE      o.order_kind = 'expansion'      AND o.status = 'paid'      AND o.amount >= 100  ) AS expansion_ranked  WHERE expansion_ranked.regional_rank <= 5) AS leaderboardORDER BY leaderboard.region ASC, leaderboard.regional_rank ASC;