Skip to content

AI Prompt: Forma3D.Connect — Phase 5o: Database Connection Pool Configuration

Purpose: This prompt instructs an AI to configure Prisma database connection pooling for optimal performance
Estimated Effort: 4-6 hours
Prerequisites: Phase 5n completed (Rate Limiting)
Output: Configured connection pool with monitoring and environment-specific tuning
Status: 🟡 PENDING

🎯 Mission

You are continuing development of Forma3D.Connect, building on the Phase 5n foundation. Your task is to implement Phase 5o: Database Connection Pool Configuration — specifically addressing TD-013 (Database Connection Pool Configuration) from the technical debt register.

Why This Matters:

Prisma connection pool using defaults can cause:

  1. Connection Exhaustion: Too few connections under load
  2. Resource Waste: Too many idle connections
  3. Timeout Errors: Default timeouts may not suit workload
  4. Hard to Debug: No visibility into pool state

Phase 5o delivers:

  • Explicit connection pool configuration
  • Environment-specific pool sizes
  • Connection timeout configuration
  • Pool monitoring and metrics

📋 Context: Technical Debt Item

TD-013: Database Connection Pool Configuration

Attribute Value
Type Infrastructure Debt
Priority Medium
Location apps/api/src/database/prisma.service.ts
Interest Rate Medium
Principal (Effort) 4-6 hours

Current Problem

Prisma connection pool uses defaults with no explicit configuration for: - Pool size - Connection timeout - Idle timeout

🛠️ Implementation Phases

Phase 1: Add Connection Pool Configuration Variables (1 hour)

Priority: Critical | Impact: High | Dependencies: None

1. Update Configuration Schema

Update apps/api/src/config/configuration.ts:

// === Database Connection Pool ===
@Transform(({ value }) => parseInt(value, 10))
@IsNumber()
@Min(1)
@Max(100)
DATABASE_POOL_SIZE: number = 10;

@Transform(({ value }) => parseInt(value, 10))
@IsNumber()
@Min(1000)
DATABASE_CONNECT_TIMEOUT_MS: number = 5000;

@Transform(({ value }) => parseInt(value, 10))
@IsNumber()
@Min(1000)
DATABASE_POOL_TIMEOUT_MS: number = 10000;

@Transform(({ value }) => parseInt(value, 10))
@IsNumber()
@Min(0)
DATABASE_IDLE_TIMEOUT_MS: number = 300000; // 5 minutes

2. Update AppConfigService

Add to apps/api/src/config/config.service.ts:

export interface DatabaseConfig {
  url: string;
  poolSize: number;
  connectTimeoutMs: number;
  poolTimeoutMs: number;
  idleTimeoutMs: number;
}

// In AppConfigService class:
get database(): DatabaseConfig {
  return {
    url: this.configService.getOrThrow<string>('DATABASE_URL'),
    poolSize: this.configService.get<number>('DATABASE_POOL_SIZE', 10),
    connectTimeoutMs: this.configService.get<number>('DATABASE_CONNECT_TIMEOUT_MS', 5000),
    poolTimeoutMs: this.configService.get<number>('DATABASE_POOL_TIMEOUT_MS', 10000),
    idleTimeoutMs: this.configService.get<number>('DATABASE_IDLE_TIMEOUT_MS', 300000),
  };
}

Phase 2: Configure Prisma with Pool Settings (2 hours)

Priority: High | Impact: High | Dependencies: Phase 1

1. Update DATABASE_URL with Pool Parameters

Connection pool settings are passed via URL query parameters in Prisma:

Before: 

DATABASE_URL=postgresql://user:password@localhost:5432/forma3d_connect

After: 

DATABASE_URL=postgresql://user:password@localhost:5432/forma3d_connect?connection_limit=10&connect_timeout=5&pool_timeout=10

However, for dynamic configuration, we'll build the URL programmatically.

2. Create Enhanced Prisma Service

Update apps/api/src/database/prisma.service.ts:

import { Injectable, OnModuleInit, OnModuleDestroy, Logger } from '@nestjs/common';
import { PrismaClient, Prisma } from '@prisma/client';
import { AppConfigService } from '../config/config.service';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  private readonly logger = new Logger(PrismaService.name);

  constructor(private readonly appConfig: AppConfigService) {
    const dbConfig = appConfig.database;

    // Build connection URL with pool parameters
    const url = new URL(dbConfig.url);
    url.searchParams.set('connection_limit', dbConfig.poolSize.toString());
    url.searchParams.set('connect_timeout', Math.floor(dbConfig.connectTimeoutMs / 1000).toString());
    url.searchParams.set('pool_timeout', Math.floor(dbConfig.poolTimeoutMs / 1000).toString());

    super({
      datasources: {
        db: {
          url: url.toString(),
        },
      },
      log: appConfig.isProduction
        ? ['error', 'warn']
        : ['query', 'info', 'warn', 'error'],
    });

    this.logger.log(`Database pool configured: size=${dbConfig.poolSize}, connectTimeout=${dbConfig.connectTimeoutMs}ms`);
  }

  async onModuleInit(): Promise<void> {
    this.logger.log('Connecting to database...');
    await this.$connect();
    this.logger.log('Database connected successfully');
  }

  async onModuleDestroy(): Promise<void> {
    this.logger.log('Disconnecting from database...');
    await this.$disconnect();
    this.logger.log('Database disconnected');
  }

  /**
   * Health check for database connectivity
   */
  async isHealthy(): Promise<boolean> {
    try {
      await this.$queryRaw`SELECT 1`;
      return true;
    } catch (error) {
      this.logger.error('Database health check failed', error);
      return false;
    }
  }
}

3. Alternative: Using Prisma Data Proxy (for Serverless)

If using serverless environments, consider Prisma Data Proxy:

// For serverless deployments
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
});

Phase 3: Add Pool Monitoring (1 hour)

Priority: Medium | Impact: Medium | Dependencies: Phase 2

1. Create Pool Metrics Service

Create apps/api/src/database/prisma-metrics.service.ts:

import { Injectable, Logger } from '@nestjs/common';
import { PrismaService } from './prisma.service';
import { Interval } from '@nestjs/schedule';

interface PoolMetrics {
  timestamp: Date;
  activeConnections: number;
  idleConnections: number;
  waitingRequests: number;
}

@Injectable()
export class PrismaMetricsService {
  private readonly logger = new Logger(PrismaMetricsService.name);
  private metrics: PoolMetrics[] = [];
  private readonly maxMetricsHistory = 60; // Keep last 60 samples

  constructor(private readonly prisma: PrismaService) {}

  /**
   * Collect pool metrics every 10 seconds
   * Note: Prisma doesn't expose pool metrics directly,
   * so we measure query latency as a proxy
   */
  @Interval(10000)
  async collectMetrics(): Promise<void> {
    const start = Date.now();

    try {
      await this.prisma.$queryRaw`SELECT 1`;
      const latency = Date.now() - start;

      // High latency might indicate pool exhaustion
      if (latency > 100) {
        this.logger.warn(`Database query latency: ${latency}ms`);
      }

      // Store metric
      this.metrics.push({
        timestamp: new Date(),
        activeConnections: 0, // Prisma doesn't expose this
        idleConnections: 0,
        waitingRequests: 0,
      });

      // Trim old metrics
      if (this.metrics.length > this.maxMetricsHistory) {
        this.metrics.shift();
      }
    } catch (error) {
      this.logger.error('Failed to collect database metrics', error);
    }
  }

  getMetrics(): PoolMetrics[] {
    return [...this.metrics];
  }

  getLatestMetric(): PoolMetrics | null {
    return this.metrics[this.metrics.length - 1] ?? null;
  }
}

2. Add Metrics to Health Check

Update apps/api/src/health/health.controller.ts:

import { PrismaService } from '../database/prisma.service';

@Controller('health')
export class HealthController {
  constructor(private readonly prisma: PrismaService) {}

  @Get()
  async check() {
    const dbHealthy = await this.prisma.isHealthy();

    return {
      status: dbHealthy ? 'ok' : 'degraded',
      timestamp: new Date().toISOString(),
      checks: {
        database: dbHealthy ? 'ok' : 'error',
      },
    };
  }

  @Get('ready')
  async readiness() {
    const dbHealthy = await this.prisma.isHealthy();

    if (!dbHealthy) {
      throw new ServiceUnavailableException('Database not available');
    }

    return { status: 'ready' };
  }
}

Phase 4: Environment-Specific Configuration (1 hour)

Priority: Medium | Impact: Medium | Dependencies: Phase 1

1. Development Configuration

Create apps/api/.env.development:

# Development - smaller pool, verbose logging
DATABASE_POOL_SIZE=5
DATABASE_CONNECT_TIMEOUT_MS=10000
DATABASE_POOL_TIMEOUT_MS=30000
DATABASE_IDLE_TIMEOUT_MS=60000

2. Production Configuration

Document in apps/api/.env.example:

# === Database Connection Pool ===
# Adjust based on your database server's max_connections
# Formula: pool_size = (max_connections - reserved) / num_instances
DATABASE_POOL_SIZE=20
DATABASE_CONNECT_TIMEOUT_MS=5000
DATABASE_POOL_TIMEOUT_MS=10000
DATABASE_IDLE_TIMEOUT_MS=300000

3. Add Pool Sizing Guidance

Add comments to configuration:

/**
 * Pool Size Guidelines:
 * - Development: 5 connections
 * - Staging: 10 connections
 * - Production: 20-50 connections (depends on database max_connections)
 * 
 * Formula: pool_size = (db_max_connections - 10) / number_of_app_instances
 * 
 * Example: PostgreSQL with max_connections=100, 3 app instances
 * pool_size = (100 - 10) / 3 = 30 per instance
 */

📁 Files to Create/Modify

Modified Files

apps/api/src/config/configuration.ts
apps/api/src/config/config.service.ts
apps/api/src/database/prisma.service.ts
apps/api/src/health/health.controller.ts
apps/api/.env.example

New Files

apps/api/src/database/prisma-metrics.service.ts
apps/api/.env.development (optional)

✅ Validation Checklist

  • Pool configuration variables added
  • PrismaService uses configured pool settings
  • Health check includes database status
  • Pool metrics collection implemented
  • Environment-specific configs documented
  • pnpm nx build api passes
  • pnpm nx test api passes
  • Application starts with configured pool

Final Verification

# Build passes
pnpm nx build api

# Tests pass
pnpm nx test api

# Start and check logs for pool configuration
pnpm nx serve api
# Look for: "Database pool configured: size=10, connectTimeout=5000ms"

# Health check works
curl http://localhost:3000/health
# Returns: {"status":"ok","checks":{"database":"ok"}}

📝 .env.example Addition

# === Database Connection Pool ===
# Pool size should be tuned based on:
# - Database max_connections setting
# - Number of application instances
# - Expected concurrent requests
DATABASE_POOL_SIZE=10
DATABASE_CONNECT_TIMEOUT_MS=5000
DATABASE_POOL_TIMEOUT_MS=10000
DATABASE_IDLE_TIMEOUT_MS=300000

END OF PROMPT

This prompt resolves TD-013 from the technical debt register by configuring the Prisma database connection pool.