Custom Queries

Custom queries allow you to transform client queries on the server before execution. This enables server-side filtering, access control, computed fields, and more.

Overview

Query transformation flow:

Client calls zero.query.myQuery(args)
Zero Cache sends query request to your API
Your server transforms it to a ZQL query
Zero Cache executes the query
Results sync to client

Query Handler

Implement a query endpoint using handleQueryRequest:

import {handleQueryRequest} from '@rocicorp/zero/server';
import {schema} from './schema.ts';
import {serverQueries} from './queries.ts';

app.post('/query', async (request, reply) => {
  const response = await handleQueryRequest(
    (name, args) => {
      // Find the query by name
      const queryFn = serverQueries[name];
      if (!queryFn) {
        throw new Error(`Unknown query: ${name}`);
      }
      
      // Return a ZQL query
      return queryFn(args);
    },
    schema,
    request.raw // Pass Request object
  );
  
  return response;
});

Defining Queries

Basic Query

import {builder as zql} from './schema.ts';

export const serverQueries = {
  myIssues: (args: {userID: string}) => {
    return zql.issue
      .where('creatorID', args.userID)
      .orderBy('created', 'desc');
  },
};

Call from client:

const issues = await zero.query.myIssues({userID: currentUser.id});

Nested Queries

Organize queries by namespace:

export const serverQueries = {
  issue: {
    mine: (args: {userID: string}) => {
      return zql.issue
        .where('creatorID', args.userID)
        .orderBy('created', 'desc');
    },
    
    assigned: (args: {userID: string}) => {
      return zql.issue
        .where('assigneeID', args.userID)
        .where('open', true)
        .orderBy('created', 'desc');
    },
    
    byProject: (args: {projectID: string}) => {
      return zql.issue
        .where('projectID', args.projectID)
        .related('creator')
        .orderBy('created', 'desc');
    },
  },
};

Call with dot notation:

const myIssues = await zero.query.issue.mine({userID});
const assigned = await zero.query.issue.assigned({userID});

Access Control

Row-Level Security

Filter data based on user permissions:

export const serverQueries = {
  issues: (args: {userID: string; role: string}) => {
    let query = zql.issue;
    
    // Non-admins can only see public issues
    if (args.role !== 'admin') {
      query = query.where('visibility', 'public');
    }
    
    return query.orderBy('created', 'desc');
  },
};

User Context

Pass authenticated user context:

app.post('/query', async (request, reply) => {
  // Extract user from auth cookie/token
  const session = await verifySession(request.cookies.session);
  
  const response = await handleQueryRequest(
    (name, rawArgs) => {
      // Add user context to args
      const args = {...rawArgs, userID: session.userID, role: session.role};
      
      const queryFn = serverQueries[name];
      if (!queryFn) {
        throw new Error(`Unknown query: ${name}`);
      }
      
      return queryFn(args);
    },
    schema,
    request.raw
  );
  
  return response;
});

Client doesn’t need to pass userID:

// Server automatically adds userID from session
const issues = await zero.query.issues();

Query Patterns

Filtering

export const serverQueries = {
  searchIssues: (args: {query: string; projectID?: string}) => {
    let q = zql.issue;
    
    if (args.projectID) {
      q = q.where('projectID', args.projectID);
    }
    
    // Simple text search
    if (args.query) {
      q = q.where('title', 'like', `%${args.query}%`);
    }
    
    return q.orderBy('created', 'desc');
  },
};

Relationships

export const serverQueries = {
  issueDetails: (args: {issueID: string}) => {
    return zql.issue
      .where('id', args.issueID)
      .related('creator')
      .related('assignee')
      .related('comments', q =>
        q.related('creator').orderBy('created', 'asc')
      )
      .one();
  },
};

Aggregation

Note: ZQL doesn’t support aggregation directly. Use these patterns:

// Return all records, aggregate on client
export const serverQueries = {
  projectStats: (args: {projectID: string}) => {
    return zql.issue.where('projectID', args.projectID);
  },
};

// Client-side aggregation
const issues = await zero.query.projectStats({projectID});
const openCount = issues.filter(i => i.open).length;

Computed Fields

Add computed data using relationships:

export const serverQueries = {
  issuesWithCounts: (args: {projectID: string}) => {
    return zql.issue
      .where('projectID', args.projectID)
      .related('comments') // Client can count comments
      .orderBy('created', 'desc');
  },
};

Client computes:

const issues = await zero.query.issuesWithCounts({projectID});
const issuesWithCounts = issues.map(issue => ({
  ...issue,
  commentCount: issue.comments.length,
}));

Pagination

export const serverQueries = {
  paginatedIssues: (args: {
    projectID: string;
    limit: number;
    offset: number;
  }) => {
    return zql.issue
      .where('projectID', args.projectID)
      .orderBy('created', 'desc')
      .limit(args.limit)
      .offset(args.offset);
  },
};

Multi-Table Queries

Use relationships to join tables:

export const serverQueries = {
  userActivity: (args: {userID: string}) => {
    // Get user with their issues and comments
    return zql.user
      .where('id', args.userID)
      .related('createdIssues', q =>
        q.orderBy('created', 'desc').limit(10)
      )
      .one();
  },
};

Advanced Patterns

Conditional Relationships

export const serverQueries = {
  issueWithDetails: (args: {issueID: string; includeComments?: boolean}) => {
    let query = zql.issue
      .where('id', args.issueID)
      .related('creator');
    
    if (args.includeComments) {
      query = query.related('comments', q =>
        q.related('creator').orderBy('created', 'asc')
      );
    }
    
    return query.one();
  },
};

Dynamic Ordering

type SortField = 'created' | 'title' | 'modified';
type SortDirection = 'asc' | 'desc';

export const serverQueries = {
  sortedIssues: (args: {
    projectID: string;
    sortBy: SortField;
    direction: SortDirection;
  }) => {
    return zql.issue
      .where('projectID', args.projectID)
      .orderBy(args.sortBy, args.direction);
  },
};

Complex Filters

export const serverQueries = {
  filterIssues: (args: {
    projectID: string;
    open?: boolean;
    assigneeID?: string;
    creatorID?: string;
    labelIDs?: string[];
  }) => {
    let query = zql.issue.where('projectID', args.projectID);
    
    if (args.open !== undefined) {
      query = query.where('open', args.open);
    }
    
    if (args.assigneeID) {
      query = query.where('assigneeID', args.assigneeID);
    }
    
    if (args.creatorID) {
      query = query.where('creatorID', args.creatorID);
    }
    
    if (args.labelIDs && args.labelIDs.length > 0) {
      query = query.related('issueLabels', q =>
        q.where('labelID', 'in', args.labelIDs)
      );
    }
    
    return query.orderBy('created', 'desc');
  },
};

Error Handling

Throw errors for invalid queries:

import {QueryParseError} from '@rocicorp/zero';

export const serverQueries = {
  issueDetails: (args: {issueID?: string}) => {
    if (!args.issueID) {
      throw new QueryParseError('issueID is required');
    }
    
    return zql.issue.where('id', args.issueID).one();
  },
};

Client receives error:

try {
  const issue = await zero.query.issueDetails({});
} catch (error) {
  console.error(error.message); // "issueID is required"
}

TypeScript Support

Define query argument types:

type MyIssuesArgs = {
  userID: string;
};

type FilterIssuesArgs = {
  projectID: string;
  open?: boolean;
  assigneeID?: string;
};

export const serverQueries = {
  myIssues: (args: MyIssuesArgs) => {
    return zql.issue
      .where('creatorID', args.userID)
      .orderBy('created', 'desc');
  },
  
  filterIssues: (args: FilterIssuesArgs) => {
    let query = zql.issue.where('projectID', args.projectID);
    
    if (args.open !== undefined) {
      query = query.where('open', args.open);
    }
    
    if (args.assigneeID) {
      query = query.where('assigneeID', args.assigneeID);
    }
    
    return query;
  },
};

Testing

Test query transformations:

import {describe, test, expect} from 'vitest';
import {serverQueries} from './queries.ts';
import {asQueryInternals} from '@rocicorp/zero/internal';

describe('serverQueries', () => {
  test('myIssues filters by userID', () => {
    const query = serverQueries.myIssues({userID: 'user123'});
    const ast = asQueryInternals(query).ast;
    
    // Verify AST contains correct filter
    expect(ast.table).toBe('issue');
    expect(ast.where).toMatchObject([
      {field: 'creatorID', op: '=', value: 'user123'},
    ]);
  });
});

Performance

Limit Results

Always limit unbounded queries:

export const serverQueries = {
  recentIssues: (args: {projectID: string}) => {
    return zql.issue
      .where('projectID', args.projectID)
      .orderBy('created', 'desc')
      .limit(100); // Prevent returning too many rows
  },
};

Selective Relationships

Only load needed relationships:

// Bad: loads all related data
return zql.issue
  .related('creator')
  .related('assignee')
  .related('comments')
  .related('labels');

// Good: load only what's needed
return zql.issue.related('creator');

Query Optimization

Zero Cache uses PostgreSQL query planner. Ensure indexes exist:

CREATE INDEX idx_issue_project_created 
  ON issue(project_id, created DESC);

CREATE INDEX idx_issue_assignee 
  ON issue(assignee_id) 
  WHERE open = true;

Security

Input Validation

import {z} from 'zod';

const searchIssuesSchema = z.object({
  query: z.string().max(100),
  projectID: z.string().uuid().optional(),
});

export const serverQueries = {
  searchIssues: (args: unknown) => {
    const validated = searchIssuesSchema.parse(args);
    
    let query = zql.issue;
    
    if (validated.projectID) {
      query = query.where('projectID', validated.projectID);
    }
    
    if (validated.query) {
      query = query.where('title', 'like', `%${validated.query}%`);
    }
    
    return query;
  },
};

Prevent SQL Injection

ZQL queries are safe from SQL injection. Parameters are properly escaped:

// Safe - params are escaped
zql.issue.where('title', 'like', `%${userInput}%`)

Access Control

Always enforce permissions:

export const serverQueries = {
  projectIssues: (args: {projectID: string; userID: string}) => {
    // Check if user has access to project
    // (This is a simplified example - in reality, verify in DB)
    
    return zql.issue
      .where('projectID', args.projectID)
      .orderBy('created', 'desc');
  },
};

Migration from Legacy Queries

Old permission-based queries can be migrated to custom queries:

// Old: Permission-based query (deprecated)
// export const permissions = { ... };

// New: Custom query
export const serverQueries = {
  myIssues: (args: {userID: string}) => {
    return zql.issue.where('creatorID', args.userID);
  },
};

Next Steps

Deployment - Deploy to production

Get Started

Core Concepts

Client Guide

Server Guide

Advanced

Custom Queries

Overview

Query Handler

Defining Queries

Basic Query

Nested Queries

Access Control

Row-Level Security

User Context

Query Patterns

Filtering

Relationships

Aggregation

Computed Fields

Multi-Table Queries

Advanced Patterns

Conditional Relationships

Dynamic Ordering

Complex Filters

Error Handling

TypeScript Support

Testing

Performance

Limit Results

Selective Relationships

Query Optimization

Security

Input Validation

Prevent SQL Injection

Access Control

Migration from Legacy Queries

Next Steps

Build docs developers (and LLMs) love

Get Started

Core Concepts

Client Guide

Server Guide

Advanced

​Overview

​Query Handler

​Defining Queries

​Basic Query

​Nested Queries

​Access Control

​Row-Level Security

​User Context

​Query Patterns

​Filtering

​Relationships

​Aggregation

​Computed Fields

​Pagination

​Multi-Table Queries

​Advanced Patterns

​Conditional Relationships

​Dynamic Ordering

​Complex Filters

​Error Handling

​TypeScript Support

​Testing

​Performance

​Limit Results

​Selective Relationships

​Query Optimization

​Security

​Input Validation

​Prevent SQL Injection

​Access Control

​Migration from Legacy Queries

​Next Steps

Build docs developers (and LLMs) love

Overview

Query Handler

Defining Queries

Basic Query

Nested Queries

Access Control

Row-Level Security

User Context

Query Patterns

Filtering

Relationships

Aggregation

Computed Fields

Pagination

Multi-Table Queries

Advanced Patterns

Conditional Relationships

Dynamic Ordering

Complex Filters

Error Handling

TypeScript Support

Testing

Performance

Limit Results

Selective Relationships

Query Optimization

Security

Input Validation

Prevent SQL Injection

Access Control

Migration from Legacy Queries

Next Steps