P-5 — Database Integration: Prisma, PostgreSQL, and Connection Pooling

With PgBouncer, your DATABASE_URL points to PgBouncer, not PostgreSQL directly. Prisma with PgBouncer in transaction mode (the default for serverless) requires the ?pgbouncer=true query parameter and directUrl for migrations:

# .env
DATABASE_URL="postgresql://user:password@pgbouncer-host:6432/mydb?pgbouncer=true"
DIRECT_URL="postgresql://user:password@postgres-host:5432/mydb"

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")     # ← PgBouncer (for app queries)
  directUrl = env("DIRECT_URL")       # ← PostgreSQL directly (for migrations)
}

Migrations use directUrl because PgBouncer's transaction mode doesn't support the advisory locks Prisma uses during migration.

Option 2: Neon's Built-In Pooling

If you're using Neon (serverless PostgreSQL), it provides built-in HTTP-based connection pooling designed specifically for serverless:

npm install @neondatabase/serverless

// lib/db.ts
import 'server-only';
import { PrismaNeon } from '@prisma/adapter-neon';
import { Pool } from '@neondatabase/serverless';
import { PrismaClient } from '@prisma/client';
 
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient | undefined };
 
function createPrismaClient() {
  const pool = new Pool({ connectionString: process.env.DATABASE_URL });
  const adapter = new PrismaNeon(pool);
  return new PrismaClient({ adapter });
}
 
export const db = globalForPrisma.prisma ?? createPrismaClient();
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = db;

Neon's serverless driver has two modes, and it's worth not conflating them: the Pool/Client classes shown above use WebSockets to emulate a persistent, TCP-like session (compatible with PrismaNeon and transactions). Separately, Neon's tagged-template neon() function does true single-query-per-HTTP-request calls with no session at all — lower latency for a single one-off query, but no transaction support. The WebSocket-based Pool is what you want when you need Prisma's transaction and connection-pooling semantics; reach for neon() directly only for simple, non-transactional one-off queries.

Option 3: Prisma Accelerate

Prisma's own connection pooling and global edge caching layer. Drop-in replacement — change your connection string, install the extension:

npm install @prisma/extension-accelerate

import { withAccelerate } from '@prisma/extension-accelerate';
 
export const db = new PrismaClient().$extends(withAccelerate());

Accelerate also provides result caching at the query level with TTLs — an additional layer on top of the Next.js caching model. It's a paid service but worth evaluating for high-traffic applications.

---

The Full Mutation Cycle

Connecting Server Actions to Prisma with proper auth and cache invalidation:

// app/actions/posts.ts
'use server';
 
import { auth } from '@/auth';
import { db } from '@/lib/db';
import { revalidateTag } from 'next/cache';
import { redirect } from 'next/navigation';
import { z } from 'zod';
import { after } from 'next/server';
 
const PostSchema = z.object({
  title: z.string().min(1, 'Title is required').max(200),
  content: z.string().min(10, 'Content must be at least 10 characters'),
  published: z.coerce.boolean().default(false),
});
 
type ActionState = {
  errors?: Record<string, string[]>;
  message?: string;
};
 
export async function createPost(
  prevState: ActionState,
  formData: FormData
): Promise<ActionState> {
  // 1. Auth check
  const session = await auth();
  if (!session?.user?.id) return { message: 'Unauthorized' };
  if (!['editor', 'admin'].includes(session.user.role)) {
    return { message: 'Forbidden' };
  }
 
  // 2. Validate
  const result = PostSchema.safeParse({
    title: formData.get('title'),
    content: formData.get('content'),
    published: formData.get('published'),
  });
 
  if (!result.success) {
    return { errors: result.error.flatten().fieldErrors };
  }
 
  // 3. Persist
  let post;
  try {
    post = await db.post.create({
      data: {
        ...result.data,
        publishedAt: result.data.published ? new Date() : null,
        authorId: session.user.id,
      },
    });
  } catch (e) {
    console.error('Failed to create post:', e);
    return { message: 'Database error. Please try again.' };
  }
 
  // 4. Invalidate cache
  revalidateTag('posts');
  revalidateTag(`user-posts-${session.user.id}`);
 
  // 5. Non-blocking side effects
  after(async () => {
    await notifySubscribers(post.id);
  });
 
  // 6. Navigate
  redirect(`/posts/${post.id}`);
}

The six steps are the canonical mutation pattern: auth → validate → persist → invalidate → side effects → navigate. Every step has a reason:

• Auth before anything — never trust that the caller is authenticated
• Validate before DB — reject bad input before touching the database
• try/catch on DB — distinguish auth/validation failures (expected) from infrastructure failures (unexpected)
• Invalidate after commit — don't invalidate before the DB write succeeds
• after() for notifications — don't block the response on non-critical work
• redirect() last — after the commit is confirmed

---

Querying Patterns

Common Prisma query patterns in Server Components:

// Paginated query with cursor-based pagination
async function getPosts(cursor?: string, take = 20) {
  return db.post.findMany({
    where: { published: true },
    orderBy: { publishedAt: 'desc' },
    take,
    ...(cursor && { cursor: { id: cursor }, skip: 1 }),
    include: { author: { select: { name: true, id: true } } },
  });
}
 
// Count + data in parallel
const [posts, totalCount] = await Promise.all([
  db.post.findMany({ where: { published: true }, take: 20 }),
  db.post.count({ where: { published: true } }),
]);
 
// Upsert — create or update
await db.user.upsert({
  where: { email: session.user.email },
  update: { lastSeenAt: new Date() },
  create: {
    email: session.user.email,
    name: session.user.name,
    lastSeenAt: new Date(),
  },
});
 
// Transaction — multiple operations atomically
await db.$transaction([
  db.post.update({ where: { id }, data: { published: true } }),
  db.user.update({ where: { id: authorId }, data: { publishedCount: { increment: 1 } } }),
]);

Cursor-based pagination over offset pagination for large datasets: SELECT ... LIMIT 20 OFFSET 10000 requires the database to scan 10,020 rows. Cursor-based pagination (WHERE id > $cursor ORDER BY id LIMIT 20) is O(log n) via the index regardless of depth.

---

Prisma and the App Router — Common Mistakes

Mistake: new PrismaClient() inside a Server Component

// ❌ Creates a new connection pool on every render
export default async function PostsPage() {
  const prisma = new PrismaClient();
  const posts = await prisma.post.findMany();
  return <PostList posts={posts} />;
}

This is how you exhaust database connections in development within minutes. Always import db from your singleton module.

Mistake: Importing Prisma in Client Components

// ❌ Pulls Prisma into the browser bundle (will fail at runtime)
'use client';
import { db } from '@/lib/db';

lib/db.ts has import 'server-only' which will throw a build error if you do this. The error is intentional.

Mistake: Running migrations against the pool URL

Always use DIRECT_URL (or directUrl in the schema) for migrations. PgBouncer in transaction mode doesn't support advisory locks. If your migration hangs indefinitely, you're probably running it against PgBouncer.

---

Database Schema for Auth.js Sessions

If you use Auth.js database sessions (not JWT), the schema needs additional tables:

// Add to schema.prisma if using @auth/prisma-adapter
 
model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?
  user              User    @relation(fields: [userId], references: [id], onDelete: Cascade)
 
  @@unique([provider, providerAccountId])
}
 
model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}
 
model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime
 
  @@unique([identifier, token])
}

Install the adapter:

npm install @auth/prisma-adapter

// auth.ts
import { PrismaAdapter } from '@auth/prisma-adapter';
import { db } from '@/lib/db';
 
export const { handlers, auth, signIn, signOut } = NextAuth({
  adapter: PrismaAdapter(db),
  session: { strategy: 'database' },
  providers: [...],
});

---

Where We Go From Here

P-6 covers Middleware — the edge layer that runs before every request. You've already seen it in action for auth redirects in P-4. P-6 goes deeper: the matcher config, how to read and write cookies at the edge, draftMode() for CMS preview workflows, and the 1ms performance budget that forces you to think carefully about what you put in Middleware.

P-7 then covers the full four-layer caching model — the most complex topic in the Practitioner phase and the one that determines whether your application handles traffic gracefully or falls over under load.

---