Server Setup

Client → Zero Cache → Your API Server → PostgreSQL
         (sync)        (mutations/queries)

npm install @rocicorp/zero

import {zeroPostgresJS} from '@rocicorp/zero/server/adapters/postgresjs';
import postgres from 'postgres';
import {schema} from './schema.ts';

const sql = postgres(process.env.ZERO_UPSTREAM_DB!);

export const dbProvider = zeroPostgresJS(schema, sql);

import {zeroDrizzle} from '@rocicorp/zero/server/adapters/drizzle';
import {drizzle} from 'drizzle-orm/node-postgres';
import {Pool} from 'pg';
import {schema} from './schema.ts';
import * as drizzleSchema from './drizzle-schema.ts';

const pool = new Pool({connectionString: process.env.ZERO_UPSTREAM_DB!});
const drizzleDb = drizzle(pool, {schema: drizzleSchema});

export const dbProvider = zeroDrizzle(schema, drizzleDb);

import {zeroPrisma} from '@rocicorp/zero/server/adapters/prisma';
import {PrismaClient} from '@prisma/client';
import {schema} from './schema.ts';

const prisma = new PrismaClient();

export const dbProvider = zeroPrisma(schema, prisma);

import {zeroPg} from '@rocicorp/zero/server/adapters/pg';
import {Pool} from 'pg';
import {schema} from './schema.ts';

const pool = new Pool({connectionString: process.env.ZERO_UPSTREAM_DB!});

export const dbProvider = zeroPg(schema, pool);

import Fastify from 'fastify';
import {handleMutateRequest, handleQueryRequest} from '@rocicorp/zero/server';
import {dbProvider} from './db.ts';
import {mutators} from './mutators.ts';
import {queries} from './queries.ts';

const app = Fastify();

// Mutation endpoint
app.post('/mutate', async (request, reply) => {
  const response = await handleMutateRequest(
    dbProvider,
    async (transact, mutation) => {
      const mutator = mutators[mutation.name];
      if (!mutator) {
        throw new Error(`Unknown mutation: ${mutation.name}`);
      }
      return await transact(async (tx, name, args) => {
        await mutator(tx, args);
      });
    },
    request.raw
  );
  return response;
});

// Query endpoint
app.post('/query', async (request, reply) => {
  const response = await handleQueryRequest(
    (name, args) => {
      const queryFn = queries[name];
      if (!queryFn) {
        throw new Error(`Unknown query: ${name}`);
      }
      return queryFn(args);
    },
    schema,
    request.raw
  );
  return response;
});

app.listen({port: 3000});

import express from 'express';
import {handleMutateRequest, handleQueryRequest} from '@rocicorp/zero/server';
import {dbProvider} from './db.ts';
import {mutators} from './mutators.ts';
import {queries} from './queries.ts';

const app = express();
app.use(express.json());

app.post('/mutate', async (req, res) => {
  const response = await handleMutateRequest(
    dbProvider,
    async (transact, mutation) => {
      const mutator = mutators[mutation.name];
      if (!mutator) {
        throw new Error(`Unknown mutation: ${mutation.name}`);
      }
      return await transact(async (tx, name, args) => {
        await mutator(tx, args);
      });
    },
    new Request(req.url, {
      method: req.method,
      headers: req.headers as HeadersInit,
      body: JSON.stringify(req.body),
    })
  );
  res.json(response);
});

app.post('/query', async (req, res) => {
  const response = await handleQueryRequest(
    (name, args) => {
      const queryFn = queries[name];
      if (!queryFn) {
        throw new Error(`Unknown query: ${name}`);
      }
      return queryFn(args);
    },
    schema,
    new Request(req.url, {
      method: req.method,
      headers: req.headers as HeadersInit,
      body: JSON.stringify(req.body),
    })
  );
  res.json(response);
});

app.listen(3000);

# PostgreSQL connection
ZERO_UPSTREAM_DB=postgresql://user:password@localhost:5432/mydb

# API endpoints
ZERO_MUTATE_URL=http://localhost:3000/mutate
ZERO_QUERY_URL=http://localhost:3000/query

# Optional: API authentication
ZERO_MUTATE_API_KEY=your-secret-key
ZERO_QUERY_API_KEY=your-secret-key

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

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

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

const userRelationships = relationships(user, ({many}) => ({
  issues: many({
    sourceField: ['id'],
    destField: ['creatorID'],
    destSchema: issue,
  }),
}));

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

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

// db.ts
import {zeroPostgresJS} from '@rocicorp/zero/server/adapters/postgresjs';
import postgres from 'postgres';
import {schema} from './schema.ts';

export const sql = postgres(process.env.ZERO_UPSTREAM_DB!);
export const dbProvider = zeroPostgresJS(schema, sql);

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

ZERO_MUTATE_FORWARD_COOKIES=true
ZERO_QUERY_FORWARD_COOKIES=true

app.post('/mutate', async (request, reply) => {
  const sessionCookie = request.cookies.session;
  // Verify session...
  
  const response = await handleMutateRequest(/*...*/);
  return response;
});

app.post('/mutate', async (request, reply) => {
  const apiKey = request.headers['x-api-key'];
  if (apiKey !== process.env.API_SECRET) {
    return reply.code(401).send({error: 'Unauthorized'});
  }
  
  const response = await handleMutateRequest(/*...*/);
  return response;
});

ZERO_MUTATE_API_KEY=your-secret-key

# docker-compose.yml
version: '3.8'
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: myapp
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

docker compose up -d

node server.js

zero-cache-dev

npm run dev

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

sudo systemctl restart postgresql

-- Replicate all tables in public schema (default)
CREATE PUBLICATION zero_public FOR TABLES IN SCHEMA public;

-- Or replicate specific tables
CREATE PUBLICATION zero_tables FOR TABLE users, issues, comments;

ZERO_APP_PUBLICATIONS=zero_public

import {ApplicationError} from '@rocicorp/zero/server';

export async function createIssue(tx, args) {
  if (!args.title) {
    throw new ApplicationError('Title is required', {
      details: {field: 'title'},
    });
  }
  
  await tx.mutate.issue.insert({
    id: nanoid(),
    title: args.title,
    creatorID: tx.clientID,
    created: Date.now(),
  });
}