Database

Kit uses Supabase (Postgres) with Drizzle ORM for type-safe queries. This guide covers the setup, schema, and development workflow.

Overview

The stack:

Supabase - Managed Postgres, local dev via the Supabase CLI
Drizzle ORM - Type-safe queries over postgres.js
drizzle-kit push - Schema sync, no migration files
RLS deny-by-default - Authorization lives in tRPC, not the database

Architecture

graph TB
    A[tRPC Procedures] --> B[Drizzle ORM]
    B --> C[postgres.js]
    C --> D[Supabase 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 * as schema from "./drizzle-schema";
import * as schemaAuth from "./drizzle-schema-auth";

const client = postgres(
  process.env.POSTGRES_URL ?? "postgresql://postgres:postgres@127.0.0.1:54322/postgres",
  // POSTGRES_URL points at Supavisor's transaction-mode pooler (:6543) in
  // production, which does not support server-side prepared statements
  { prepare: false },
);

export const db = drizzle({
  client,
  schema: { ...schemaAuth, ...schema },
  casing: "snake_case",
});

POSTGRES_URL is the only connection env var. It defaults to local Supabase.

Schema

Two schema files:

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
export const waitlist = pgTable("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(),
})).enableRLS();

export const todo = pgTable(
  "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)],
).enableRLS();

Every table calls .enableRLS() with no policies - deny by default. The public schema is reachable through PostgREST with the anon key, so this blocks direct access. Authorization lives in tRPC. The server's Drizzle connection is unaffected: the table owner bypasses RLS.

If you regenerate the auth schema, re-add .enableRLS() to every table.

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: "./supabase/migrations",
  dialect: "postgresql",
  dbCredentials: {
    // drizzle-kit needs a direct connection, not the pooler
    url: nonPoolingUrl,
  },
  schemaFilter: ["public"],
  casing: "snake_case",
} satisfies Config;

Development Workflow

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

pnpm db:start         # Start local Supabase
pnpm db:stop          # Stop local Supabase
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("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(),
})).enableRLS();

// 2. Define relations
export const postRelations = relations(post, ({ one }) => ({
  organization: one(organization, {
    fields: [post.organizationId],
    references: [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: (t, { eq }) => eq(t.organizationId, organization.id),
  orderBy: (t, { desc }) => desc(t.createdAt),
});

// 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 Supabase

Config lives in packages/db/supabase/config.toml. Supabase Auth and the Data API (PostgREST) are disabled — better-auth owns auth, and all queries go through tRPC/Drizzle. Storage is enabled with a public avatars bucket; writes go through /api/account/avatar with the service-role key, so no storage policies are needed.

Every table enables RLS with no policies, so the Data API serves zero rows to anon/authenticated — authorization lives in tRPC. Hosted projects should disable the Data API under Settings → API; nothing in the app uses it.

pnpm db:start   # supabase start (Postgres on :54322, Studio on :54323)

For hosted projects, create the avatars bucket in the Supabase dashboard.

Best Practices

Scope by organization - Multi-tenant tables reference organization.id and get an index on it
Check membership in tRPC - 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; Supavisor handles it

Troubleshooting

Connection errors

pnpm -F db status   # Is Supabase running?
echo $POSTGRES_URL

Schema drift

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

Prepared statement errors in production

POSTGRES_URL points at the transaction pooler (:6543) - the client must keep prepare: false.

Next Steps

Authentication - How auth uses these tables
API development - Build tRPC procedures with Drizzle queries