Prisma is the best ORM for Next.js projects.
Type-safe queries, auto-generated types, clean migrations — it makes working with databases feel like working with TypeScript objects.
Here's the complete setup.
Install
CODE
npm install prisma @prisma/client
npx prisma init
This creates:
prisma/schema.prisma— your database schema
.env— withDATABASE_URLplaceholder
Schema Design
CODE
// prisma/schema.prisma
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model User {
id String @id @default(cuid())
name String
email String @unique
password String
role Role @default(USER)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
posts Post[]
profile Profile?
@@index([email])
}
model Post {
id String @id @default(cuid())
title String
content String
published Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
author User @relation(fields: [authorId], references: [id])
authorId String
@@index([authorId])
}
model Profile {
id String @id @default(cuid())
bio String?
avatar String?
user User @relation(fields: [userId], references: [id])
userId String @unique
}
enum Role {
USER
ADMIN
}
Run Migration
CODE
# Create and apply migration
npx prisma migrate dev --name init
# Push schema without migration (good for prototyping)
npx prisma db push
# Open Prisma Studio — visual database browser
npx prisma studio
The Connection Problem in Next.js
Next.js hot reloading creates new Prisma Client instances on every file change. This exhausts your database connections fast.
Fix it with the global singleton pattern:
CODE
// lib/db.ts
import { PrismaClient } from '@prisma/client';
const globalForPrisma = globalThis as unknown as {
prisma: PrismaClient | undefined;
};
export const db =
globalForPrisma.prisma ??
new PrismaClient({
log: process.env.NODE_ENV === 'development'
? ['query', 'error', 'warn']
: ['error'],
});
if (process.env.NODE_ENV !== 'production') {
globalForPrisma.prisma = db;
}
Import db from this file everywhere — never create new PrismaClient() directly.
CRUD Operations
CODE
import { db } from '@/lib/db';
// CREATE
const user = await db.user.create({
data: {
name: 'Anas',
email: '[email protected]',
password: hashedPassword,
},
});
// READ one
const user = await db.user.findUnique({
where: { email: '[email protected]' },
include: { profile: true }, // join profile
});
// READ many with filters
const posts = await db.post.findMany({
where: {
published: true,
authorId: userId,
},
orderBy: { createdAt: 'desc' },
take: 10, // limit
skip: 0, // offset
select: { // only fetch what you need
id: true,
title: true,
createdAt: true,
author: {
select: { name: true }
}
}
});
// UPDATE
const updated = await db.user.update({
where: { id: userId },
data: { name: 'New Name' },
});
// DELETE
await db.user.delete({
where: { id: userId },
});
// UPSERT — create or update
const profile = await db.profile.upsert({
where: { userId },
create: { userId, bio: 'New bio' },
update: { bio: 'Updated bio' },
});
Using in API Routes
CODE
// app/api/posts/route.ts
import { NextRequest } from 'next/server';
import { db } from '@/lib/db';
import { z } from 'zod';
const CreatePostSchema = z.object({
title: z.string().min(3).max(100),
content: z.string().min(10),
});
export async function GET(request: NextRequest) {
try {
const posts = await db.post.findMany({
where: { published: true },
orderBy: { createdAt: 'desc' },
include: {
author: {
select: { name: true, email: true }
}
}
});
return Response.json({ success: true, data: posts });
} catch (error) {
return Response.json({ error: 'Failed to fetch posts' }, { status: 500 });
}
}
export async function POST(request: NextRequest) {
try {
const body = await request.json();
const result = CreatePostSchema.safeParse(body);
if (!result.success) {
return Response.json(
{ error: result.error.errors[0].message },
{ status: 400 }
);
}
const post = await db.post.create({
data: {
...result.data,
authorId: 'user-id-from-auth', // get from session
},
});
return Response.json({ success: true, data: post }, { status: 201 });
} catch (error: any) {
// Handle unique constraint violation
if (error.code === 'P2002') {
return Response.json({ error: 'Already exists' }, { status: 409 });
}
return Response.json({ error: 'Server error' }, { status: 500 });
}
}
The Prisma error code P2002 is the equivalent of MongoDB's 11000 — unique constraint violation.
Transactions
When multiple operations must succeed or fail together:
CODE
// Both operations succeed or both fail
const result = await db.$transaction(async (tx) => {
// Deduct credits from sender
const sender = await tx.user.update({
where: { id: senderId },
data: { credits: { decrement: amount } },
});
if (sender.credits < 0) {
throw new Error('Insufficient credits');
}
// Add credits to receiver
const receiver = await tx.user.update({
where: { id: receiverId },
data: { credits: { increment: amount } },
});
return { sender, receiver };
});
If the error is thrown inside the transaction — both updates are rolled back automatically.
Seeding the Database
CODE
// prisma/seed.ts
import { PrismaClient } from '@prisma/client';
const db = new PrismaClient();
async function main() {
// Create admin user
await db.user.upsert({
where: { email: '[email protected]' },
update: {},
create: {
name: 'Admin',
email: '[email protected]',
password: 'hashed-password',
role: 'ADMIN',
},
});
console.log('Database seeded');
}
main()
.catch(console.error)
.finally(() => db.$disconnect());
Add to package.json:
CODE
"prisma": {
"seed": "ts-node prisma/seed.ts"
}
Run with: npx prisma db seed
Common Error Codes
| Code | Meaning | Fix |
|---|---|---|
P2002 | Unique constraint failed | Handle duplicate data |
P2025 | Record not found | Check if record exists first |
P2003 | Foreign key constraint | Check related record exists |
P2016 | Query interpretation error | Fix your query syntax |
Environment Setup
CODE
# .env
# PostgreSQL (recommended for production)
DATABASE_URL="postgresql://user:password@localhost:5432/mydb"
# Or use Supabase (free PostgreSQL hosting)
DATABASE_URL="postgresql://postgres:[password]@db.[project].supabase.co:5432/postgres"
# Or use Neon (free serverless PostgreSQL)
DATABASE_URL="postgresql://user:[email protected]/neondb?sslmode=require"
Prisma takes 30 minutes to set up and saves hundreds of hours of manual SQL and type casting.
I use it in all my production Next.js projects.
My templates:
Vollständiger Original-Bericht
Ausführliche Details, Code-Beispiele & Hersteller-Stellungnahme auf dev.to.
SOCIAL SHARE CARD GENERATOR