Rado GitHub

Filter operators

All comparison and logical operators are exported from rado. Each returns a typed boolean SQL expression you can pass to where, having, join conditions or combine with other operators.

import {and, eq, gt, inArray, isNull, like, not, or} from 'rado'

Comparison

OperatorSQLExample
eq(a, b)a = beq(User.id, 1)
ne(a, b)a <> bne(User.name, 'Ada')
gt(a, b)a > bgt(Post.views, 100)
gte(a, b)a >= bgte(User.age, 18)
lt(a, b)a < blt(Post.views, 10)
lte(a, b)a <= blte(User.age, 65)

Both sides are typed: eq(User.id, 'one') is a compile error if id is a number. Columns can be compared to other columns, expressions, subqueries and plain values:

eq(Post.authorId, User.id) // column to column
eq(User.id, 1) // column to value
eq(sql`lower(${User.name})`, 'ada') // expression to value

Null checks

isNull(User.email) // "email" is null
isNotNull(User.email) // "email" is not null

Logical combinators

and(eq(User.active, true), gt(User.id, 10))
or(eq(User.name, 'Ada'), eq(User.name, 'Grace'))
not(eq(User.name, 'Ada'))

and/or skip undefined arguments, which makes conditional filters easy to assemble:

where(
  and(
    eq(Post.published, true),
    authorId !== undefined ? eq(Post.authorId, authorId) : undefined
  )
)

Membership

inArray(User.id, [1, 2, 3])      // "id" in (1, 2, 3)
notInArray(User.id, [1, 2, 3])

// Subqueries are accepted directly
inArray(Post.authorId, db.select(User.id).from(User).where(...))

Edge case handled for you: inArray(x, []) compiles to false (and notInArray(x, []) to true) instead of producing invalid SQL.

Ranges

between(Post.views, 10, 100) // "views" between 10 and 100
notBetween(Post.views, 10, 100)

Pattern matching

like(User.name, 'A%') // case-sensitive
notLike(User.name, 'A%')
ilike(User.name, 'a%') // case-insensitive (PostgreSQL)
notILike(User.name, 'a%')

Arrays (PostgreSQL)

For array columns:

arrayContains(Post.tags, ['sql']) // tags @> '{sql}'
arrayContained(Post.tags, ['sql', 'ts']) // tags <@ '{sql,ts}'
arrayOverlaps(Post.tags, ['sql', 'ts']) // tags && '{sql,ts}'

Existence

import {exists} from 'rado'

await db
  .select()
  .from(User)
  .where(exists(db.select().from(Post).where(eq(Post.authorId, User.id))))

Conditional expressions with when

A typed case expression, in two flavors:

import {when} from 'rado'

// case when <condition> then <value> ... else <fallback> end
const label = when(
  [lt(User.age, 18), 'minor'],
  [gte(User.age, 65), 'senior'],
  'adult' // else
)

// case <subject> when <value> then <result> ... end
const parity = when(sql`${User.id} % 2`, [0, 'even'], [1, 'odd'])

await db.select({name: User.name, label}).from(User)

Sorting helpers

asc and desc wrap expressions for orderBy:

import {asc, desc} from 'rado'

db.select().from(User).orderBy(asc(User.name), desc(User.id))

When you need more

Anything missing from this page can be written with the sql tag. It is fully typed and parameterized:

where(sql`${User.name} similar to ${pattern}`)
PreviousDeleteNextAggregates