Supported Postgres Features

Postgres has a massive featureset, of which Zero supports a growings subset.

Object Types

• Tables are synced
• Views are not synced
• Generated columns are not synced
• Indexes aren’t synced per-se but we do implicitly add indexes to the replica that match the upstream indexes. In the future this will be customizable.

Column Names

Currently Zero syncs column names exactly as they appear in Postgres. There is no ability to map column names from i.e., FULL_NAME in Postgres to fullName in TypeScript.

We will add column name mapping to Zero soon. See https://bugs.rocicorp.dev/issue/3106.

Column Types

Postgres Type	Type to put in schema.ts	Resulting JS/TS Type
All numeric types	number	number
char, varchar, text	string	string
bool	boolean	boolean
date, timestamp, timestampz	number	number
json, jsonb	json	JSONValue
enum	enumeration	string

Other Postgres column types aren’t supported. They will be ignored when replicating (the synced data will be missing that column) and you will get a warning when zero-cache starts up.

If your schema has a pg type not listed here, you can support it in Zero by using a trigger to map it to some type that Zero can support. For example if you have an enum type Mood used by column user_mood mood, you can use a trigger to map it to a user_mood_text text column. You would then use another trigger to map changes to user_mood_text back to user_mood so that the data can be updated by Zero.

Let us know if the lack of a particular column type is hindering your use of Zero. It can likely be added.

Column Defaults

Default values are allowed in the Postgres schema but there currently is no way to use them from a Zero app. The create mutation requires all columns to be specified, except when columns are nullable (in which case,they default to null). Since there is no way to leave non-nullable columns off the insert, there is no way for PG to apply the default. This is a known issue and will be fixed in the future.

Primary Keys

Primary keys are required on all synced tables.

It is also strongly recommended that primary keys be client-generated random strings like uuid, ulid, nanoid, etc. This makes optimistic creation and updates much easier.

If you want to have a short auto-incrementing numeric ID for ux reasons (ie, a bug number), that is possible – See Demo Video!

Multi-column primary and foreign keys are supported.

Limiting Replication

You can use Permissions to limit tables and rows from replicating to Zero. In the near future you’ll also be able to use Permissions to limit individual columns.

Until then, a workaround is to use the Postgres publication feature to control the tables and columns that are replicated into zero-cache.

In your pg schema setup, create a Postgres publication with the tables and columns you want: CREATE PUBLICATION zero_data FOR TABLE users (col1, col2, col3, ...), issues, comments . If no such publication is present, Zero defaults to creating a publication that publishes the entire public schema.

To limit what is synced from the zero-cache replica to actual clients (e.g., web browsers) you can use read permissions.

Self-Referential Relationships

Tables with relationships to themselves (i.e., comment that can have a parent comment ) are supported with two caveats:

1. Our related syntax has no sense of recursion, so you need to define your query manually to whatever level of depth you want. This actually ends up often being what you want – to get just a few levels of the tree at a time.

2. Our createTableSchema helper can’t deal with recursive types, so you have to first define the object outside createTableSchema then pass it as an arg, like so:

   Copyconst foo = {
     tableName: 'issue',
     columns: {
       id: {type: 'string'},
       title: {type: 'string'},
     },
     primaryKey: ['id'],
     relationships: {
       self: {
         source: 'id',
         dest: {
           field: 'id',
           schema: () => issueSchema,
         },
       },
     },
   } as const;
   const issueSchema = createTableSchema(foo);
   

   See also https://bugs.rocicorp.dev/issue/3103.