Core Ideas | BtrQL

BtrQL reuse is compile-time source organization. Modules, relation types, table-type column lists, extension methods, extension columns, val bindings, compile-time macros, and project compilation keep shared logic in checked BtrQL while generated SQL stays explicit.

Extension methods and extension columns use receiver-oriented reuse: a named row-shape contract owns reusable relation operations and scalar computations that apply to any compatible relation.

Reusable relation logic

This example shows reusable relation types and extension methods called from multiple use sites before SQL lowering.

source.btrql

using analyticsval cutoff = 7 + 3type AuditCols = [id: INT, created_at: TIMESTAMP]type UserShape = AuditCols ++ [name: VARCHAR, active: BOOLEAN]extension UserShape {  method keepRecent =    self      .where(id >= cutoff)}extension [id: INT] {  method keepRecentIds =    self      .where(id >= cutoff)}view recent_users =  users    .keepRecent()view recent_active_users =  users    .keepRecent()    .where(active == TRUE)    .select(id, name)view recent_user_ids =  users    .keepRecentIds()    .select(id)view recent_named_ids =  users    .keepRecentIds()    .where(name != '')    .select(id)

schemas.json

{  "analytics": {    "tables": {      "users": [        { "name": "id", "type": "INTEGER" },        { "name": "name", "type": "VARCHAR" },        { "name": "active", "type": "BOOLEAN" },        { "name": "created_at", "type": "TIMESTAMP" }      ]    }  }}

postgresql.sql

CREATE VIEW "recent_users" ASSELECT *FROM (  SELECT    "users"."id",    "users"."name",    "users"."active",    "users"."created_at"  FROM "analytics"."users" AS "users") AS "_q1"WHERE "id" >= 10;CREATE VIEW "recent_active_users" ASSELECT  "id",  "name"FROM (  SELECT    "users"."id",    "users"."name",    "users"."active",    "users"."created_at"  FROM "analytics"."users" AS "users") AS "_q3"WHERE  "id" >= 10  AND "active" = TRUE;CREATE VIEW "recent_user_ids" ASSELECT "id"FROM (  SELECT    "users"."id",    "users"."name",    "users"."active",    "users"."created_at"  FROM "analytics"."users" AS "users") AS "_q5"WHERE "id" >= 10;CREATE VIEW "recent_named_ids" ASSELECT "id"FROM (  SELECT    "users"."id",    "users"."name",    "users"."active",    "users"."created_at"  FROM "analytics"."users" AS "users") AS "_q7"WHERE  "id" >= 10  AND "name" <> '';

clickhouse.sql

CREATE VIEW `recent_users` ASSELECT *FROM (  SELECT    `users`.`id`,    `users`.`name`,    `users`.`active`,    `users`.`created_at`  FROM `analytics`.`users` AS `users`) AS `_q1`WHERE `id` >= 10;CREATE VIEW `recent_active_users` ASSELECT  `id`,  `name`FROM (  SELECT    `users`.`id`,    `users`.`name`,    `users`.`active`,    `users`.`created_at`  FROM `analytics`.`users` AS `users`) AS `_q3`WHERE  `id` >= 10  AND `active` = TRUE;CREATE VIEW `recent_user_ids` ASSELECT `id`FROM (  SELECT    `users`.`id`,    `users`.`name`,    `users`.`active`,    `users`.`created_at`  FROM `analytics`.`users` AS `users`) AS `_q5`WHERE `id` >= 10;CREATE VIEW `recent_named_ids` ASSELECT `id`FROM (  SELECT    `users`.`id`,    `users`.`name`,    `users`.`active`,    `users`.`created_at`  FROM `analytics`.`users` AS `users`) AS `_q7`WHERE  `id` >= 10  AND `name` <> '';

Relation types and extension columns

A relation type names a reusable row contract. It can drive typed projections, grouped keys, column removal, and extension receivers.

type AuditCols = [id: INT, created_at: TIMESTAMP]type UserShape = AuditCols ++ [name: VARCHAR, active: BOOLEAN]users  .select[UserShape]

An extension column names a receiver-bound scalar expression. A relation that satisfies the extension's minimum row contract can use that column in filters or request it by name in select and addColumn. Dependencies such as is_engaged are substituted into later extension-column expressions, but they are not added to the output unless requested.

using analyticstype 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)

This example compiles with the PostgreSQL and ClickHouse C# prototypes when the analytics.users schema supplies the receiver columns. The former column recipe declaration and addColumn[Type] request are removed syntax and produce migration diagnostics.

Table type algebra

An extension method has a symbolic table return type. T0 is the open type of the receiver (self); T1, T2, and later variables are table-kind parameters in declaration order. A variable is open: call-site columns beyond its minimum receiver contract remain present until a projection closes that variable. ScalarType0, ScalarType1, and later names are schematic scalar slots; when the compiler knows a scalar type, Outline shows the concrete type instead.

Form	Meaning
`A ++ B`	concatenate table shapes, or add the columns in `B`
`A -- B`	remove the named, type-compatible columns in `B`
`A && B`	keep the columns shared by both declared table types

Derived return types normally normalize to open variables plus ordered ++ and -- deltas. Read the deltas in pipeline order. A rename is therefore a removal followed by an addition, and replacing a column's scalar type uses the same pair.

receiver        table argument        computed delta         removal delta   T0       ++       T2         ++ [newColumn: ScalarType1] -- [removedColumn: ScalarType2]    └────────────────────────────── pipeline result ──────────────────────────────┘=> T0 ++ T2 ++ [newColumn: ScalarType1] -- [removedColumn: ScalarType2]

For example, an extension body that joins its second table argument, adds newColumn, and removes removedColumn produces exactly that return type. ++ T2 means row-shape concatenation; it does not mean SQL set union.

Return-type diagrams for table methods

The diagrams below cover every table method classified by extension-method return inference. Methods with the same shape rule are grouped together. Tn means whichever open table variable is on the left of the call, R is a right-side relation result, and R? is the nullable-relation constructor defined in the join section below.

Shape-preserving methods

Filtering, ordering, validation, paging, aliases, native clauses, and DML commands preserve the incoming table type. DML keeps this type until a returning step projects another shape.

Tn ── where | qualify | orderBy | distinct | distinctOn | rowShape   ── limit | offset | as   ── prewhere | sample | limitBy | settings | format   ── insert | update | delete | deleteFrom | mergeInto ─────────────▶ Tn

Set methods

union, unionAll, intersect, and except return the type of their left operand. They validate the right operand but do not concatenate it.

self:T0  ── union | unionAll | intersect | except (other:T1) ──▶ T0other:T1 ── union | unionAll | intersect | except (self:T0)  ──▶ T1

Column delta methods

addColumn appends new columns and replaces a collision with an ordered remove/add pair. removeColumn(...) and removeColumn[Type] subtract columns. ClickHouse arrayJoin and leftArrayJoin retain the input columns and append each named element alias.

Tn ── addColumn(expr -> newColumn) ─────────────▶ Tn ++ [newColumn: ScalarType0]Tn ── removeColumn(oldColumn) ──────────────────▶ Tn -- [oldColumn: ScalarType1]Tn ── addColumn(expr -> existingColumn) ────────▶ Tn -- [existingColumn: OldType]                                                    ++ [existingColumn: NewType]Tn ── arrayJoin(items -> item) ─────────────────▶ Tn ++ [item: ElementType]Tn ── leftArrayJoin(items -> item) ─────────────▶ Tn ++ [item: ElementType]

Projection methods

Projection first decides which input columns survive under their original names. The removed columns are the complement of the preserved columns; they are not a second set of changes.

Tn ── select / select[Type] / returning / returningWith   ── (preserve existing columns under their original names)   ──▶ Tn -- [columns not preserved under their original names]       ≡ [preserved columns with their original names and types]

A computed projection output is added after that closing projection.

Tn ── select / returning / returningWith (..., expression -> result)   ──▶ Tn -- [columns not preserved under their original names]           ++ [result: ExpressionType]

Rename-only algebra

A direct-column alias is a rename. Its type rule is independent of the projection or grouping operation that contains it.

Tn ── sourceColumn -> renamedColumn ──▶ Tn -- [sourceColumn: SourceType]                                             ++ [renamedColumn: SourceType]

Inside a projection, sourceColumn is already among the columns not preserved under their original names. The normalized projection therefore removes it once and adds renamedColumn; it does not apply a second removal.

Grouping methods

Grouping preserves direct keys under their original names, removes non-keys, and adds aggregate results. If a key is aliased, apply the rename-only rule above to that key separately.

Tn ── group { by(key columns) aggregate(...) } / group { by[Type] ... }   ──▶ Tn -- [every non-key column]           ++ [aggregate result columns: ResultTypes]

Join and APPLY methods

Each rule below is independent. In a symbolic relation result, X? means "the same columns as X, with every scalar made nullable." A bare X keeps the columns' existing nullability.

innerJoin / crossJoin : T0 ++ T1leftJoin              : T0 ++ T1?rightJoin             : T0? ++ T1fullJoin              : T0? ++ T1?crossApply(R)          : T0 ++ RouterApply(R)          : T0 ++ R?

An inner or cross join keeps both sides as they are. A left join can produce a left row with no matching right row, so only T1 becomes nullable. A right join is the mirror image; a full join may lack either side. Likewise, outerApply retains a left row even when its dependent result has no row, so the columns contributed by R become nullable.

The compiler and Outline now preserve this as a first-class symbolic relation type: an extension method containing a left join renders T0 ++ T1?, not a remove/add replacement for every column in T1. Instantiating T1? makes all bound T1 columns nullable, including extra call-site columns beyond the method's minimum contract. Nested extension calls preserve the wrapper during type substitution.

Tn? is result-type notation, not BtrQL source syntax. PostgreSQL renders the resulting nullable scalar details as forms such as INTEGER?; ClickHouse uses forms such as Nullable(Int32). A declared table function has a closed, concrete return shape and no open Tn variable, so its result continues to show per-column scalar types. If table functions later gain table-kind parameters, the same Tn? relation algebra can apply to them. There is no established ! suffix; a bare relation variable means "preserve current nullability."

jsonTable and rowsFromWithOrdinality contribute their declared result columns through the APPLY rule. outerApply itself is available only in dialects that support that operation.

Extension-method composition

Think of an extension method as a reusable row-shape transformation:

T0 stands for the actual relation to the left of the method call.
T1, T2, and later variables stand for table arguments in declaration order.
A call binds those placeholders to the receiver and arguments at that point in the pipeline.
The method's additions and removals are applied, and the next call continues from that result.

For a simple table-argument call, the binding is:

call:    current_users.join_orders(current_orders)()bind:    T0 = current_users, T1 = current_ordersresult:  current_users ++ current_orders

Here is a complete example that compiles with both C# prototypes:

using analyticsextension [user_id: INTEGER, user_name: VARCHAR, region: VARCHAR] {  method only_region()(wanted_region: VARCHAR) =    self      .where(region == wanted_region)  method join_orders(other: [    order_id: INTEGER,    buyer_id: INTEGER,    amount: INTEGER,    status: VARCHAR  ]) =    self      .innerJoin(other on user_id == buyer_id)  method reshaped_join(other: [    order_id: INTEGER,    buyer_id: INTEGER,    amount: INTEGER,    status: VARCHAR  ]) =    self      .removeColumn(region)      .crossJoin(        other          .removeColumn(buyer_id)          .addColumn(amount + 1 -> adjusted_amount)      )}view regional_orders =  users    .only_region()('EU')    .join_orders(orders)()view public_order_rows =  users    .reshaped_join(orders)()

Read its inferred shapes in ordinary language:

only_region filters rows and keeps T0 unchanged.
join_orders appends the table argument, producing T0 ++ T1.
reshaped_join keeps each change attached to its source: T0 -- [region: VARCHAR] ++ T1 -- [buyer_id: INTEGER] ++ [adjusted_amount: INTEGER].

regional_orders shows call-site composition: filter users, then join orders. The first call does not change the row shape, so the second call still receives the complete user relation as its T0. public_order_rows invokes the origin-preserving reshape so that example is compiled too.

An extension column is the scalar special case: requesting column score =... has symbolic result T0 ++ [score: ConcreteScalarType].

Modules and projects

BtrQL modules are file-based. A source file such as input/reporting/shared.btrql becomes module reporting.shared, and configured source roots decide where module lookup begins.

import reporting.shared { base_users -> users_base }using analyticsview active_users =  users_base    .where(active == TRUE)

A project ties source files, generated output, target dialect, and schema metadata together.

{  "output-folder": "generated",  "target-dialect": "postgresql"}

The schema cache stores database metadata used for offline checks: schemas, tables, columns, types, and callable signatures. It does not store table rows. A project build follows imports in dependency order, writes generated SQL, and reports missing modules, duplicate names, import cycles, stale schema references, and diagnostics before deployment.

Post-compile actions

Project compiler integrations should expose enough result data for post-compile work. The main project compiler supports configured post-compile hooks after SQL generation and reports hook diagnostics and artifacts.

The native project APIs currently return compiled modules with source paths, output paths, SQL, diagnostics, output roots, and relation-shape maps after CompileDirectory(...), so callers can attach their own post-compile steps. Those APIs do not currently run configured hook files themselves; their editor build responses expose successful default post-compile status when no hook runner is configured.