Connection Pooling | Bootspring Docs

Patterns for managing database connections efficiently in serverless and high-traffic environments.

Overview#

Connection pooling optimizes database access:

Reduced connection overhead
Better resource utilization
Serverless-compatible
Improved scalability

Prerequisites:

Prisma ORM setup
PgBouncer or Neon for pooling (optional)

Implementation#

Prisma Connection Pooling#

// lib/db.ts
import { PrismaClient } from '@prisma/client'

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

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

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma
}

Connection URL with Pool Settings#

# .env
# PostgreSQL connection with pool settings
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?connection_limit=20&pool_timeout=30"

# Using PgBouncer (recommended for serverless)
DATABASE_URL="postgresql://user:password@pgbouncer:6432/mydb?pgbouncer=true"

# Direct URL for migrations (bypasses PgBouncer)
DIRECT_URL="postgresql://user:password@localhost:5432/mydb"

// prisma/schema.prisma
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

Serverless Connection Handling#

// lib/db/serverless.ts
import { PrismaClient } from '@prisma/client'
import { Pool } from '@neondatabase/serverless'

// For Neon serverless driver
const pool = new Pool({ connectionString: process.env.DATABASE_URL })

// For edge functions
export async function queryWithServerlessPool<T>(
  query: string,
  params?: any[]
): Promise<T[]> {
  const client = await pool.connect()
  try {
    const result = await client.query(query, params)
    return result.rows as T[]
  } finally {
    client.release()
  }
}

// Prisma with connection limit for serverless
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: `${process.env.DATABASE_URL}?connection_limit=1`
    }
  }
})

export { prisma }

PgBouncer Configuration#

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Pool settings
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5

# Timeouts
server_connect_timeout = 15
server_idle_timeout = 600
server_lifetime = 3600

Connection Lifecycle Management#

// lib/db/lifecycle.ts
import { PrismaClient } from '@prisma/client'

class DatabaseManager {
  private static instance: PrismaClient | null = null
  private static connectionCount = 0

  static async getClient(): Promise<PrismaClient> {
    if (!this.instance) {
      this.instance = new PrismaClient({
        log: ['error', 'warn']
      })
      await this.instance.$connect()
      console.log('Database connected')
    }
    this.connectionCount++
    return this.instance
  }

  static async releaseClient(): Promise<void> {
    this.connectionCount--
    if (this.connectionCount <= 0 && this.instance) {
      // Optionally disconnect when no clients are using it
      // await this.instance.$disconnect()
      // this.instance = null
    }
  }

  static async disconnect(): Promise<void> {
    if (this.instance) {
      await this.instance.$disconnect()
      this.instance = null
      this.connectionCount = 0
      console.log('Database disconnected')
    }
  }

  static async healthCheck(): Promise<boolean> {
    try {
      const client = await this.getClient()
      await client.$queryRaw`SELECT 1`
      return true
    } catch {
      return false
    }
  }
}

// Graceful shutdown
process.on('SIGINT', async () => {
  await DatabaseManager.disconnect()
  process.exit(0)
})

process.on('SIGTERM', async () => {
  await DatabaseManager.disconnect()
  process.exit(0)
})

export { DatabaseManager }

Connection Pool Monitoring#

// lib/db/monitoring.ts
import { PrismaClient } from '@prisma/client'

interface PoolMetrics {
  activeConnections: number
  idleConnections: number
  waitingRequests: number
  totalQueries: number
}

class MonitoredPrismaClient extends PrismaClient {
  private metrics: PoolMetrics = {
    activeConnections: 0,
    idleConnections: 0,
    waitingRequests: 0,
    totalQueries: 0
  }

  constructor() {
    super({
      log: [
        { level: 'query', emit: 'event' },
        { level: 'error', emit: 'event' }
      ]
    })

    // Track queries
    this.$on('query' as never, (e: any) => {
      this.metrics.totalQueries++
      console.log(`Query: ${e.query} - Duration: ${e.duration}ms`)
    })

    this.$on('error' as never, (e: any) => {
      console.error('Database error:', e)
    })
  }

  getMetrics(): PoolMetrics {
    return { ...this.metrics }
  }

  async getPoolStatus(): Promise<object> {
    const result = await this.$queryRaw<any[]>`
      SELECT
        count(*) FILTER (WHERE state = 'active') as active,
        count(*) FILTER (WHERE state = 'idle') as idle,
        count(*) as total
      FROM pg_stat_activity
      WHERE datname = current_database()
    `
    return result[0]
  }
}

export const monitoredPrisma = new MonitoredPrismaClient()

Health Check Endpoint#

// app/api/health/db/route.ts
import { NextResponse } from 'next/server'
import { monitoredPrisma } from '@/lib/db/monitoring'

export async function GET() {
  try {
    const status = await monitoredPrisma.getPoolStatus()
    const metrics = monitoredPrisma.getMetrics()

    return NextResponse.json({
      healthy: true,
      poolStatus: status,
      metrics
    })
  } catch (error) {
    return NextResponse.json(
      { healthy: false, error: 'Database connection failed' },
      { status: 503 }
    )
  }
}

Retry Logic with Backoff#

// lib/db/retry.ts
import { PrismaClient, Prisma } from '@prisma/client'

interface RetryOptions {
  maxRetries?: number
  baseDelay?: number
  maxDelay?: number
}

export async function withRetry<T>(
  operation: () => Promise<T>,
  options: RetryOptions = {}
): Promise<T> {
  const { maxRetries = 3, baseDelay = 100, maxDelay = 5000 } = options

  let lastError: Error | null = null

  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      return await operation()
    } catch (error) {
      lastError = error as Error

      // Check if error is retryable
      if (error instanceof Prisma.PrismaClientKnownRequestError) {
        // Connection errors
        if (['P1001', 'P1002', 'P1008', 'P1017'].includes(error.code)) {
          const delay = Math.min(baseDelay * Math.pow(2, attempt), maxDelay)
          console.log(`Retrying after ${delay}ms (attempt ${attempt + 1}/${maxRetries})`)
          await new Promise(resolve => setTimeout(resolve, delay))
          continue
        }
      }

      // Non-retryable error
      throw error
    }
  }

  throw lastError
}

// Usage
const users = await withRetry(() => prisma.user.findMany(), {
  maxRetries: 3,
  baseDelay: 100
})

Edge-Compatible Setup#

// lib/db/edge.ts
import { neon } from '@neondatabase/serverless'

// For Edge Runtime (no persistent connections)
const sql = neon(process.env.DATABASE_URL!)

export async function queryEdge<T>(
  query: string,
  params?: any[]
): Promise<T[]> {
  const result = await sql(query, params)
  return result as T[]
}

// Usage in Edge API route
export const runtime = 'edge'

export async function GET() {
  const users = await queryEdge<User>('SELECT * FROM users LIMIT 10')
  return Response.json(users)
}

Best Practices#

Use singleton pattern - Prevent connection exhaustion in development
Configure connection limits - Match pool size to database limits
Use PgBouncer for serverless - Efficient connection reuse
Implement health checks - Monitor connection pool status
Handle connection errors - Implement retry logic with backoff

Prisma - Prisma setup and basics
Transactions - Transaction handling
Queries - Query optimization