Filters & Where

Every way to filter your data — from simple equality to complex nested conditions. All filters are type-safe: wrong field names or wrong value types are compile errors.

#Two filter APIs

Zanith has two ways to filter data. The object syntax works in CRUD methods (findMany, findFirst, etc.). The field reference syntaxworks in the relational query builder (.query().where()).

#Object syntax (CRUD methods)

#Direct values

The simplest filter — field equals a value:

5 lines


1db.user.findMany({ where: { email: '[email protected]' } })
2// → WHERE "email" = $1
3 
4db.user.findMany({ where: { name: null } })
5// → WHERE "name" IS NULL

#Comparison operators

7 lines


1{ age: { equals: 25 } }          // = 25
2{ age: { not: 25 } }             // != 25
3{ age: { gt: 18 } }              // > 18
4{ age: { gte: 18 } }             // >= 18
5{ age: { lt: 65 } }              // < 65
6{ age: { lte: 65 } }             // <= 65
7{ age: { gte: 18, lt: 65 } }     // >= 18 AND < 65 (combined)

#String operators

All string operators use case-insensitive matching (ILIKE):

3 lines


1{ email: { contains: 'example' } }     // ILIKE '%example%'
2{ name: { startsWith: 'Al' } }        // ILIKE 'Al%'
3{ name: { endsWith: 'son' } }         // ILIKE '%son'

#List operators

2 lines


1{ id: { in: ['a', 'b', 'c'] } }        // IN ($1, $2, $3)
2{ id: { notIn: ['x', 'y'] } }         // NOT IN ($1, $2)

#Null checks

3 lines


1{ name: null }                         // IS NULL
2{ name: { equals: null } }             // IS NULL (explicit)
3{ name: { not: null } }                // IS NOT NULL

#Logical combinators

Combine multiple conditions with AND, OR, and NOT:

16 lines


1// AND — all conditions must match
2{ AND: [{ age: { gte: 18 } }, { age: { lt: 65 } }] }
3 
4// OR — any condition can match
5{ OR: [{ role: 'ADMIN' }, { role: 'MODERATOR' }] }
6 
7// NOT — negate a condition
8{ NOT: { role: 'USER' } }
9 
10// Nested — combine freely
11{
12  OR: [
13    { email: { contains: 'admin' } },
14    { AND: [{ role: 'ADMIN' }, { active: true }] },
15  ],
16}

#Operator reference

Operator	SQL output	Works on
`equals`	`= $1`	All types
`not`	`!= $1`	All types
`gt`	`> $1`	number, string, Date
`gte`	`>= $1`	number, string, Date
`lt`	`< $1`	number, string, Date
`lte`	`<= $1`	number, string, Date
`in`	`IN ($1, $2, ...)`	All types
`notIn`	`NOT IN ($1, ...)`	All types
`contains`	`ILIKE '%val%'`	string
`startsWith`	`ILIKE 'val%'`	string
`endsWith`	`ILIKE '%val'`	string

#Field reference syntax (query builder)

In the relational query builder, you use typed field references instead of objects. These give you autocomplete and let you filter across joined models.

12 lines


1db.post.query()
2  .with({ author: true })
3  .where(({ post, author }) =>
4    or(
5      post.title.contains('urgent'),
6      and(
7        post.published.eq(true),
8        author.role.in(['ADMIN', 'MODERATOR'])
9      )
10    )
11  )
12  .execute();

#Available methods on field references

Method	SQL	Example
`.eq(value)`	`= $1`	`post.published.eq(true)`
`.neq(value)`	`!= $1`	`post.status.neq('ARCHIVED')`
`.gt(value)`	`> $1`	`post.viewCount.gt(100)`
`.gte(value)`	`>= $1`	`contract.value.gte(10000)`
`.lt(value)`	`< $1`	`user.age.lt(65)`
`.lte(value)`	`<= $1`	`user.age.lte(65)`
`.contains(str)`	`ILIKE '%str%'`	`post.title.contains('guide')`
`.startsWith(str)`	`ILIKE 'str%'`	`user.name.startsWith('Al')`
`.endsWith(str)`	`ILIKE '%str'`	`user.email.endsWith('@corp.com')`
`.in(values)`	`IN ($1, $2, ...)`	`author.role.in(['ADMIN', 'USER'])`
`.isNull()`	`IS NULL`	`user.name.isNull()`
`.isNotNull()`	`IS NOT NULL`	`user.name.isNotNull()`
`.like(pattern)`	`LIKE $1`	`user.email.like('%@%.com')`
`.ilike(pattern)`	`ILIKE $1`	`user.email.ilike('%admin%')`

#JSONB methods

Method	SQL	Example
`.jsonText(key)`	`->>key = $1`	`post.metadata.jsonText('type').eq('article')`
`.jsonContains(obj)`	`@> $1::jsonb`	`post.metadata.jsonContains({ featured: true })`
`.jsonHasKey(key)`	`? $1`	`post.metadata.jsonHasKey('tags')`

#Array methods

Method	SQL	Example
`.arrayContains(vals)`	`@> ARRAY[$1, ...]`	`post.tags.arrayContains(['typescript'])`

#Combining with and(), or(), not()

19 lines


1import { and, or, not } from 'zanith';
2 
3// AND — all must be true
4and(post.published.eq(true), author.role.eq('ADMIN'))
5 
6// OR — any can be true
7or(post.title.contains('urgent'), post.viewCount.gt(1000))
8 
9// NOT — negate
10not(post.status.eq('ARCHIVED'))
11 
12// Nest freely
13or(
14  post.title.contains('urgent'),
15  and(
16    post.published.eq(true),
17    not(author.role.eq('USER'))
18  )
19)