Prisma 6 Next.js 16 — P2024 Connection Pool Exhaustion

// src/lib/prisma.ts — Prisma 6 + Next.js 16 singleton
import { PrismaClient } from '@prisma/client';
import { withAccelerate } from '@prisma/extension-accelerate';
// import { PrismaNeon } from '@prisma/adapter-neon'; // for edge

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

function createClient() {
  // For edge: const adapter = new PrismaNeon({ connectionString: process.env.DATABASE_URL });
  const client = new PrismaClient({
    // adapter,
    datasourceUrl: process.env.DATABASE_URL, // add ?connection_limit=1&pool_timeout=0
    log: process.env.NODE_ENV === 'development' 
      ? [{ emit: 'event', level: 'query' }, { emit: 'stdout', level: 'error' }]
      : ['error'],
  }).$extends(withAccelerate()); // global pooling
  
  return client;
}

export const prisma = globalForPrisma.prisma ?? createClient();
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

// Slow query logging (>50ms)
if (process.env.NODE_ENV === 'development') {
  prisma.$on('query', (e) => {
    if (e.duration > 50) console.warn(`[Prisma] ${e.duration}ms | ${e.query.substring(0,120)}`);
  });
}

export async function dbHealthCheck() {
  try { await prisma.$queryRaw`SELECT 1`; return true; } catch { return false; }
}

import { prisma } from '@/lib/prisma';

// BAD: N+1
export async function bad() {
  const posts = await prisma.post.findMany();
  return Promise.all(posts.map(p => prisma.user.findUnique({ where: { id: p.authorId } })));
}

// GOOD: include
 export async function good() {
  return prisma.post.findMany({
    include: { author: { select: { id: true, name: true } } },
    where: { published: true },
    take: 20
  });
}

// GOOD: batch with $transaction
export async function stats() {
  const [posts, users] = await prisma.$transaction([
    prisma.post.count(),
    prisma.user.count()
  ]);
  return { posts, users };
}

import { prisma } from '@/lib/prisma';

export async function transferFunds(fromId: string, toId: string, amount: number) {
  // Retry wrapper for Serializable
  for (let i = 0; i < 3; i++) {
    try {
      return await prisma.$transaction(async (tx) => {
        const from = await tx.account.findUniqueOrThrow({ where: { id: fromId } });
        if (from.balance < amount) throw new Error('Insufficient');
        await tx.account.update({ where: { id: fromId }, data: { balance: { decrement: amount } } });
        await tx.account.update({ where: { id: toId }, data: { balance: { increment: amount } } });
        return tx.transaction.create({ data: { fromAccountId: fromId, toAccountId: toId, amount } });
      }, { maxWait: 5000, timeout: 10000, isolationLevel: 'Serializable' });
    } catch (e: any) {
      if (e.code === 'P2034' && i < 2) continue; // serialization failure, retry
      throw e;
    }
  }
}

// Atomic create with tags
export async function createPostWithTags(data: any, tagNames: string[]) {
  return prisma.$transaction(async (tx) => {
    const post = await tx.post.create({ data });
    const tags = await Promise.all(tagNames.map(n => tx.tag.upsert({ where: { name: n }, create: { name: n }, update: {} })));
    await tx.post.update({ where: { id: post.id }, data: { tags: { connect: tags.map(t => ({ id: t.id })) } } });
    return { post, tags };
  });
}

import { Prisma } from '@prisma/client';
import { prisma } from './prisma';

export const softDelete = Prisma.defineExtension(client => client.$extends({
  query: {
    $allModels: {
      async findMany({ args, query }) { args.where = { ...args.where, deletedAt: null }; return query(args); },
      async findFirst({ args, query }) { args.where = { ...args.where, deletedAt: null }; return query(args); },
      async delete({ model, args, query }) { return (client as any)[model].update({ where: args.where, data: { deletedAt: new Date() } }); },
    }
  }
}));

export const createTenant = (tenantId: string) => Prisma.defineExtension(client => client.$extends({
  query: {
    $allModels: {
      async findMany({ args, query }) { args.where = { ...args.where, tenantId }; return query(args); },
      async findFirst({ args, query }) { args.where = { ...args.where, tenantId }; return query(args); },
      async findUnique({ args, query }) { args.where = { ...args.where, tenantId }; return query(args); },
      async create({ args, query }) { args.data = { ...args.data, tenantId }; return query(args); },
      async createMany({ args, query }) { args.data = Array.isArray(args.data) ? args.data.map(d => ({ ...d, tenantId })) : args.data; return query(args); },
      async update({ args, query }) { args.where = { ...args.where, tenantId }; return query(args); },
      async updateMany({ args, query }) { args.where = { ...args.where, tenantId }; return query(args); },
      async delete({ args, query }) { args.where = { ...args.where, tenantId }; return query(args); },
      async deleteMany({ args, query }) { args.where = { ...args.where, tenantId }; return query(args); },
      async upsert({ args, query }) { args.where = { ...args.where, tenantId }; args.create = { ...args.create, tenantId }; return query(args); },
      async count({ args, query }) { args.where = { ...args.where, tenantId }; return query(args); },
    }
  }
}));

export const prismaWithExtensions = prisma.$extends(softDelete);

import { cache as reactCache } from 'react';
import { cache } from 'next/cache';
import { revalidateTag } from 'next/cache';
import { prisma } from './prisma';

export const getPost = reactCache(async (slug: string) => 
  prisma.post.findUnique({ where: { slug }, select: { id: true, title: true, content: true } })
);

export const getCachedPosts = cache(
  async (page = 1) => prisma.post.findMany({ where: { published: true }, take: 20, skip: (page-1)*20 }),
  ['posts'],
  { tags: ['posts'], revalidate: 60 }
);

export async function createPost(data: any) {
  const post = await prisma.post.create({ data });
  revalidateTag('posts');
  return post;
}

generator client {
  provider = "prisma-client-js"
  output   = "../../node_modules/.prisma/client" // monorepo
}

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

enum PostStatus { DRAFT PUBLISHED ARCHIVED }

model Post {
  id          String     @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  title       String
  slug        String     @unique
  status      PostStatus @default(DRAFT)
  authorId    String
  author      User       @relation(fields: [authorId], references: [id], onDelete: Cascade)
  publishedAt DateTime?
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt
  deletedAt   DateTime?
  @@index([authorId])
  @@index([status, publishedAt])
  @@map("posts")
}

model User {
  id        String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  email     String @unique
  posts     Post[]
  createdAt DateTime @default(now())
  @@index([email])
  @@map("users")
}

// io.thecodeforge — javascript tutorial

// NEXT.JS MIDDLEWARE — handles auth, never touches Prisma
import { NextResponse } from 'next/server';
import { verifySession } from '@/lib/auth';

export function middleware(request) {
  const token = request.cookies.get('session_token');
  if (!token || !verifySession(token.value)) {
    return NextResponse.redirect(new URL('/login', request.url));
  }
  return NextResponse.next();
}

export const config = {
  matcher: ['/dashboard/:path*', '/api/protected/:path*']
};

// PRISMA EXTENDS — handles data concerns, never auth
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient().$extends({
  query: {
    user: {
      async findMany({ args, query }) {
        args.where = { ...args.where, deletedAt: null };
        return query(args);
      }
    }
  }
});

// io.thecodeforge — javascript tutorial

// BAD: raw query leaking types everywhere
const users = await prisma.$queryRawUnsafe(`
  SELECT id, email, profile->>'bio' as bio
  FROM users
  WHERE metadata->>'role' = 'admin'
`);
// TypeScript thinks this is unknown[]. Good luck.

// GOOD: typed wrapper with a single source of truth
import { Prisma } from '@prisma/client';

type AdminWithBio = {
  id: string;
  email: string;
  bio: string | null;
};

export async function getAdminUsersWithBio(): Promise<AdminWithBio[]> {
  return prisma.$queryRaw<
    AdminWithBio[]
  >(
    Prisma.sql`
      SELECT id, email, profile->>'bio' as bio
      FROM users
      WHERE metadata->>'role' = 'admin'
    `
  );
}

// Now all callers get typed results. Schema changes break here first.

// io.thecodeforge — javascript tutorial

import { PrismaClient } from '@prisma/client'

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

const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
})

globalForPrisma.prisma = prisma

export default prisma

// io.thecodeforge — javascript tutorial

{
  "scripts": {
    "migrate:dev": "prisma migrate dev --name $npm_config_name",
    "migrate:review": "prisma migrate diff --from-schema-datamodel prisma/schema.prisma --to-schema-datasource prisma/schema.prisma",
    "migrate:deploy": "prisma migrate deploy",
    "migrate:generate-sql": "prisma migrate dev --create-only --name $npm_config_name"
  }
}