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 TypeScript 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 table function to define each table in your Zero schema:

import {table, string, boolean} from '@rocicorp/zero';

const user = table("user")
  .columns({
    id: string(),
    name: string(),
    partner: boolean(),
  })
  .primaryKey("id");

Column types are defined with the boolean(), number(), string(), json(), and enumeration() helpers. See Column Types for how database types are mapped to these types.

😬Warning

Name Mapping

Use from() to map a TypeScript table or column name to a different database name:

const userPref = table("userPref")
  // Map TS "userPref" to DB name "user_pref"
  .from("user_pref")
  .columns({
    id: string(),
    // Map TS "orgID" to DB name "org_id"
    orgID: string().from("org_id"),
  });

Multiple Schemas

You can also use from() to access other Postgres schemas:

// Sync the "event" table from the "analytics" schema.
const event = table("event")
    .from("analytics.event");

Optional Columns

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

const user = table("user")
  .columns({
    id: string(),
    name: string(),
    nickName: string().optional(),
  })
  .primaryKey("id");

An optional column can store 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 alongside an enum Postgres column type.

import {table, string, enumeration} from '@rocicorp/zero';

const user = table("user")
  .columns({
    id: string(),
    name: string(),
    mood: enumeration<'happy' | 'sad' | 'taco'>(),
  })
  .primaryKey("id");

Custom JSON Types

Use the json helper to define a column that stores a JSON-compatible value:

import {table, string, json} from '@rocicorp/zero';

const user = table("user")
  .columns({
    id: string(),
    name: string(),
    settings: json<{theme: 'light' | 'dark'}>(),
  })
  .primaryKey("id");

Compound Primary Keys

Pass multiple columns to primaryKey to define a compound primary key:

const user = table("user")
  .columns({
    orgID: string(),
    userID: string(),
    name: string(),
  })
  .primaryKey("orgID", "userID");

Relationships

Use the relationships function to define relationships between tables. Use the one and many helpers to define singular and plural relationships, respectively:

const messageRelationships = relationships(message, ({ one, many }) => ({
  sender: one({
    sourceField: ["senderID"],
    destField: ["id"],
    destSchema: user,
  }),
  replies: many({
    sourceField: ["id"],
    destSchema: message,
    destField: ["parentMessageID"],
  }),
}));

This creates "sender" and "replies" relationships that can later be queried with the related ZQL clause:

const messagesWithSenderAndReplies = z.query.messages
  .related('sender')
  .related("replies");

This will return an object for each message row. Each message will have a sender field that is a single User object or null, and a replies field that is an array of Message objects.

Many-to-Many Relationships

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

const issueRelationships = relationships(issue, ({ many }) => ({
  labels: many({
    sourceField: ["id"],
    destSchema: issueLabel,
    destField: ["issueID"],
  },{
    sourceField: ["labelID"],
    destSchema: label,
    destField: ["id"],
  }),
}));
🤔Note

Compound Keys Relationships

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 messageRelationships = relationships(message, ({ one }) => ({
  sender: one({
    sourceField: ["senderOrgID", "senderUserID"],
    destSchema: user,
    destField: ["orgID", "userID"],
  }),
}));

Circular Relationships

Circular relationships are fully supported:

const commentRelationships = relationships(comment, ({ one }) => ({
  parent: one({
    sourceField: ["parentID"],
    destSchema: comment,
    destField: ["id"],
  }),
}));

Database Schemas

Use createSchema to define the entire Zero schema:

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

export const schema = createSchema(
  1,  // Schema version. See [Schema Migrations](/docs/migrations) for more info.
  {
    tables: [user, medium, message],
    relationships: [
      userRelationships,
      mediumRelationships,
      messageRelationships,
    ],
  });