Database

Init uses Postgres with Drizzle ORM for type-safe queries - a Docker container locally, Vercel Postgres in production. This guide covers the setup, schema, and development workflow.

Overview

The stack:

Postgres - postgres:18-alpine via Docker Compose locally, Vercel 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(
  process.env.POSTGRES_URL ?? "postgresql://postgres:postgres@127.0.0.1:54322/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. It defaults to the local Docker container. 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)],
);

Every table is declared through pgTable.withRLS with no policies - deny by default. Nothing in this stack exposes 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. 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 (Docker)
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

One container, defined in packages/db/docker-compose.yml:

pnpm db:start    # docker compose up -d --wait (Postgres on :54322 by default)
pnpm db:stop     # Stop the container, keep the data
pnpm db:reset    # Drop the volume, recreate, re-push the schema
pnpm -F db studio  # Drizzle Studio, to browse data

Port 54322 rather than 5432, so a Postgres already installed on the host doesn't clash — and POSTGRES_PORT overrides it, which is how two projects run at once. --wait blocks on the container's healthcheck, which checks over TCP - the throwaway server that runs during first-boot initdb answers on the Unix socket, so a socket check would return before the real server is listening.

COMPOSE_PROJECT_NAME in .env namespaces the Docker volume. Compose otherwise derives the project name from the compose file's directory - db for every repo cloned from this template - and they would all share one database. pnpm bootstrap sets it from the repo folder name.

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 -F db status   # Is the Postgres container running?
echo $POSTGRES_URL

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

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