RooQL docs

Start here

In Claude: paste the prompt on the home page into a new chat. Claude reads rooql.com/llms.txt, writes an Ossie model for your data and builds an artifact whose every number is compiled from it.

In your own app: load the compiler, load a model, compile a query, and run the SQL through your connection. The browser build at rooql.com/rooql.js defines a global rooql. It's about 16 KB gzipped, with no dependencies. Read every line and download it from the code page.

<script src="/rooql.js"></script>
<script type="module">
  const model = await rooql.loadOssieModel(modelJsonText);
  const compiler = rooql.createRooql({ model, dialect: "snowflake" });
  const { sql, columns, modelHash } = compiler.compile({
    metrics: ["revenue"],
    dimensions: ["customers.segment"],
  });
  const rows = await yourWarehouse.query(sql);
</script>

RooQL never runs queries and never sees credentials. Your warehouse's permissions stay the real access control.

Write a model

A model is one Ossie 0.2.0.dev0 JSON document: datasets (tables), their fields, relationships between datasets, and metrics. Here is a small shop with a fact table and a lookup table.

{
  "version": "0.2.0.dev0",
  "name": "shop",
  "datasets": [
    {
      "name": "orders",
      "source": "ANALYTICS.PUBLIC.ORDERS",
      "primary_key": ["order_id"],
      "fields": [
        { "name": "order_id", "expression": "order_id", "datatype": "Integer" },
        { "name": "customer_id", "expression": "customer_id", "datatype": "Integer" },
        { "name": "ordered_at", "expression": "ordered_at", "datatype": "Date" },
        { "name": "status", "expression": "status", "datatype": "String" },
        { "name": "amount", "expression": "amount", "datatype": "Decimal" },
        { "name": "refund", "expression": "COALESCE(refund_amount, 0)", "datatype": "Decimal" }
      ]
    },
    {
      "name": "customers",
      "source": "ANALYTICS.PUBLIC.CUSTOMERS",
      "primary_key": ["id"],
      "fields": [
        { "name": "id", "expression": "id", "datatype": "Integer" },
        { "name": "segment", "expression": "segment", "datatype": "String" },
        { "name": "country", "expression": "country", "datatype": "String" }
      ]
    }
  ],
  "relationships": [
    {
      "name": "orders_to_customers",
      "from": "orders", "to": "customers",
      "from_columns": ["customer_id"], "to_columns": ["id"]
    }
  ],
  "metrics": [
    { "name": "revenue", "expression": "SUM(orders.amount - orders.refund)" },
    { "name": "order_count", "expression": "COUNT(orders.order_id)" },
    { "name": "buying_customers", "expression": "COUNT(DISTINCT customers.id)" },
    { "name": "average_order_value", "expression": "revenue / NULLIF(order_count, 0)" }
  ]
}

Datasets

source is a table or view name. Double-quoted or backtick-quoted parts are fine; SQL queries are not. Declare primary_key: it decides which relationships are one-to-one.

Fields

A column name, or a row-level expression over the same dataset's columns, such as COALESCE(refund_amount, 0) or CASE WHEN … END. Aggregates belong in metrics. datatype is optional but helps: String, Integer, Decimal, Float, Boolean, Date, Time, DateTime, DateTimeTz. access_modifier: "private" hides a field from queries.

Relationships

Go from the many side (from) to the one side (to), on one or more columns. If the from columns are that dataset's key, the link is one-to-one.

Expressions

A plain string, or Ossie's { "dialects": [...] } form. RooQL reads the OSSIE_SQL_2026 entry, else ANSI_SQL, else an entry with no dialect. Vendor-only expressions are skipped, as the spec allows. Vendor custom_extensions are ignored.

Metrics

A metric is an aggregate over fields, written dataset.field, or arithmetic over other metrics, written by name.

• Aggregates: SUM, COUNT, COUNT(DISTINCT …), AVG, MIN, MAX, MEDIAN, STDDEV, VARIANCE (and their _POP/_SAMP forms), APPROX_COUNT_DISTINCT.
• Filtered aggregates through CASE: SUM(CASE WHEN orders.status = 'completed' THEN orders.amount ELSE 0 END).
• Metrics from metrics: revenue / NULLIF(order_count, 0). References are inlined, and cycles are refused.
• Every field in a metric must sit inside an aggregate. Nested aggregates and window functions are refused.

Queries

compile(query) takes a plain object and returns the SQL with metadata. Every name is checked against the model, and values are typed by their field and safely inlined.

compiler.compile({
  metrics: ["revenue", "average_order_value"],
  dimensions: ["customers.segment", { field: "orders.ordered_at", grain: "month" }],
  filters: [
    { field: "orders.status", operator: "eq", value: "completed" },
    { field: "orders.ordered_at", operator: "between", values: ["2026-01-01", "2026-06-30"] },
    { field: "customers.country", operator: "in", values: ["Italy", "Portugal"] }
  ],
  having: [{ metric: "revenue", operator: "gt", value: 1000 }],
  orderBy: [{ field: "revenue", direction: "desc" }],
  limit: 20
});

The result is frozen and deterministic: the same model, query and dialect always give byte-identical SQL.

compiler.catalog() lists the queryable datasets, fields, relationships and metrics.

Joins

Each query anchors on one fact table: the one its metrics aggregate. RooQL LEFT JOINs out to every other dataset the query needs, along many-to-one and one-to-one relationships. Orders whose customer is missing land in a NULL group instead of disappearing.

SELECT
  "t1"."SEGMENT" AS "d0",
  SUM(("t0"."AMOUNT" - COALESCE("t0"."REFUND_AMOUNT", 0))) AS "m0"
FROM "ANALYTICS"."PUBLIC"."ORDERS" AS "t0"
LEFT JOIN "ANALYTICS"."PUBLIC"."CUSTOMERS" AS "t1" ON "t0"."CUSTOMER_ID" = "t1"."ID"
GROUP BY "t1"."SEGMENT"

RooQL refuses, rather than guesses, when a join could give a wrong number:

• Double-counting. Joining orders to customers repeats each customer once per order, so SUM(customers.credit_limit) by order status is refused. COUNT(DISTINCT), MIN and MAX survive the repetition and are allowed.
• Two facts in one query. Revenue from orders and refunds from a returns table can't share a query yet. Run one query per fact and combine the results at a shared grain.
• Two possible paths between the same tables, a dataset that can't be reached, and COUNT(*) with no other metric to say which table to count.

Functions

Expressions support arithmetic, comparisons, AND, OR, NOT, IN, BETWEEN, LIKE/ILIKE, IS [NOT] NULL, IS [NOT] DISTINCT FROM, CASE, CAST/TRY_CAST and typed literals such as DATE '2026-01-31'. Functions come from a fixed list, translated for the selected dialect. Unsupported combinations are refused, because a function that means different things in two databases gives silently different numbers.

Dialects

Pass dialect: "snowflake", dialect: "duckdb" or dialect: "bigquery" to createRooql. RooQL translates the same Ossie model and metric queries into the SQL each database expects, including BigQuery's GoogleSQL.

const compiler = rooql.createRooql({ model, dialect: "bigquery" });
const { sql } = compiler.compile({ metrics: ["revenue"] });

Unquoted table and column names follow Snowflake's uppercase rule; DuckDB matches them without regard to case. BigQuery preserves dataset and table case and emits backtick-quoted identifiers. For a project ID with dashes, write the model's source as `my-project.Analytics.Orders`, including the backticks.

BigQuery uses DATETIME for DateTime fields and TIMESTAMP for DateTimeTz fields, with UTC calendar arithmetic. Weeks start on Monday. Date functions need a declared or inferred temporal datatype. Exact MEDIAN, precision-constrained DECIMAL(p,s) casts and SPLIT_PART with a nonliteral delimiter return UNSUPPORTED_EXPRESSION.

Errors

Every problem throws a RooqlError with a code, a message that says how to fix it, and a path to the part of the model or query at fault.

Limits

RooQL follows the Ossie core spec and the draft Foundational Semantics proposal, and implements a large subset rather than all of it. Not supported yet: metrics from several facts in one query, cross-grain aggregates, window functions, many-to-many relationships, datasets defined by a SQL query, nested aggregates, percentiles, YAML models and other output dialects. Each fails with a typed error today.

Every query shape is tested by running RooQL's SQL in DuckDB and comparing it with independently written SQL. Every model in the Apache Ossie repository is checked to load, or to be refused with a typed error.