Next.js + Prisma + Postgres

Project context

This is a Next.js 15 App Router project using Prisma as the ORM and Postgres as the database. Schema is the source of truth; migrations are versioned and never modified after merge. Prisma is server-only — never imported into client components.

Stack

• Next.js 15 App Router, React 19, TypeScript strict
• Prisma 6+ as the ORM
• Postgres 15+ (managed via Neon, Supabase, or self-hosted)
• pnpm as the package manager
• Tailwind 4 for styling

Environment

DATABASE_URL=          — pooled connection used by serverless functions
DIRECT_URL=            — direct connection used by Prisma migrations only

If deploying to Vercel or similar, use a connection pooler (PgBouncer / Neon's pooler) for DATABASE_URL and keep DIRECT_URL for migrations.

Prisma client setup

The Prisma client is a singleton. In dev, next dev hot-reloads modules — without the singleton pattern, you'll get connection-pool exhaustion.

// lib/prisma.ts
import 'server-only'
import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient }

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({ log: process.env.NODE_ENV === 'development' ? ['query', 'error'] : ['error'] })

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

The import 'server-only' line is non-negotiable. It hard-fails the build if anything client-side tries to import this file.

Schema conventions

• One model per database table
• Use @id @default(cuid()) for primary keys (or UUIDs if needed for cross-system references)
• Always set createdAt DateTime @default(now()) and updatedAt DateTime @updatedAt
• Add @@index([...]) for any field used in where filters
• Use @@map to keep column names snake_case in the DB while staying camelCase in TypeScript

model Post {
  id        String   @id @default(cuid())
  title     String
  authorId  String   @map("author_id")
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt       @map("updated_at")

  @@index([authorId])
  @@map("posts")
}

Migration workflow

• pnpm prisma migrate dev --name <description> — generate a migration locally
• Review the SQL before committing — Prisma sometimes generates surprising migrations
• pnpm prisma migrate deploy — applied in CI / production
• Never edit a migration after it's been committed; create a new one to amend
• Never run prisma db push against production — that's a dev-loop tool only

Querying patterns

Simple reads — call prisma.<model>.findMany() directly from a server component.

Transactions for multi-write operations:

await prisma.$transaction(async (tx) => {
  const post = await tx.post.create({ data })
  await tx.auditLog.create({ data: { postId: post.id, action: 'create' } })
  return post
})

Selecting only what you need. Always use select (or include for relations) to avoid over-fetching:

const posts = await prisma.post.findMany({
  select: { id: true, title: true, author: { select: { name: true } } },
})

Batch reads — if you'll query inside a .map(), refactor to findMany({ where: { id: { in: ids } } }) then .map over the result. Never N+1.

Server Actions for mutations

// app/posts/actions.ts
'use server'

import { z } from 'zod'
import { revalidatePath } from 'next/cache'
import { prisma } from '@/lib/prisma'

const Schema = z.object({ title: z.string().min(1).max(200) })

export async function createPost(input: FormData) {
  const data = Schema.parse({ title: input.get('title') })
  const post = await prisma.post.create({ data })
  revalidatePath('/posts')
  return post
}

Type-safe inputs and outputs

• Use Prisma's generated types: Prisma.PostGetPayload<{ include: { author: true } }> for query result types
• Validate inputs with Zod at every action / route handler boundary
• Don't pass raw Prisma types to client components — define a UI-shaped type and project to it

Patterns to avoid

• prisma in client components. It bundles the client into the browser; the build will fail or break at runtime
• include: { everything: true }. Always use select to be explicit
• Editing migrations after merge. Always create a new migration
• prisma db push in CI. Always migrate deploy
• Transactions inside Promise.all. Each transaction is a separate connection — they don't compose; use prisma.$transaction([...]) instead
• Calling Prisma from a route handler that's hit by Edge runtime. Prisma needs Node.js runtime — set export const runtime = 'nodejs' if needed

Testing

• Use a separate Postgres database for tests (Docker or test-containers)
• Reset between tests via TRUNCATE of mutable tables, or prisma.$transaction rollback
• Mock Prisma only at integration boundaries (e.g. mocking the Stripe webhook handler that uses Prisma) — don't mock Prisma itself in unit tests; use a real test DB

Tooling

• pnpm dev — Next.js dev
• pnpm prisma generate — regenerate Prisma client (run after every schema change)
• pnpm prisma migrate dev --name <name> — new migration
• pnpm prisma studio — local DB browser
• pnpm prisma migrate deploy — production migration runner

AI behavioral rules

• Never modify a migration after it's been committed — generate a new one
• Always run pnpm prisma generate after editing schema.prisma
• When asked to add a model field, also add the migration; don't suggest db push
• Use select for every query; never include: true without a reason
• Mark Prisma-using files with import 'server-only' if they could plausibly be imported from a client component
• Validate all inputs with Zod at action / route boundaries
• Run lint, typecheck, and tests before declaring a task done