Architecture

Database

Postgres with Drizzle ORM: local Supabase in development, schema push, and authorization in oRPC

Init uses Postgres with Drizzle ORM for type-safe queries - local Supabase in development (Postgres as a native process), a Supabase project in production by default. This guide covers the setup, schema, and development workflow.

Overview

The stack:

  • Postgres - Postgres 17 from the Supabase CLI locally, a Supabase project (or any Postgres) in production
  • Drizzle ORM - Type-safe queries over postgres.js
  • drizzle-kit push - Schema sync, no migration files
  • RLS deny-by-default - Authorization lives in oRPC, not the database

Architecture

graph TB
    A[oRPC Procedures] --> B[Drizzle ORM]
    B --> C[postgres.js]
    C --> D[(Postgres)]

    E[drizzle-schema.ts] --> F[drizzle-kit push]
    F --> D

Client

One server-side client, shared by the API and better-auth:

// packages/db/src/drizzle-client.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

import { relations } from "./drizzle-relations";

const client = postgres(
  // Locally `pnpm db:start` writes POSTGRES_URL; the fallback only lets code that
  // never queries (a build, a unit test) import this
  process.env.POSTGRES_URL ?? "postgresql://postgres:postgres@127.0.0.1:1/postgres",
  // POSTGRES_URL is the pooled URL in production, and transaction-mode
  // poolers do not support server-side prepared statements
  { prepare: false },
);

export const db = drizzle({ client, relations });

POSTGRES_URL is the only connection env var the app reads; locally, pnpm db:start writes it into .env.local. drizzle-kit alone prefers POSTGRES_URL_NON_POOLING when set.

Schema

Two schema files and one relation graph:

  • packages/db/src/drizzle-relations.ts - defineRelations over both schema files; it powers db.query.* and is how the better-auth adapter finds its tables
  • packages/db/src/drizzle-schema.ts - application tables (waitlist, todo)
  • packages/db/src/drizzle-schema-auth.ts - better-auth generated tables (user, session, account, verification, organization, member, invitation)

Application Tables

// packages/db/src/drizzle-schema.ts
// Unnamed columns take snake_case SQL names from this creator (organizationId -> organization_id)
const pgTable = pgTableCreator((name) => name, "snake_case");

export const waitlist = pgTable.withRLS("waitlist", (t) => ({
  id: t.uuid().notNull().primaryKey().defaultRandom(),
  userId: t.text().references(() => user.id, { onDelete: "set null" }),
  source: t.text(),
  email: t.text().notNull().unique(),
}));

export const todo = pgTable.withRLS(
  "todo",
  (t) => ({
    id: t.uuid().notNull().primaryKey().defaultRandom(),
    organizationId: t
      .text()
      .notNull()
      .references(() => organization.id, { onDelete: "cascade" }),
    title: t.text().notNull(),
    description: t.text(),
    completed: t.boolean().notNull().default(false),
    createdAt: t.timestamp({ withTimezone: true }).notNull().defaultNow(),
    updatedAt: t.timestamp({ withTimezone: true }).notNull().defaultNow(),
  }),
  (table) => [index("todo_organization_id_idx").on(table.organizationId)],
);

Row Level Security

Every table is declared through pgTable.withRLS with no policies - deny by default. The app never hands Postgres to untrusted clients, so it's defense in depth rather than the primary control: it means a connection made with a non-owner role reads nothing by default. That includes Supabase's Data API, which serves the public schema to the anon and authenticated roles while it's on; the app doesn't use it, so turn it off (Deployment). Authorization lives in oRPC. The server's Drizzle connection is unaffected: the table owner bypasses RLS.

If you regenerate the auth schema, declare every table through pgTable.withRLS again.

Auth Tables

Generated from the better-auth config - don't hand-edit:

cd packages/db && pnpm generate:auth-schema

Configuration

// packages/db/drizzle.config.ts
export default {
  schema: ["./src/drizzle-schema-auth.ts", "./src/drizzle-schema.ts"],
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    // POSTGRES_URL_NON_POOLING ?? POSTGRES_URL - drizzle-kit needs a direct connection
    url,
  },
  schemaFilter: ["public"],
} satisfies Config;

Development Workflow

Init uses drizzle-kit push - schema goes straight to the database, no migration files.

pnpm db:start         # Start local Postgres (Supabase)
pnpm db:stop          # Stop local Postgres
pnpm db:push          # Push schema to local database
pnpm db:push-remote   # Push schema to production (.env.production.local)
pnpm db:reset         # Reset local database, then push schema

Adding a Table

// 1. Define in packages/db/src/drizzle-schema.ts
export const post = pgTable.withRLS("post", (t) => ({
  id: t.uuid().notNull().primaryKey().defaultRandom(),
  organizationId: t
    .text()
    .notNull()
    .references(() => organization.id, { onDelete: "cascade" }),
  title: t.text().notNull(),
  createdAt: t.timestamp({ withTimezone: true }).notNull().defaultNow(),
}));

// 2. Wire it into the relation graph in packages/db/src/drizzle-relations.ts
export const relations = defineRelations({ ...schemaAuth, ...schema }, (r) => ({
  // ...
  post: {
    organization: r.one.organization({ from: r.post.organizationId, to: r.organization.id }),
  },
}));
# 3. Push it
pnpm db:push

Types flow automatically - no codegen step.

Query Patterns

// Relational query
const todos = await db.query.todo.findMany({
  where: { organizationId: organization.id },
  orderBy: { createdAt: "desc" },
});

// Insert
const [created] = await db.insert(todo).values({ organizationId, title }).returning();

// Update
await db
  .update(todo)
  .set({ completed: true, updatedAt: new Date() })
  .where(and(eq(todo.id, id), eq(todo.organizationId, organizationId)));

// Delete
await db.delete(todo).where(eq(todo.id, id));

// Upsert-ish: ignore conflicts
await db.insert(waitlist).values(input).onConflictDoNothing({ target: waitlist.email });

Infer types from the schema:

import type { InferInsertModel, InferSelectModel } from "drizzle-orm";

type Todo = InferSelectModel<typeof todo>;
type NewTodo = InferInsertModel<typeof todo>;

Local Postgres

Local Supabase, from the Supabase CLI that pnpm install brings in. packages/db/supabase/config.toml turns on its stack backend ([experimental] stack, CLI 2.119+), whose native runtime runs Postgres as a plain process on macOS 14+ (Apple silicon) and Linux (glibc 2.35+). The config runs Postgres alone: the app uses none of Supabase's other services.

pnpm db:start      # Start this branch's database; write its URL into .env.local
pnpm db:stop       # Stop it, keep the data
pnpm db:reset      # Wipe it, re-push the schema (and re-seed, from the root script)
pnpm -F db status  # What the CLI is running for this checkout
pnpm -F db studio  # Drizzle Studio, to browse data

The CLI keeps one database per checkout and git branch, each with its own port from 20000 up, so clones, worktrees and parallel agents never share data or fight over a port. That's why the config pins no port: a pinned one makes a second branch's database fail to bind. pnpm db:start is two stock CLI calls: supabase start --runtime native, then supabase status --env --override-name DB_URL=POSTGRES_URL into .env.local, which the with-env scripts load before .env. A branch you haven't used before starts empty - pnpm db:push && pnpm db:seed there.

  • The first start downloads Postgres (~400 MB, cached in ~/.supabase/cache); later starts take seconds.
  • Postgres refuses to run as root. Cloud sessions run as root, so the SessionStart hook in .claude/settings.json creates a supabase-postgres system user and exports SUPABASE_NATIVE_POSTGRES_USER for the session; elsewhere as root, the CLI's error says how.
  • Intel Macs and Windows aren't supported: the native runtime needs Apple silicon or Linux.
  • Keep major_version in config.toml the same as production's (SHOW server_version;).

Best Practices

  • Scope by organization - Multi-tenant tables reference organization.id and get an index on it
  • Check membership in oRPC - RLS won't save you; procedures must verify org access
  • Use relational queries - db.query.x.findMany({ with }) beats N+1s
  • Let the pooler pool - Don't tune postgres.js connection counts; the pooler handles it

Troubleshooting

Connection errors

pnpm db:start       # Start this branch's database and point .env at it
pnpm -F db status   # What is running for this checkout?

Schema drift

pnpm db:reset   # Nuke local, re-push schema

Prepared statement errors in production

POSTGRES_URL is the pooled URL, and transaction-mode poolers do not support server-side prepared statements - the client must keep prepare: false.

Next Steps

  1. Authentication - How auth uses these tables
  2. API development - Build oRPC procedures with Drizzle queries

Authentication

better-auth with the Drizzle adapter: email and GitHub sign-in, sessions, and organizations with roles

Data Modelling

Schema decisions for every new feature: where tables live, house rules, and multi-tenant ownership

On this page

OverviewArchitectureClientSchemaApplication TablesRow Level SecurityAuth TablesConfigurationDevelopment WorkflowAdding a TableQuery PatternsLocal PostgresBest PracticesTroubleshootingNext Steps