Database Patterns in Next.js

// lib/db.ts
import { Pool } from 'pg';

// Single connection pool
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export async function getUsers() {
  const { rows } = await pool.query('SELECT * FROM users');
  return rows;
}

// In Server Component
export default async function UsersPage() {
  const users = await getUsers();
  return <UserList users={users} />;
}

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

const globalForPrisma = global as { prisma?: PrismaClient };

export const prisma = globalForPrisma.prisma ?? new PrismaClient();

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

// Usage in Server Component
export default async function PostsPage() {
  const posts = await prisma.post.findMany({
    include: { author: true },
    orderBy: { createdAt: 'desc' },
  });
  return <PostList posts={posts} />;
}

// For serverless (Prisma)
// Use connection pooler like PgBouncer or Prisma Accelerate

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

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

// lib/db.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client);

// Schema
import { pgTable, text, serial } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').unique(),
});

// Query
const allUsers = await db.select().from(users);