ButtercupQL

ButtercupQL is a Python-like language for SQL, named after the buttercup flower. A query is written one step per line, in the order the data flows, with Python’s own expressions. ButtercupQL compiles it to the SQL your database already runs: PostgreSQL, SQLite, DuckDB and ClickHouse.

# Critical cases nobody has taken, riskiest first
from cases
where status == "open" and band in ["critical", "high"]
where raised > now() - days(7)
sort -risk
take 20
select key, account, risk

becomes, for PostgreSQL,

SELECT key, account, risk
FROM cases
WHERE status = $1 AND band IN ($2, $3) AND raised > now() - INTERVAL '7 days'
ORDER BY risk DESC NULLS LAST
LIMIT 20
-- parameters: ["open", "critical", "high"]

The values are passed as parameters, never pasted into the SQL, so a query can’t be turned into another by what it is given. Cases with no risk come last, as they do on every database.

Why

• It reads like Python. Indentation, # comments, ==, and, in, None, f-strings, def and import mean what they mean in Python, and give the rows Python’s meaning gives: 7 / 2 is 3.5 and sort -risk puts missing values last, on every database.
• It is faster to write. The table comes first, so an editor can complete column names from the first word. Grouping columns are written once, one where works before and after grouping, and logic in several steps needs no subqueries.
• It runs as fast as SQL written by hand. The compiler writes one flat SELECT where it can, uses each database’s own forms, and passes values as parameters so plans can be reused.
• It is safer and clearer. x == None means IS NULL. Names are checked against the schema, and every mistake is shown at its place, with what to write instead.

In this book

• Getting started installs the bcql command and compiles a first query.
• Steps to Mistakes and their messages teach the language, each part with the SQL it becomes.
• The databases to The playground are about running it: from the command line, from Python and Rust, in an editor and in a browser.
• Functions and methods lists everything built in.

Every example in this book is checked by ButtercupQL’s tests: each query compiles to exactly the SQL shown beside it.

Getting started

Installing bcql

bcql is the ButtercupQL command. It compiles queries, checks them, and runs the language server editors use. Any of these installs it:

• A release. Each release has bcql built for Linux, macOS and Windows: unpack it and put it on your PATH.
• With Python, which also gives you the buttercupql package: pip install buttercupql.
• With Rust: cargo install --git https://github.com/mkusiappiah/ButtercupQL bcql.

bcql --version says which version you have. You can also try ButtercupQL without installing anything, in the playground.

A first query

ButtercupQL files end in .bcql. Write this in open.bcql:

# Open cases, riskiest first
from cases
where status == "open"
sort -risk
take 10

and compile it:

$ bcql compile open.bcql
SELECT *
FROM cases
WHERE status = $1
ORDER BY risk DESC NULLS LAST
LIMIT 10
-- parameters: ["open"]

The SQL comes first, then the values for its placeholders. --dialect chooses the database: postgres, which is the default, sqlite, duckdb or clickhouse. Each gets its own placeholders:

from cases
where status == "open"
sort -risk
take 10

SELECT *
FROM cases
WHERE status = ?
ORDER BY risk DESC
LIMIT 10
-- parameters: ["open"]

Checking names

Give bcql the database’s own CREATE TABLE statements with --schema, and every name is checked against them. Had line 3 said stauts:

$ bcql check --schema schema.sql open.bcql
error: there is no column `stauts` in `cases`
  ┌─ open.bcql:3:7
  │
3 │ where stauts == "open"
  │       ^^^^^^ did you mean `status`?

checked 1 file: 1 error

With a schema, the SQL can also use what the tables say: a column that is never missing needs no NULLS LAST, and a column that points to another table can be followed with a dot.

Next

• Steps shows each step a query is made of.
• Python shows how to compile and run queries from a program.
• Editors sets up colours, completion and problems as you type.

Steps

A query is a list of steps, one per line. The first is from; the rest can come in any order, as many times as needed. Each step takes rows and gives rows.

from and where

from names the table. Each where keeps the rows where its condition is true; several are joined with AND:

from cases
where status == "open"
where risk >= 90

SELECT *
FROM cases
WHERE status = $1 AND risk >= 90
-- parameters: ["open"]

Adding columns

A step that gives a name a value adds a column, or replaces one of the same name. Later steps can use it:

from transactions
fee = amount * 0.01
net = amount - fee
where net > 1_000

SELECT *, amount * 0.01 AS fee, amount - amount * 0.01 AS net
FROM transactions
WHERE amount - amount * 0.01 > 1000

select and del

select keeps the columns named, in that order. A column can be computed and named in place:

from cases
select key, risk, urgent = risk >= 90

SELECT key, risk, risk >= 90 AS urgent
FROM cases

Long lists go in brackets, over as many lines as needed:

from cases
select (
    key,
    status,
    risk,
)

SELECT key, status, risk
FROM cases

del drops columns instead. With a schema, ButtercupQL knows the other columns and writes them:

from cases
del notes, assignee

SELECT id, key, account_id, status, band, risk, raised, account, operator
FROM cases

sort

sort orders the rows by one column or more. A - sorts that column from high to low:

from cases
sort -risk, raised

SELECT *
FROM cases
ORDER BY risk DESC NULLS LAST, raised

Missing values come last whichever way a column is sorted, on every database: SQL’s own default puts them first on PostgreSQL when sorting high to low. With a schema that says a column is never missing, NULLS LAST is left out.

reverse=True turns the whole order around, as Python’s sorted(..., reverse=True) does:

from cases
sort raised, reverse=True

SELECT *
FROM cases
ORDER BY raised DESC NULLS LAST

take and skip

take keeps the first rows, and skip leaves out the first rows:

from cases
sort -risk
skip 40
take 20

SELECT *
FROM cases
ORDER BY risk DESC NULLS LAST
LIMIT 20 OFFSET 40

Steps run in the order they are written, so a where after a take keeps rows from those taken, and ButtercupQL writes the subquery that means:

from cases
sort -risk
take 100
where status == "open"

SELECT *
FROM (
  SELECT *
  FROM cases
  ORDER BY risk DESC NULLS LAST
  LIMIT 100
) AS _q1
WHERE status = $1
ORDER BY risk DESC NULLS LAST
-- parameters: ["open"]

distinct

from cases
select operator, band
distinct

SELECT DISTINCT operator, band
FROM cases

Several steps on a line

; puts several steps on one line, and a \ at the end of a line joins the next one to it:

from cases; where risk > 90; take 5

SELECT *
FROM cases
WHERE risk > 90
LIMIT 5

Names that are not plain words

A name with spaces, or a name that is one of Python’s keywords, goes in backticks. The step words (where, sort and the others) are ordinary names everywhere except at the start of a line:

from `order lines`
where `class` == "A" and sort > 3

SELECT *
FROM "order lines"
WHERE class = $1 AND sort > 3
-- parameters: ["A"]

Expressions

Expressions are Python’s, and they mean what they mean in Python, on every database. Where SQL alone would give something else, ButtercupQL writes what makes it the same.

Comparisons

== compares; a single = gives a column a value. Comparisons chain as in Python:

from cases
where 1 <= risk <= 100 and status != "closed"

SELECT *
FROM cases
WHERE risk BETWEEN 1 AND 100 AND status <> $1
-- parameters: ["closed"]

Membership

in and not in take a list. not in with None in the list still means what it says, where SQL’s NOT IN would quietly give no rows:

from cases
where band in ["critical", "high"]
where operator not in ["Acme", None]

SELECT *
FROM cases
WHERE band IN ($1, $2) AND operator NOT IN ($3) AND operator IS NOT NULL
-- parameters: ["critical", "high", "Acme"]

A query in brackets is a list too:

from cases
where account_id in (from accounts; where operator == "Acme"; select id)

SELECT *
FROM cases
WHERE account_id IN (
  SELECT id
  FROM accounts
  WHERE operator = $1
)
-- parameters: ["Acme"]

Arithmetic

/ divides exactly, // rounds down and % takes the divisor’s sign, as in Python. SQL’s own operators give 3 for 7 / 2 on PostgreSQL and SQLite, and round towards zero:

from transactions
share = amount / 3
blocks = amount // 1_000
rest = amount % 1_000

SELECT *, amount * 1.0 / 3 AS share, floor(amount * 1.0 / 1000) AS blocks, (amount % 1000 + 1000) % 1000 AS rest
FROM transactions

Conditionals

from cases
level = "urgent" if risk >= 90 else "normal"

SELECT *, CASE WHEN risk >= 90 THEN $1 ELSE $2 END AS level
FROM cases
-- parameters: ["urgent", "normal"]

Text

Text has Python’s methods, and in finds one text in another. startswith() becomes a LIKE pattern that an index can serve, passed as a parameter, with any % or _ in the text matched as it is:

from cases
where notes.startswith("50%") and "fraud" in notes.lower()
title = f"{key}: {status.upper()}"

SELECT *, CAST(key AS TEXT) || $1 || upper(status) AS title
FROM cases
WHERE notes LIKE $2 ESCAPE '\' AND strpos(lower(notes), $3) > 0
-- parameters: [": ", "50\\%%", "fraud"]

Dates and times

now() and today() are the moment and the day the query runs. days(), hours(), minutes(), seconds() and weeks() are lengths of time to add or take away, and .year, .month, .day, .hour, .minute and .second read a part of a date:

from cases
where raised > now() - days(7)
where raised.year == 2026
day = raised.date()

SELECT *, CAST(raised AS DATE) AS day
FROM cases
WHERE raised > now() - INTERVAL '7 days' AND CAST(EXTRACT(YEAR FROM raised) AS INTEGER) = 2026

On SQLite, which keeps dates as text, the same query moves dates with SQLite’s own functions:

from cases
where raised > now() - days(7)

SELECT *
FROM cases
WHERE raised > datetime('now', '-7 days')

date("2026-10-20") and datetime("2026-10-20 14:30") write a date or a moment into a query. A date that does not exist is a mistake ButtercupQL finds, rather than the database.

Values in the SQL

Text becomes a parameter; numbers, True, False and None are written into the SQL. literal() writes text into the SQL too, for a partial index that needs to see the value:

from cases
where status == literal("open") and band == "high"

SELECT *
FROM cases
WHERE status = 'open' AND band = $1
-- parameters: ["high"]

Missing values

In SQL, x = NULL is never true: a classic and silent bug. In ButtercupQL, x == None and x is None both mean what they say:

from cases
where assignee is None
where closed != None

SELECT *
FROM cases
WHERE assignee IS NULL AND closed IS NOT NULL

Comparisons

A comparison with a missing value is never true, as in SQL: where risk != 90 leaves out the rows with no risk. To keep them, say so:

from cases
where risk != 90 or risk is None

SELECT *
FROM cases
WHERE risk <> 90 OR risk IS NULL

Sorting

Missing values sort last, whichever way the sort goes, on every database. PostgreSQL would put them first when sorting high to low, and SQLite and DuckDB first when sorting low to high, so ButtercupQL says where they go:

from cases
sort -risk

SELECT *
FROM cases
ORDER BY risk DESC NULLS LAST

from cases
sort risk

SELECT *
FROM cases
ORDER BY risk NULLS LAST

Totals of no rows

sum() of no rows is 0, any() is False and all() is True, as in Python. mean(), min() and max() of no rows are missing.

from transactions
where amount > 1_000_000_000
summarize:
    money = sum(amount)
    largest = max(amount)

SELECT coalesce(sum(amount), 0) AS money, max(amount) AS largest
FROM transactions
WHERE amount > 1000000000

Truth

A where takes a value’s truth as Python does: 0, "" and None are false. With a schema that says notes is text, ButtercupQL writes what that means:

from cases
where notes

SELECT *
FROM cases
WHERE notes <> $1
-- parameters: [""]

A missing value is false, so not notes holds where notes is missing, as not None does in Python. SQL would take the missing value as neither true nor false, and keep no row, so ButtercupQL says so:

from cases
where not notes

SELECT *
FROM cases
WHERE NOT ((notes <> $1) IS TRUE)
-- parameters: [""]

A comparison is not a value’s truth: with a missing side, it is neither true nor false, as in SQL, and so is its not. where not (risk > 90) keeps no row with no risk.

Grouping

group by gives one row for each group, and its indented block says what to work out for each: the group’s totals.

from transactions
where time > now() - days(30)
group by operator:
    total = count()
    money = sum(amount)
    big = count(where=amount > 1_000_000)
where money > 1_000_000          # after grouping: the same `where` becomes HAVING
sort -money

SELECT operator, count(*) AS total, coalesce(sum(amount), 0) AS money, count(*) FILTER (WHERE amount > 1000000) AS big
FROM transactions
WHERE time > now() - INTERVAL '30 days'
GROUP BY operator
HAVING coalesce(sum(amount), 0) > 1000000
ORDER BY money DESC

• The by columns and the block’s names are the rows’ columns. Nothing is written twice, as SQL’s SELECT and GROUP BY need.
• A where after the block keeps groups by their totals: SQL’s HAVING, with the same word.
• group by operator with no block keeps one row for each group, with no totals.
• A by column can be worked out and named in place: group by month = raised.month:.

Totals

Each total can take where=, a condition of its own. It becomes FILTER (WHERE ...) on PostgreSQL, SQLite and DuckDB, and countIf() or sumIf() on ClickHouse:

from transactions
group by operator:
    big = count(where=amount > 1_000_000)
    big_money = sum(amount, where=amount > 1_000_000)

SELECT operator, countIf(amount > 1000000) AS big, coalesce(sumIf(amount, amount > 1000000), 0) AS big_money
FROM transactions
GROUP BY operator

summarize

summarize works out totals for all the rows at once, in one row:

from cases
where status == "open"
summarize:
    open = count()
    riskiest = max(risk)
    unassigned = count(where=assignee is None)

SELECT count(*) AS open, max(risk) AS riskiest, count(*) FILTER (WHERE assignee IS NULL) AS unassigned
FROM cases
WHERE status = $1
-- parameters: ["open"]

Grouping twice

A grouped query can be grouped again, with no subquery to write:

# How many operators had more than 100 cases?
from cases
group by operator:
    n = count()
where n > 100
summarize:
    operators = count()

SELECT count(*) AS operators
FROM (
  SELECT operator, count(*) AS n
  FROM cases
  GROUP BY operator
  HAVING count(*) > 100
) AS _q1

Pivots

pivot makes a column of each total for each value of a column: one row for each group of by, with the open cases of each band side by side.

from cases
where status == "open"
pivot band in ["critical", "high", "low"] by operator:
    open = count()
sort operator

SELECT operator, count(*) FILTER (WHERE band = $1) AS critical, count(*) FILTER (WHERE band = $2) AS high, count(*) FILTER (WHERE band = $3) AS low
FROM cases
WHERE status = $4
GROUP BY operator
ORDER BY operator
-- parameters: ["critical", "high", "low", "open"]

The values are written out, so the columns are known before the query runs. With one total, each column is named by its value; with several, by the value and the total’s name: critical_open, critical_riskiest. With no by, the totals are of every row, in one row.

from cases
pivot band in ["critical", "high"]:
    open = count(where=status == "open")
    riskiest = max(risk)

SELECT countIf(status = {p1:String} AND band = {p2:String}) AS critical_open, maxIfOrNull(risk, band = {p3:String}) AS critical_riskiest, countIf(status = {p4:String} AND band = {p5:String}) AS high_open, maxIfOrNull(risk, band = {p6:String}) AS high_riskiest
FROM cases
-- parameters: ["open", "critical", "critical", "open", "high", "high"]

Window functions

A total given over=, sort= or rows= keeps every row, with the total of the rows around it beside each one. The window functions work the same way.

from transactions
share = amount / sum(amount, over=operator)        # each row's share of its operator's total
so_far = sum(amount, sort=time)                    # a running total
place = rank(over=operator, sort=-amount)          # 1 for each operator's largest
before = lag(amount, sort=time)                    # the value on the row before

SELECT *, amount * 1.0 / coalesce(sum(amount) OVER (PARTITION BY operator), 0) AS share, coalesce(sum(amount) OVER (ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS so_far, rank() OVER (PARTITION BY operator ORDER BY amount DESC NULLS LAST) AS place, lag(amount, 1) OVER (ORDER BY time) AS before
FROM transactions

• The window functions are row_number(), rank(), dense_rank(), lag(x, n) and lead(x, n), which take default=, and first(x) and last(x). Every one but row_number() needs sort=.
• A sorted total runs from the first row to the current one, as pandas’ cumsum does. SQL’s own default also takes in the rows that tie with the current one.
• A window value counts the rows the steps before kept: after take 5, row_number() numbers those 5.

Moving totals

rows= keeps the window to the last rows: here the mean of each day and the six before it.

from transactions
week = mean(amount, sort=time, rows=7)

SELECT *, avg(amount) OVER (ORDER BY time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS week
FROM transactions

Keeping rows by a window value

A where after a window value keeps rows by it. On DuckDB it is a QUALIFY in the same query:

# The three riskiest open cases of each operator
from cases
where status == "open"
place = rank(over=operator, sort=-risk)
where place <= 3

SELECT *, rank() OVER (PARTITION BY operator ORDER BY risk DESC) AS place
FROM cases
WHERE status = $1
QUALIFY rank() OVER (PARTITION BY operator ORDER BY risk DESC) <= 3
-- parameters: ["open"]

and on the others, a query around it:

# The three riskiest open cases of each operator
from cases
where status == "open"
place = rank(over=operator, sort=-risk)
where place <= 3

SELECT *
FROM (
  SELECT *, rank() OVER (PARTITION BY operator ORDER BY risk DESC NULLS LAST) AS place
  FROM cases
  WHERE status = $1
) AS _q1
WHERE place <= 3
-- parameters: ["open"]

Joins and links

Joins

A join is one line: the table, and how its rows match. A table’s own columns are read with its name and a dot.

from cases
join accounts on account_id == accounts.id
where accounts.operator == "Acme"

SELECT *
FROM cases
JOIN accounts ON account_id = accounts.id
WHERE accounts.operator = $1
-- parameters: ["Acme"]

left join keeps the rows that have no match, with the other table’s columns missing:

from cases
left join accounts on account_id == accounts.id
where accounts.id is None          # the cases with no account

SELECT *
FROM cases
LEFT JOIN accounts ON account_id = accounts.id
WHERE accounts.id IS NULL

Links

When the schema says which column points to which table, a query follows the link with a dot, and ButtercupQL writes the join. A column that REFERENCES another table, such as account_id, is followed by its name without _id:

from cases
where account.operator == "Acme"          # cases.account_id → accounts.id
select key, risk, account.balance

SELECT cases.key, cases.risk, account.balance
FROM cases
LEFT JOIN accounts AS account ON cases.account_id = account.id
WHERE account.operator = $1
-- parameters: ["Acme"]

• Each link is joined once, with LEFT JOIN, so a row whose link leads nowhere stays, with its linked values missing: where account.id is None finds the cases with no account.
• A link from the linked table goes on: account.bank.name.
• Links need a schema, with the database’s REFERENCES or FOREIGN KEY constraints in it. The Schemas chapter shows the one these examples use.

A link can be grouped by, like any value:

from cases
where status == "open"
group by operator = account.operator:
    open = count()
    riskiest = max(risk)
sort -open

SELECT account.operator, count(*) AS open, max(cases.risk) AS riskiest
FROM cases
LEFT JOIN accounts AS account ON cases.account_id = account.id
WHERE cases.status = $1
GROUP BY account.operator
ORDER BY open DESC
-- parameters: ["open"]

Named queries and def

Named queries

Brackets make a query a value, as they make any long Python expression span lines. A named query is read with from, like a table:

open_cases = (
    from cases
    where status == "open"
)

from open_cases
group by band:
    n = count()

SELECT band, count(*) AS n
FROM cases
WHERE status = $1
GROUP BY band
-- parameters: ["open"]

A named query used once is written in place, where the database can optimise across it, rather than as a separate step it must work out first.

Values

A name given a value before the query can be used in it. The name means the value even where a table has a column of the same name, so a new column can never change what a query does:

threshold = 1_000_000

from transactions
where amount > threshold

SELECT *
FROM transactions
WHERE amount > 1000000

def: steps written once

A def holds steps. Calling it on a line of its own puts them in place:

def recent(days_back=7):
    """The rows raised in the last `days_back` days."""
    where raised > now() - days(days_back)

from cases
recent(30)
where assignee is None
take 10

SELECT *
FROM cases
WHERE raised > now() - INTERVAL '30 days' AND assignee IS NULL
LIMIT 10

Its parameters take defaults, and arguments by name, as in Python: recent(), recent(30) and recent(days_back=30).

Queries with arguments

A def whose body starts with from is a query. Called in a from, it is read in place:

def cases_for(account: str, days_back: int = 30):
    from cases
    where account_id == account
    where raised > now() - days(days_back)

from cases_for("A1", 7)
sort -risk

SELECT *
FROM cases
WHERE account_id = $1 AND raised > now() - INTERVAL '7 days'
ORDER BY risk DESC NULLS LAST
-- parameters: ["A1"]

Compiled on its own, its parameters become the SQL’s placeholders, given values when the query runs. With bcql, that is --query cases_for:

def cases_for(account: str, days_back: int = 30):
    from cases
    where account_id == account
    where raised > now() - days(days_back)

SELECT *
FROM cases
WHERE account_id = $1 AND raised > now() - $2 * INTERVAL '1 day'
-- parameters: [{"arg": "account"}, {"arg": "days_back"}]
-- arguments: account, days_back = 30

From Python, compile(text, query="cases_for") gives the query, and bind(account="A1") the values for its placeholders, with days_back’s default: see Python. On ClickHouse, the placeholders are named and typed:

def cases_for(account: str, days_back: int = 30):
    from cases
    where account_id == account
    where raised > now() - days(days_back)

SELECT *
FROM cases
WHERE account_id = {account:String} AND raised > now() - toIntervalDay({days_back:Int64})
-- parameters: [{"arg": "account"}, {"arg": "days_back"}]
-- arguments: account, days_back = 30

Combining queries

Two queries combine with Python’s operators, as lists and sets do:

watched = (
    from cases
    where band == "critical"
    select key
) | (
    from cases
    where assignee is None
    select key
)

from watched
sort key

SELECT *
FROM (
  SELECT key
  FROM cases
  WHERE band = $1
  UNION
  SELECT key
  FROM cases
  WHERE assignee IS NULL
) AS _q1
ORDER BY key
-- parameters: ["critical"]

The second query’s columns are matched to the first’s by name, so their order does not matter. Two queries with different columns are a mistake:

both = (from cases; select key) + (from accounts; select id)
from both

error: the queries have different columns
  ┌─ query.bcql:1:35
  │
1 │ both = (from cases; select key) + (from accounts; select id)
  │                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^ the first gives `key`, and this one `id`
  = help: give both the same columns with `select`; they are matched by name

checked 1 file: 1 error

Recursive queries

A named query that reads itself is recursive. Its first part gives the first rows, and its second part reads the rows found so far to give the next, until it gives no more. These are the accounts money reaches from A1, through the transfers between accounts, and how many transfers away each is:

reached = (
    from [{"id": "A1", "steps": 0}]
) + (
    from transfers
    join reached on source == reached.id
    where reached.steps < 5
    select id = target, steps = reached.steps + 1
)

from reached

WITH RECURSIVE reached AS (
  SELECT *
  FROM (
    SELECT $1 AS id, 0 AS steps
  ) AS _q1
  UNION ALL
  SELECT transfers.target AS id, reached.steps + 1 AS steps
  FROM transfers
  JOIN reached ON transfers.source = reached.id
  WHERE reached.steps < 5
)
SELECT *
FROM reached
-- parameters: ["A1"]

With +, every row found is kept: a loop in the transfers is followed until the condition on steps ends it. With |, a row the query has found already is not added again, so where the rows hold only the accounts, select id = target, a loop ends by itself. ClickHouse keeps every row, and takes + alone.

The first part names the columns. A recursive query can make rows of its own, with no table to read: a row for each of the last seven days.

week = (
    from [{"n": 0}]
) + (
    from week
    where n < 6
    select n = n + 1
)

from week
select n, day = today() - days(n)

WITH RECURSIVE week AS (
  SELECT *
  FROM (
    SELECT 0 AS n
  ) AS _q1
  UNION ALL
  SELECT n + 1 AS n
  FROM week
  WHERE n < 6
)
SELECT n, CURRENT_DATE - n AS day
FROM week

Changing data

A query that changes rows ends with the change. The steps before it choose the rows, as they choose the rows a query gives.

update

update gives columns new values: with =, or with +=, -= and the others, as in Python.

from cases
where status == "open" and raised < now() - days(90)
update status = "stale", risk += 1

UPDATE cases SET status = $1, risk = risk + 1
WHERE status = $2 AND raised < now() - INTERVAL '90 days'
-- parameters: ["stale", "open"]

The new values keep Python’s meaning, as every value does: n / 2 divides exactly, and n // 2 rounds down. On ClickHouse, an update is a mutation, which mutations_sync = 1 makes finish before the statement returns:

from cases
where status == "open" and raised < now() - days(90)
update status = "stale", risk += 1

ALTER TABLE cases UPDATE status = {p1:String}, risk = risk + 1
WHERE status = {p2:String} AND raised < now() - INTERVAL 90 DAY
SETTINGS mutations_sync = 1
-- parameters: ["stale", "open"]

Only where chooses the rows to change. A sort or a take before a change is a mistake, since a database changes the rows a condition chooses, in no order:

from cases
sort -risk
update status = "urgent"

error: only `where` can choose the rows to update
  ┌─ query.bcql:2:1
  │
2 │ sort -risk
  │ ^^^^^^^^^^ this can't come before `update`
  = help: `update` works on every row the `where`s keep, in no order

checked 1 file: 1 error

A condition can follow a link: the rows are then chosen by their key.

from cases
where account.operator == "Acme"
update risk = 0

UPDATE cases SET risk = 0
WHERE id IN (
  SELECT cases.id
  FROM cases
  LEFT JOIN accounts AS account ON cases.account_id = account.id
  WHERE account.operator = $1
)
-- parameters: ["Acme"]

delete

from cases
where status == "closed" and raised < now() - days(365)
delete

DELETE FROM cases
WHERE status = $1 AND raised < now() - INTERVAL '365 days'
-- parameters: ["closed"]

A delete with no where deletes every row of the table. It is what was written, so it compiles, with a warning:

from cases
delete

warning: this deletes every row
  ┌─ query.bcql:2:1
  │
2 │ delete
  │ ^^^^^^ no `where` chooses the rows of `cases`

checked 1 file: 1 warning

insert into

insert into adds the rows of the steps before to a table. Their columns go into the table’s columns of the same names, so a select chooses them:

from cases
where status == "closed"
select key, risk, raised
insert into archive

INSERT INTO archive (key, risk, raised)
SELECT key, risk, raised
FROM cases
WHERE status = $1
-- parameters: ["closed"]

Rows can be written in the query, as a list of dicts. Every row has the same keys, which are the columns:

from [
    {"id": "A9", "operator": "Acme", "balance": 0},
    {"id": "B4", "operator": "Beta", "balance": 120},
]
insert into accounts

INSERT INTO accounts (id, operator, balance)
VALUES (?, ?, 0), (?, ?, 120)
-- parameters: ["A9", "Acme", "B4", "Beta"]

The rows changed

A select after a change gives the rows it changed, as they are after it:

from cases
where key == "C1"
update status = "closed"
select key, status

UPDATE cases SET status = $1
WHERE key = $2
RETURNING key, status
-- parameters: ["closed", "C1"]

ClickHouse can’t give them, and says so. Only a select can follow a change.

Changes in a def

A def whose steps change rows is called on a line of its own, and its change is made there:

def close_stale(days_old: int = 90):
    from cases
    where status == "open" and raised < now() - days(days_old)
    update status = "closed"

close_stale(30)

UPDATE cases SET status = $1
WHERE status = $2 AND raised < now() - INTERVAL '30 days'
-- parameters: ["closed", "open"]

Compiled on its own, its parameters are placeholders, as a query def’s are, for a program to give each time it runs it: with bcql, --query close_stale.

def close_stale(days_old: int = 90):
    from cases
    where status == "open" and raised < now() - days(days_old)
    update status = "closed"

UPDATE cases SET status = $1
WHERE status = $2 AND raised < now() - $3 * INTERVAL '1 day'
-- parameters: ["closed", "open", {"arg": "days_old"}]
-- arguments: days_old = 90

To keep it in the database itself, as a procedure, write @stored on the line before it: see Stored defs.

Transactions

The statements under with transaction: run together: all of them, or none of them if one fails.

with transaction:
    from accounts
    where id == "A1"
    update balance -= 100

    from accounts
    where id == "B4"
    update balance += 100

BEGIN;

UPDATE accounts SET balance = balance - 100
WHERE id = $1;
-- parameters: ["A1"]

UPDATE accounts SET balance = balance + 100
WHERE id = $1;
-- parameters: ["B4"]

COMMIT;

ClickHouse runs no transactions, so it refuses a with transaction: block rather than run its statements one by one. From Python, run() runs a whole text as one transaction.

Several statements

A file can hold several statements: changes to rows, changes to the tables, and queries. They run in the order they are written, and bcql compile writes each, ended with ;, with its own parameters. A file gives the rows of one query; the changes before it are done first.

from cases
where status == "open" and raised < now() - days(90)
update status = "stale"

from cases
group by status:
    n = count()

UPDATE cases SET status = $1
WHERE status = $2 AND raised < now() - INTERVAL '90 days';
-- parameters: ["stale", "open"]

SELECT status, count(*) AS n
FROM cases
GROUP BY status;

Tables and permissions

Tables, indexes and permissions are statements of their own, each on its own line, with a block below it where it has parts. Each is written in the form its database takes, and refused with a clear message where the database has no such thing.

create table

A table’s columns are written as Python’s annotations: a name, a colon and a type. A column is never missing unless its type says it can be, with | None or Optional[...]. A value after = is its default.

create table archive:
    key: str = field(primary_key=True)
    account_id: str | None = field(references=accounts.id)
    status: str = "closed"
    risk: int | None
    raised: datetime
    archived: datetime = now()

CREATE TABLE archive (
  key TEXT NOT NULL,
  account_id TEXT REFERENCES accounts (id),
  status TEXT NOT NULL DEFAULT 'closed',
  risk BIGINT,
  raised TIMESTAMP NOT NULL,
  archived TIMESTAMP NOT NULL DEFAULT (now()),
  PRIMARY KEY (key)
)

field() says the rest, as Python’s dataclasses do: primary_key=True, unique=True, references=accounts.id for a column that holds another table’s key, and default=. A key of several columns is a line of its own: primary_key(account_id, day), or unique(account_id, day). create table archive, exist_ok=True: creates the table only if there is none of that name.

The types are Python’s, and each database gets its own for them:

A ClickHouse table keeps its rows in the order of its primary key, and a column that can be missing is Nullable. ClickHouse keeps no unique keys and no references, so they are left out, with a warning:

create table archive:
    key: str = field(primary_key=True)
    account_id: str | None = field(references=accounts.id)
    risk: int | None
    raised: datetime

CREATE TABLE archive (
  key String,
  account_id Nullable(String),
  risk Nullable(Int64),
  raised DateTime
)
ENGINE = MergeTree
ORDER BY (key)

Indexes

create index on cases(-risk, raised) makes an index of the columns, in the order a sort by them takes: - for high to low.

create index on cases(-risk, raised)

CREATE INDEX cases_risk_raised_idx ON cases (risk DESC NULLS LAST, raised)

create unique index makes one in which no two rows have the same values. A name can be given after index, and where= keeps only the rows a condition chooses:

create unique index open_key on cases(key), where=status == "open"

CREATE UNIQUE INDEX open_key ON cases (key) WHERE status = 'open'

ClickHouse has no indexes like these.

alter table

alter table changes a table, a change on each line of its block: a column written as in create table is added, rename old to new renames one, and del drops one.

alter table cases:
    region: str = "east"
    rename risk to score
    del notes

ALTER TABLE cases ADD COLUMN region TEXT NOT NULL DEFAULT 'east';

ALTER TABLE cases RENAME COLUMN risk TO score;

ALTER TABLE cases DROP COLUMN notes;

drop table archive drops a table, and drop index cases_risk_raised_idx an index. missing_ok=True drops it only if it is there.

Checked as they are written

With a schema, the names of each statement are checked against the tables, and a create table, an alter table or a drop table changes the tables the statements after it are checked against:

create table watched:
    key: str = field(primary_key=True)
    since: datetime

from watched
where sicne > now() - days(7)

error: there is no column `sicne` in `watched`
  ┌─ query.bcql:6:7
  │
6 │ where sicne > now() - days(7)
  │       ^^^^^ did you mean `since`?

checked 1 file: 1 error

Permissions

grant lets users or roles read or change tables, and revoke takes their permissions away again. The permissions are read, write, which is insert, update and delete, each of those three alone, and all.

grant read, write on cases, accounts to analyst
revoke write on accounts from analyst

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE cases, accounts TO analyst;

REVOKE INSERT, UPDATE, DELETE ON TABLE accounts FROM analyst;

ClickHouse names its permissions in its own way, one table at a time:

grant read, write on cases, accounts to analyst
revoke write on accounts from analyst

GRANT SELECT, INSERT, ALTER UPDATE, ALTER DELETE ON cases TO analyst;

GRANT SELECT, INSERT, ALTER UPDATE, ALTER DELETE ON accounts TO analyst;

REVOKE INSERT, ALTER UPDATE, ALTER DELETE ON accounts FROM analyst;

SQLite and DuckDB have no users, and so no permissions.

Stored defs

A def written with @stored on the line before it is kept in the database, which then runs it by name. A query def becomes a function on PostgreSQL, a macro on DuckDB, and a view with parameters on ClickHouse. PostgreSQL needs the types of the parameters, and of the columns the query gives, which the schema says:

@stored
def risky_for(account: str, at_least: int = 50):
    from cases
    where account_id == account and risk >= at_least
    select key, risk

CREATE OR REPLACE FUNCTION risky_for(account TEXT, at_least BIGINT DEFAULT 50)
RETURNS TABLE (key TEXT, risk BIGINT)
LANGUAGE sql STABLE
AS $bcql$
SELECT key, risk
FROM cases
WHERE account_id = risky_for.account AND risk >= risky_for.at_least
$bcql$

@stored
def risky_for(account: str, at_least: int = 50):
    from cases
    where account_id == account and risk >= at_least
    select key, risk

CREATE OR REPLACE MACRO risky_for(account, at_least := 50) AS TABLE
SELECT key, risk
FROM cases
WHERE account_id = account AND risk >= at_least

A def that changes rows becomes a procedure on PostgreSQL:

@stored
def close_stale(days_old: int = 90):
    from cases
    where status == "open" and raised < now() - days(days_old)
    update status = "closed"

CREATE OR REPLACE PROCEDURE close_stale(days_old BIGINT DEFAULT 90)
LANGUAGE sql
BEGIN ATOMIC
UPDATE cases SET status = 'closed'
WHERE status = 'open' AND raised < now() - close_stale.days_old * INTERVAL '1 day';
END

SQLite keeps no queries, DuckDB keeps none that change rows, and ClickHouse’s views take no defaults: each says so.

Modules

A file’s defs, named queries and values can be used by other files, with Python’s own import lines. Say shared.bcql holds:

# Steps the reports share
threshold = 1_000_000

def recent(days_back=7):
    """The payments of the last `days_back` days."""
    where time > now() - days(days_back)

busy = (
    from transactions
    where amount > threshold
)

Then another file in the same folder imports from it:

from shared import recent, threshold

from transactions
recent(30)
where amount > threshold

SELECT *
FROM transactions
WHERE time > now() - INTERVAL '30 days' AND amount > 1000000

or imports it whole, and reads its names with a dot:

import shared

from shared.busy
summarize:
    money = sum(amount)

SELECT coalesce(sum(amount), 0) AS money
FROM transactions
WHERE amount > 1000000

Where modules are found

• import shared reads shared.bcql in the folder of the file compiled, and import reports.daily reads reports/daily.bcql there.
• from .lib import recent reads lib.bcql beside the importing file; each further dot is a folder up.
• import shared as s and from shared import recent as last give a name of your own.
• A module that imports itself, through others or directly, is a mistake ButtercupQL reports.

A module’s names are its own

A module’s defs, named queries and values mean what its own names say, as a Python function sees its own module’s names: busy above uses shared’s threshold, whatever the importing file calls threshold.

A mistake in a module is shown in the module’s own text, with its own name, and an editor shows it on the import that reads the module.

From programs

bcql reads modules from files beside the one it compiles. A program gives the compiler a loader instead: the Python package takes path=, for files, or modules=, a dict of texts (see Python), and Rust takes any Loader (see Rust).

Schemas

A schema is the database’s own CREATE TABLE statements. Given one, ButtercupQL checks every name against it, and writes SQL that uses what the tables say. The examples in this book that use a schema, and the playground, use this one:

CREATE TABLE accounts (
    id TEXT PRIMARY KEY,
    operator TEXT NOT NULL,
    balance NUMERIC
);

CREATE TABLE cases (
    id INTEGER PRIMARY KEY,
    key TEXT NOT NULL,
    account_id TEXT REFERENCES accounts (id),
    status TEXT NOT NULL,
    band TEXT,
    risk INTEGER,
    raised TIMESTAMP NOT NULL,
    assignee TEXT,
    notes TEXT,
    account TEXT,
    operator TEXT
);

CREATE TABLE transactions (
    id INTEGER PRIMARY KEY,
    account_id TEXT REFERENCES accounts (id),
    operator TEXT NOT NULL,
    amount INTEGER NOT NULL,
    time TIMESTAMP NOT NULL
);

bcql takes it with --schema schema.sql, the Python package with schema=, and an editor from its settings.

Every name is checked

A name that is not in the table is a mistake, with the name most likely meant:

from cases
where stauts == "open"

error: there is no column `stauts` in `cases`
  ┌─ query.bcql:2:7
  │
2 │ where stauts == "open"
  │       ^^^^^^ did you mean `status`?

checked 1 file: 1 error

Types are checked too: text can’t be compared with a number.

from cases
where risk == "high"

error: A whole number can't be compared with text
  ┌─ query.bcql:2:7
  │
2 │ where risk == "high"
  │       ^^^^^^^^^^^^^^

checked 1 file: 1 error

SQL that uses what the tables say

• A column that is never missing needs no NULLS LAST when it is sorted, so an ordinary index serves the sort:

from cases
sort -raised

SELECT *
FROM cases
ORDER BY raised DESC

• A column that points to another table can be followed with a dot: see Links.
• del writes the columns that are left, by name.

Writing a schema

ButtercupQL reads CREATE TABLE statements as PostgreSQL, SQLite and DuckDB write them: each column’s name and type, NOT NULL, PRIMARY KEY, and REFERENCES in the column or as a FOREIGN KEY of the table. A type it does not know is taken as a value of any type, and what else a statement says is left to the database.

Mistakes and their messages

ButtercupQL reports every mistake in a file at once, each at its place, with what to write instead. bcql check shows them as below, an editor shows them as you type, and the Python package raises them together as one CompileError.

from cases
where status = "open"
order by risk desc
limit 20

error: use `==` to compare
  ┌─ query.bcql:2:14
  │
2 │ where status = "open"
  │              ^ `=` gives a column a value; `==` compares two values

error: there is no step `order`
  ┌─ query.bcql:3:1
  │
3 │ order by risk desc
  │ ^^^^^ ButtercupQL calls this `sort`
  = help: for example: `sort -risk, raised`

error: there is no step `limit`
  ┌─ query.bcql:4:1
  │
4 │ limit 20
  │ ^^^^^ ButtercupQL calls this `take`
  = help: for example: `take 20`

checked 1 file: 3 errors

Habits from other languages

Words from SQL and from data-frame libraries get their ButtercupQL form: order by and sort_values are sort, limit and head are take, having and filter are where, groupby is group by, agg is summarize, and mutate and assign are a column step. SQL’s operators are caught too:

from cases
where risk > 90 && band <> "low"
sort risk desc

error: use `and`
  ┌─ query.bcql:2:17
  │
2 │ where risk > 90 && band <> "low"
  │                 ^^ `&&` is not how ButtercupQL writes “and”

error: use `!=` for “not equal to”
  ┌─ query.bcql:2:25
  │
2 │ where risk > 90 && band <> "low"
  │                         ^^

error: there is no `desc` in ButtercupQL
  ┌─ query.bcql:3:11
  │
3 │ sort risk desc
  │           ^^^^ write `-risk` to sort high to low

checked 1 file: 3 errors

Misspelt names

A name that is not known gets the one most likely meant, when one is close: a step, a function, a column of the schema, or a definition of the file.

from cases
wehre risk > 90

error: there is no step `wehre`
  ┌─ query.bcql:2:1
  │
2 │ wehre risk > 90
  │ ^^^^^ did you mean `where`?

checked 1 file: 1 error

from cases
level = rond(risk)

error: there is no function `rond`
  ┌─ query.bcql:2:9
  │
2 │ level = rond(risk)
  │         ^^^^ did you mean `round`?

checked 1 file: 1 error

A mistake in how a line is written stops the file from compiling, so the mistakes that only compiling finds, such as a misspelt function, are shown once the lines read.

Mistakes that would give wrong rows

Some SQL runs, but does not do what it says. ButtercupQL refuses to write it:

from transactions
total = sum(amount)

error: `sum()` is a total
  ┌─ query.bcql:2:9
  │
2 │ total = sum(amount)
  │         ^^^^^^^^^^^ a total is worked out from many rows
  = help: it goes in the block of a `group by` or a `summarize`:

              summarize:
                  n = count()
  = help: to keep every row with its total beside it, give the total a window: `sum(..., over=operator)`

checked 1 file: 1 error

A step whose work a later step undoes is a warning: the query runs, but likely not as meant.

from cases
sort -risk
group by operator:
    n = count()
where n > 100
summarize:
    operators = count()

warning: this sort has no effect
  ┌─ query.bcql:2:1
  │
2 │ sort -risk
  │ ^^^^^^^^^^ grouping does not keep the rows' order
  = help: to order the groups, sort after the grouping

checked 1 file: 1 warning

The databases

The same query gives the same rows on PostgreSQL, SQLite, DuckDB and ClickHouse, and they are the rows Python’s meaning gives. Each database gets the SQL written in its own forms. ButtercupQL’s tests run the SQL on all four and compare the rows.

from transactions
where time > now() - days(30)
group by operator:
    payments = count()
    big = count(where=amount > 1_000_000)
    average = mean(amount)
sort -payments

PostgreSQL

SELECT operator, count(*) AS payments, count(*) FILTER (WHERE amount > 1000000) AS big, avg(amount) AS average
FROM transactions
WHERE time > now() - INTERVAL '30 days'
GROUP BY operator
ORDER BY payments DESC

• Placeholders are $1, $2. Python’s psycopg takes %s, which --params format writes.
• Missing values are put last with NULLS LAST, which an index made the same way can serve: CREATE INDEX ON cases (risk DESC NULLS LAST).
• Text is sorted in the database’s own order, its collation: by the locale of the database, unless it or the column uses the C collation. ButtercupQL does not write COLLATE, which would keep PostgreSQL from using its indexes for the sort.

SQLite

from transactions
where time > now() - days(30)
group by operator:
    payments = count()
    big = count(where=amount > 1_000_000)
    average = mean(amount)
sort -payments

SELECT operator, count(*) AS payments, count(*) FILTER (WHERE amount > 1000000) AS big, avg(amount) AS average
FROM transactions
WHERE time > datetime('now', '-30 days')
GROUP BY operator
ORDER BY payments DESC

• Placeholders are ?.
• SQLite keeps dates and times as text. ButtercupQL compares them in the form SQLite’s own date functions write, 2026-10-20 14:30:00, and moves them with those functions: datetime('now', '-30 days').
• SQLite builds often leave out the maths functions, so ButtercupQL does without them: // and % are worked out from casts, and x ** 2 to x ** 4 are multiplications. Other powers need SQLite’s power().
• startswith() becomes GLOB, which matches capitals exactly as Python does; SQLite’s LIKE ignores case.

DuckDB

from transactions
where time > now() - days(30)
group by operator:
    payments = count()
    big = count(where=amount > 1_000_000)
    average = mean(amount)
sort -payments

SELECT operator, count(*) AS payments, count(*) FILTER (WHERE amount > 1000000) AS big, avg(amount) AS average
FROM transactions
WHERE time > now() - INTERVAL '30 days'
GROUP BY operator
ORDER BY payments DESC

• Placeholders are $1, $2.
• DuckDB’s / already divides exactly and it puts missing values last by itself, so its SQL is the plainest.
• A where on a window value is a QUALIFY, in the same query.

ClickHouse

from transactions
where time > now() - days(30)
group by operator:
    payments = count()
    big = count(where=amount > 1_000_000)
    average = mean(amount)
sort -payments

SELECT operator, count(*) AS payments, countIf(amount > 1000000) AS big, avgOrNull(amount) AS average
FROM transactions
WHERE time > now() - INTERVAL 30 DAY
GROUP BY operator
ORDER BY payments DESC

• Placeholders are named and typed: {p1:String}, and a query argument’s own name, {account:String}.
• Totals with a condition are countIf() and sumIf(), and avgOrNull(), minOrNull() and maxOrNull() make the total of no rows missing, as on the other databases.
• A query with a LEFT JOIN gets SETTINGS join_use_nulls = 1, so that a row with no match has missing values rather than empty ones.
• Text functions are ClickHouse’s own: startsWith(), lowerUTF8(), lengthUTF8().

Placeholders

--params writes another style of placeholder, for drivers that take it:

With the colon and braces styles, each value is passed by name, and a query’s argument used twice is passed once. With dollar, an argument used twice is one $n.

The bcql command

bcql: the ButtercupQL command

Usage:
  bcql compile [OPTIONS] FILE
  bcql check [OPTIONS] FILE...
  bcql lsp
  bcql --version
  bcql --help

Commands:
  compile    Write the SQL a query compiles to, and its parameters
  check      Read each file and report every mistake in it
  lsp        Run the language server, for editors, on standard input and output

Options:
  --dialect NAME    The database: sqlite, postgres, duckdb or clickhouse
                    [default: postgres]
  --schema FILE     The database's CREATE TABLE statements, to check names against
  --query NAME      Compile a named query or a query def of the file, with the
                    def's parameters left as placeholders
  --params STYLE    Placeholders: dollar ($1), question (?), format (%s),
                    colon (:p1) or braces ({p1:String}) [default: the database's own]
  --json            Write the SQL and its parameters as JSON
  --color WHEN      Colour the messages: auto, always or never [default: auto]

A FILE of `-` is read from standard input. `compile` and `check` exit with 0
when there is no error, with 1 when there is one, and with 2 when the command
is wrong.

bcql compile

bcql compile FILE writes the SQL of the file’s query to standard output, then a comment with the values of its placeholders, in order, and for a query def, its arguments. Problems go to standard error, so the SQL can be piped on. With report.bcql holding the cases_for query of Queries with arguments:

$ bcql compile report.bcql --query cases_for --dialect sqlite
SELECT *
FROM cases
WHERE account_id = ? AND raised > datetime('now', CAST(-? AS TEXT) || ' days')
-- parameters: [{"arg": "account"}, {"arg": "days_back"}]
-- arguments: account, days_back = 30

--json writes the same as one JSON object, for programs:

$ bcql compile open.bcql --json
{"sql": "SELECT *\nFROM cases\nWHERE status = $1", "params": ["open"], "args": []}

bcql check

bcql check reads each file given and reports every mistake in them, then says how many it found. A file with a query is compiled too, for the mistakes only compiling finds, and so are each named query and query def of a file of definitions. It is the command for a CI job: it exits with 1 when any file has an error.

$ bcql check --schema schema.sql queries/*.bcql
checked 12 files: no problems found

--color always colours the messages for a terminal that bcql can’t tell is one. Colour is used only in a terminal, and never when NO_COLOR is set.

bcql lsp

bcql lsp is the language server: an editor starts it and speaks the Language Server Protocol to it on standard input and output. See Editors.

Python

The buttercupql package compiles ButtercupQL from Python, and brings the bcql command with it. It is the Rust compiler itself, built for Python, so it compiles a query in microseconds: fast enough to compile on every request.

pip install buttercupql

The package is on PyPI. One wheel serves every Python from 3.9, on Linux, macOS and Windows.

Compiling and running a query

compile() gives the SQL and the values for its placeholders, ready for the database’s own driver:

import sqlite3
import buttercupql

db = sqlite3.connect(":memory:")
db.executescript("""
    CREATE TABLE cases (key TEXT, status TEXT, band TEXT, risk INTEGER);
    INSERT INTO cases VALUES ('C1', 'open', 'critical', 95), ('C2', 'open', 'low', 40),
                             ('C3', 'closed', 'high', 90), ('C4', 'open', 'high', NULL);
""")

query = buttercupql.compile("""
    from cases
    where status == "open" and band in ["critical", "high"]
    sort -risk
    select key, risk
""", dialect="sqlite")

assert query.sql == "SELECT key, risk\nFROM cases\nWHERE status = ? AND band IN (?, ?)\nORDER BY risk DESC"
assert query.params == ["open", "critical", "high"]
assert db.execute(query.sql, query.params).fetchall() == [("C1", 95), ("C4", None)]

A query indented inside a Python string reads as it is: the first line sets the column.

dialect is "postgres", the default, "sqlite", "duckdb" or "clickhouse". params chooses another style of placeholder: "format" writes %s, as psycopg takes them.

query = buttercupql.compile('from cases\nwhere status == "open"\n', params="format")
assert query.sql.endswith("WHERE status = %s")

Running on a database

run() compiles a text and runs it with the database’s own driver, and gives the rows of its query: a list of tuples, as fetchall() gives them, with the names of the columns in columns.

rows = buttercupql.run(db, """
    from cases
    where status == "open"
    sort -risk
    select key, risk
""")
assert rows == [("C1", 95), ("C2", 40), ("C4", None)]
assert rows.columns == ["key", "risk"]

The driver says which database it is, and which placeholders it takes: sqlite3; psycopg, psycopg2 or pg8000 for PostgreSQL; duckdb; or a clickhouse_connect client. For another DB-API driver, dialect= names the database. rows.to_pandas() gives the rows as a pandas DataFrame.

A text that changes the database runs statement by statement, in order, as one transaction: all of it, or none of it if a statement fails. ClickHouse has no transactions, so there each statement stands once it has run. The rows given are the last query’s:

rows = buttercupql.run(db, """
    create table archive:
        key: str = field(primary_key=True)
        risk: int | None

    from cases
    where status == "closed"
    select key, risk
    insert into archive

    from cases
    where status == "closed"
    delete

    from archive
""")
assert rows == [("C3", 90)]

query= runs a query def of the text, with its arguments given by name, as bind() takes them.

The database’s own tables

read_schema() reads the tables of the database a connection is to, with their columns and the links between them, for names to be checked against: a Schema for schema=.

schema = buttercupql.read_schema(db)
assert schema.tables == ["archive", "cases"]
try:
    buttercupql.run(db, "from archive\nsort -rsik\n", schema=schema)
except buttercupql.CompileError as error:
    assert error.diagnostics[0].label == "did you mean `risk`?"

It reads SQLite’s, DuckDB’s and PostgreSQL’s tables and views, and ClickHouse’s. In a notebook, the %%bcql magic reads them for each cell: see Notebooks.

Statements

A text compiles to one statement for a query, and to one for each change in a text that changes the database. statements gives each, to run one by one in order, with its SQL, its values and its kind: "query", "change", "definition" for a change to the database itself, and "begin" and "commit" around a with transaction: block. sql and params are all the statements together.

script = buttercupql.compile("""
    create table seen:
        key: str

    from cases
    where risk < 50
    update band = "low"
""", dialect="sqlite")
assert [statement.kind for statement in script.statements] == ["definition", "change"]
update = script.statements[1]
assert (update.sql, update.params) == ("UPDATE cases SET band = ?\nWHERE risk < 50", ["low"])

Mistakes

A text that does not compile raises buttercupql.CompileError, a ValueError. It holds every problem found, and its message shows each under its line, as bcql does:

try:
    buttercupql.compile('from cases\nwhere status = "open"\n', path="open.bcql")
except buttercupql.CompileError as error:
    [problem] = error.diagnostics
    assert (problem.severity, problem.line, problem.column) == ("error", 2, 14)
    assert problem.message == "use `==` to compare"
    assert str(error).startswith("error: use `==` to compare\n  ┌─ open.bcql:2:14\n")

check() gives the problems of a text without raising, and an empty list when there are none:

assert [p.message for p in buttercupql.check("from cases\nwehre risk > 90\n")] == ["there is no step `wehre`"]
assert buttercupql.check("from cases\nsort -risk\n") == []

Schemas

schema= checks every name against the database’s tables. It takes the CREATE TABLE statements, a buttercupql.Schema, or a dict of tables and their columns’ types:

schema = "CREATE TABLE cases (key text NOT NULL, status text NOT NULL, risk integer);"
try:
    buttercupql.compile('from cases\nwhere stauts == "open"\n', schema=schema)
except buttercupql.CompileError as error:
    assert error.diagnostics[0].label == "did you mean `status`?"

tables = {"cases": {"key": "text not null", "risk": "integer"}}
assert buttercupql.compile("from cases\nsort -key\n", schema=tables).sql.endswith("ORDER BY key DESC")

Queries with arguments

query= compiles a named query or a query def in place of the main query. A def’s parameters become placeholders, and bind() gives their values when the query runs, with the defaults for those not given:

text = """
def open_for(account: str, rows: int = 10):
    from cases
    where account_id == account
    take rows
"""
query = buttercupql.compile(text, dialect="sqlite", query="open_for")
assert query.sql == "SELECT *\nFROM cases\nWHERE account_id = ?\nLIMIT ?"
assert [(arg.name, arg.type, arg.default) for arg in query.args] == [("account", "str", None), ("rows", "int", 10)]
assert query.bind(account="A1") == ["A1", 10]
assert query.bind(account="A1", rows=3) == ["A1", 3]

The query can be compiled once and run with different arguments as often as needed. bind_named() gives the values by name, for named placeholders such as ClickHouse’s:

query = buttercupql.compile(text, dialect="clickhouse", query="open_for")
assert query.bind_named(account="A1") == {"account": "A1", "rows": 10}

A date or a time given to bind() is passed as text, in the form databases read: 2026-10-20 14:30:00.

Modules

A text that imports modules needs to know where they are. path= names the file the text is from, and its imports are read from files beside it; modules= gives the modules’ texts instead:

shared = "threshold = 1_000_000\ndef recent(days_back=7):\n    where raised > now() - days(days_back)\n"
query = buttercupql.compile(
    "from shared import recent, threshold\nfrom cases\nrecent(30)\nwhere amount > threshold\n",
    modules={"shared": shared},
)
assert query.sql == "SELECT *\nFROM cases\nWHERE raised > now() - INTERVAL '30 days' AND amount > 1000000"

The command

The package installs bcql, which python -m buttercupql also runs: see The bcql command.

Notebooks

In Jupyter, IPython, and the notebooks of VS Code and other editors, a cell of ButtercupQL runs on a database and shows its rows. The buttercupql package brings the cell magic: pip install buttercupql, then load it once in the notebook.

%load_ext buttercupql

A cell that starts with %%bcql and the name of a connection runs on that database. Any connection that run() takes will do:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
    CREATE TABLE cases (key TEXT NOT NULL, status TEXT NOT NULL, band TEXT, risk INTEGER);
    INSERT INTO cases VALUES ('C1', 'open', 'critical', 95), ('C2', 'open', 'low', 40),
                             ('C3', 'closed', 'high', 90), ('C4', 'open', 'high', NULL);
""")

%%bcql db
from cases
where status == "open" and risk > 30
sort -risk
select key, band, risk

The rows are a pandas DataFrame when pandas is installed, and a table of their own when it is not. The cells after it run on the same connection without naming it again.

Mistakes, before anything runs

Each cell’s names are checked against the database’s own tables, read from the database as the cell runs. A mistake is shown with its suggestion, and nothing runs:

%%bcql
from cases
where stauts == "open"

error: there is no column `stauts` in `cases`
  ┌─ <query>:2:7
  │
2 │ where stauts == "open"
  │       ^^^^^^ did you mean `status`?

The SQL

--sql shows the SQL the cell compiles to, for the connection’s database, without running it:

%%bcql --sql
from cases
where status == "open"
sort -risk

SELECT *
FROM cases
WHERE status = ?
ORDER BY risk DESC
-- parameters: ["open"]

Keeping the rows

-o keeps the rows in a variable, for the cells after to use:

%%bcql -o bands
from cases
group by band:
    cases = count()
    riskiest = max(risk)
sort band

assert len(bands) == 3

A cell that changes the database runs as run() runs a text: statement by statement, as one transaction. With no query to give rows, it says how many statements ran.

Options

%%bcql? shows them in the notebook.

Rust

ButtercupQL is written in Rust, and the buttercupql crate is the compiler. It has no dependencies.

[dependencies]
buttercupql = { git = "https://github.com/mkusiappiah/ButtercupQL" }

Compiling

compile() takes the text and the options: the database, and optionally a schema, a loader for modules, a style of placeholder and a query to compile in place of the main one.

use buttercupql::{Dialect, Options, Value};

let text = "from cases\nwhere status == \"open\"\nsort -risk\ntake 20\n";
let compiled = buttercupql::compile(text, &Options::new(Dialect::Postgres)).unwrap();
assert_eq!(
    compiled.sql,
    "SELECT *\nFROM cases\nWHERE status = $1\nORDER BY risk DESC NULLS LAST\nLIMIT 20"
);
assert_eq!(compiled.values().unwrap(), [Value::Text("open".to_string())]);

On failure, compile() returns every problem found. Diagnostic::render() shows one as bcql does, under its line:

use buttercupql::{Dialect, Options, SourceFile};

let text = "from cases\nwhere status = \"open\"\n";
let problems = buttercupql::compile(text, &Options::new(Dialect::Sqlite)).unwrap_err();
let shown = problems[0].render(&SourceFile::new("open.bcql", text), false);
assert!(shown.starts_with("error: use `==` to compare\n  ┌─ open.bcql:2:14\n"));

Options

use buttercupql::{Dialect, FileLoader, Options, ParamStyle, Schema};

let schema = Schema::from_sql("CREATE TABLE cases (key text NOT NULL, risk integer);").unwrap();
let loader = FileLoader::new("queries");
let options = Options::new(Dialect::DuckDb)
    .with_schema(&schema)                  // check names, and use what the tables say
    .with_name("queries/report.bcql")      // the name messages show, and where imports are found from
    .with_loader(&loader)                  // where modules come from
    .with_params(ParamStyle::Colon)        // `:p1` placeholders
    .with_query("for_operator");           // a named query or a query `def`

Queries with arguments

A query def compiled with with_query() keeps its parameters as placeholders, and bind() gives the values, in order, with defaults for those not given:

use buttercupql::{Dialect, Options, Value};

let text = "def for_account(account: str, rows: int = 10):\n    from cases\n    where account_id == account\n    take rows\n";
let compiled = buttercupql::compile(text, &Options::new(Dialect::Postgres).with_query("for_account")).unwrap();
assert_eq!(compiled.sql, "SELECT *\nFROM cases\nWHERE account_id = $1\nLIMIT $2");
let values = compiled.bind(&[("account", Value::Text("A1".to_string()))]).unwrap();
assert_eq!(values, [Value::Text("A1".to_string()), Value::Int(10)]);

Reading without compiling

parse() reads a text into its syntax tree, buttercupql::ast, with every problem found, as editors do while you type. buttercupql::builtins describes every step, function and method.

The crate’s own documentation, with cargo doc -p buttercupql --open, describes each of these.

Editors

bcql lsp is ButtercupQL’s language server. An editor that speaks the Language Server Protocol starts it, and it:

• shows each problem as you type, at its place with its message, as bcql check finds them. Every query of a file is checked: the main query, each named query and each query def. A problem in an imported module is shown on the import that reads it, with a link to its place in the module.
• completes names: the steps and statements at the start of a line, and stored after @; the tables after from, join, insert into, alter table, drop table and on, those the file creates too; functions, the file’s own definitions and, with a schema, the columns and links of the tables read; and after a dot, a table’s or a link’s columns, a module’s names, or the methods.
• describes a name when the pointer rests on it: a step, a statement, a function, a column with its type, a link, or a def with its docstring.
• goes to a definition, in the file or in a module it imports.
• lists a file’s definitions, for the editor’s outline: its defs, named queries and values, the tables it creates, and each change to rows, such as insert into archive.
• gives the SQL of a text for the editor to show, with the buttercupql/compile request: its SQL and parameters, and each statement of a text of several.

It reads the files a file imports as the editor has them, saved or not.

Visual Studio Code

The ButtercupQL extension adds colours to .bcql files, runs the language server, and adds Show SQL: the SQL of the query beside the file, kept up to date as you type. With the cursor in a query def or a named query, it shows that one, its arguments left as placeholders. A file of several statements shows each, ended with ; and with its own parameters.

Each release has the extension: one file for each platform, with bcql in it, and one for any platform, which uses the bcql on your PATH. Install it with Extensions: Install from VSIX…, or:

code --install-extension buttercupql-darwin-arm64-0.2.0.vsix

Settings

Other editors

Any editor with a language server client can use bcql lsp: give it the command bcql lsp for files ending in .bcql. The server takes two settings, when it starts (as its initialization options) or when they change (workspace/didChangeConfiguration), on their own or under "buttercupql":

{ "dialect": "sqlite", "schema": "schema.sql" }

For example, in Neovim:

vim.filetype.add({ extension = { bcql = "buttercupql" } })
vim.lsp.config("buttercupql", {
  cmd = { "bcql", "lsp" },
  filetypes = { "buttercupql" },
  root_markers = { ".git" },
  init_options = { dialect = "postgres", schema = "schema.sql" },
})
vim.lsp.enable("buttercupql")

and in Helix’s languages.toml:

[language-server.bcql]
command = "bcql"
args = ["lsp"]
config = { dialect = "postgres", schema = "schema.sql" }

[[language]]
name = "buttercupql"
scope = "source.buttercupql"
file-types = ["bcql"]
comment-token = "#"
language-servers = ["bcql"]

The server counts places in a line in UTF-16, as the protocol says, or in UTF-8 when the editor offers it.

The playground

Open the playground to write ButtercupQL in your browser and see the SQL it compiles to as you type.

• Choose the database at the top: PostgreSQL, SQLite, DuckDB or ClickHouse.
• Each mistake is underlined, and its message shown in place of the SQL, as bcql check shows it.
• The schema below the query checks names, and lets links be followed. It starts with the tables of Schemas; change them, or clear them to compile without a schema.
• A query def or a named query can be compiled on its own: choose it beside the SQL, and see its arguments.
• A text of several statements, such as the example that changes data, shows each statement’s SQL, and the parameters of each.
• Share copies a link with the query, the database and the schema in it.

The compiler runs in the browser: it is ButtercupQL’s own compiler, built for WebAssembly. Nothing you write is sent anywhere; a shared link holds the query in the part of the address after #, which browsers do not send.

Functions and methods

Everything ButtercupQL has built in. An editor shows these descriptions as you type, from the same list.

Steps

A step that adds a column, name = expression, has no word of its own.

Statements

Each of these is a statement of its own, on a line of its own: a change to the database itself. See Changing data and Tables and permissions.

A def written with @stored on the line before it is kept in the database: see Stored defs.

Totals

Totals work over the rows of a group, in a group by or summarize block. Given over=, sort= or rows=, they work over a window of rows instead, and keep every row: see Window functions. Each can take where=, a condition of its own.

Window functions

Values

Dates and times

Methods

Parts of a date

Design and research

ButtercupQL’s design is written down in two documents in the repository:

• The language design says what each form means and why, and records each decision made while building it.
• The research looks at the languages that came before it, and at how to make a query faster to write than SQL and as fast to run.

Goals

1. Read like Python. Indentation, # comments, and Python’s own expressions and values.
2. One step per line, in the order the data flows. Each step takes rows and gives rows.
3. Faster to write than SQL, and never slower to run. The compiler writes the SQL a careful person would write.
4. Compile to SQL; never replace the database.
5. Safe by default. Every value becomes a parameter, so a query can never inject SQL, and names are checked against the schema when one is given.

Not yet

• Writing data (insert, update, delete) and creating tables.
• match, recursive queries, pivots, and subtracting one date from another.

The changelog lists what each version added.

The specification

This part of the book defines ButtercupQL: what a text must be to be one, and what it means. It is written for those who implement the language, and for anyone who needs to know exactly what a query does. The chapters before it teach the language by example; where one of them and this part disagree, this part is the language.

It describes ButtercupQL 0.2, the language of the compiler on the repository’s main branch.

• Lexical structure: how a text is split into tokens, lines and blocks.
• Grammar: the forms of files, statements, steps and expressions.
• Meaning: what each form does: values and their types, missing values, each step, totals and windows, links, definitions, changes to rows, tables, and scripts.
• The databases: how each database is given the meaning, and what each can’t do.

Conformance

An implementation of ButtercupQL conforms when:

1. it takes every text this part allows, and refuses every text it does not, with an error at the place of each mistake it finds;
2. for a text it takes, the rows it gives on each database it supports are the rows the text’s meaning gives;
3. it refuses a text that asks a database for something the database can’t do, rather than doing something else.

The conformance suite holds cases that check these, each a text and the rows or the errors it gives. ButtercupQL’s own tests run every case on SQLite, PostgreSQL, DuckDB and ClickHouse. The words of the messages are an implementation’s own; the suite’s are those of ButtercupQL’s compiler.

Notation

The grammar is written in EBNF, with these readings:

A rule’s name is in lowercase: step, expression. Where the grammar alone would take a text that is still a mistake, such as a column with two names, Meaning says so.

Lexical structure

A text is split into tokens: names, keywords, numbers, strings, operators and brackets. The end of a line, and where a line starts, give the text its shape.

Characters

A text is Unicode, written in UTF-8. A byte order mark at its start is left out. A line ends with \n, and \r\n is the same. The characters that change the order text is shown in, U+202A to U+202E and U+2066 to U+2069, are refused anywhere in a text, in its strings and comments too: they can make a query read differently from what it does.

Space and comments

Spaces, tabs and form feeds separate tokens and are otherwise left out. # starts a comment, which runs to the end of its line. A \ as the last character of a line joins the next line to it, as one line; a \ anywhere else outside a string is a mistake.

Lines

A line’s first token starts something: a step, a statement or a definition. Each ends at the end of its line, or at a ;, which lets several share a line: from cases; where risk > 90; take 5. Inside brackets, (, [ and {, the end of a line ends nothing: a step can run over as many lines as its brackets hold.

Blocks

The first line of a text sets its column, and each of its lines starts there: a text indented inside a Python string reads as it is. A line that ends with : opens a block. The lines of the block start further in than the line of the :, all at the column of the first of them, and the block ends before the first line that starts less far in. Its lines can be on the rest of the :’s line instead, where a step allows: summarize: n = count().

A line that starts further in than its block, or less far in where no block ends, is a mistake. A tab moves to the next multiple of 8; a line whose place, beside the lines it is compared with, depends on how wide a tab is, because it mixes tabs and spaces in another way, is a mistake.

In the grammar, NEWLINE stands for the end of a line or a ;, INDENT for the start of a block, and DEDENT for its end.

Names

A name starts with a letter or _, and goes on with letters, digits and _, the letters and digits of any script. An accent written as a character of its own, U+0300 to U+036F, and the joiners U+200C and U+200D can follow a letter inside a name.

A name in backticks, `order date`, is a name whatever it holds: any characters but a backtick and the end of a line, with two backticks for one. It can’t be empty. A keyword in backticks is a name: `from`.

Keywords

These are Python’s keywords that ButtercupQL uses. They are never names:

and, as, def, del, else, False, from, if, import, in, is, None, not, or, pass, True.

These are Python’s other keywords. ButtercupQL refuses each where a name or a step could be, with a message that says what to write instead: assert, async, await, break, class, continue, elif, except, finally, for, global, lambda, nonlocal, raise, return, try, while, yield, and with, but for with transaction:.

The words of the steps, where, select, sort, take, skip, distinct, group, summarize, join, left, update, delete, insert and pivot, and of the statements, create, alter, drop, grant and revoke, are names. Each has its meaning only where a step or a statement starts, at the start of a line or after a ;, and before anything that would give it a value: select = 3 makes a column named select. Elsewhere it is a name like any other. The words that follow them, such as by, on, into, table, index, unique, rename, to and transaction, are names too, with their meaning only where the grammar puts them.

Numbers

A whole number is decimal digits: 20, 1_000_000. It can be hexadecimal, octal or binary, after 0x, 0o or 0b: 0xff. An _ goes between two digits, or after the base. A decimal whole number other than 0 can’t start with 0.

A number with a fraction or an exponent is a fraction: 3.5, .5, 1e6, 2.5e-3. Letters straight after a number, as in 3px, are a mistake, and a complex number, 2j, is refused.

Strings

A string is written as Python writes one: in " or ', on one line, or in """ or ''', over as many lines as it needs. Its escapes are Python’s: \\, \', \", \a, \b, \f, \n, \r, \t, \v, \0, \x and two hexadecimal digits, \u and four, \U and eight, and a \ at the end of a line, which joins it to the next. \N{name} is refused: the character itself, or \u and its code, is written instead. Strings written one after the other are one: "open" "ed" is "opened".

Before the quote, r makes a raw string, in which \ is a \; u changes nothing; and f makes an f-string, as rf makes a raw one. In an f-string, { and } hold a value, and {{ and }} stand for { and }: f"{key}: {risk}". A format after the value, such as {risk:.2f} or {key!r}, is not supported yet. b, for bytes, is refused.

Operators and brackets

The operators are + - * / // % **, == != < <= > >=, = and the operators that give a value where it is, += -= *= /= //= %= **=, then ->, ., ,, : and ;. Between two queries, | and & combine them. @ starts a decorator at the start of a line.

The other languages’ ways of writing these, &&, ||, !, <>, ~ and :=, are each refused with ButtercupQL’s way: and, or, not, !=.

Grammar

The grammar of a ButtercupQL text, from a file down to an atom. The specification says how to read it, and Lexical structure what its tokens are. NEWLINE is the end of a line or a ;.

Files

A file is its docstring, its imports, and then its definitions and statements, in any order. A file with no statements is a module: other files import its definitions.

file           = [ docstring ] { import } { definition | statement } ;
docstring      = STRING NEWLINE ;

import         = "import" module [ "as" NAME ] { "," module [ "as" NAME ] } NEWLINE
               | "from" { "." } [ module ] "import" imported NEWLINE ;
imported       = import_names | "(" import_names [ "," ] ")" ;
import_names   = NAME [ "as" NAME ] { "," NAME [ "as" NAME ] } ;
module         = dotted ;
dotted         = NAME { "." NAME } ;

Definitions

definition     = value | def ;
value          = NAME "=" expression NEWLINE ;
def            = { decorator } "def" NAME "(" [ parameters ] ")" [ "->" type ]
                 ":" def_body ;
decorator      = "@" NAME NEWLINE ;
parameters     = parameter { "," parameter } [ "," ] ;
parameter      = NAME [ ":" type ] [ "=" expression ] ;
type           = ( dotted | "None" ) [ "[" type { "," type } "]" ] ;
def_body       = step { NEWLINE step } NEWLINE
               | NEWLINE INDENT [ docstring ] step NEWLINE { step NEWLINE } DEDENT ;

A value whose expression is a query in brackets is a named query.

Statements

statement      = query
               | call NEWLINE
               | create_table | create_index | alter_table | drop
               | grant | revoke | transaction ;

query          = from_step NEWLINE { step NEWLINE } ;

create_table   = "create" "table" dotted { option } ":" NEWLINE
                 INDENT table_line NEWLINE { table_line NEWLINE } DEDENT ;
table_line     = column | ( "primary_key" | "unique" ) "(" NAME { "," NAME } ")" ;
column         = NAME ":" type [ "|" "None" ] [ "=" expression ] ;
option         = "," NAME "=" expression ;

create_index   = "create" [ "unique" ] "index" [ NAME ] "on" dotted
                 "(" sort_key { "," sort_key } ")" { option } NEWLINE ;

alter_table    = "alter" "table" dotted ":" NEWLINE
                 INDENT table_change NEWLINE { table_change NEWLINE } DEDENT ;
table_change   = column | "del" NAME | "rename" NAME "to" NAME ;

drop           = "drop" ( "table" | "index" ) dotted { option } NEWLINE ;

grant          = "grant" permissions "on" tables "to" roles NEWLINE ;
revoke         = "revoke" permissions "on" tables "from" roles NEWLINE ;
permissions    = NAME { "," NAME } ;
tables         = dotted { "," dotted } ;
roles          = NAME { "," NAME } ;

transaction    = "with" "transaction" ":" NEWLINE
                 INDENT statement { statement } DEDENT ;

Steps

A step is on a line of its own, or after a ;. A word that names a step is a step only where a step starts, and not when an = follows it, which makes it a column.

step           = from_step | where_step | column_step | select_step
               | del_step | sort_step | take_step | skip_step
               | distinct_step | group_step | summarize_step
               | pivot_step | join_step | call_step | "pass" | change ;
change         = update_step | delete_step | insert_step ;

from_step      = "from" source ;
source         = ( dotted | call | "(" query ")" | list ) [ "as" NAME ] ;
where_step     = "where" expression ;
column_step    = NAME ( "=" | update_operator ) expression ;
select_step    = "select" fields ;
del_step       = "del" dotted { "," dotted } ;
sort_step      = "sort" sort_key { "," sort_key } [ "," "reverse" "=" expression ] ;
sort_key       = [ "-" ] expression ;
take_step      = "take" expression ;
skip_step      = "skip" expression ;
distinct_step  = "distinct" ;
group_step     = "group" "by" fields [ ":" totals ] ;
summarize_step = "summarize" ":" totals ;
pivot_step     = "pivot" sum "in" ( list | tuple ) [ "by" fields ] ":" totals ;
join_step      = [ "left" ] "join" source "on" expression ;
call_step      = call ;

update_step    = "update" assignment { "," assignment } ;
assignment     = NAME ( "=" | update_operator ) expression ;
update_operator = "+=" | "-=" | "*=" | "/=" | "//=" | "%=" | "**=" ;
delete_step    = "delete" ;
insert_step    = "insert" "into" dotted ;

fields         = field { "," field } [ "," ] | "(" field { "," field } [ "," ] ")" ;
field          = [ NAME "=" ] expression ;
totals         = total { NEWLINE total } NEWLINE
               | NEWLINE INDENT total NEWLINE { total NEWLINE } DEDENT ;
total          = NAME "=" expression ;
call           = dotted "(" [ arguments ] ")" ;

The first step of a query is its from, and a query has no other. A change ends its query: only a select can follow it, which gives the rows changed, and a new statement starts after it. Before an update or a delete, only where, pass and calls can follow the from. A def’s body is a query when its first step is a from, and steps to put in place otherwise.

Expressions

The forms bind as Python’s do, from the loosest to the tightest.

expression     = disjunction [ "if" disjunction "else" expression ] ;
disjunction    = conjunction { ( "or" | "|" ) conjunction } ;
conjunction    = inversion { ( "and" | "&" ) inversion } ;
inversion      = "not" inversion | comparison ;
comparison     = sum { comparator sum } ;
comparator     = "==" | "!=" | "<" | "<=" | ">" | ">="
               | "in" | "not" "in" | "is" | "is" "not" ;
sum            = term { ( "+" | "-" ) term } ;
term           = factor { ( "*" | "/" | "//" | "%" ) factor } ;
factor         = ( "-" | "+" ) factor | power ;
power          = primary [ "**" factor ] ;
primary        = atom { "." NAME | "(" [ arguments ] ")" } ;
arguments      = argument { "," argument } [ "," ] ;
argument       = [ NAME "=" ] expression ;

atom           = NAME | NUMBER | strings | "True" | "False" | "None"
               | "(" expression ")" | tuple | list | set | dict | "(" query ")" ;
strings        = ( STRING | FSTRING ) { STRING | FSTRING } ;
tuple          = "(" [ expression "," [ expression { "," expression } [ "," ] ] ] ")" ;
list           = "[" [ expression { "," expression } [ "," ] ] "]" ;
set            = "{" expression { "," expression } [ "," ] "}" ;
dict           = "{" expression ":" expression
                 { "," expression ":" expression } [ "," ] "}" ;

| and & stand between two queries alone: queries in brackets, or the names of named queries. + and - between two queries combine them too. A comparison chains, as in Python: a < b <= c is a < b and b <= c, with b read once.

Python’s other forms are refused, each with a message that says what to write: comprehensions, subscripts such as name[0], lambda, :=, * and ** in arguments and parameters, / and * among parameters, and a parameter without a default after one with a default.

Meaning

What each form of the grammar does. A query works on rows: a list of them, each with the same named columns, and an order where one has been asked for. Each step takes the rows of the steps before it and gives rows to the step after it. The meaning is Python’s wherever Python has one, and SQL’s where Python has none, as for a comparison with a missing value.

Texts and statements

A text’s statements run in the order they are written: its queries, its changes to rows, and its changes to the database. Each create table, alter table and drop table changes the tables the statements after it read. The rows a text gives are those of its query, the one statement that is a query and changes no rows; a text has one at most, and a module has none. A with transaction: block’s statements run together, all of them or none.

Values

Kinds

A value is a whole number (int), a number with a fraction (float), an exact decimal (Decimal), text (str), a truth (bool), a date (date), a date and time (datetime), a length of time, or missing (None). A column’s kind is the one its table gives it, as a schema says, and a value’s kind follows from its parts. A value whose kind no schema says is of a kind not known, and is taken as it is.

A number written in a text is a whole number, or a fraction when it has a point or an exponent; a string is text; True and False are truths; None is the missing value. Text written in a query is passed to the database as a parameter, never written into its SQL, unless literal() asks for it there; numbers, truths, dates and times are written into it. Where a database takes no parameters, as in a table’s default or a stored def, text is written into the SQL too, its quotes doubled.

Numbers

+, - and * are Python’s. / divides exactly, even two whole numbers: 7 / 2 is 3.5. // divides and rounds down: -7 // 2 is -4. % gives the remainder with the sign of the number divided by: -7 % 3 is 2. ** raises to a power. Unary - and + are Python’s, and so are abs(), int(), which drops the fraction, and float().

round(x) rounds to a whole number and round(x, n) to n places, a negative n to tens, hundreds and on; as in Python, a half goes to the even neighbour: round(2.5) is 2, and round(3.5) is 4. A fraction rounded to places is scaled in the database’s own arithmetic, so in the rare case where the scaled fraction is a half only in that arithmetic, as round(2.675, 2) is, the answer is that of Python’s Decimal, 2.68, rather than Python’s float 2.67.

Text

+ joins text, and so does an f-string, in which each value in braces is written as text. len() counts characters. lower(), upper(), strip(), replace(), startswith() and endswith() are Python’s methods; x in y, with both text, says whether x is in y. Each minds capitals, and takes its text exactly: % and _ are characters, not patterns. str() writes a value as text.

Dates and times

date("2026-10-20") and datetime("2026-10-20 14:30") are a date and a date and time; today() and now() are today and now. days(n), hours(n), minutes(n), seconds(n) and weeks(n) are lengths of time, which add to a date or a time, or take from it. .year, .month, .day, .hour, .minute and .second are the parts of a date or a time, as numbers, and .date() is the day of a date and time. Dates and times compare in time’s order.

Missing values

• A comparison with a missing value is neither true nor false, as in SQL, and so are not of it and an and or an or that it decides. A where keeps a row only when its condition is true.
• x == None and x is None hold when x is missing; x != None and x is not None when it is not.
• x in [a, b] holds when x is one of the values; with None among them, when x is missing too. x not in [a, b] holds when x is there and is none of them, None among them or not.
• Arithmetic, text and dates made from a missing value are missing.
• Missing values sort last, high to low and low to high.

Truth

Where a condition is wanted, a value is taken by Python’s truth: 0, 0.0, "", False and None are false, and every other value is true. A missing value is false, so not notes holds where notes is missing. and, or and not are on truths, and give truths.

Names

A name is, in this order:

1. a parameter of the def it is in;
2. a value named before, threshold = 80, in the text or imported into it: the name means the value even where a column has the name too, so a new column can never change what a query does;
3. a column of the rows at that place, the newest first. When no column has the name exactly, one whose name is the same but for capitals is.

A name that is none of these is a mistake, with a suggestion where a name is close to it. A name that is the column of two tables is a mistake: table.column says which. A dotted name is a table’s column, cases.risk; a link’s, account.operator; a module’s value or def, shared.recent; or a part of a date: raised.year.

Steps

from gives the rows of a table, of a named query, of a call of a query def, of a query in brackets, or of rows written in the text: a list of dicts, whose keys, the same in every row, are the columns. as names what it reads.

where keeps the rows its condition is true for. After a group by, it keeps the groups; after a window value, it is worked out after the value.

A column step, name = value, adds a column to every row, or gives one a new value. name += value and its kin are name = name + value. The steps after it read the new value.

select makes the rows’ columns those it names, in its order, each named by its = or by the column it reads. Its values read the columns before it, not each other: select risk = risk * 2, before = risk gives the old risk as before. Two columns of one name are a mistake.

del drops the columns it names.

sort puts the rows in the order of its keys, the first first, each low to high or, after -, high to low; reverse=True turns each around. It is stable, as Python’s is: rows that tie keep the order they had, so a sort after another keeps the first for its ties. A key reads the columns at its place: a sort before a select that gives a column a new value sorts by the old one.

take n keeps the first n rows, and skip n drops them; n is a whole number, or a def’s parameter. Steps after them work on the rows they kept: a take after a take keeps the fewer, and a where after a take keeps some of the rows taken.

distinct drops the rows that repeat one before them in every column.

group by gives one row for each group of rows with the same values of its columns; rows with a missing value make a group of their own. The row’s columns are the group’s values, then the totals of the block, each worked out over the group’s rows. With no block, the rows are the groups’ values alone.

summarize gives one row of totals, worked out over every row, even when there are none.

pivot col in [a, b] by key: is a group by key whose totals are each worked out for each value: a total of the rows where col == a, then of those where col == b. The columns are named by the values, or, with several totals, by the value and the total’s name: a_n. With no by, the totals are of every row, in one row. The values are written in the text, each a string, a number, a truth or None.

join source on condition pairs each row with each of the source’s rows for which the condition is true, and drops the rows that pair with none. left join keeps those, with the source’s columns missing. The columns of both can be read, and table.column reads one that both have.

pass does nothing. A call of a def whose body is steps puts them in its place, its parameters given the call’s arguments.

Totals

A total is worked out over the rows of a group, in a block of group by, summarize or pivot. count() is the number of rows, and count(x) of the values of x that are not missing; count_distinct(x) is the number of different values. sum(x), mean(x), which avg(x) is too, min(x) and max(x) leave out missing values. any(condition) says whether the condition is true on some row, and all(condition) whether on every row.

Of no rows, or no values, count() and sum() are 0, any() is False and all() is True, as in Python; mean(), min() and max() are missing. Each total takes where=, a condition of its own: count(where=status == "open") counts the open rows alone. A total in a block can read the totals before it.

Window functions

A total with over=, sort= or rows= is a window value: worked out for each row, over a window of rows, and keeping every row. over= names the columns whose values split the rows into groups; sort= orders each group; and rows=n makes the window the row and the n - 1 before it. With sort=, a total runs: each row’s is of the rows up to it. row_number(), rank() and dense_rank() number the rows in the order of sort=; lag(x) and lead(x) are the value of the row before and after, missing at either end; first(x) and last(x) are the first and last of the window.

A window value counts the rows the steps before it kept: after a take 3, it is worked out over three rows. A condition on it is worked out after it, so it needs a name of its own first: place = rank(sort=-risk), then where place <= 3.

Links

A column whose table says it holds another table’s key, as SQL’s REFERENCES does, is a link to that table. The link is named for the column, without _id: account_id is the link account. account.operator reads the operator of the account a row points to, and a link reads on through the next: account.bank.name. Each link is followed once in a query, keeping every row: a row whose link leads nowhere has its linked values missing.

Definitions

A value, name = value, is worked out once, with no rows: a number, a text or a date, or a value made from them. A named query, name = (from ...), is read by from and join where it is named, and in other queries.

A def whose body starts with from is a query, read by from and join where it is called: from cases_for("A1"). Otherwise its body is steps, put in place where it is called on a line of a query. A parameter takes the argument given in its place or by its name, and its default when none is. Compiled on its own, a query def’s parameters are left as placeholders, given values when the query runs. A def whose steps change rows is called on a line of its own, as a statement.

An import reads another file’s definitions: import shared makes them shared.name, and from shared import recent makes recent one of the text’s own names. A module is found by its dotted name, from the text’s own folder, or from its own with from . import.

Combining queries

a + b gives the rows of a, then those of b. a | b gives the rows of either, each once; a & b those in both, each once; and a - b those of a that are not in b, each once. The second query’s columns are matched to the first’s by their names; two queries with different columns are a mistake.

A named query that reads itself is recursive. Its first part gives the first rows, and names the columns; its second reads the rows found so far, and gives the next, until it gives no more. With +, every row it finds is kept; with |, a row found already is not added again, so the query ends when it finds no new row. A recursive query reads itself only in its second part.

Changes to rows

A change is the last step of its query. update gives the columns it names new values, on the rows the steps before it choose, each value worked out from the row as it was. delete removes those rows; with no where, it removes every row, and says so with a warning. insert into table adds the rows of the steps before it to the table, each column into the table’s column of the same name; the table’s other columns take their defaults.

Only where, pass and calls of defs of wheres can choose the rows an update or a delete changes: they work on rows in no order. A condition that follows a link chooses the rows by their keys. A select after a change gives the rows changed, as they are after it.

Tables

create table name: makes a table, its columns in the block below it, each written as a Python annotation: name: type. The types are int, float, Decimal, str, bool, date, datetime and bytes. A column is never missing unless its type ends with | None, or is Optional[...]. = value gives its default, and field() the rest: primary_key=True, unique=True, references=table.column and default=. A line primary_key(a, b) or unique(a, b) makes a key of several columns. exist_ok=True makes the table only if there is none of its name.

create index on table(keys) makes an index of the keys, each low to high or, after -, high to low, with missing values last; create unique index one in which no two rows have the same values. A name may follow index. where= keeps only the rows a condition chooses, and exist_ok=True makes it only if there is none of its name.

alter table name: changes a table, a change on each line: a column, written as in create table, is added; rename a to b renames a column; del a drops one. drop table and drop index drop a table or an index, and missing_ok=True only if it is there.

grant lets users or roles do what it names on tables, and revoke takes it away: read; insert, update and delete; write, which is those three; and all.

@stored on the line before a def keeps it in the database, to be run by its name: a query def as a function, a macro or a view with parameters, and a def that changes rows as a procedure. A stored def’s parameters say their types where its database needs them.

Mistakes

A text that does not fit the grammar, a name that means nothing, a value of the wrong kind for its place, a step where it can’t be, and a form a database can’t run are mistakes. An implementation reports every mistake it finds in a text, each at its place, and compiles none of a text that has one. A warning is a text that compiles but likely does not do what was meant, such as a delete with no where, or a sort that a grouping after it undoes.

The databases

A text means the same on every database. This chapter says how ButtercupQL’s compiler gives each database that meaning, which another implementation can do in its own way, and what each database can’t do, which every implementation refuses alike.

SQLite

• Placeholders are ?.
• / is written x * 1.0 / y, so that whole numbers divide exactly. SQLite’s builds may have no floor() and no power(), so // is written with CAST, and a whole power up to 16 is multiplied out. Any other power needs a build with SQLite’s math functions, as Python’s sqlite3 has.
• SQLite puts missing values first when sorting low to high, so NULLS LAST is written there.
• Dates and times are text, '2026-10-20 14:30:00', worked on with date(), datetime() and strftime(); the columns of date and datetime are TEXT.
• SQLite has no users, so grant and revoke are refused, and keeps no queries, so @stored is.

PostgreSQL

• Placeholders are $1, or %s for psycopg.
• PostgreSQL puts missing values first when sorting high to low, so DESC NULLS LAST is written there, and in an index’s keys.
• A stored query def is a SQL function, RETURNS TABLE with its columns’ types, which the schema says, or RETURNS SETOF a table for its whole rows. A stored def that changes rows is a procedure, written with BEGIN ATOMIC, which needs PostgreSQL 14 or later. Its parameters are written with their types.

DuckDB

• Placeholders are $1.
• / already divides exactly, and missing values already sort last.
• A condition on a window value is written in QUALIFY.
• A stored query def is a table macro. DuckDB keeps no procedures, so a stored def that changes rows is refused.
• DuckDB has no users, so grant and revoke are refused. Its indexes take no condition and no direction, so where= on an index is refused, and its keys are written low to high.
• DuckDB refuses some changes to tables that other tables’ keys point to, and says so as it runs them: renaming one of their columns, making one never missing, and giving back the rows an update changed.

ClickHouse

• Placeholders are named and typed, {p1:String}, and their values are passed by name.
• A total’s condition is written in ClickHouse’s own functions, countIf() and sumIf(), and a total of no rows that is missing in Python, max(), with maxIfOrNull().
• A query with a left join is given SETTINGS join_use_nulls = 1, so that a row with no match has missing values, not empty ones.
• ClickHouse reads a name as a column the query gives wherever the name is written alone, so where a select gives a column the name of one it reads, every column of the query is named with its table.
• An update is a mutation, ALTER TABLE ... UPDATE, with mutations_sync = 1 so that it is done before the statement returns. A delete with no where is written WHERE true.
• A table is a MergeTree, its rows kept in the order of its primary key, and a column that can be missing is Nullable. ClickHouse keeps no unique keys and no references, so they are left out, with a warning.
• A stored query def is a view with parameters, which take no defaults: they are left out, with a warning.
• Permissions are granted one table at a time, and write is INSERT, ALTER UPDATE and ALTER DELETE.
• ClickHouse runs no transactions, has no indexes like create index, can’t give back the rows a change makes, and keeps every row of a recursive query, so with transaction:, create index, a select after a change, and | in a recursive query are refused.