Zero Schema

Zero applications have both a database schema (the normal backend database schema that all web apps have) and a Zero schema. The two schemas are related, but not the same:

  • The Zero schema is usually a subset of the server-side schema. It only needs to includes the tables and columns that the Zero client uses.
  • The Zero schema includes authorization rules that control access to the database.
  • The Zero schema includes relationships that explicitly define how entities are related to each other.
  • In order to support smooth schema migration, the two schemas don’t change in lockstep. Typically the database schema is changed first, then the Zero schema is changed later.

This page describes the core Zero schema which defines the tables, column, and relationships your Zero app can access. For information on permissions, see Authentication and Permissions. For information on migration see Schema Migration.

Defining the Zero Schema

The Zero schema is encoded in a Type Script file that is conventionally called schema.ts file. For example, see the schema file forhello-zero.

Building the Zero Schema

The schema is defined in TypeScript for convenience, but what zero-cache actually uses is a JSON encoding of it.

During development, start zero-cache with the zero-cache-dev script. This script watches for changes to schema.ts and automatically rebuilds the JSON schema and restarts zero-cache when it changes.

For production, you should run the zero-build-schema explicitly to generate the JSON file.

Table Schemas

Use the createTableSchema helper to define each table in your Zero schema:

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

const userSchema = createTableSchema({
  tableName: 'user',
  columns: {
    id: 'string',
    name: 'string',
    partner: 'boolean',
  },
  primaryKey: 'id',
});

Columns can have the types boolean, number, string, null, json. See Column Types for how database types are mapped to these types.

😬Warning

Optional Columns

Columns can be marked optional. This corresponds to the SQL concept nullable.

const userSchema = createTableSchema({
  tableName: 'user',
  columns: {
    id: 'string',
    name: 'string',
    nickName: {type: 'string', optional: true},
  },
  primaryKey: 'id',
});

An optional column can have a value of the specified type or null to mean no value.

🤔Note

Enumerations

Use the enumeration helper to define a column that can only take on a specific set of values. This is most often used along side an enum Postgres column type.

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

const {enumeration} = column;
const userSchema = createTableSchema({
  tableName: 'user',
  columns: {
    id: 'string',
    name: 'string',
    mood: enumeration<'happy', 'sad', 'ok'>(),
  },
  primaryKey: 'id',
});

Custom JSON Types

Use the json helper to define a column that only store a specific subtype of json:

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

const {json} = column;
const userSchema = createTableSchema({
  tableName: 'user',
  columns: {
    id: 'string',
    name: 'string',
    settings: json<{theme: 'light' | 'dark'}>(),
  },
  primaryKey: 'id',
});

Compound Primary Keys

Use an array for compound primary keys:

const userSchema = createTableSchema({
  tableName: 'user',
  columns: {
    orgID: 'string',
    userID: 'string',
    name: 'string',
  },
  primaryKey: ['orgID', 'userID'],
});

Relationships

Use the relationships field to define relationships between tables:

const messageSchema = createTableSchema({
  tableName: 'message',
  columns: {
    id: 'string',
    senderID: 'string',
  },
  primaryKey: 'id',
  relationships: {
    sender: {
      sourceField: 'senderID',
      destSchema: userSchema,
      destField: 'id',
    },
  },
});

This creates a "sender" relationship that can later be queried with the related ZQL clause:

const messagesWithSener = z.query.messages.related('sender');

Many-to-Many Relationships

You can create many-to-many relationships by chaining the relationship definitions. Assuming a label table and an issueLabel junction table, you can define a labels relationship like this:

const issueSchema = {
  tableName: 'issue',
  columns: {
    id: 'string',
    title: 'string',
  },
  primaryKey: 'id',
  relationships: {
    labels: [
      {
        sourceField: 'id',
        destField: 'issueID',
        destSchema: issueLabelSchema,
      },
      {
        sourceField: 'labelID',
        destField: 'id',
        destSchema: labelSchema,
      },
    ],
  },
};
🤔Note

Relationships and Compound Keys

Relationships can traverse compound keys. Imagine a user table with a compound primary key of orgID and userID, and a message table with a related senderOrgID and senderUserID. This can be represented in your schema with:

const messageSchema = createTableSchema({
  tableName: 'message',
  columns: {
    id: 'string',
    senderOrgID: 'string',
    senderUserID: 'string',
  },
  primaryKey: 'id',
  relationships: {
    sender: {
      sourceField: ['senderOrgID', 'senderUserID'],
      destSchema: userSchema,
      destField: ['orgID', 'userID'],
    },
  },
});

Database Schemas

Use createSchema to define the entire Zero schema:

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

export const schema = createSchema({
  version: 1,
  tables: {
    user: userSchema,
    medium: mediumSchema,
    message: messageSchema,
  },
});

The version field is used as part of schema migration.