How to fix P1002: The database server was reached but timed out in Prisma

DATABASE_URL="postgresql://user:pass@host:5432/db?connect_timeout=30&pool_timeout=30"

DATABASE_URL="mysql://user:pass@host:3306/db?connect_timeout=30&pool_timeout=30"

SELECT count(*) as active_connections
FROM pg_stat_activity
WHERE state = 'active';

SELECT query, mean_time, calls
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

SELECT * FROM pg_locks WHERE NOT granted;

DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=5"

DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=1&pool_timeout=30"

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

generator client {
  provider = "prisma-client-js"
}

const prisma = new PrismaClient({
  log: ['query', 'error', 'warn'],
});

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?

  @@index([email])  // Add this for faster lookups
}

// ❌ Bad - fetches all fields
const users = await prisma.user.findMany();

// ✅ Good - only fetch needed fields
const users = await prisma.user.findMany({
  select: { id: true, email: true },
});

npx prisma migrate dev

npm install @prisma/extension-accelerate

import { PrismaClient } from '@prisma/client';
import { withAccelerate } from '@prisma/extension-accelerate';

const prisma = new PrismaClient().$extends(withAccelerate());

[databases]
mydb = host=actual-db.com port=5432 user=dbuser password=dbpass

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 10

# Change from direct connection
DATABASE_URL="postgresql://user:[email protected]:5432/postgres"

# To pooler connection (transaction mode)
DATABASE_URL="postgresql://user:[email protected]:6543/postgres"

async function withRetry<T>(
  fn: () => Promise<T>,
  maxAttempts = 3,
  baseDelay = 100
): Promise<T> {
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      return await fn();
    } catch (error) {
      if (attempt === maxAttempts) throw error;

      // Check if it's a timeout error
      if (error.code === 'P1002' || error.code === 'P2024') {
        const delay = baseDelay * Math.pow(2, attempt - 1);
        console.log(`Retry attempt ${attempt} after ${delay}ms`);
        await new Promise(resolve => setTimeout(resolve, delay));
      } else {
        throw error;
      }
    }
  }
}

// Usage
const user = await withRetry(() =>
  prisma.user.findUnique({ where: { id: 1 } })
);