Reading Data with ZQL

ZQL is Zero’s query language.

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

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

Unlike queries in classic databases, the result of a ZQL query is a view that updates automatically and efficiently as the underlying data changes. You can call a query’s materialize() method to get a view, but more typically you run queries via some framework-specific bindings. For example see useQuery for React or SolidJS.

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 (modulo column permissions).

Copyconst z = new Zero(...);

// Returns a query that selects all rows and columns from the issue table.
z.query.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.

Ordering

You can sort query results by adding an orderBy clause:

Copyz.query.issue.orderBy('created', 'desc');

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

Copy// Order by priority descending. For any rows with same priority,
// then order by created desc.
z.query.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:

Copy// Actually means: z.query.issue.orderBy('id', 'asc');
z.query.issue;

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

Limit

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

Copyz.query.issue.orderBy('created', 'desc').limit(100);

Paging

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

Copylet start: IssueRow | undefined;
while (true) {
  let q = z.query.issue.orderBy('created', 'desc').limit(100);
  if (start) {
    q = q.start(start);
  }
  const batch = 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:

Copyz.query.issue.start(row, {inclusive: true});

Uniqueness

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

Copyconst result = z.query.issue.where('id', 42).one();
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.

Copy// Get all issues and their related comments
z.query.issue.related('comments');

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

You can fetch multiple relationships in a single query:

Copyz.query.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.

Copyz.query.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 z.query.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.

Nested Relationships

You can nest relationships arbitrarily:

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

Where

You can filter a query with where():

Copyz.query.issue.where('priority', '=', 'high');

The first parameter is always a column name from the table being queried. Intellisense 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, json, 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.

Equals is the Default Comparison Operator

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

Copyz.query.issue.where('priority', 'high');

Comparing to null

As in SQL, ZQL’s null is not equal to itself (null ≠ null).

This is required 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.

When you purposely want to compare to null ZQL supports IS and IS NOT operators that work just like in SQL:

Copy// Find employees not in any org.
z.query.employee.where('orgID', 'IS', null);

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:

Copy// Get all issues that have priority 'critical' or else have both
// priority 'medium' and not more than 100 votes.
z.query.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:

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

Relationship Filters

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

Copy// Find all orgs that have at least one employee
z.query.organization.whereExists('employees');

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

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

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

Copy// Get all issues that have comments that have reactions
z.query.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:

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

Query and Data Lifetime

ZQL reuses data already synced to the client when possible for instant query results.

But what determines whether such data will be available? Zero tracks which queries return each row, and keeps a row around as long as any query that returns it is still running.

This means that to reason about whether a query will return results locally (without flicker), you should think about what other queries are currently running, not about what data is local. Data exists locally if and only if there is a query running that returns that data.

Zero currently has a number of different features (and one bug) that influence whether a query is running:

1. UI Queries

const myView = myQuery.materialize() creates a query on both the client and server that lives until you call myView.destroy().

Typically you won't use this API though, you'll use the higher-level useQuery. This means that the lifetime of useQuery queries is just the lifetime of the component that called them. The data synced by these queries will be deleted when the query unmounts.

2. Preload Queries

const result = myQuery.preload() creates a query only on the server. The query lives until you call result.cleanup(). This is useful when you want to preload a large query, but don't need to display most of it. For example, zbugs currently uses preload() to load all issue rows and the first 10 comments from each issue.

3. One-Time Queries

const result = myQuery.run(). This runs a query exactly once. The query is immediately destroyed implicitly.

4. Open Queries are Leaked on Page Unload

An open bug results in any queries open on page unload staying open forever for the browser profile.

Putting it All Together

Putting these four mechanisms together, the current behavior is something like:

• The lifetime of useQuery queries is component lifetime.
• The lifetime of preload queries is forever (unless you call cleanup(), which almost nobody does).

This is pretty bad, but still somehow works surprisingly well for zbugs.

Future Behavior

We are working on adding an LRU cache for queries, so that they aren't removed from the server when the client stops listening. This means that if you do a query, then the same query a little later, it will typically resolve instantly automatically.

Completeness

Zero returns whatever data it has in its cache immediately for a query, then falls back to the server for any missing data. Sometimes it's useful to know the difference between these two types of results. To do so, use the result from useQuery:

Copyconst [issues, issuesResult] = useQuery(z.query.issue);
if (issueResult.type === 'complete') {
  console.log('All data is present');
} else {
  console.log('Some data is missing');
}

The possible values of result.type are currently complete and unknown.

The complete value is currently only returned when Zero has received the server result. But in the future, Zero will be able to return this result type when it knows that all possible data for this query is already available locally. Additionally, we plan to add a prefix result for when the data is known to be a prefix of the complete result. See Consistency for more information.

Preloading

See Preloading.

Running Queries Once

Usually subscribing to a query is what you want in a reactive UI but every so often running a query once is all that’s needed.

Copyconst results = z.query.issue.where('foo', 'bar').run();