Module P-5·24 min read

Setting up Prisma with the App Router, why new PrismaClient() per request will kill your database at scale, PgBouncer and Neon for serverless pooling, and the full mutation pattern via Server Actions.

JJS

Written by Jatin Jain Saraf · Senior Software Engineer

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

Q: What is the main reason a singleton pattern is necessary for the PrismaClient in a Next.js application during development?

To prevent the database from running out of connections due to Next.js module hot-reloading creating a new instance on every file change. — In Next.js development, the module system is hot-reloaded on file changes. Without the singleton pattern, each hot reload creates a new `PrismaClient` instance with its own connection pool, which will rapidly exhaust database connections.

Q: When deploying a Next.js app to a serverless environment with Prisma, how should you handle database connection pooling?

Use connection poolers like PgBouncer, Neon's HTTP-based pooling, or Prisma Accelerate, because serverless functions don't share `globalThis` memory across invocations. — The singleton pattern only fixes development hot-reloads. For serverless production environments where each invocation is a separate Node process, an external or HTTP-based pooler like PgBouncer, Neon Serverless, or Prisma Accelerate is necessary to manage connection limits.

Q: In the recommended full mutation cycle within a Server Action, what is the best practice regarding cache invalidation?

Perform cache invalidation (`revalidateTag`) only after the database write has successfully completed. — You should only invalidate the cache after confirming the database commit has succeeded. If you invalidate beforehand and the database operation fails, the cache could serve out-of-sync or corrupted data.

Who this is for: Practitioners who need to connect a Next.js App Router application to a PostgreSQL database using Prisma. This module goes beyond "run prisma generate and it works" — it covers the connection pooling failure mode that takes down serverless applications at scale, the correct singleton pattern, and the full mutation cycle from Server Action to database to cache invalidation.

The Problem You Will Hit at Scale

Let me front-load the most important thing in this module, because it's the issue most engineers only discover in production.

In a serverless environment (Vercel Functions, AWS Lambda), each function invocation is an isolated process. Without connection pooling at the application level, each invocation creates a new database connection. At low traffic, this works fine. At scale:

100 concurrent requests → 100 new database connections opened
PostgreSQL's default max connections: 100
Result: PrismaClientInitializationError: Unable to start a transaction in the given time

The application starts returning 500 errors. The database is overwhelmed not by query load but by connection overhead.

The solution is connection pooling — a pool of pre-established connections that function invocations borrow and return, rather than opening and closing on every request. There are two levels at which this needs to be solved in a Next.js + Prisma application:

Application-level: The Prisma singleton pattern — ensures one PrismaClient instance is reused across hot-reloaded development sessions and within a single server process.
Infrastructure-level: PgBouncer, Neon connection pooling, or Prisma Accelerate — sits between your serverless functions and PostgreSQL, pooling connections externally.

Both are required. The singleton alone is insufficient for serverless.

Schema Setup

Install Prisma and initialise:

bash

npm install @prisma/client
npm install -D prisma
npx prisma init

This creates prisma/schema.prisma and a .env with DATABASE_URL. A basic schema for an application with posts and users:

prisma

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id           String   @id @default(cuid())
  email        String   @unique
  name         String?
  passwordHash String?
  role         String   @default("viewer")
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  posts        Post[]
}

model Post {
  id          String   @id @default(cuid())
  title       String
  content     String
  published   Boolean  @default(false)
  publishedAt DateTime?
  authorId    String
  author      User     @relation(fields: [authorId], references: [id])
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@index([authorId])
  @@index([published, publishedAt])
}

Generate the Prisma client and push the schema:

bash

npx prisma generate
npx prisma db push  # for development (no migration files)
# or
npx prisma migrate dev --name init  # for production (generates migration files)

The Singleton Pattern — Mandatory in Next.js

In Next.js development, the module system is hot-reloaded on file changes. Without the singleton pattern, each hot reload creates a new PrismaClient instance with its own connection pool — eventually exhausting database connections in development, and creating confusion about which instance is active.

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

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

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

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

What this does: In development, the first time the module is imported, globalForPrisma.prisma is undefined, so a new PrismaClient is created and stored on globalThis. On subsequent hot reloads, globalForPrisma.prisma already exists, so the same instance is reused. In production, hot reloading doesn't happen, so the globalThis trick isn't needed — the module is imported once.

import 'server-only' ensures this module is never accidentally imported in a Client Component, which would expose database credentials.

Connection Pooling for Serverless

The singleton solves the development hot-reload problem. It does not solve the serverless concurrency problem. In production on Vercel, each serverless function invocation is a separate Node.js process — globalThis doesn't persist between them.

There are three standard solutions:

Option 1: PgBouncer (Self-Hosted)

PgBouncer is a lightweight connection pooler that sits in front of PostgreSQL. Your application connects to PgBouncer; PgBouncer maintains a pool of connections to PostgreSQL.

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

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

prisma

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:

bash

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:

bash

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

tsx

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

tsx

// ❌ 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:

prisma

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

bash

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.

Knowledge Check

What is the main reason a singleton pattern is necessary for the PrismaClient in a Next.js application during development?

When deploying a Next.js app to a serverless environment with Prisma, how should you handle database connection pooling?

In the recommended full mutation cycle within a Server Action, what is the best practice regarding cache invalidation?

Test your knowledge with more question sets

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

PreviousModule P-4: Authentication with Auth.js (NextAuth v5)Next Module P-6: Middleware — Routing Logic at the Edge

Discussion

Join the discussion

Loading comments...