PowQL Reference

PowQL is a pipeline-oriented query language. You name the table, chain operations left to right, and project fields -- all in reading order.

Prefer SQL? PowDB also ships a SQL frontend that lowers a supported subset to this same engine and plan cache — see docs/SQL.md. Everything below is the native PowQL language.

On this page

Data Types
Schema Definition
Queries
Expressions
Aggregates
GROUP BY + HAVING
Joins
Mutations
Transactions
DDL
Materialized Views
Window Functions
PowQL vs SQL Cheat Sheet

Data Types

PowQL has seven data types plus a null representation.

Type	PowQL Name	Size	Description
Integer	`int`	8 bytes	64-bit signed integer
Float	`float`	8 bytes	IEEE 754 double precision
Boolean	`bool`	1 byte	`true` or `false`
String	`str`	Variable	UTF-8 text
DateTime	`datetime`	8 bytes	Unix timestamp
UUID	`uuid`	16 bytes	128-bit identifier
Bytes	`bytes`	Variable	Raw binary data
Null	(empty)	0 bytes	Absence of a value

Fields marked required cannot be null. All other fields are nullable by default. Use is null / is not null to check, and ?? to coalesce. A field marked unique (e.g. required unique email: str, modifiers in either order) auto-creates a unique index and rejects duplicate non-null values on insert/update/upsert.

Schema Definition

Tables are defined using the type keyword:

type User {
  required name: str,
  required email: str,
  age: int
}

type Record {
  required id: int,
  required title: str,
  score: float,
  active: bool,
  created_at: datetime,
  ref_id: uuid,
  payload: bytes
}

Queries

Queries are pipeline-oriented: start with a table name, then chain operations left to right.

Full Scan

User

Filter

User filter .age > 30
User filter .name = "Alice"
User filter .age > 25 and .status = "active"
User filter .age < 20 or .age > 60

Projection

Select specific fields using { } braces. Reference fields with .field dot syntax:

User { .name, .email }
User filter .age > 30 { .name, .age }

-- Aliases
User { full_name: .name, years: .age }

-- Computed expressions
User { .name, double_age: .age * 2 }

Ordering

User order .age
User order .age desc
User order .age asc, .name desc

Limit and Offset

User limit 10
User order .age desc limit 5
User order .age offset 20 limit 10

Distinct

User distinct { .name }
User filter .age > 20 distinct { .status }

Full Pipeline

The full pipeline order:

Table [distinct] [filter expr] [group keys [having expr]] [order keys] [limit n] [offset n] { projection }

Example:

User filter .age > 18 order .name asc limit 100 offset 20 { .name, .email, .age }

Expressions

Comparison Operators

Operator	Meaning	Example
`=`	Equal	`User filter .name = "Alice"`
`!=`	Not equal	`User filter .score != 0`
`<` `>`	Less/greater than	`User filter .age > 30`
`<=` `>=`	Less/greater or equal	`User filter .age >= 18`

Logical Operators

User filter .age > 25 and .status = "active"
User filter .age < 20 or .age > 60
User filter not .active

IN, BETWEEN, LIKE

-- IN list
User filter .name in ("Alice", "Bob")
User filter .name not in ("Alice")

-- BETWEEN (inclusive)
User filter .age between 25 and 35

-- LIKE pattern matching
User filter .name like "Ali%"
User filter .name not like "A%"

NULL Checks and Coalesce

User filter .age is null
User filter .age is not null

-- Coalesce: return left if non-null, else right
User { .name, display_age: .age ?? 0 }

CASE WHEN

User {
  .name,
  label: case
    when .age > 30 then "senior"
    when .age >= 20 then "adult"
    else "young"
  end
}

Functions

Category	Functions
String	`upper`, `lower`, `length`, `trim`, `substring`, `concat`
Math	`abs`, `round`, `ceil`, `floor`, `sqrt`, `pow`
Date/Time	`now`, `extract`, `date_add`, `date_diff`
Type	`cast(expr, "type")`

-- String functions
User filter upper(.name) = "ALICE"
User { full: concat(.name, " - ", .email) }
User { sub: substring(.name, 1, 3) }

-- Math functions
User { .name, rounded: round(.score, 2) }

-- Date/time functions (datetime is microseconds since the Unix epoch)
insert Event { name := "login", ts := 1700000000000000 }
Event { .name, yr: extract("year", .ts) }

-- Cast
User { .name, age_str: cast(.age, "str") }

Aggregates

Aggregate functions wrap a query in function-call syntax:

count(User)                              -- count all rows
count(User filter .age > 30)             -- count with filter
sum(User { .age })                       -- sum a column
avg(User { .age })                       -- average
min(User { .age })                       -- minimum
max(User { .age })                       -- maximum
count(distinct User { .name })           -- count unique values

GROUP BY and HAVING

-- Count users per name
User group .name { .name, n: count(.name) }

-- Multiple aggregates per group
User group .status {
  .status,
  total: count(.name),
  avg_age: avg(.age),
  youngest: min(.age),
  oldest: max(.age)
}

-- HAVING: filter groups after aggregation
User group .status having count(.name) > 5 { .status, n: count(.name) }

-- Filter before grouping
User filter .age >= 30 group .name { .name, n: count(.name) }

-- count(*) and count(distinct) in groups
User group .age { .age, count(*) }
Sale group .dept { .dept, count(distinct .item) }

Joins

PowQL supports inner, left outer, right outer, and cross joins. Aliases disambiguate fields.

-- Inner join (default)
User as u join Order as o on u.id = o.user_id

-- Left outer join
User as u left join Order as o on u.id = o.user_id

-- Right outer join
User as u right join Order as o on u.id = o.user_id

-- Cross join (no ON clause)
User as u cross join Product as p

-- With filter and projection
User as u join Order as o on u.id = o.user_id
  filter o.total > 75 { u.name, o.total }

-- Multi-table join
User as u join Order as o on u.id = o.user_id
  join Product as p on o.product_id = p.id

The engine automatically selects hash join (O(L+R)) for equi-joins and nested loop for non-equi predicates.

Mutations

INSERT

insert User { name := "Alice", email := "alice@example.com", age := 30 }

-- Omitted fields default to null
insert User { name := "Bob", email := "bob@example.com" }

-- Multi-row insert: separate row blocks with commas.
-- One statement = one WAL fsync = one round trip; all-or-nothing on validation.
insert User
  { name := "Alice", email := "alice@example.com", age := 30 },
  { name := "Bob",   email := "bob@example.com" },
  { name := "Carol", email := "carol@example.com", age := 41 }

UPDATE

-- Set a literal value
User filter .name = "Alice" update { age := 31 }

-- Expression referencing current row
User filter .name = "Alice" update { age := .age + 5 }

-- Update all rows
User update { age := .age * 2 }

DELETE

User filter .name = "Bob" delete
User filter .age < 18 delete

-- Delete all rows
User delete

UPSERT

The on column must be unique — declare it unique in the type, or run alter User add unique .email on a column that is not already plainly indexed (adding unique to an already-indexed column errors; declare it unique up front instead).

-- Insert or update on conflict (.email must be unique)
upsert User on .email { name := "Alice", email := "alice@example.com", age := 30 }

-- Explicit conflict handling
upsert User on .email { name := "Alice", email := "alice@example.com", age := 30 }
  on conflict { age := 30 }

Transactions

Statements run in autocommit by default -- each is durable on its own. Wrap multiple statements in begin / commit to apply them atomically, or rollback to discard:

-- Atomic multi-statement write
begin
insert User { name := "Alice", email := "alice@example.com", age := 30 }
insert User { name := "Bob", email := "bob@example.com", age := 25 }
commit    -- apply all changes

-- Discard uncommitted changes
begin
insert User { name := "Charlie", email := "charlie@example.com", age := 40 }
rollback  -- discard all changes

Transactions are per-connection; other connections never see uncommitted rows, and a connection that closes before commit rolls back implicitly. Nested begin is an error. Statements between begin and commit share a single WAL fsync, so bulk loads inside a transaction run roughly 50x faster than autocommit with identical durability.

Concurrency: PowDB has no MVCC. An explicit transaction holds the whole write-admission gate for its lifetime, so from begin until commit or rollback every other connection that needs the gate (readers included) waits. Keep explicit transactions short, and prefer autocommit on read-mostly paths (an autocommit writer releases admission before the fsync wait). A connection that waits past the server timeout (5 seconds by default, POWDB_TX_WAIT_TIMEOUT_MS) fails with a transaction gate timeout error rather than blocking forever.

DDL

Create Table

type User { required name: str, required email: str, age: int }

Alter Table

-- Add a column
alter User add column status: str
alter User add required active: bool

-- Drop a column
alter User drop column email

-- Create an index
alter User add index .email
alter User add index .age

-- Add a unique constraint (scans for existing dups first; fails if any)
alter User add unique .email

Drop Table

drop User

Materialized Views

-- Create a view
materialize OldUsers as User filter .age > 28
materialize UserNames as User { .name }

-- Query it like a table
OldUsers
OldUsers filter .name = "Alice"
count(OldUsers)

-- Manual refresh
refresh OldUsers

-- Drop
drop view OldUsers

Views auto-refresh when underlying data changes. No stale reads.

Window Functions

-- ROW_NUMBER
User { .name, .dept, rn: row_number() over (partition .dept order .age) }

-- RANK / DENSE_RANK
User { .name, r: rank() over (order .score desc) }
User { .name, dr: dense_rank() over (partition .dept order .score desc) }

-- Aggregate windows
User { .name, .salary, dept_avg: avg(.salary) over (partition .dept) }
User { .name, running_total: sum(.amount) over (order .date) }

PowQL vs SQL Cheat Sheet

Operation	PowQL	SQL
Select all	`User`	`SELECT * FROM User`
Select columns	`User { .name, .age }`	`SELECT name, age FROM User`
Where	`User filter .age > 30`	`SELECT * FROM User WHERE age > 30`
Order + Limit	`User order .age desc limit 5`	`... ORDER BY age DESC LIMIT 5`
Count	`count(User filter .age > 30)`	`SELECT COUNT(*) FROM User WHERE age > 30`
Sum	`sum(User { .age })`	`SELECT SUM(age) FROM User`
Group By	`User group .status { .status, count(.name) }`	`SELECT status, COUNT(name) FROM User GROUP BY status`
Inner Join	`User as u join Order as o on u.id = o.user_id`	`... JOIN Order o ON u.id = o.user_id`
Insert	`insert User { name := "Alice", age := 30 }`	`INSERT INTO User (name, age) VALUES ('Alice', 30)`
Update	`User filter .id = 1 update { age := 31 }`	`UPDATE User SET age = 31 WHERE id = 1`
Delete	`User filter .id = 1 delete`	`DELETE FROM User WHERE id = 1`
Create table	`type User { required name: str }`	`CREATE TABLE User (name TEXT NOT NULL)`
Create index	`alter User add index .email`	`CREATE INDEX ON User (email)`
Unique column	`type User { unique email: str }`	`CREATE TABLE User (email TEXT UNIQUE)`
Add unique	`alter User add unique .email`	`CREATE UNIQUE INDEX ON User (email)`
Alias	`User { full_name: .name }`	`SELECT name AS full_name FROM User`
NULL check	`User filter .age is null`	`... WHERE age IS NULL`
Coalesce	`.age ?? 0`	`COALESCE(age, 0)`

Key Syntactic Differences

Concept	PowQL	SQL
Field reference	`.field` (dot prefix)	`field` (bare identifier)
Assignment	`:=`	`=` or `SET col = val`
Table definition	`type Name { ... }`	`CREATE TABLE Name (...)`
Required / NOT NULL	`required field: type`	`field TYPE NOT NULL`
Unique constraint	`unique field: type`	`field TYPE UNIQUE`
String literals	`"double quotes"`	`'single quotes'`
Query shape	Pipeline: `Table verb verb { proj }`	Clausal: `SELECT proj FROM Table WHERE ...`
Aggregates	Wrapping: `count(Table filter ...)`	Inline: `SELECT COUNT(*) FROM ...`