RiverQL Reference

find users

find [name, email] from users

find [name, email, department] from users
where status = "active"

find users where status = "active"

find users where status = "active" and age > 21

find [name, salary] from users
order by salary desc

find [name, department, salary] from employees
order by department asc, salary desc

find users limit 10

find [name, email] from users
order by created_at desc
limit 20 offset 40

find [name, email, department, salary] from users
where status = "active" and salary > 50000
order by salary desc
limit 10

find users where age > 21
find users where salary >= 75000
find products where price < 100

find users where status = "active" and department = "Engineering"
find users where department = "Sales" or department = "Marketing"
find users where not status = "banned"

find users
where (department = "Sales" or department = "Marketing")
  and salary > 60000

find users where deleted_at is null
find users where email_verified_at is not null

-- coalesce: first non-null value
find [name, coalesce(nickname, name) as display_name] from users
 
-- nullif: returns NULL if values are equal (avoids division by zero)
find [name, nullif(discount, 0) as effective_discount] from products
 
-- ifnull: two-argument coalesce shorthand
find [name, ifnull(phone, "N/A") as contact] from users

find [name, created_at] from users
where created_at between "2024-01-01" and "2024-12-31"
 
find products where price between 10 and 50

find users where status in ("active", "pending")
find users where department not in ("HR", "Legal")

find users where name like "%smith%"
find users where email like "%@gmail.com"
find users where name like "J_n"
find users where name ilike "%smith%"  -- case-insensitive

find [name, price * 1.1 as price_with_tax] from products
find [first || " " || last as full_name] from users
find [abs(amount), round(price, 2), ceil(score), floor(score)] from products

-- Function style
find [name, cast(age as string) as age_str] from users
 
-- Shorthand operator ::
find [name, created_at::string as date_str] from users

find users where created_at > now() - 30d
find users where last_login < now() - 90d
find users where created_at > now() - 1h

:start_date = "2024-01-01"
:end_date = "2024-12-31"
 
find [name, total, created_at] from orders
where created_at between :start_date and :end_date

find [u.name, o.total]
from users as u
join orders as o on u.id = o.user_id

find [u.name, o.total]
from users as u
left join orders as o on u.id = o.user_id

find [u.name, o.total]
from users as u
right join orders as o on u.id = o.user_id

find [u.name, o.total]
from users as u
full join orders as o on u.id = o.user_id

find [u.name, p.name as product]
from users as u
cross join products as p
limit 100

find [u.name, o.total, p.name as product]
from users as u
join orders as o on u.id = o.user_id
join order_items as oi on o.id = oi.order_id
join products as p on oi.product_id = p.id

find [e.name, m.name as manager]
from employees as e
left join employees as m on e.manager_id = m.id

find [u.name, o.total, o.status]
from users as u
join orders as o on u.id = o.user_id
where o.status = "paid" and o.total > 100
order by o.total desc
limit 20

find [count(*)] from users
find [sum(total)] from orders
find [avg(salary)] from employees
find [min(price), max(price)] from products

find [
  count(*) as total_orders,
  sum(total) as revenue,
  avg(total) as avg_order,
  min(total) as smallest,
  max(total) as largest
]
from orders
where status = "paid"

find [department, count(*) as headcount]
from employees
group by department

find [department, status, count(*) as cnt]
from employees
group by department, status

find [
  category,
  count(*) as product_count,
  avg(price) as avg_price,
  min(price) as cheapest,
  max(price) as most_expensive
]
from products
group by category
order by product_count desc

find [user_id, count(*) as order_count]
from orders
group by user_id
having count(*) > 5

find [department, avg(salary) as avg_sal]
from employees
where status = "active"
group by department
having avg(salary) > 75000

find [u.name, count(*) as order_count, sum(o.total) as total_spent]
from users as u
join orders as o on u.id = o.user_id
where o.status = "paid"
group by u.name
having total_spent > 1000
order by total_spent desc
limit 10

function() over (partition by col order by col)

find [
  name,
  department,
  salary,
  row_number() over (partition by department order by salary desc) as rank
]
from employees

-- rank(): ties share a rank, next rank has a gap (1, 1, 3...)
find [name, score, rank() over (order by score desc) as position]
from players
 
-- dense_rank(): no gaps (1, 1, 2...)
find [name, score, dense_rank() over (order by score desc) as position]
from players

find [
  date,
  revenue,
  lag(revenue, 1) over (order by date) as prev_day,
  lead(revenue, 1) over (order by date) as next_day
]
from daily_stats

-- Day-over-day change
find [
  date,
  revenue,
  revenue - lag(revenue, 1) over (order by date) as daily_change
]
from daily_stats

find [
  date,
  amount,
  sum(amount) over (order by date) as running_total
]
from transactions

find [
  name,
  department,
  salary,
  avg(salary) over (partition by department) as dept_avg,
  salary - avg(salary) over (partition by department) as diff_from_avg
]
from employees

find [
  name,
  department,
  salary,
  avg(salary) over w as dept_avg,
  max(salary) over w as dept_max,
  min(salary) over w as dept_min
]
from employees
window w as (partition by department)

-- Top 3 highest-paid employees per department
find * from (
  find [
    name, department, salary,
    row_number() over (partition by department order by salary desc) as rn
  ]
  from employees
) as ranked
where rn <= 3

with active_users as (
  find * from users where status = "active"
)
find [name, email] from active_users

with
  paid_orders as (
    find * from orders where status = "paid"
  ),
  user_totals as (
    find [user_id, sum(total) as revenue]
    from paid_orders
    group by user_id
  )
find [u.name, ut.revenue]
from users as u
join user_totals as ut on u.id = ut.user_id
where ut.revenue > 10
order by ut.revenue desc

with recursive org_tree as (
  -- Base case: top-level employees
  find * from employees where manager_id is null
  union all
  -- Recursive: join children to parent
  find [e.*]
  from employees as e
  join org_tree as t on e.manager_id = t.id
)
find * from org_tree

-- Scalar subquery in WHERE
find [name, salary] from users
where salary > (
  find [avg(salary)] from users
)

-- IN subquery
find [name, department] from employees
where department in (
  find distinct [department] from departments
  where budget > 100000
)

-- EXISTS / NOT EXISTS
find [name] from users as u
where exists (
  find [1] from orders as o where o.user_id = u.id
)
 
-- Find users with no orders
find [name] from users as u
where not exists (
  find [1] from orders as o where o.user_id = u.id
)

-- Derived table (subquery in FROM)
find * from (
  find [user_id, sum(total) as revenue]
  from orders
  group by user_id
) as user_revenue
where revenue > 500

-- UNION (deduplicates)
find [name, email] from customers
union
find [name, email] from suppliers
 
-- UNION ALL (keeps duplicates — faster)
find [name, email] from customers
union all
find [name, email] from suppliers
 
-- INTERSECT (rows in both)
find [user_id] from orders where year = 2024
intersect
find [user_id] from orders where year = 2025
 
-- EXCEPT (rows only in first)
find [user_id] from users
except
find [user_id] from orders where created_at > now() - 30d

-- Searched CASE
find [
  name, salary,
  case
    when salary < 50000 then "Low"
    when salary >= 50000 and salary < 100000 then "Medium"
    when salary >= 100000 then "High"
    else "Unknown"
  end as salary_band
]
from users

-- Simple CASE
find [
  name,
  case status
    when "active" then "Active User"
    when "suspended" then "Suspended"
    else "Unknown"
  end as status_label
]
from users

-- CASE in ORDER BY for custom sort priority
find [name, priority] from tasks
order by case priority
  when "urgent" then 1
  when "high"   then 2
  when "normal" then 3
  else 4
end

-- Join PostgreSQL users with MySQL orders
find [u.name, o.total]
from users@pg as u
join orders@mysql as o on u.id = o.user_id
where o.status = "paid"

-- Cross-database CTE
with
  pg_users as (
    find [id, name] from users@pg
    where status = "active"
  ),
  mongo_logs as (
    find [user_id, action, timestamp]
    from logs@mongo
    where timestamp > now() - 7d
  )
find [pg_users.name, count(*) as login_count]
from pg_users
join mongo_logs on pg_users.id = mongo_logs.user_id
where mongo_logs.action = "login"
group by pg_users.name
order by login_count desc
limit 10

-- Single row
create users { name: "Alice", email: "alice@example.com", age: 30 }
 
-- Multiple rows
create users [
  { name: "Alice", email: "alice@example.com" },
  { name: "Bob",   email: "bob@example.com" },
  { name: "Carol", email: "carol@example.com" }
]
 
-- Insert from query
create active_users_backup (
  find * from users where status = "active"
)
 
-- Target a specific connection
create users@pg { name: "Dave", email: "dave@example.com" }

-- Basic update
update users
set status = "inactive", updated_at = now()
where last_login < now() - 90d
 
-- Update with expressions
update products
set price = price * 1.1
where category = "premium"
 
-- Target a specific connection
update users@pg
set status = "verified"
where email_verified_at is not null

-- Basic delete
remove users where status = "banned"
 
-- Delete with subquery
remove users
where id not in (
  find distinct [user_id] from orders
  where created_at > now() - 365d
)
 
-- Delete on a specific connection
remove logs@mongo
where timestamp < now() - 30d

-- Basic table
create table products (
  name string,
  price float,
  category string default "general",
  created_at datetime
)
 
-- With primary key and constraints
create table users (
  id int primary key,
  name string not null,
  email string not null,
  status string default "active"
)
 
-- Idempotent (if not exists)
create table if not exists cache (
  key string primary key,
  value json,
  expires_at datetime
)

alter table users add column bio string
alter table users add column tier string not null default "free"
alter table users drop column temp_data
alter table users alter column age type float
alter table users alter column status type string not null default "active"
alter table users alter column status drop default
alter table users rename column name to full_name
alter table users rename to customers
alter table users@pg add column notes string

-- Simple persist
find * from users >> user_backup
 
-- With conflict handling (upsert)
find [user_id, sum(total) as revenue]
from orders
group by user_id
>> user_revenue@pg
insert if exists on conflict replace
 
-- Ignore duplicates
find distinct [email] from new_signups
>> verified_emails
insert if exists on conflict ignore

describe users
 
-- Target a specific connection
describe users@pg
describe orders@mysql
describe inventory.products@pg

show tables
 
-- For a specific connection
show tables @pg
show tables @mongo
show tables @mysql

explain find [name] from users
where department = "Engineering"
order by salary desc

-- Cross-database explain
explain find [u.name, o.total]
from users@pg as u
join orders@mysql as o on u.id = o.user_id

:start_date = "2024-01-01"
:end_date = "2024-12-31"
:min_amount = 100
 
find [name, total, created_at] from orders
where created_at between :start_date and :end_date
  and total > :min_amount

-- This is a single-line comment
find users  -- inline comment
 
/* This is a
   multi-line comment */
find users where status = "active"

:threshold = 1000;
find [name, total] from orders where total > :threshold;
describe orders

- name: pg
  kind: postgres
  uri: "postgres://river:river@localhost:5432/river"
  schema: public
 
- name: mysql
  kind: mysql
  uri: "mysql://river:river@localhost:3306/river"
  schema: river
 
- name: mongo
  kind: mongodb
  uri: "mongodb://localhost:27017"
 
- name: sqlite
  kind: sqlite
  uri: "sqlite:river.db?mode=rwc"