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.

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.

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 as 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
insert Event { name := "login", ts := now() }
Event { .name, yr: extract("year", .ts) }

-- Cast
User { .name, age_str: cast(.age as 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" }

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

-- Insert or update on conflict
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 }

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

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)
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
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 ...