Next.js – Database Connection

Next.js

Database Connection

Connecting to a database using a generic driver (e.g., pg for PostgreSQL) directly in Next.js Server Actions or Route Handlers.

Installation: Install PostgreSQL client.

📄terminal
BASH

npm install pg
npm install -D @types/pg

# .env
# DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Database Config: Singleton pattern to manage DB connection pooling during hot reloads.

📄lib/db.ts
TS

import { Pool } from 'pg';

const globalForPg = global as unknown as { pool: Pool };

export const pool =
  globalForPg.pool ||
  new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 10,
    idleTimeoutMillis: 30000,
  });

if (process.env.NODE_ENV !== 'production') {
  globalForPg.pool = pool;
}

Usage in Server Component: Fetching data directly in a React Server Component.

📄app/users/page.tsx
TSX

import { pool } from '@/lib/db';

interface User {
  id: number;
  name: string;
  email: string;
}

export default async function UsersPage() {
  const client = await pool.connect();
  let users: User[] = [];
  
  try {
    const res = await client.query('SELECT id, name, email FROM users ORDER BY id DESC LIMIT 10');
    users = res.rows;
  } finally {
    client.release();
  }

  return (
    <div>
      <h2>User List</h2>
      <ul>
        {users.map((user) => (
          <li key={user.id}>{user.name} ({user.email})</li>
        ))}
      </ul>
    </div>
  );
}