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 by hand

This page describes using the schema to define your tables, columns, and relationships.

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

Migrations

Zero uses TypeScript-style structural typing to detect schema changes and implement smooth migrations.

How it Works

When the Zero client connects to zero-cache it sends a copy of the schema it was constructed with. zero-cache compares this schema to the one it has, and rejects the connection with a special error code if the schema is incompatible.

By default, The Zero client handles this error code by calling location.reload(). The intent is to to get a newer version of the app that has been updated to handle the new server schema.

🤔Note

If you want to delay this reload, you can do so by providing the onUpdateNeeded constructor parameter:

const z = new Zero({
  onUpdateNeeded: updateReason => {
    if (reason.type === 'SchemaVersionNotSupported') {
      // Do something custom here, like show a banner.
      // When you're ready, call `location.reload()`.      
    }
  },
})

If the schema changes while a client is running in a compatible way, zero-cache syncs the schema change to the client so that it's ready when the app reloads and gets new code that needs it. If the schema changes while a client is running in an incompatible way, zero-cache will close the client connection with the same error code as above.

Schema Change Process

Like other database-backed applications, Zero schema migration generally follow an “expand/migrate/contract” pattern:

  1. Implement and run an “expand” migration on the backend that is backwards compatible with existing schemas. Add new rows, tables, as well as any defaults and triggers needed for backwards compatibility.
  2. Add any new permissions required for the new tables/columns by running zero-deploy-permissions.
  3. Update and deploy the client app to use the new schema.
  4. Optionally, after some grace period, implement and run a “contract” migration on the backend, deleting any obsolete rows/tables.

Steps 1-3 can generally be done as part of one deploy by your CI pipeline, but step 4 would be weeks later when most open clients have refreshed and gotten new code.