Zero Schema

Zero applications have both a database schema (the normal backend database schema that all web apps have) and a Zero schema. The purpose of the Zero schema is to:

  1. Provide typesafety for ZQL queries
  2. Define first-class relationships between tables
  3. Define permissions for access control

You do not need to define the Zero schema entirely by hand. Community-contributed converters exist for Prisma and Drizzle that generate the tables and relationships. It is good to know how to write the schema by hand, however, for debugging and conceptual understanding.

This page describes using the schema to define your tables, columns, and relationships. For information on permissions, see 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.

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,
    ],
  });