Database

PostgreSQL schema, Drizzle ORM, and migrations - the foundation for Zero sync

Takeout uses PostgreSQL as the source of truth, with Drizzle ORM for schema management. Zero replicates from Postgres to provide real-time sync to clients. This page covers the database layer—for client-side queries and mutations, see the Zero page.

Architecture

PostgreSQL holds two schemas: schema-private for auth tables that stay server-side only, and schema-public for application data that syncs to Zero clients. Zero replicates from the public schema to give clients access with real-time updates.

Schema Files

The database schema is split into two files in src/database/:

schema-public.ts

Tables that sync to Zero clients. This is your application data:

// src/database/schema-public.ts
import {
  boolean,
  integer,
  pgTable,
  text,
  timestamp,
  varchar,
} from 'drizzle-orm/pg-core'

export const userPublic = pgTable('userPublic', {
  id: varchar('id').primaryKey(),
  name: text('name'),
  username: varchar('username').unique(),
  image: text('image'),
  joinedAt: timestamp('joinedAt').defaultNow(),
  postsCount: integer('postsCount').default(0),
})

export const post = pgTable('post', {
  id: varchar('id').primaryKey(),
  userId: varchar('userId').references(() => userPublic.id),
  image: text('image').notNull(),
  caption: text('caption'),
  commentCount: integer('commentCount').default(0),
  createdAt: timestamp('createdAt').defaultNow(),
})

schema-private.ts

Authentication and sensitive data that stays server-side only:

// src/database/schema-private.ts
export const user = pgTable('user', {
  id: varchar('id').primaryKey(),
  email: varchar('email').unique().notNull(),
  emailVerified: boolean('emailVerified').default(false),
  role: varchar('role').default('user'),
  banned: boolean('banned').default(false),
})

export const session = pgTable('session', {
  id: varchar('id').primaryKey(),
  userId: varchar('userId').references(() => user.id),
  token: text('token').unique().notNull(),
  expiresAt: timestamp('expiresAt').notNull(),
  ipAddress: text('ipAddress'),
})

Both schemas feed into Drizzle migrations—“private” means the data doesn’t sync to clients, not that it’s excluded from migrations.

Migrations

Takeout has a unified migration system that combines Drizzle schema migrations with custom TypeScript migrations.

Workflow

1. Edit src/database/schema-public.ts or schema-private.ts
2. Run migrations:

bun migrate run

That’s it. CI handles building and deploying migrations automatically.

Custom Migrations

For data migrations or changes that can’t be expressed in schema:

bun db:migrate-add backfill-user-counts

This creates a TypeScript migration file:

// src/database/migrations/0003_backfill_user_counts.ts
import type { PoolClient } from 'pg'

export async function up(client: PoolClient) {
  await client.query(`
    UPDATE "userPublic" u
    SET "postsCount" = (
      SELECT COUNT(*) FROM "post" WHERE "userId" = u.id
    )
  `)
}

We don’t do down migrations—only forward.

How It Works

bun migrate build does three things:

1. Runs drizzle-kit generate to create SQL from schema changes
2. Wraps SQL migrations in TypeScript (using ?raw imports)
3. Bundles everything into a single deployable file

The migration runner tracks applied migrations in a migrations table and runs them in a transaction.

Server-Side Queries

For server-side code (API routes, server actions), use Drizzle ORM:

import { getDb } from '~/database'
import { userPublic } from '~/database/schema-public'
import { eq } from 'drizzle-orm'

const db = getDb()
const user = await db.select().from(userPublic).where(eq(userPublic.id, userId))

See the Drizzle ORM docs for the full query API.

For client-side queries that sync in real-time, use Zero’s ZQL—see the Zero page and Zero’s Reading Data docs.

Aggregates and Triggers

Zero doesn’t support aggregates, but even if it did, computing aggregates on read is slow for any database. Queries like SELECT COUNT(*) FROM comments WHERE postId = ? seem innocent but become problematic at scale—they lock rows, block writes, and get slower as data grows.

The solution is denormalization: pre-computing and storing derived values alongside your source data. Instead of counting comments on every read, we store post.commentCount and update it via triggers whenever comments change. This trades a bit of write complexity for dramatically faster reads.

This is a well-established pattern used by most high-traffic applications. For example, post.commentCount is updated automatically when comments are added or removed.

Simple Counter Pattern

-- in a migration file
CREATE OR REPLACE FUNCTION update_post_comment_count()
RETURNS TRIGGER AS $$
DECLARE
  target_post_id varchar;
  new_count integer;
BEGIN
  IF TG_OP = 'DELETE' THEN
    target_post_id := OLD."postId";
  ELSE
    target_post_id := NEW."postId";
  END IF;

  -- check post still exists (cascade delete safety)
  IF NOT EXISTS (SELECT 1 FROM "post" WHERE "id" = target_post_id) THEN
    RETURN NULL;
  END IF;

  SELECT COUNT(*) INTO new_count
  FROM "comment"
  WHERE "postId" = target_post_id;

  UPDATE "post"
  SET "commentCount" = new_count
  WHERE "id" = target_post_id;

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER post_comment_count_trigger
AFTER INSERT OR DELETE ON "comment"
FOR EACH ROW
EXECUTE FUNCTION update_post_comment_count();

Key patterns:

• Recalculate the count from scratch (safer than incrementing)
• Check parent exists before updating (prevents cascade delete errors)
• Use AFTER triggers to ensure the source data is committed

For complex aggregates like time-windowed stats or multi-table relationships, create dedicated stats tables with their own triggers.

Local Development

Starting the Database

# start postgres via docker
bun backend

# or just the database without other services
docker compose up postgres

Connecting

# interactive psql
bun env:dev bunx postgres psql

# run a query
bun env:dev bunx postgres psql --query "SELECT * FROM \"userPublic\" LIMIT 5"

Or use a GUI like TablePlus with the connection string from ZERO_UPSTREAM_DB (default: postgresql://user:password@127.0.0.1:5433/postgres).

Troubleshooting

Migration file is blank/empty

Drizzle found no schema changes. Check that:

• Your changes are saved
• You’re editing the right file (schema-public.ts vs schema-private.ts)
• The schema doesn’t already match the database

Check which migrations have run

bun env:dev bunx postgres psql --query "SELECT * FROM migrations ORDER BY id"

Migration failed

Migrations run in a transaction, so failures should rollback automatically. If you need to manually fix:

1. Check what migrations have run (query above)
2. If a bad migration was applied, write a new migration to fix it
3. If stuck in a partial state, you may need to manually delete from the migrations table:

DELETE FROM migrations WHERE name = '0003_bad_migration';

Then fix your schema and run migrations again.

Schema changes not detected

• Both schema-public.ts and schema-private.ts go to Drizzle
• “Private” means it doesn’t sync to Zero clients, not “excluded from migrations”
• Run bun migrate run which regenerates migrations fresh

Zero not seeing changes

After schema changes:

1. Run bun migrate run to update Postgres
2. Run bun tko zero generate to regenerate Zero types
3. Restart the Zero server

Zero replicates from Postgres, so the database must be updated first.

Learn More

• Zero page - Client-side queries and mutations
• Drizzle ORM docs - Schema and query reference
• PostgreSQL docs