How to Use PostgreSQL with Next.js: Simple Setup Guide

To use PostgreSQL with Next.js, set up a PostgreSQL database and connect it using an ORM like Prisma. Create API routes in Next.js to interact with the database securely on the server side.

Syntax

Here is the basic syntax to connect Next.js with PostgreSQL using Prisma ORM:

• Install Prisma: Add Prisma and its client to your project.
• Define schema: Create a schema.prisma file to describe your database models.
• Generate client: Run Prisma commands to generate the client for database queries.
• Use in API routes: Import Prisma client in Next.js API routes to query the database.

bash, prisma, javascript

npm install @prisma/client prisma

// schema.prisma
// datasource and generator setup

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

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

// Generate client
npx prisma generate

// Example API route usage
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

export default async function handler(req, res) {
  const users = await prisma.user.findMany();
  res.json(users);
}

Example

This example shows a simple Next.js API route that fetches all users from a PostgreSQL database using Prisma.

javascript

// pages/api/users.js
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export default async function handler(req, res) {
  try {
    const users = await prisma.user.findMany();
    res.status(200).json(users);
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch users' });
  } finally {
    await prisma.$disconnect();
  }
}

Output

[{"id":1,"name":"Alice","email":"[e-mail]"},{"id":2,"name":"Bob","email":"[e-mail]"}]

Common Pitfalls

Common mistakes when using PostgreSQL with Next.js include:

• Not setting the DATABASE_URL environment variable correctly.
• Using Prisma client outside API routes or React components causing connection leaks.
• Forgetting to disconnect Prisma client after queries.
• Exposing database credentials in client-side code.

Always keep database access on the server side and use environment variables for sensitive data.

javascript

// Wrong: Using Prisma client in React component (client-side)
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

export default function Users() {
  // This will cause errors because Prisma client should not run in browser
  const [users, setUsers] = React.useState([]);
  React.useEffect(() => {
    async function fetchUsers() {
      // Prisma client cannot be used here; fetch from API instead
    }
    fetchUsers();
  }, []);
  return <div>{JSON.stringify(users)}</div>;
}

// Right: Use Prisma client only in API routes or getServerSideProps
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

export async function getServerSideProps() {
  const users = await prisma.user.findMany();
  await prisma.$disconnect();
  return { props: { users } };
}

export default function Users({ users }) {
  return <div>{JSON.stringify(users)}</div>;
}

Quick Reference

Summary tips for using PostgreSQL with Next.js:

• Use Prisma ORM for easy database access.
• Keep database queries in API routes or server-side functions.
• Store DATABASE_URL in .env.local and never expose it client-side.
• Run npx prisma migrate dev to apply schema changes.
• Disconnect Prisma client after queries to avoid connection issues.

Key Takeaways

Use Prisma ORM to connect Next.js with PostgreSQL easily and safely.

Always keep database access on the server side using API routes or server functions.

Store your database URL in environment variables and never expose it to the client.

Disconnect Prisma client after queries to prevent connection leaks.

Run Prisma migrations to keep your database schema in sync with your models.