Walkthroughs

Use walkthroughs after the syntax pages. Each example keeps BtrQL source on the left and generated SQL on the right, so you can see how several constructs work together.

Recommended order:

Query walkthroughs for projection, filtering, ordering, grouping, joins, set operations, windows, and shared helper patterns.
Modules and projects for multi-file source and project-level compilation.
Cross-dialect workflows for syntax that changes shape between SQL targets.

Short single-construct examples live in Syntax overview. Diagnostic examples are covered in Errors.

Query walkthroughs

BtrQL source patterns that combine multiple syntax elements with generated SQL.

Query Pipeline

This example shows a bare top-level executable statement with a compact where -> addColumn -> orderBy -> limit pipeline.

source.btrql

using analyticsusers  .where(active == TRUE)  .addColumn(active -> active_flag)  .orderBy(created_at.desc)  .limit(10)

schemas.json

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

postgresql.sql

SELECT  "users"."id",  "users"."name",  "users"."active",  "users"."created_at",  "users"."active" AS "active_flag"FROM "analytics"."users" AS "users"WHERE "users"."active" = TRUEORDER BY "created_at" DESCFETCH NEXT 10 ROWS ONLY;

clickhouse.sql

SELECT  `users`.`id`,  `users`.`name`,  `users`.`active`,  `users`.`created_at`,  `users`.`active` AS `active_flag`FROM `analytics`.`users` AS `users`WHERE `users`.`active` = TRUEORDER BY `created_at` DESCLIMIT 10;

Joins, WITH Bindings, And Set Operations

This example shows statement-local with {... } bindings feeding a unionAll(...) composition and then a joined query with an explicit projection and orderBy(...).

source.btrql

using analyticswith {  active_users =    users      .where(active == TRUE)      .select(id, name)  inactive_users =    users      .where(active == FALSE)      .select(id, name)  selected_users =    active_users      .unionAll(inactive_users)}selected_users  .innerJoin(orders on selected_users.id == orders.user_id)  .select(    selected_users.id -> user_id,    selected_users.name,    orders.order_id,    orders.amount  )  .orderBy(orders.amount.desc)

schemas.json

{  "analytics": {    "tables": {      "users": [        { "name": "id", "type": "INTEGER" },        { "name": "name", "type": "VARCHAR" },        { "name": "active", "type": "BOOLEAN" }      ],      "orders": [        { "name": "order_id", "type": "INTEGER" },        { "name": "user_id", "type": "INTEGER" },        { "name": "amount", "type": "DECIMAL" }      ]    }  }}

postgresql.sql

WITH "active_users" AS (  SELECT    "users"."id",    "users"."name"  FROM "analytics"."users" AS "users"  WHERE "users"."active" = TRUE),"inactive_users" AS (  SELECT    "users"."id",    "users"."name"  FROM "analytics"."users" AS "users"  WHERE "users"."active" = FALSE),"selected_users" AS (  SELECT *  FROM "active_users"  UNION ALL  SELECT *  FROM "inactive_users")SELECT  "selected_users"."id" AS "user_id",  "selected_users"."name",  "orders"."order_id",  "orders"."amount"FROM "selected_users"INNER JOIN "analytics"."orders" AS "orders"  ON "selected_users"."id" = "orders"."user_id"ORDER BY "orders"."amount" DESC;

clickhouse.sql

WITH `active_users` AS (  SELECT    `users`.`id`,    `users`.`name`  FROM `analytics`.`users` AS `users`  WHERE `users`.`active` = TRUE),`inactive_users` AS (  SELECT    `users`.`id`,    `users`.`name`  FROM `analytics`.`users` AS `users`  WHERE `users`.`active` = FALSE),`selected_users` AS (  SELECT *  FROM `active_users`  UNION ALL  SELECT *  FROM `inactive_users`)SELECT  `selected_users`.`id` AS `user_id`,  `selected_users`.`name`,  `orders`.`order_id`,  `orders`.`amount`FROM `selected_users`INNER JOIN `analytics`.`orders` AS `orders`  ON `selected_users`.`id` = `orders`.`user_id`ORDER BY `orders`.`amount` DESC;

UNION Column Order

This example shows that BtrQL aligns a unionAll right-hand query by column name before emitting SQL, so positional SQL set operations keep the left-hand column order.

source.btrql

using analyticsview reordered_union =  users    .select(id, name)    .unionAll(      users        .select(name, id)    )

schemas.json

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

postgresql.sql

CREATE VIEW "reordered_union" ASSELECT  "users"."id",  "users"."name"FROM "analytics"."users" AS "users"UNION ALLSELECT  "id",  "name"FROM (  SELECT    "users"."name",    "users"."id"  FROM "analytics"."users" AS "users") AS "_q1";

clickhouse.sql

CREATE VIEW `reordered_union` ASSELECT  `users`.`id`,  `users`.`name`FROM `analytics`.`users` AS `users`UNION ALLSELECT  `id`,  `name`FROM (  SELECT    `users`.`name`,    `users`.`id`  FROM `analytics`.`users` AS `users`) AS `_q1`;

Grouped Queries, Aggregates, And Builtins

This example shows grouped projections, aggregate builtin functions, having(...), and an orderBy(...) applied after aggregation.

source.btrql

using analyticswith {  normalized_orders =    orders      .select(        order_id,        lower(coalesce(status, 'unknown')) -> normalized_status,        round(amount) -> rounded_amount,        amount      )}normalized_orders  .group {    by(normalized_status)    aggregate(      count(order_id) -> order_count,      sum(rounded_amount) -> rounded_total_amount,      avg(amount) -> average_amount,      min(amount) -> minimum_amount,      max(amount) -> maximum_amount    )    having(count(order_id) > 1)  }  .orderBy(normalized_status.asc)

schemas.json

{  "analytics": {    "tables": {      "orders": [        { "name": "order_id", "type": "INTEGER" },        { "name": "status", "type": "VARCHAR" },        { "name": "amount", "type": "DECIMAL" }      ]    }  }}

postgresql.sql

WITH "normalized_orders" AS (  SELECT    "orders"."order_id",    LOWER(COALESCE("orders"."status", 'unknown')) AS "normalized_status",    ROUND("orders"."amount") AS "rounded_amount",    "orders"."amount"  FROM "analytics"."orders" AS "orders")SELECT  "normalized_status",  COUNT("order_id") AS "order_count",  SUM("rounded_amount") AS "rounded_total_amount",  AVG("amount") AS "average_amount",  MIN("amount") AS "minimum_amount",  MAX("amount") AS "maximum_amount"FROM "normalized_orders"GROUP BY "normalized_status"HAVING COUNT("order_id") > 1ORDER BY "normalized_status";

clickhouse.sql

WITH `normalized_orders` AS (  SELECT    `orders`.`order_id`,    LOWER(COALESCE(`orders`.`status`, 'unknown')) AS `normalized_status`,    ROUND(`orders`.`amount`) AS `rounded_amount`,    `orders`.`amount`  FROM `analytics`.`orders` AS `orders`)SELECT  `normalized_status`,  COUNT(`order_id`) AS `order_count`,  SUM(`rounded_amount`) AS `rounded_total_amount`,  AVG(`amount`) AS `average_amount`,  MIN(`amount`) AS `minimum_amount`,  MAX(`amount`) AS `maximum_amount`FROM `normalized_orders`GROUP BY `normalized_status`HAVING COUNT(`order_id`) > 1ORDER BY `normalized_status`;

Window And QUALIFY

This example shows a statement-local with {... } CTE, a window expression, distinct, orderBy, and qualify. PostgreSQL lowers the post-window filter through a derived-table WHERE.

source.btrql

using analyticswith {  active_users =    users      .where(active == TRUE)}active_users  .select(    id,    row_number().over {      partitionBy(id)      orderBy(id.desc)    } -> rn  )  .distinct  .orderBy(rn.desc)  .qualify(rn > 0)

schemas.json

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

postgresql.sql

SELECT * FROM (WITH "active_users" AS (  SELECT    "users"."id",    "users"."active"  FROM "analytics"."users" AS "users"  WHERE "users"."active" = TRUE)SELECT DISTINCT  "id",  ROW_NUMBER() OVER (    PARTITION BY "id"    ORDER BY "id" DESC  ) AS "rn"FROM "active_users") AS "_q1"WHERE "rn" > 0ORDER BY "rn" DESC;

clickhouse.error.txt

Dialect 'clickhouse' does not support QUALIFY: Current ClickHouse lowering rejects QUALIFY.

Schema Using And Callables

This example shows how using analytics loads both tables and callable signatures.

source.btrql

using analyticsview active_users =  users    .where(is_active(active))call ping(1)

schemas.json

{  "analytics": {    "tables": {      "users": [        { "name": "id", "type": "INTEGER" },        { "name": "name", "type": "VARCHAR" },        { "name": "active", "type": "BOOLEAN" }      ]    },    "callableSignatures": {      "is_active": "(BOOLEAN) -> BOOLEAN",      "ping": "(INTEGER)"    }  }}

postgresql.sql

CREATE VIEW "active_users" ASSELECT  "users"."id",  "users"."name",  "users"."active"FROM "analytics"."users" AS "users"WHERE "is_active"("users"."active");CALL "ping"(1);

clickhouse.error.txt

Dialect 'clickhouse' does not support procedure calls: Procedure calls are not enabled for ClickHouse in the current support surface.

Relation Type And Extension Reuse

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` <> '';

Modules and projects

Multi-file source and project-level compilation examples.

Module Imports

This example shows qualified, unqualified, and aliased access to the same imported module.

schemas.json

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

postgresql.sql

CREATE VIEW "copied_qualified" ASSELECT "reporting_shared_base"."id"FROM "reporting"."reporting_shared_base" AS "reporting_shared_base";CREATE VIEW "copied_unqualified" ASSELECT "reporting_shared_base"."id"FROM "reporting"."reporting_shared_base" AS "reporting_shared_base";CREATE VIEW "copied_aliased" ASSELECT "reporting_shared_base"."id"FROM "reporting"."reporting_shared_base" AS "reporting_shared_base";

clickhouse.sql

CREATE VIEW `copied_qualified` ASSELECT `base`.`id`FROM `base` AS `base`;CREATE VIEW `copied_unqualified` ASSELECT `base`.`id`FROM `base` AS `base`;CREATE VIEW `copied_aliased` ASSELECT `base`.`id`FROM `base` AS `base`;

Project Compilation

This example shows a minimal multi-file project compiled through project.json.

project/project.json

{  "output-folder": "generated",  "target-dialect": "postgresql",  "schema-cache-path": "schemas.json"}

project/schemas.json

{  "analytics": {    "tables": {      "users": [        { "name": "id", "type": "INTEGER" },        { "name": "name", "type": "VARCHAR" },        { "name": "active", "type": "BOOLEAN" }      ]    },    "callableSignatures": {      "ping": "(INTEGER)"    }  }}

project/src/main.btrql

using analyticsimport utils.testDependency { active_users -> imported_active }val cutoff = 10view recent_users =  imported_active    .where(id >= cutoff)    .select(id, name)

project/src/utils/testDependency.btrql

using analyticsview active_users =  users    .where(active == TRUE)

expected/postgresql/main.sql

CREATE VIEW "recent_users" ASSELECT  "active_users"."id",  "active_users"."name"FROM "active_users" AS "active_users"WHERE "active_users"."id" >= 10;

expected/postgresql/utils/testDependency.sql

CREATE VIEW "active_users" ASSELECT  "users"."id",  "users"."name",  "users"."active"FROM "analytics"."users" AS "users"WHERE "users"."active" = TRUE;

expected/clickhouse/main.sql

CREATE VIEW `recent_users` ASSELECT  `active_users`.`id`,  `active_users`.`name`FROM `active_users` AS `active_users`WHERE `active_users`.`id` >= 10;

expected/clickhouse/utils/testDependency.sql

CREATE VIEW `active_users` ASSELECT  `users`.`id`,  `users`.`name`,  `users`.`active`FROM `analytics`.`users` AS `users`WHERE `users`.`active` = TRUE;

Cross-dialect workflows

Feature workflows across multiple SQL targets.

RETURNING Across Dialects

This example shows a single UPDATE... RETURNING shape across the current public dialect set.

source.btrql

using analyticsusers  .where(active == TRUE)  .update {    into users    set(FALSE -> active)  }  .returning(id, active)

schemas.json

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

postgresql.sql

UPDATE "analytics"."users" SET  "active" = FALSEWHERE "active" = TRUERETURNING "id", "active";

clickhouse.error.txt

Dialect 'clickhouse' does not support RETURNING / OUTPUT: Current ClickHouse lowering rejects RETURNING.

Materialized Views Across Dialects

This example shows the current PostgreSQL materialized-view path and the explicit compile-time unsupported diagnostics for the rest of the current public dialect set.

source.btrql

using analyticsmaterialized view active_snapshot =  users    .where(active == TRUE)    .select(id, name)refresh materialized view active_snapshotdrop materialized view active_snapshot

schemas.json

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

postgresql.sql

CREATE MATERIALIZED VIEW "active_snapshot" ASSELECT  "users"."id",  "users"."name"FROM "analytics"."users" AS "users"WHERE "users"."active" = TRUE;REFRESH MATERIALIZED VIEW "active_snapshot";DROP MATERIALIZED VIEW "active_snapshot";

clickhouse.error.txt

Dialect 'clickhouse' does not support materialized views: Current ClickHouse lowering rejects materialized view statements.

JSON_TABLE Projection

This example documents the public jsonTable(...) surface for the currently promoted subset.

source.btrql

jsonTable('[{"status":"open"}]', '$[*]', status, TEXT, '$.status')  .select(status)

postgresql.sql

SELECT "status"FROM JSON_TABLE('[{"status":"open"}]', '$[*]' COLUMNS("status" TEXT PATH '$.status'));

mergeInto Upsert

This example documents the shared public mergeInto(...) surface that backs the promoted PostgreSQL upsert subsets.

source.btrql

using analyticsuser_import  .mergeInto(users) {    on(users.user_id == user_import.user_id)    whenMatchedUpdate(      user_import.user_name -> user_name,      user_import.active -> active,      user_import.region -> region    )    whenNotMatchedInsert(user_id, user_name, active, region)  }

schemas.json

{  "analytics": {    "tables": {      "users": [        { "name": "user_id", "type": "INTEGER" },        { "name": "user_name", "type": "VARCHAR" },        { "name": "active", "type": "BOOLEAN" },        { "name": "region", "type": "VARCHAR" }      ],      "user_import": [        { "name": "user_id", "type": "INTEGER" },        { "name": "user_name", "type": "VARCHAR" },        { "name": "active", "type": "BOOLEAN" },        { "name": "region", "type": "VARCHAR" }      ]    }  }}

postgresql.sql

INSERT INTO "analytics"."users" ("user_id", "user_name", "active", "region")SELECT  "user_import"."user_id",  "user_import"."user_name",  "user_import"."active",  "user_import"."region"FROM "analytics"."user_import" AS "user_import"ON CONFLICT ("user_id") DO UPDATE  SET    "user_name" = "excluded"."user_name",    "active" = "excluded"."active",    "region" = "excluded"."region";