Supported Postgres Features

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

Object Names

Table and column names must begin with a letter or underscore
- This can be followed letters, numbers, underscores, and hyphens
- Regex: /^[A-Za-z_]+[A-Za-z0-9_-]*$/
The column name _0_version is reserved for internal use

Object Types

Tables are synced
Views are not synced
identity generated columns are synced
All other 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 Types

Postgres Type	Type to put in `schema.ts`	Resulting JS/TS Type
All numeric types	`number`	`number`
`char`, `varchar`, `text`, `uuid`	`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.

IDs

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

🤔Why are client-generated IDs better?

Imagine that the PK of your table is an auto-incrementing integer. If you optimistically create an entity of this type, you will have to give it some ID – the type will require it locally, but also if you want to optimistically create relationships to this row you’ll need an ID.

You could sync the highest value seen for that table, but there are race conditions and it is possible for that ID to be taken by the time the creation makes it to the server. Your database can resolve this and assign the next ID, but now the relationships you created optimistically will be against the wrong row. Blech.

GUIDs makes a lot more sense in local-first applications.

If your table has a natural key you can use that and it has less problems. But there is still the chance for a conflict. Imagine you are modeling orgs and you choose domainName as the natural key. It is possible for a race to happen and when the creation gets to the server, somebody has already chosen that domain name. In that case, the best thing to do is reject the write and show the user an error.

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

Primary Keys

Each table synced with Zero must have either a primary key or at least one unique index.

This is needed so that Zero can identify rows during sync, to distinguish between an edit and a remove/add.

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;

Then, specify this publication in the App Publications zero-cache option. (By default, Zero creates 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.

Schema changes

Most Postgres schema changes are supported as is.

Two cases require special handling:

Adding columns

Adding a column with a non-constant DEFAULT value is not supported.

This includes any expression with parentheses, as well as the special functions CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP (due to a constraint of SQLite).

However, the DEFAULT value of an existing column can be changed to any value, including non-constant expressions. To achieve the desired column default:

Add the column with no DEFAULT value
Backfill the column with desired values
Set the column's DEFAULT value

BEGIN;

ALTER TABLE foo ADD bar ...;  -- without a DEFAULT value
UPDATE foo SET bar = ...;
ALTER TABLE foo ALTER bar SET DEFAULT ...;

COMMIT;

Changing publications

Postgres allows you to change published tables/columns with an ALTER PUBLICATION statement. Zero automatically adjusts the table schemas on the replica, but it does not receive the pre-existing data.

To stream the pre-existing data to Zero, make an innocuous UPDATE after adding the tables/columns to the publication:

BEGIN;

ALTER PUBLICATION zero_data ADD TABLE foo;

ALTER TABLE foo REPLICA IDENTITY FULL;
UPDATE foo SET id = id;  -- For some column "id" in "foo"
ALTER TABLE foo REPLICA IDENTITY DEFAULT;

COMMIT;