DEV Community

我妻良樹
我妻良樹

Posted on

Optimizing Connection Pools with PrismaClient Singleton Pattern in Next.js

#postgres #nextjs #performance #architecture

Overview

In our Next.js application, we encountered connection pool exhaustion issues during development hot reloads because each page was creating its own PrismaClient instance. This article explains how we resolved this problem by implementing a proper singleton pattern and optimizing connection pool settings for Neon Database.

Tech Stack

  • Next.js 14+ (App Router)
  • Prisma ORM (v5.x)
  • PostgreSQL (Neon Database)
  • TypeScript (v5.x)
  • Singleton Pattern
  • Edge Runtime Support

Background & Challenges

Problem Discovery

We were experiencing the following issues in our development environment:

// Each page was creating its own instance (BAD practice)
// app/dashboard/page.tsx
export default async function DashboardPage() {
  const prisma = new PrismaClient(); // ❌ Creates new instance every time

  const customers = await prisma.customer.findMany();
  // ...
}

// app/emails/[id]/page.tsx
export default async function EmailDetailPage() {
  const prisma = new PrismaClient(); // ❌ Creates new instance every time

  const email = await prisma.email.findUnique();
  // ...
}
Enter fullscreen mode Exit fullscreen mode

Problems encountered:

  1. New PrismaClient instances were created on every hot reload
  2. Old instances weren't properly disposed, leading to connection pool exhaustion
  3. Slow connections after idle periods
  4. Unnecessary connections in production environment

Error Messages 

Error: Can't reach database server at `xxx.xxx.xxx.xxx:5432`
Please make sure your database server is running at `xxx.xxx.xxx.xxx:5432`.

Prisma Client could not connect to the database.
Enter fullscreen mode Exit fullscreen mode

Performance Measurements

Connection time comparison:

  • Before singleton: Initial access 500ms, subsequent accesses 300-400ms
  • After singleton: Initial access 200ms, subsequent accesses 50-100ms

Implementation

1. Implementing the Singleton Pattern

We implemented a proper singleton pattern in packages/database/src/index.ts:

// packages/database/src/index.ts
import { PrismaClient } from '@prisma/client';

// Global variable type definition
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

// Create or retrieve PrismaClient instance
export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
    datasources: {
      db: {
        url: process.env.DATABASE_URL,
      },
    },
  });

// Save globally only in development (hot reload support)
if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

// Re-export types
export * from '@prisma/client';
Enter fullscreen mode Exit fullscreen mode

Key Points:

  1. Leveraging globalThis

    • globalThis persists even when variables are reset during Next.js hot reloads
    • Prevents unnecessary instance creation in development environment

  2. Environment-specific Handling

    • production: Creates new instance each time (serverless environment support)
    • development: Saves globally (hot reload support)

  3. Log Level Configuration

    • development: Outputs query logs for improved development efficiency
    • production: Error logs only for optimal production performance

2. Optimizing Connection Pool Settings

We added connection pool settings for Neon Database in .env:

# .env.example
# Connection pool settings for Neon Database
DATABASE_URL="postgresql://user:password@host/dbname?connection_limit=10&pool_timeout=10&connect_timeout=10"

# Parameter descriptions:
# - connection_limit: Maximum number of connections (Neon free plan allows up to 10)
# - pool_timeout: Timeout for acquiring connection from pool (seconds)
# - connect_timeout: Database connection timeout (seconds)
Enter fullscreen mode Exit fullscreen mode

Connection Count Design:

  • Neon free plan: Maximum 10 connections
  • Recommended setting: connection_limit=10
  • Production environment: Adjust based on number of application instances

3. Standardizing Usage Across Pages

We updated all 7 files to use the shared instance:

// Before (BAD)
import { PrismaClient } from '@prisma/client';

export default async function Page() {
  const prisma = new PrismaClient(); // ❌
  // ...
}

// After (GOOD)
import { prisma } from '@myapp/database'; // ✅

export default async function Page() {
  // Use shared instance directly
  const customers = await prisma.customer.findMany();
  // ...
}
Enter fullscreen mode Exit fullscreen mode

Modified Files:

  1. app/dashboard/page.tsx
  2. app/customers/page.tsx
  3. app/customers/[id]/page.tsx
  4. app/emails/[id]/page.tsx
  5. app/auth/signin/page.tsx
  6. app/layout.tsx
  7. app/api/attachments/[attachmentId]/download/route.ts

4. Explicitly Setting Next.js Cache Strategy

To ensure data consistency, we explicitly set dynamic rendering:

// app/dashboard/page.tsx
export const dynamic = 'force-dynamic'; // ✅ Disable caching

export default async function DashboardPage() {
  const customers = await prisma.customer.findMany();
  // Always fetch latest data
}
Enter fullscreen mode Exit fullscreen mode

Cache Strategy Options:

  • force-dynamic: Always use dynamic rendering (prioritize real-time data)
  • force-static: Static generation (prioritize performance)
  • auto: Next.js automatically determines (default)

Performance Improvement Results

Connection Time Improvements

Scenario Before After Improvement
Initial Access 500ms 200ms 60% faster
Subsequent Accesses 300-400ms 50-100ms 75% faster
After Hot Reload 600ms 150ms 75% faster

Connection Pool Status

Before:

Active connections: 15-20
Idle connections: 5-10
Total: 20-30 (significantly exceeding limit of 10)
→ Errors occurred
Enter fullscreen mode Exit fullscreen mode

After:

Active connections: 3-5
Idle connections: 5-7
Total: 8-12 (within limit)
→ Stable operation
Enter fullscreen mode Exit fullscreen mode

Memory Usage

  • Before: ~10MB PrismaClient per page
  • After: ~10MB shared instance (fixed)
  • Memory reduction: ~70-80MB (across 8 pages)

Edge Runtime Support

Constraints and Solutions for Edge Environment

Considerations when using Prisma in Next.js Edge Runtime:

// Use Prisma Adapter for Edge Runtime
import { PrismaClient } from '@prisma/client/edge';
import { withAccelerate } from '@prisma/extension-accelerate';

// Edge environment configuration
export const prismaEdge = new PrismaClient({
  datasourceUrl: process.env.DATABASE_URL
}).$extends(withAccelerate());

// Usage example (Edge API Route)
export const runtime = 'edge';

export async function GET() {
  const users = await prismaEdge.user.findMany({
    cacheStrategy: { ttl: 60 } // Leverage Accelerate caching
  });

  return Response.json(users);
}
Enter fullscreen mode Exit fullscreen mode

Edge Runtime Selection Criteria:

  • Global distribution needed → Edge Runtime
  • Complex database operations → Node.js Runtime
  • Real-time priority → Node.js Runtime
  • Scalability priority → Edge Runtime

Memory Leak Prevention

Proper $disconnect() Invocation 

// Implementing graceful shutdown
import { prisma } from '@myapp/database';

// Cleanup on process termination
async function gracefulShutdown() {
  console.log('Gracefully shutting down...');

  try {
    await prisma.$disconnect();
    console.log('Database connections closed');
  } catch (error) {
    console.error('Error during shutdown:', error);
  }

  process.exit(0);
}

// Signal handling
process.on('SIGTERM', gracefulShutdown);
process.on('SIGINT', gracefulShutdown);

// Proper handling within Next.js API Routes
export async function GET(request: Request) {
  try {
    const data = await prisma.user.findMany();
    return Response.json(data);
  } catch (error) {
    // Ensure connection cleanup even on errors
    if (error instanceof Error && error.message.includes('P2024')) {
      // When connection pool is full
      await prisma.$disconnect();
      await new Promise(resolve => setTimeout(resolve, 100));
      // Retry or return error
    }
    throw error;
  }
}
Enter fullscreen mode Exit fullscreen mode

Monitoring Memory Usage 

// Memory monitoring and alerts
setInterval(async () => {
  const memUsage = process.memoryUsage();

  // Display memory usage in MB
  const report = {
    rss: Math.round(memUsage.rss / 1024 / 1024),
    heapTotal: Math.round(memUsage.heapTotal / 1024 / 1024),
    heapUsed: Math.round(memUsage.heapUsed / 1024 / 1024),
    external: Math.round(memUsage.external / 1024 / 1024)
  };

  console.log('Memory usage:', report);

  // Alert if threshold exceeded
  if (report.heapUsed > 500) { // Over 500MB
    console.warn('High memory usage detected!');
    // Reset connections if necessary
    await prisma.$disconnect();
    await prisma.$connect();
  }
}, 60000); // Every 1 minute
Enter fullscreen mode Exit fullscreen mode

Connection Pool Monitoring

Leveraging Prisma.$metrics 

// packages/database/src/monitoring.ts
import { prisma } from './index';

export async function enableMetrics() {
  // Enable metrics
  await prisma.$metrics.json();
}

export async function getPoolMetrics() {
  const metrics = await prisma.$metrics.json();

  // Extract connection pool related metrics
  const poolMetrics = {
    activeConnections: 0,
    idleConnections: 0,
    waitingClients: 0,
    totalQueries: 0
  };

  metrics.counters.forEach(counter => {
    switch (counter.key) {
      case 'prisma_pool_connections_open':
        poolMetrics.activeConnections = counter.value;
        break;
      case 'prisma_pool_connections_idle':
        poolMetrics.idleConnections = counter.value;
        break;
      case 'prisma_pool_connections_busy':
        poolMetrics.waitingClients = counter.value;
        break;
      case 'prisma_client_queries_total':
        poolMetrics.totalQueries = counter.value;
        break;
    }
  });

  return poolMetrics;
}

// Periodic monitoring
export function startPoolMonitoring() {
  setInterval(async () => {
    const metrics = await getPoolMetrics();

    console.log('Connection Pool Status:', {
      ...metrics,
      timestamp: new Date().toISOString()
    });

    // Send to CloudWatch or Datadog
    await sendToMonitoring(metrics);

    // Check alert conditions
    if (metrics.activeConnections > 8) {
      await sendAlert({
        level: 'WARNING',
        message: `High connection count: ${metrics.activeConnections}/10`,
        metrics
      });
    }
  }, 30000); // Every 30 seconds
}
Enter fullscreen mode Exit fullscreen mode

Dashboard Example 

// app/api/admin/metrics/route.ts
import { NextResponse } from 'next/server';
import { getPoolMetrics } from '@myapp/database/monitoring';

export async function GET() {
  try {
    const metrics = await getPoolMetrics();
    const health = {
      status: metrics.activeConnections < 8 ? 'healthy' : 'warning',
      database: {
        connections: {
          active: metrics.activeConnections,
          idle: metrics.idleConnections,
          total: metrics.activeConnections + metrics.idleConnections,
          limit: 10
        },
        queries: {
          total: metrics.totalQueries,
          qps: calculateQPS(metrics.totalQueries)
        }
      },
      timestamp: new Date().toISOString()
    };

    return NextResponse.json(health);
  } catch (error) {
    return NextResponse.json(
      { error: 'Failed to fetch metrics' },
      { status: 500 }
    );
  }
}
Enter fullscreen mode Exit fullscreen mode

Transaction Management Considerations

Proper Transaction Handling 

// Connection management within transactions
export async function transferCredits(
  fromUserId: string,
  toUserId: string,
  amount: number
) {
  // Using Interactive Transaction
  return await prisma.$transaction(async (tx) => {
    // 1. Check sender's balance
    const sender = await tx.user.findUnique({
      where: { id: fromUserId },
      select: { credits: true }
    });

    if (!sender || sender.credits < amount) {
      throw new Error('Insufficient credits');
    }

    // 2. Deduct from sender
    await tx.user.update({
      where: { id: fromUserId },
      data: { credits: { decrement: amount } }
    });

    // 3. Add to recipient
    await tx.user.update({
      where: { id: toUserId },
      data: { credits: { increment: amount } }
    });

    // 4. Record transaction log
    const log = await tx.transactionLog.create({
      data: {
        fromUserId,
        toUserId,
        amount,
        timestamp: new Date()
      }
    });

    return log;
  }, {
    maxWait: 5000, // Maximum wait time before transaction starts
    timeout: 10000, // Transaction timeout
    isolationLevel: 'Serializable' // Isolation level
  });
}

// With error handling
export async function safeTransfer(
  fromUserId: string,
  toUserId: string,
  amount: number
) {
  let retries = 3;

  while (retries > 0) {
    try {
      return await transferCredits(fromUserId, toUserId, amount);
    } catch (error) {
      if (error instanceof Error) {
        // Retry on deadlock or timeout
        if (error.message.includes('P2034') || error.message.includes('P2024')) {
          retries--;
          console.log(`Retrying transaction... (${retries} attempts left)`);
          await new Promise(resolve => setTimeout(resolve, 1000));
        } else {
          throw error; // Throw other errors immediately
        }
      }
    }
  }

  throw new Error('Transaction failed after maximum retries');
}
Enter fullscreen mode Exit fullscreen mode

Handling Test Environments

Mocking with Jest 

// __tests__/setup.ts
import { PrismaClient } from '@prisma/client';
import { mockDeep, mockReset, DeepMockProxy } from 'jest-mock-extended';

// Create Prisma Client mock
export const prismaMock = mockDeep<PrismaClient>() as unknown as DeepMockProxy<PrismaClient>;

// jest.setup.js
jest.mock('@myapp/database', () => ({
  __esModule: true,
  prisma: prismaMock
}));

// Test file example
import { prismaMock } from './setup';
import { getUserById } from '../services/user';

describe('User Service', () => {
  beforeEach(() => {
    mockReset(prismaMock);
  });

  it('should get user by id', async () => {
    const mockUser = {
      id: '1',
      name: 'Test User',
      email: 'test@example.com'
    };

    prismaMock.user.findUnique.mockResolvedValue(mockUser);

    const user = await getUserById('1');

    expect(user).toEqual(mockUser);
    expect(prismaMock.user.findUnique).toHaveBeenCalledWith({
      where: { id: '1' }
    });
  });
});
Enter fullscreen mode Exit fullscreen mode

Integration Test Setup 

// __tests__/integration/setup.ts
import { PrismaClient } from '@prisma/client';
import { execSync } from 'child_process';

// Test database URL
const TEST_DATABASE_URL = process.env.TEST_DATABASE_URL || 'postgresql://test:test@localhost:5432/test';

// Test Prisma client
export const testPrisma = new PrismaClient({
  datasources: {
    db: {
      url: TEST_DATABASE_URL
    }
  }
});

// Pre-test setup
export async function setupTestDatabase() {
  // Run migrations
  execSync('npx prisma migrate deploy', {
    env: { ...process.env, DATABASE_URL: TEST_DATABASE_URL }
  });

  // Insert seed data
  await testPrisma.user.createMany({
    data: [
      { name: 'Alice', email: 'alice@test.com' },
      { name: 'Bob', email: 'bob@test.com' }
    ]
  });
}

// Post-test cleanup
export async function teardownTestDatabase() {
  // Delete data (considering foreign key constraints)
  await testPrisma.transactionLog.deleteMany();
  await testPrisma.user.deleteMany();

  // Close connection
  await testPrisma.$disconnect();
}
Enter fullscreen mode Exit fullscreen mode

Operational Considerations

Deployment Considerations 

# .github/workflows/deploy.yml
name: Deploy with Zero Downtime

on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Run migrations
        run: |
          # Blue-Green deployment support
          npx prisma migrate deploy
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

      - name: Health check before deployment
        run: |
          # Check connection pool status
          curl https://api.example.com/api/admin/metrics

      - name: Deploy to production
        run: |
          # Rolling deployment
          kubectl set image deployment/api api=myapp:${{ github.sha }}
          kubectl rollout status deployment/api

      - name: Post-deployment verification
        run: |
          # Check connection pool after deployment
          sleep 30
          curl https://api.example.com/api/admin/metrics
Enter fullscreen mode Exit fullscreen mode

Monitoring and Alert Settings 

// monitoring/alerts.ts
export const alertRules = [
  {
    name: 'high_connection_count',
    condition: 'connections.active > 8',
    severity: 'warning',
    action: 'notify_slack'
  },
  {
    name: 'connection_pool_exhausted',
    condition: 'connections.active >= 10',
    severity: 'critical',
    action: 'page_oncall'
  },
  {
    name: 'slow_queries',
    condition: 'query.duration > 1000',
    severity: 'warning',
    action: 'log_to_dashboard'
  }
];
Enter fullscreen mode Exit fullscreen mode

Troubleshooting

Common Issues and Solutions

1. "Too many connections" Error

Symptom: Database connection count reaches limit

Solution:

// Connection reset script
async function resetConnections() {
  console.log('Resetting all database connections...');

  // Disconnect all existing connections
  await prisma.$disconnect();

  // Wait briefly
  await new Promise(resolve => setTimeout(resolve, 1000));

  // Establish new connection
  await prisma.$connect();

  console.log('Connections reset successfully');
}

// Integrate into error handler
app.use(async (error, req, res, next) => {
  if (error.code === 'P2024') {
    await resetConnections();
    // Retry or return error response
  }
  next(error);
});
Enter fullscreen mode Exit fullscreen mode

2. Memory Leak During Hot Reload

Symptom: Memory usage continues increasing in development environment

Solution:

// Cleanup for development environment
if (process.env.NODE_ENV === 'development') {
  // HMR (Hot Module Replacement) cleanup
  if (module.hot) {
    module.hot.dispose(async () => {
      await prisma.$disconnect();
    });
  }
}
Enter fullscreen mode Exit fullscreen mode

3. Connection Errors in Serverless Environment

Symptom: Intermittent connection errors in Lambda functions

Solution:

// Connection management for Serverless
let prisma: PrismaClient;

export function getPrismaClient() {
  if (!prisma) {
    prisma = new PrismaClient({
      datasources: {
        db: {
          url: process.env.DATABASE_URL + '?connection_limit=1' // Serverless uses 1 connection
        }
      }
    });
  }
  return prisma;
}

// Lambda handler
export const handler = async (event: any) => {
  const client = getPrismaClient();

  try {
    // Execute query
    const result = await client.user.findMany();
    return { statusCode: 200, body: JSON.stringify(result) };
  } finally {
    // Maintain connection for cold start optimization in Lambda
    // await client.$disconnect(); // Don't disconnect
  }
};
Enter fullscreen mode Exit fullscreen mode

Technical Details

How globalThis Works

In Next.js development environment, modules are reloaded on file changes:

// Hot reload behavior

// First execution
const prisma = new PrismaClient(); // Create instance A
globalForPrisma.prisma = prisma;  // Save globally

// File change (hot reload)

// Second execution
const prisma = globalForPrisma.prisma ?? new PrismaClient();
// → Reuse instance A saved globally
// → No new instance created
Enter fullscreen mode Exit fullscreen mode

Behavior in Production Environment

In production, we consider serverless function characteristics:

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}
// Don't save globally in production
// → Each Lambda/Edge function has its own instance
Enter fullscreen mode Exit fullscreen mode

Reason:

  • Serverless environments have independent memory space per function
  • Global variables are not shared
  • More efficient for each function to manage its own PrismaClient

Connection Pool Lifecycle 

// Connection pool management
const prisma = new PrismaClient(); // Create pool (for 10 connections)

// 1. When executing query
await prisma.customer.findMany();
// → Acquire 1 connection from pool
// → Execute query
// → Return connection to pool

// 2. During idle time
// → Connections remain maintained waiting for next query
// → Timeout after connect_timeout

// 3. On application termination
await prisma.$disconnect();
// → Properly close all connections
Enter fullscreen mode Exit fullscreen mode

Lessons Learned

Unexpected Pitfalls

  1. Environment-specific Behavior in Next.js

    • development: Modules reset on hot reload
    • production: Independent per serverless function
    • Design must accommodate both scenarios

  2. PrismaClient Initialization Cost

    • Instance creation takes 100-200ms
    • Schema loading and validation
    • Connection pool initialization

  3. Neon Database Connection Limits

    • Free plan: Maximum 10 connections
    • Paid plans: Scalable
    • Must consider during application design

Useful Knowledge for the Future

  1. Singleton Pattern Template 
   // Generic singleton pattern
   const globalForX = globalThis as unknown as {
     x: SomeClass | undefined;
   };

   export const x =
     globalForX.x ??
     new SomeClass(config);

   if (process.env.NODE_ENV !== 'production') {
     globalForX.x = x;
   }
Enter fullscreen mode Exit fullscreen mode
  1. Connection Pool Setting Best Practices 
   connection_limit = Total connections / Number of app instances
   pool_timeout = Query timeout + margin
   connect_timeout = Network latency + margin
Enter fullscreen mode Exit fullscreen mode
  1. Gradual Migration Strategy 
   Phase 1: Create shared instance
   Phase 2: Migrate one file at a time and test
   Phase 3: Measure performance after migrating all files
   Phase 4: Optimize connection pool settings
Enter fullscreen mode Exit fullscreen mode

Better Implementation Discovered

Before (creating in each page):

// page1.tsx
const prisma = new PrismaClient();

// page2.tsx
const prisma = new PrismaClient();

// page3.tsx
const prisma = new PrismaClient();
// → 3 instances, 30 connections
Enter fullscreen mode Exit fullscreen mode

After (shared instance):

// database/index.ts
export const prisma = new PrismaClient();

// page1.tsx
import { prisma } from '@myapp/database';

// page2.tsx
import { prisma } from '@myapp/database';

// page3.tsx
import { prisma } from '@myapp/database';
// → 1 instance, 10 connections
Enter fullscreen mode Exit fullscreen mode

Conclusion

Implementing the PrismaClient singleton pattern was a highly effective approach for database connection optimization in Next.js environments. Key takeaways from this implementation:

  • Environment-appropriate Implementation: Consider different behaviors in development/production
  • Leveraging globalThis: Key point for hot reload support
  • Connection Pool Settings: Optimization according to database plan

Particularly with Next.js App Router, where Server Components execute on each request, implementing the singleton pattern is extremely important. This enabled significant improvements in performance, memory usage, and connection count.

I recommend implementing the singleton pattern early when using Prisma with Next.js. This can prevent issues during hot reloads in development and connection pool exhaustion in production.

References

The code presented in this article is simplified from actual production code. In real implementations, additional considerations such as error handling and security checks are required.

Related Technologies: Next.js, Prisma ORM, PostgreSQL, Neon Database, TypeScript, Singleton Pattern, Connection Pool Optimization, Performance Tuning

Author: Development Team

Top comments (0)