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
Nested Projections
Entity Links
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.

Nested Projections (Shaped Results)

A projection field can be a whole correlated child query. The result is one row per parent with its children as a native JSON array: no join fan-out to undo, no NULL-vs-empty ambiguity (a parent with zero children gets []), and no JSON text round-trip.

# One row per user; orders nested as a JSON array
User as u {
  u.name,
  orders: Order as o filter o.user_id = u.id
    order o.total desc limit 3
    { o.total, o.product_id }
}
# Alice, [{"product_id":102,"total":20.25},{"product_id":101,"total":9.5}]
# Cara,  []

Children support their own filter, order, limit, and offset per parent, and nesting composes to multiple levels. PowDB's SQL frontend has no equivalent by design, because a SQL SELECT list is flat. Other engines reach the same shape through a correlated subquery and a JSON aggregate function.

Entity Links (Relationship Traversal)

Declare a relationship once, then traverse it by name. Cardinality is derived from the schema: a link onto a unique key is to-one and reads as a scalar path; anything else is to-many and reads as a labeled block.

# Declare once
link Order.user -> User on user_id = id
link User.orders -> Order on id = user_id

# To-one: scalar path
Order as o { o.id, o.user.name }

# To-many: labeled block returning a JSON array
User as u { u.name, orders: u.orders order total desc { total } }

A scalar hop through a non-unique key is refused with a typed error naming the fix, never answered by silently multiplying rows. Inspect declared links with schema links or describe <Type>.

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 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. A statement between begin and commit does not fsync on its own; the WAL fsyncs every 64 records and at the commit, so a bulk load inside a transaction runs roughly 50x faster than autocommit with identical durability.

Concurrency: PowDB has no MVCC. An explicit transaction holds the write-admission gate from begin until commit or rollback, so every other connection that needs to write waits for it. Reads are admitted until the transaction's first write; from that write on, readers wait too. 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) }

This page is a working subset. The complete language reference (JSON documents and -> paths, subqueries, UPSERT, EXPLAIN, prepared queries, introspection, reserved words, the full function list) is docs/POWQL.md.

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