c.where('creatorID', 42), {flip: true}), ), ) ``` You can manually flip just one or a few of the `whereExists` clauses in a query, leaving the rest to be planned automatically.
Or, for AI assistants
ALWAYS readllms.txtfor curated documentation pages and examples.

Zero Query Language

Inspired by SQL, ZQL is expressed in TypeScript with heavy use of the builder pattern. If you have used Drizzle or Kysely, ZQL will feel familiar.

ZQL queries are composed of one or more clauses that are chained together into a query.

Create a Builder

To get started, use createBuilder.

If you use drizzle-zero or prisma-zero, this happens automatically and an instance is stored in the zql constant exported from schema.ts:

import {zql} from 'schema.ts'

// zql.myTable.where(...)

Otherwise, create an instance manually:

// schema.ts
// ...
export const zql = createBuilder(schema)

Select

ZQL queries start by selecting a table. There is no way to select a subset of columns; ZQL queries always return the entire row, if permissions allow it.

import {zql} from 'zero.ts'

// Returns a query that selects all rows and columns from the
// issue table.
zql.issue

This is a design tradeoff that allows Zero to better reuse the row locally for future queries. This also makes it easier to share types between different parts of the code.

🧑‍🏫Data returned from ZQL should be considered immutable

Ordering

You can sort query results by adding an orderBy clause:

zql.issue.orderBy('created', 'desc')

Multiple orderBy clauses can be present, in which case the data is sorted by those clauses in order:

// Order by priority descending. For any rows with same priority,
// then order by created desc.
zql.issue
  .orderBy('priority', 'desc')
  .orderBy('created', 'desc')

All queries in ZQL have a default final order of their primary key. Assuming the issue table has a primary key on the id column, then:

// Actually means: zql.issue.orderBy('id', 'asc');
zql.issue

// Actually means: zql.issue.orderBy('priority', 'desc').orderBy('id', 'asc');
zql.issue.orderBy('priority', 'desc')

Limit

You can limit the number of rows to return with limit():

zql.issue.orderBy('created', 'desc').limit(100)

Paging

You can start the results at or after a particular row with start():

let start: IssueRow | undefined
while (true) {
  let q = zql.issue
    .orderBy('created', 'desc')
    .limit(100)
  if (start) {
    q = q.start(start)
  }
  const batch = await q.run()
  console.log('got batch', batch)

  if (batch.length < 100) {
    break
  }
  start = batch[batch.length - 1]
}

By default start() is exclusive - it returns rows starting after the supplied reference row. This is what you usually want for paging. If you want inclusive results, you can do:

zql.issue.start(row, {inclusive: true})

Getting a Single Result

If you want exactly zero or one results, use the one() clause. This causes ZQL to return Row|undefined rather than Row[].

const result = await zql.issue
  .where('id', 42)
  .one()
  .run()
if (!result) {
  console.error('not found')
}

one() overrides any limit() clause that is also present.

Relationships

You can query related rows using relationships that are defined in your Zero schema.

// Get all issues and their related comments
zql.issue.related('comments')

Relationships are returned as hierarchical data. In the above example, each row will have a comments field, which is an array of the corresponding comments rows.

You can fetch multiple relationships in a single query:

zql.issue
  .related('comments')
  .related('reactions')
  .related('assignees')

Refining Relationships

By default all matching relationship rows are returned, but this can be refined. The related method accepts an optional second function which is itself a query.

zql.issue.related(
  'comments',
  // It is common to use the 'q' shorthand variable for this parameter,
  // but it is a _comment_ query in particular here, exactly as if you
  // had done zql.comment.
  q =>
    q
      .orderBy('modified', 'desc')
      .limit(100)
      .start(lastSeenComment)
)

This relationship query can have all the same clauses that top-level queries can have.

😬Order and limit not supported in junction relationships

Nested Relationships

You can nest relationships arbitrarily:

// Get all issues, first 100 comments for each (ordered by modified,desc),
// and for each comment all of its reactions.
zql.issue.related('comments', q =>
  q
    .orderBy('modified', 'desc')
    .limit(100)
    .related('reactions')
)

Where

You can filter a query with where():

zql.issue.where('priority', '=', 'high')

The first parameter is always a column name from the table being queried. TypeScript completion will offer available options (sourced from your Zero Schema).

Comparison Operators

Where supports the following comparison operators:

Operator	Allowed Operand Types	Description
`=` , `!=`	boolean, number, string	JS strict equal (===) semantics
`<` , `<=`, `>`, `>=`	number	JS number compare semantics
`LIKE`, `NOT LIKE`, `ILIKE`, `NOT ILIKE`	string	SQL-compatible `LIKE` / `ILIKE`
`IN` , `NOT IN`	boolean, number, string	RHS must be array. Returns true if rhs contains lhs by JS strict equals.
`IS` , `IS NOT`	boolean, number, string, null	Same as `=` but also works for `null`

TypeScript will restrict you from using operators with types that don’t make sense – you can’t use > with boolean for example.

🤔Note

Equals is the Default Comparison Operator

Because comparing by = is so common, you can leave it out and where defaults to =.

zql.issue.where('priority', 'high')

Comparing to `null`

As in SQL, ZQL’s null cannot be compared with =, !=, <, or any other normal comparison operator. Comparing any value to null with such operators is always false:

Comparison	Result
`42 = null`	`false`
`42 < null`	`false`
`42 > null`	`false`
`42 != null`	`false`
`null = null`	`false`
`null != null`	`false`

These semantics feel a bit weird, but they are consistent with SQL. The reason SQL does it this way is to make join semantics work: if you’re joining employee.orgID on org.id you do not want an employee in no organization to match an org that hasn’t yet been assigned an ID.

For when you purposely do want to compare to null ZQL supports IS and IS NOT operators that also work just like in SQL:

// Find employees not in any org.
zql.employee.where('orgID', 'IS', null)

// Find employees in an org other than 42 OR employees in NO org
zql.employee.where('orgID', 'IS NOT', 42)

TypeScript will prevent you from comparing to null with other operators.

Compound Filters

The argument to where can also be a callback that returns a complex expression:

// Get all issues that have priority 'critical' or else have both
// priority 'medium' and not more than 100 votes.
zql.issue.where(({cmp, and, or, not}) =>
  or(
    cmp('priority', 'critical'),
    and(
      cmp('priority', 'medium'),
      not(cmp('numVotes', '>', 100))
    )
  )
)

cmp is short for compare and works the same as where at the top-level except that it can’t be chained and it only accepts comparison operators (no relationship filters – see below).

Note that chaining where() is also a one-level and:

// Find issues with priority 3 or higher, owned by aa
zql.issue
  .where('priority', '>=', 3)
  .where('owner', 'aa')

Comparing Literal Values

The where clause always expects its first parameter to be a column name as a string. Same with the cmp helper:

// "foo" is a column name, not a string:
zql.issue.where('foo', 'bar')

// "foo" is a column name, not a string:
zql.issue.where(({cmp}) => cmp('foo', 'bar'))

To compare to a literal value, use the cmpLit helper:

zql.issue.where(cmpLit('foobar', 'foo' + 'bar'))

This is particularly useful for implementing permissions, because the first parameter can be a field of your context:

zql.issue.where(cmpLit(ctx.role, 'admin'))

Relationship Filters

Your filter can also test properties of relationships. Currently the only supported test is existence:

// Find all orgs that have at least one employee
zql.organization.whereExists('employees')

The argument to whereExists is a relationship, so just like other relationships, it can be refined with a query:

// Find all orgs that have at least one cool employee
zql.organization.whereExists('employees', q =>
  q.where('location', 'Hawaii')
)

As with querying relationships, relationship filters can be arbitrarily nested:

// Get all issues that have comments that have reactions
zql.issue.whereExists('comments', q =>
  q.whereExists('reactions')
)

The exists helper is also provided which can be used with and, or, cmp, and not to build compound filters that check relationship existence:

// Find issues that have at least one comment or are high priority
zql.issue.where({cmp, or, exists} =>
  or(
    cmp('priority', 'high'),
    exists('comments'),
  ),
)

Type Helpers

You can get the TypeScript type of the result of a query using the QueryResultType helper:

import type {QueryResultType} from '@rocicorp/zero'

const complexQuery = zql.issue.related(
  'comments',
  q => q.related('author')
)
type MyComplexResult = QueryResultType<typeof complexQuery>

// MyComplexResult is: readonly IssueRow & {
//   readonly comments: readonly (CommentRow & {
//     readonly author: readonly AuthorRow|undefined;
//   })[];
// }[]

You can get the type of a single row with QueryRowType:

import type {QueryRowType} from '@rocicorp/zero'

type MySingleRow = QueryRowType<typeof complexQuery>

// MySingleRow is: readonly IssueRow & {
//   readonly comments: readonly (CommentRow & {
//     readonly author: readonly AuthorRow|undefined;
//   })[];
// }

// Find the first 100 documents that user 42 can edit,
// ordered by created desc. Because each user is an editor
// of only a few documents, flip:true is much faster than
// flip:false.
zql.documents.whereExists('editors',
    e => e.where('userID', 42),
    {flip: true}
  ),
  .orderBy('created', 'desc')
  .limit(100)

Or with exists:

// Find issues created by user 42 or that have a comment
// by user 42. Because user 42 has commented on only a
// few issues, flip:true is much faster than flip:false.
zql.issue.where({cmp, or, exists} =>
  or(
    cmp('creatorID', 42),
    exists('comments',
      c => c.where('creatorID', 42),
      {flip: true}),
  ),
)

You can manually flip just one or a few of the whereExists clauses in a query, leaving the rest to be planned automatically.

PreviousWriting Data

NextZQL on the Server

For AI assistants

Get Started

Learning Zero

Using Zero

Postgres

Integrations

Deployment

Debugging

Old Stuff

Meta

Or, for AI assistants