Zero Schema

Zero applications have both a database schema (the normal backend schema all web apps have) and a Zero schema.

The Zero schema is conventionally located in schema.ts in your app's source code. The Zero schema serves two purposes:

  1. Provide typesafety for ZQL queries
  2. Define first-class relationships between tables

The Zero schema is usually generated from your backend schema, but can be defined by hand for more control.

Generating from Database

If you use Drizzle or Prisma ORM, you can generate schema.ts with drizzle-zero or prisma-zero:

npm install -D drizzle-zero
npx drizzle-zero generate

Writing by Hand

You can also write Zero schemas by hand for full control.

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.

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.

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']
      }
    )
  })
)

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({
  tables: [user, medium, message],
  relationships: [
    userRelationships,
    mediumRelationships,
    messageRelationships
  ]
})

Default Type Parameter

Use DefaultTypes to register the your Schema type with Zero:

declare module '@rocicorp/zero' {
  interface DefaultTypes {
    schema: Schema
  }
}

This prevents having to pass Schema manually to every Zero API.

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 request a newer version of the app that has been updated to handle the new server schema.

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

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 in a compatible way while a client is running, zero-cache syncs the schema change to the client so that it's ready when the app reloads.

If the schema changes in an incompatible way while a client is running, zero-cache will close the client connection with the same error code as above.

Schema Change Process

Like other database-backed applications, Zero schema migrations 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. Update and deploy the client app to use the new schema.
  3. Optionally, after some grace period, implement and run a “contract” migration on the backend, deleting any obsolete rows/tables.

Steps 1 and 2 can generally be done as part of a single deploy in your CI pipeline, but step 4 should be weeks later, when most open clients have refreshed the application.