Learn › Next.js › Databases with Prisma

Databases with Prisma

⏱ 20 min read read

Setting Up Prisma:

npm install prisma @prisma/client

npx prisma init # creates prisma/schema.prisma + .env

# .env

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

# or SQLite for local dev:

DATABASE_URL="file:./dev.db"

Prisma Schema:

// prisma/schema.prisma

generator client {

provider = "prisma-client-js"

}

datasource db {

provider = "sqlite" // or "postgresql"

url = env("DATABASE_URL")

}

model Student {

id Int @id @default(autoincrement())

name String

email String @unique

createdAt DateTime @default(now())

grades Grade[]

}

model Grade {

id Int @id @default(autoincrement())

subject String

score Float

student Student @relation(fields: [studentId], references: [id])

studentId Int

}

npx prisma migrate dev --name init # run migration + generate
client

npx prisma studio # open visual DB browser

npx prisma db push # push schema without migration (dev)

Prisma Client --- CRUD:

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

// Use a singleton in development

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

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

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

Always use a Prisma singleton in Next.js dev to avoid 'too many
connections'.

Put it in lib/prisma.ts and import from there everywhere.

Prisma works in Server Components and Route Handlers --- never in
Client Components.

Popular alternative: Drizzle ORM (lighter, SQL-first).

Code Example

// lib/prisma.ts --- singleton

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

const g = global as unknown as { prisma: PrismaClient };

export const prisma = g.prisma ?? new PrismaClient({ log:
['error'] });

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

// app/api/students/route.ts --- CRUD with Prisma

import { NextRequest, NextResponse } from 'next/server';

import { prisma } from '@/lib/prisma';

// GET /api/students

export async function GET() {

const students = await prisma.student.findMany({

orderBy: { createdAt: 'desc' },

include: { grades: true },

});

return NextResponse.json(students);

}

// POST /api/students

export async function POST(req: NextRequest) {

const { name, email } = await req.json();

const student = await prisma.student.create({

data: { name, email },

});

return NextResponse.json(student, { status: 201 });

}

// app/api/students/[id]/route.ts

// GET one student with grades

export async function GET(\_: NextRequest, { params }: { params: {
id: string } }) {

const student = await prisma.student.findUnique({

where: { id: Number(params.id) },

include: { grades: true },

});

if (!student) return NextResponse.json({ error: 'Not found' }, {
status: 404 });

return NextResponse.json(student);

}

// DELETE student (cascade deletes grades via schema)

export async function DELETE(\_: NextRequest, { params }: { params: {
id: string } }) {

await prisma.grade.deleteMany({ where: { studentId: Number(params.id)
} });

await prisma.student.delete({ where: { id: Number(params.id) } });

return new NextResponse(null, { status: 204 });

}

// Server Component --- read data directly (no API needed)

// app/students/page.tsx

import { prisma } from '@/lib/prisma';

export default async function StudentsPage() {

const students = await prisma.student.findMany({ orderBy: { name:
'asc' } });

return <ul>{students.map(s => <li
key={s.id}>{s.name}</li>)}</ul>;

}

← Forms & Server Actions Authentication with NextAuth.js →