Next.js and PostgreSQL Integration with Prisma Made Easy

1. Install PostgreSQL Locally

Install PostgreSQL on your system. During the setup process, take note of the following:

• Username (default: postgres)

• Password (you’ll define it during setup)

• Port (default: 5432)

You’ll need these for your Prisma configuration later.

---

2. Install Prisma (ORM for PostgreSQL)

Install Prisma CLI and client packages:

npm install prisma --save-dev
npm install @prisma/client
npx prisma init

This will create two important files:

• prisma/schema.prisma

• .env

---

3. Configure Your Database URL

In your .env file, add your connection string:

DATABASE_URL="postgresql://postgres:your_password@localhost:5432/ecor?schema=public"

⚠️ Important:
If your password includes special characters like @, #, etc., you must URL-encode them:

• @ → %40

• # → %23

Example:

DATABASE_URL="postgresql://postgres:my%40secure%23pass@localhost:5432/ecor?schema=public"

---

4. Define Your Prisma Schema

Edit prisma/schema.prisma to define your data model:

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

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

model Product {
  id          Int      @id @default(autoincrement())
  name        String
  description String?
  price       Float
  createdAt   DateTime @default(now())
}

---

5. Migrate Database & Generate Prisma Client

Run the following command to create your database tables:

npx prisma migrate dev --name init

If everything runs smoothly, check your local database (e.g., using pgAdmin 4). A Product table should now be created.

❗ Error Tip:
If you get any errors, double-check the DATABASE_URL inside both .env and .env.local (if used). Make sure the password, DB name, and port are correct.

---

6. Setup Prisma Client in Next.js

Create a helper file at lib/prisma.ts:

import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: ['query'],
  });

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

This ensures only one instance of PrismaClient is used during development to avoid connection issues.

---

7. Example API Route: Fetch All Products

Create an API route to fetch products:

// app/api/products/route.ts

import { NextResponse } from 'next/server';
import { prisma } from '@/lib/prisma';

export async function GET() {
  const products = await prisma.product.findMany();
  return NextResponse.json(products);
}

---

8. Test It Out

Run your development server:

npm run dev

Open your browser and visit:

http://localhost:3000/api/products

You should see an empty list of products (or the products you added).

---

That’s it! 🎉 You now have a fully working PostgreSQL + Prisma setup in your Next.js project.