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

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:

1. Application-level: The Prisma singleton pattern — ensures one PrismaClient instance is reused across hot-reloaded development sessions and within a single server process.
2. 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.

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

ts
// 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 uses HTTP for individual queries (no persistent TCP connection), which is what makes it work well in serverless environments where TCP connections are expensive to establish per invocation.

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

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

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

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

ts
// 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-5 covers Middleware — the edge layer that runs before every request. You've already seen it in action for auth redirects in P-3. P-5 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-6 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.