lib/server/db/index.ts

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as dotenv from "dotenv";
import * as schema from "./schema";
// Load environment variables from .dev.env
dotenv.config({ path: "./.dev.env" });
/**
 * Database connection singleton implementation
 * This ensures the connection pool is initialized only once across the application
 *
 * This implementation follows the recommended pattern for Next.js applications
 * to prevent connection pool exhaustion during development and production.
 */
// Define a more specific global object to avoid namespace conflicts
// This is a key improvement for Next.js module caching
const globalForDb = global as unknown as {
  pgConnection: {
    client?: ReturnType;
    db?: ReturnType;
    connectionId?: string;
    isConnected: boolean;
  };
};
// Initialize the global object if it doesn't exist
if (!globalForDb.pgConnection) {
  globalForDb.pgConnection = {
    client: undefined,
    db: undefined,
    connectionId: undefined,
    isConnected: false,
  };
}
// Check for required environment variables
if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL environment variable is not defined");
}
// Configure connection pool options based on environment
const connectionOptions = {
  max: process.env.NODE_ENV === "production" ? 20 : 5, // Smaller pool for development
  idle_timeout: 30, // Connection idle timeout in seconds
  connect_timeout: 10, // Connection timeout in seconds
  // Add additional options as needed
};
// Function to create a new database connection with a unique ID for tracking
function createDbConnection() {
  try {
    console.log("[Database] Initializing database connection...");
    // Create the PostgreSQL client with the configured options
    const client = postgres(
      process.env.DATABASE_URL as string,
      connectionOptions
    );
    console.log("[Database] PostgreSQL client created");
    // Initialize Drizzle ORM with the client and schema
    const db = drizzle(client, {
      schema, // Pass the schema for better type safety and relations
      logger: process.env.NODE_ENV !== "production", // Enable logging in development
    });
    console.log("[Database] Drizzle ORM initialized");
    // Generate a unique connection ID for debugging
    const connectionId = Math.random().toString(36).substring(2, 10);
    console.log(`[Database] Connection ID: ${connectionId}`);
    return { client, db, connectionId };
  } catch (error) {
    console.error(
      "[Database] Failed to initialize database connection:",
      error
    );
    throw error;
  }
}
// Get or create the database connection
let db: ReturnType;
// This is the key part - we use a more reliable approach to singleton management
if (process.env.NODE_ENV === "production") {
  // In production, we create a new connection for each serverless function instance
  // but reuse it for subsequent requests to that instance
  console.log("[Database] Production environment detected");
  if (!globalForDb.pgConnection.isConnected) {
    const conn = createDbConnection();
    globalForDb.pgConnection.client = conn.client;
    globalForDb.pgConnection.db = conn.db;
    globalForDb.pgConnection.connectionId = conn.connectionId;
    globalForDb.pgConnection.isConnected = true;
    console.log(
      `[Database] Created new production connection with ID: ${conn.connectionId}`
    );
    db = conn.db;
  } else {
    console.log(
      `[Database] Reusing production connection with ID: ${globalForDb.pgConnection.connectionId}`
    );
    db = globalForDb.pgConnection.db!;
  }
} else {
  // In development, we want to reuse the connection across hot reloads
  console.log("[Database] Development environment detected");
  if (!globalForDb.pgConnection.isConnected) {
    const conn = createDbConnection();
    globalForDb.pgConnection.client = conn.client;
    globalForDb.pgConnection.db = conn.db;
    globalForDb.pgConnection.connectionId = conn.connectionId;
    globalForDb.pgConnection.isConnected = true;
    console.log(
      `[Database] Created new development connection with ID: ${conn.connectionId}`
    );
    db = conn.db;
  } else {
    console.log(
      `[Database] Reusing development connection with ID: ${globalForDb.pgConnection.connectionId}`
    );
    db = globalForDb.pgConnection.db!;
  }
}
console.log("[Database] Database connection ready");
/**
 * Helper function to check if we're using a cached connection
 * You can call this in your API routes or server components to debug
 */
export function getConnectionStatus() {
  return {
    isConnected: globalForDb.pgConnection.isConnected,
    connectionId: globalForDb.pgConnection.connectionId,
    environment: process.env.NODE_ENV || "unknown",
    timestamp: new Date().toISOString(),
  };
}
export { db };
/**
 * Best practices for connection pooling with postgres and Drizzle ORM:
 *
 * 1. Use a singleton pattern (implemented above) to ensure only one connection pool exists
 * 2. Configure appropriate pool size based on your application's needs:
 *    - For production, start with ~20 connections and adjust based on monitoring
 *    - For development, a smaller pool (1-5) is sufficient
 * 3. Consider using a connection pooler like PgBouncer for high-traffic applications
 * 4. Implement proper error handling and connection retry logic
 * 5. For serverless environments, consider specialized connection management approaches
 * 6. Implement graceful shutdown to properly close connections when the app terminates
 *
 * Future improvements:
 * - Add connection health checks
 * - Implement retry logic for transient connection failures
 * - Add metrics collection for connection pool usage
 * - Consider implementing a connection pooler for production environments
 */

app/debug/db-status/page.tsx

"use client";
import { useState, useEffect } from "react";
import { Button } from "@/components/uix/button";
import {
  Card,
  CardContent,
  CardDescription,
  CardHeader,
  CardTitle,
} from "@/components/uix/card";
import { Badge } from "@/components/uix/badge";
interface ConnectionStatus {
  isConnected: boolean;
  connectionId?: string;
  environment: string;
  timestamp: string;
  connectionTest?: {
    success: boolean;
    queryTime: string;
    result: any;
  };
  requestTime: string;
}
interface ApiResponse {
  status: string;
  message: string;
  data: ConnectionStatus;
}
export default function DbStatusPage() {
  const [status, setStatus] = useState(null);
  const [loading, setLoading] = useState(false);
  const [error, setError] = useState(null);
  const [refreshCount, setRefreshCount] = useState(0);
  useEffect(() => {
    async function fetchStatus() {
      setLoading(true);
      setError(null);
      try {
        const response = await fetch("/api/debug/db-status");
        if (!response.ok) {
          throw new Error(`HTTP error! status: ${response.status}`);
        }
        const data: ApiResponse = await response.json();
        setStatus(data.data);
      } catch (err) {
        setError(
          err instanceof Error ? err.message : "An unknown error occurred"
        );
        console.error("Error fetching DB status:", err);
      } finally {
        setLoading(false);
      }
    }
    fetchStatus();
  }, [refreshCount]);
  const handleRefresh = () => {
    setRefreshCount((prev) => prev + 1);
  };
  return (
    

      
Database Connection Status
      

        
          {loading ? "Refreshing..." : "Refresh Status"}
        
        
          Refresh count: {refreshCount}
        
      
      {error && (
        

          
Error:
          
{error}
        
      )}
      {status && (
        

          
            
              Connection Status
              
                Current database connection information
              
            
            
              

                

                  Connection Status:
                  
                    {status.isConnected ? "Connected" : "Disconnected"}
                  
                
                

                  Connection ID:
                  
                    {status.connectionId || "N/A"}
                  
                
                

                  Environment:
                  {status.environment}
                
                

                  Last Updated:
                  
                    {new Date(status.timestamp).toLocaleString()}
                  
                
              
            
          
          
            
              Connection Test
              Results from test query
            
            
              {status.connectionTest ? (
                

                  

                    Test Status:
                    
                      {status.connectionTest.success ? "Success" : "Failed"}
                    
                  
                  

                    Query Time:
                    
                      {status.connectionTest.queryTime}
                    
                  
                  

                    
                      Query Result:
                    
                    
                      {JSON.stringify(status.connectionTest.result, null, 2)}
                    


) : (

  No connection test data available

)}



)}

  
How to Use This Page
  

    

      This page helps you verify that your database connection singleton
      is working correctly
    
    

      Click Refresh Status multiple times to check if the
      same connection is being reused
    
    

      The Connection ID should remain the same across
      refreshes in development mode
    
    

      Check the server logs to see if new connections are being created
    
  


);
}

app/api/debug/db-status/route.ts

import { NextResponse } from "next/server";
import { getConnectionStatus, db } from "@/lib/server/db";
import { sql } from "drizzle-orm";
export async function GET() {
  try {
    // Get the current connection status
    const status = getConnectionStatus();
    // Perform a simple query to test the connection
    const startTime = performance.now();
    const testResult = await db.execute(sql`SELECT 1 as connection_test`);
    const queryTime = performance.now() - startTime;
    // Return the status as JSON
    return NextResponse.json({
      status: "success",
      message: "Database connection status",
      data: {
        ...status,
        connectionTest: {
          success: true,
          queryTime: `${queryTime.toFixed(2)}ms`,
          result: testResult,
        },
        requestTime: new Date().toISOString(),
      },
    });
  } catch (error) {
    console.error("[API] Error fetching database status:", error);
    return NextResponse.json(
      {
        status: "error",
        message: "Failed to fetch database status",
        error: error instanceof Error ? error.message : String(error),
      },
      { status: 500 }
    );
  }
}

app/api/debug/db-test/route.ts

import { NextResponse } from "next/server";
import { db } from "@/lib/server/db";
import { sql } from "drizzle-orm";
export async function GET() {
  try {
    // Perform a simple query to test the database connection
    const startTime = performance.now();
    // Simple query that doesn't require any tables
    const result = await db.execute(sql`SELECT 1 as test`);
    const endTime = performance.now();
    const queryTime = endTime - startTime;
    // Return the result as JSON
    return NextResponse.json({
      status: "success",
      message: "Database connection test successful",
      data: {
        result,
        queryTime: `${queryTime.toFixed(2)}ms`,
        timestamp: new Date().toISOString(),
      },
    });
  } catch (error) {
    console.error("[API] Error testing database connection:", error);
    return NextResponse.json(
      {
        status: "error",
        message: "Database connection test failed",
        error: error instanceof Error ? error.message : String(error),
      },
      { status: 500 }
    );
  }
}