Schema

Zero uses a schema-first approach where you define your data model using TypeScript. The schema provides type safety, enables relationship traversal, and maps to your PostgreSQL database.

Quick Example

Here’s a complete schema definition from the zbugs reference app:

import {
  boolean,
  createSchema,
  enumeration,
  number,
  relationships,
  string,
  table,
} from '@rocicorp/zero';

const user = table('user')
  .columns({
    id: string(),
    login: string(),
    name: string().optional(),
    avatar: string(),
    role: enumeration<'admin' | 'member'>(),
  })
  .primaryKey('id');

const issue = table('issue')
  .columns({
    id: string(),
    title: string(),
    open: boolean(),
    creatorID: string(),
    assigneeID: string().optional(),
  })
  .primaryKey('id');

const issueRelationships = relationships(issue, ({one}) => ({
  creator: one({
    sourceField: ['creatorID'],
    destField: ['id'],
    destSchema: user,
  }),
  assignee: one({
    sourceField: ['assigneeID'],
    destField: ['id'],
    destSchema: user,
  }),
}));

export const schema = createSchema({
  tables: [user, issue],
  relationships: [issueRelationships],
});

Tables

Defining Tables

Tables are defined using a fluent builder API:

const project = table('project')
  .columns({
    id: string(),
    name: string(),
    issueCount: number().optional(),
    archived: boolean(),
  })
  .primaryKey('id');

table

function

Creates a table builder with the specified name. The name should match your PostgreSQL table name.

columns

method

Defines the columns for the table. Returns a new builder with the columns set.

primaryKey

method

Specifies the primary key column(s). Required before passing to createSchema.

Column Types

Zero supports the following column types:

string
number
boolean
enumeration
json

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

Maps to PostgreSQL TEXT, VARCHAR, or CHAR.TypeScript type: string or string | undefined (if optional)

const issue = table('issue')
  .columns({
    id: string(),
    shortID: number(),
    priority: number().optional(),
  })
  .primaryKey('id');

Maps to PostgreSQL INTEGER, BIGINT, SMALLINT, NUMERIC, REAL, or DOUBLE PRECISION.TypeScript type: number or number | undefined (if optional)

const issue = table('issue')
  .columns({
    id: string(),
    open: boolean(),
    pinned: boolean().optional(),
  })
  .primaryKey('id');

Maps to PostgreSQL BOOLEAN.TypeScript type: boolean or boolean | undefined (if optional)

type Status = 'open' | 'in_progress' | 'closed';

const issue = table('issue')
  .columns({
    id: string(),
    status: enumeration<Status>(),
  })
  .primaryKey('id');

Use for string literal unions. Maps to PostgreSQL TEXT, VARCHAR, or native ENUM.TypeScript type: The specific union type (e.g., 'open' | 'in_progress' | 'closed')

type Metadata = {
  tags: string[];
  priority: number;
};

const issue = table('issue')
  .columns({
    id: string(),
    metadata: json<Metadata>(),
  })
  .primaryKey('id');

Maps to PostgreSQL JSON or JSONB.TypeScript type: ReadonlyJSONValue or your custom type

Optional Columns

Mark columns as optional with .optional():

const user = table('user')
  .columns({
    id: string(),
    email: string(),
    name: string().optional(),      // string | undefined
    phoneNumber: string().optional(), // string | undefined
  })
  .primaryKey('id');

In the type system, optional columns are explicitly type | undefined, not just type?. This ensures proper handling of null vs undefined.

Composite Primary Keys

Tables can have multi-column primary keys:

const issueLabel = table('issueLabel')
  .columns({
    issueID: string(),
    labelID: string(),
    projectID: string(),
  })
  .primaryKey('labelID', 'issueID');  // Composite key

Server Name Mapping

If your PostgreSQL table or column name differs from your client-side name:

// Table name mapping
const user = table('user')
  .from('public.users')  // PostgreSQL table is "users" in "public" schema
  .columns({
    id: string(),
    email: string().from('email_address'),  // Column mapping
  })
  .primaryKey('id');

If your PostgreSQL table is in the public schema, you can omit the schema prefix. Zero automatically strips public. prefixes.

Relationships

Relationships enable querying related data across tables.

One-to-One Relationships

const issue = table('issue')
  .columns({
    id: string(),
    creatorID: string(),
  })
  .primaryKey('id');

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

const issueRelationships = relationships(issue, ({one}) => ({
  creator: one({
    sourceField: ['creatorID'],  // Foreign key in issue table
    destField: ['id'],           // Primary key in user table
    destSchema: user,
  }),
}));

Usage in queries:

const issuesWithCreators = zero.query.issue
  .related('creator');

// Returns:
// Array<{
//   id: string;
//   creatorID: string;
//   creator: {id: string; name: string} | undefined;
// }>

One-to-Many Relationships

const project = table('project')
  .columns({id: string(), name: string()})
  .primaryKey('id');

const issue = table('issue')
  .columns({id: string(), projectID: string(), title: string()})
  .primaryKey('id');

const projectRelationships = relationships(project, ({many}) => ({
  issues: many({
    sourceField: ['id'],
    destField: ['projectID'],
    destSchema: issue,
  }),
}));

Usage in queries:

const projectsWithIssues = zero.query.project
  .related('issues');

// Returns:
// Array<{
//   id: string;
//   name: string;
//   issues: Array<{id: string; projectID: string; title: string}>;
// }>

Many-to-Many (Junction) Relationships

For many-to-many relationships through a junction table:

const issue = table('issue')
  .columns({id: string(), title: string()})
  .primaryKey('id');

const label = table('label')
  .columns({id: string(), name: string()})
  .primaryKey('id');

const issueLabel = table('issueLabel')
  .columns({
    issueID: string(),
    labelID: string(),
  })
  .primaryKey('issueID', 'labelID');

const issueRelationships = relationships(issue, ({many}) => ({
  // Direct access to junction table
  issueLabels: many({
    sourceField: ['id'],
    destField: ['issueID'],
    destSchema: issueLabel,
  }),
  
  // Skip junction to get labels directly
  labels: many(
    {
      sourceField: ['id'],
      destField: ['issueID'],
      destSchema: issueLabel,
    },
    {
      sourceField: ['labelID'],
      destField: ['id'],
      destSchema: label,
    },
  ),
}));

Usage:

// Get issues with their labels
const issues = zero.query.issue
  .related('labels');

// Returns:
// Array<{
//   id: string;
//   title: string;
//   labels: Array<{id: string; name: string}>;
// }>

Composite Foreign Keys

Relationships can use multiple columns:

const orderLine = table('orderLine')
  .columns({
    orderID: string(),
    lineNumber: number(),
    productID: string(),
  })
  .primaryKey('orderID', 'lineNumber');

const product = table('product')
  .columns({
    id: string(),
    name: string(),
  })
  .primaryKey('id');

const orderLineRelationships = relationships(orderLine, ({one}) => ({
  product: one({
    sourceField: ['productID'],  // Single column
    destField: ['id'],
    destSchema: product,
  }),
}));

Multi-Hop Relationships

Chain relationships for deeper traversals:

const comment = table('comment')
  .columns({id: string(), issueID: string(), creatorID: string()})
  .primaryKey('id');

const issue = table('issue')
  .columns({id: string(), projectID: string()})
  .primaryKey('id');

const project = table('project')
  .columns({id: string(), name: string()})
  .primaryKey('id');

const commentRelationships = relationships(comment, ({one}) => ({
  // Two-hop: comment -> issue -> project
  project: one(
    {
      sourceField: ['issueID'],
      destField: ['id'],
      destSchema: issue,
    },
    {
      sourceField: ['projectID'],
      destField: ['id'],
      destSchema: project,
    },
  ),
}));

Creating the Schema

Once tables and relationships are defined, create the schema:

export const schema = createSchema({
  tables: [
    user,
    project,
    issue,
    comment,
    label,
    issueLabel,
  ],
  relationships: [
    projectRelationships,
    issueRelationships,
    commentRelationships,
  ],
});

tables

array

required

Array of table definitions created with table().

relationships

array

Array of relationship definitions created with relationships().

Type Augmentation

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

This enables:

Type-safe query building
Autocomplete for table and column names
Correct return types for queries
Compile-time errors for invalid queries

Schema Validation

Zero validates your schema at runtime:

const schema = createSchema({
  tables: [user, issue],
  relationships: [issueRelationships],
});

// Throws if:
// - Table has no primary key
// - Relationship references non-existent table
// - Relationship field doesn't exist in table
// - Multiple tables reference the same serverName
// - Relationship name conflicts with column name

Common Validation Errors

Missing primary key

// ❌ Error: Table "user" is missing a primary key
const user = table('user')
  .columns({id: string(), name: string()});
  // Missing: .primaryKey('id')

Relationship to non-existent table

// ❌ Error: destination table "post" is missing in the schema
const userRelationships = relationships(user, ({many}) => ({
  posts: many({
    sourceField: ['id'],
    destField: ['userID'],
    destSchema: post,  // 'post' not in schema.tables
  }),
}));

Invalid relationship field

// ❌ Error: source field "userId" is missing in table "issue"
const issueRelationships = relationships(issue, ({one}) => ({
  creator: one({
    sourceField: ['userId'],  // Should be 'creatorID'
    destField: ['id'],
    destSchema: user,
  }),
}));

Name conflict

const issue = table('issue')
  .columns({
    id: string(),
    creator: string(),  // ❌ Column named 'creator'
  })
  .primaryKey('id');

// ❌ Error: Relationship "creator" has same name as column
const issueRelationships = relationships(issue, ({one}) => ({
  creator: one({...}),  // Name conflict
}));

Schema Migrations

Zero doesn’t manage PostgreSQL schema migrations. Use your preferred migration tool:

Drizzle
Prisma
node-pg-migrate
Raw SQL

import {drizzle} from 'drizzle-orm/postgres-js';
import {migrate} from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';

const sql = postgres(process.env.DATABASE_URL);
const db = drizzle(sql);

await migrate(db, {migrationsFolder: './drizzle'});

See zbugs app for a complete example.

npx prisma migrate dev --name add_user_table

npm run migrate up

-- migrations/001_create_users.sql
CREATE TABLE users (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL,
  name TEXT
);

After updating your PostgreSQL schema, update your Zero schema definition to match. The Zero schema should mirror your PostgreSQL structure.

Best Practices

Keep schema and database in sync

Your Zero schema should accurately reflect your PostgreSQL schema. Mismatches will cause runtime errors.

Use enumerations for literal unions

// ✅ Good: Type-safe status
status: enumeration<'open' | 'closed'>(),

// ❌ Avoid: Plain string loses type safety
status: string(),

Explicit optional fields

// ✅ Good: Explicit undefined
name: string().optional(),  // string | undefined

// The type system will catch missing null checks

Name relationships meaningfully

// ✅ Good: Clear relationship name
creator: one({...}),
assignee: one({...}),

// ❌ Avoid: Generic names
user1: one({...}),
user2: one({...}),

Use composite keys when appropriate

// ✅ Good: Natural composite key for junction table
const issueLabel = table('issueLabel')
  .columns({issueID: string(), labelID: string()})
  .primaryKey('issueID', 'labelID');

// ❌ Avoid: Artificial ID for junction tables
// (though sometimes necessary for other reasons)

Example: Complete Schema

Here’s the full schema from the zbugs app:

import {
  boolean,
  createSchema,
  enumeration,
  number,
  relationships,
  string,
  table,
} from '@rocicorp/zero';
import type {Role} from './auth';

const user = table('user')
  .columns({
    id: string(),
    login: string(),
    name: string().optional(),
    avatar: string(),
    role: enumeration<Role>(),
  })
  .primaryKey('id');

const project = table('project')
  .columns({
    id: string(),
    name: string(),
    issueCountEstimate: number().optional(),
  })
  .primaryKey('id');

const issue = table('issue')
  .columns({
    id: string(),
    shortID: number().optional(),
    title: string(),
    open: boolean(),
    created: number(),
    modified: number(),
    projectID: string(),
    creatorID: string(),
    assigneeID: string().optional(),
  })
  .primaryKey('id');

const comment = table('comment')
  .columns({
    id: string(),
    issueID: string(),
    created: number(),
    body: string(),
    creatorID: string(),
  })
  .primaryKey('id');

const issueRelationships = relationships(issue, ({one, many}) => ({
  project: one({
    sourceField: ['projectID'],
    destField: ['id'],
    destSchema: project,
  }),
  creator: one({
    sourceField: ['creatorID'],
    destField: ['id'],
    destSchema: user,
  }),
  assignee: one({
    sourceField: ['assigneeID'],
    destField: ['id'],
    destSchema: user,
  }),
  comments: many({
    sourceField: ['id'],
    destField: ['issueID'],
    destSchema: comment,
  }),
}));

const commentRelationships = relationships(comment, ({one}) => ({
  creator: one({
    sourceField: ['creatorID'],
    destField: ['id'],
    destSchema: user,
  }),
  issue: one({
    sourceField: ['issueID'],
    destField: ['id'],
    destSchema: issue,
  }),
}));

export const schema = createSchema({
  tables: [user, project, issue, comment],
  relationships: [issueRelationships, commentRelationships],
});

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

Next Steps

Queries

Learn how to query your schema

Relationships

Query related data efficiently

Permissions

Control access to your data

Mutations

Write data with type-safe mutators

Get Started

Core Concepts

Client Guide

Server Guide

Advanced

Quick Example

Tables

Defining Tables

Column Types

Optional Columns

Composite Primary Keys

Server Name Mapping

Relationships

One-to-One Relationships

One-to-Many Relationships

Many-to-Many (Junction) Relationships

Composite Foreign Keys

Multi-Hop Relationships

Creating the Schema

Type Augmentation

Schema Validation

Common Validation Errors

Schema Migrations

Best Practices

Example: Complete Schema

Next Steps

Queries

Relationships

Permissions

Mutations

Build docs developers (and LLMs) love

Get Started

Core Concepts

Client Guide

Server Guide

Advanced

​Quick Example

​Tables

​Defining Tables

​Column Types

​Optional Columns

​Composite Primary Keys

​Server Name Mapping

​Relationships

​One-to-One Relationships

​One-to-Many Relationships

​Many-to-Many (Junction) Relationships

​Composite Foreign Keys

​Multi-Hop Relationships

​Creating the Schema

​Type Augmentation

​Schema Validation

​Common Validation Errors

​Schema Migrations

​Best Practices

​Example: Complete Schema

​Next Steps

Queries

Relationships

Permissions

Mutations

Build docs developers (and LLMs) love

Quick Example

Tables

Defining Tables

Column Types

Optional Columns

Composite Primary Keys

Server Name Mapping

Relationships

One-to-One Relationships

One-to-Many Relationships

Many-to-Many (Junction) Relationships

Composite Foreign Keys

Multi-Hop Relationships

Creating the Schema

Type Augmentation

Schema Validation

Common Validation Errors

Schema Migrations

Best Practices

Example: Complete Schema

Next Steps