CRUD Operations
CRUD operations are the foundation of any database-driven application. CRUD stands for Create, Read, Update, and Delete - the four basic operations you can perform on data. In this section, we'll implement complete CRUD functionality using Next.js API routes and Prisma.
What You'll Learn
- Create: Adding new records to your database
- Read: Fetching and filtering data from your database
- Update: Modifying existing records
- Delete: Removing records from your database
CRUD Operations Overview
Create
POST requests to add new data
Read
GET requests to fetch data
Update
PUT requests to modify data
Delete
DELETE requests to remove data
1. Create Operations (POST)
Create operations add new records to your database. Let's start with creating a new user:
Creating a User
src/app/api/users/route.ts
import { NextRequest, NextResponse } from 'next/server'
import { prisma } from '@/lib/db'
export async function POST(request: NextRequest) {
try {
const body = await request.json()
const { email, name } = body
if (!email) {
return NextResponse.json(
{ error: 'Email is required' },
{ status: 400 }
)
}
const user = await prisma.user.create({
data: {
email,
name,
},
select: {
id: true,
name: true,
email: true,
createdAt: true,
_count: {
select: { tasks: true },
},
},
})
return NextResponse.json(user, { status: 201 })
} catch (error) {
console.error('Error creating user:', error)
return NextResponse.json(
{ error: 'Failed to create user' },
{ status: 500 }
)
}
}Creating a Task with Relationships
When creating tasks, we need to handle relationships to users and categories:
src/app/api/tasks/route.ts
export async function POST(request: NextRequest) {
try {
const body = await request.json()
const { title, description, priority, userId, categoryId, dueDate } = body
if (!title || !userId) {
return NextResponse.json(
{ error: 'Title and userId are required' },
{ status: 400 }
)
}
const task = await prisma.task.create({
data: {
title,
description,
priority: priority || 'MEDIUM',
userId,
categoryId: categoryId || null,
dueDate: dueDate ? new Date(dueDate) : null,
},
include: {
user: {
select: {
id: true,
name: true,
email: true,
},
},
category: {
select: {
id: true,
name: true,
color: true,
},
},
},
})
return NextResponse.json(task, { status: 201 })
} catch (error) {
console.error('Error creating task:', error)
return NextResponse.json(
{ error: 'Failed to create task' },
{ status: 500 }
)
}
}2. Read Operations (GET)
Read operations fetch data from your database. They can be simple queries or complex queries with filtering, sorting, and relationships.
Basic Read Operation
src/app/api/users/route.ts
export async function GET() {
try {
const users = await prisma.user.findMany({
select: {
id: true,
name: true,
email: true,
createdAt: true,
_count: {
select: { tasks: true },
},
},
orderBy: {
createdAt: 'desc',
},
})
return NextResponse.json(users)
} catch (error) {
console.error('Error fetching users:', error)
return NextResponse.json(
{ error: 'Failed to fetch users' },
{ status: 500 }
)
}
}Advanced Read with Filtering
You can add query parameters to filter and search your data:
src/app/api/tasks/route.ts
export async function GET(request: NextRequest) {
try {
const { searchParams } = new URL(request.url)
const completed = searchParams.get('completed')
const categoryId = searchParams.get('categoryId')
const priority = searchParams.get('priority')
const where: Record<string, string | boolean> = {}
if (completed !== null) {
where.completed = completed === 'true'
}
if (categoryId) {
where.categoryId = categoryId
}
if (priority) {
where.priority = priority
}
const tasks = await prisma.task.findMany({
where,
include: {
user: {
select: {
id: true,
name: true,
email: true,
},
},
category: {
select: {
id: true,
name: true,
color: true,
},
},
},
orderBy: [
{ completed: 'asc' },
{ priority: 'desc' },
{ createdAt: 'desc' },
],
})
return NextResponse.json(tasks)
} catch (error) {
console.error('Error fetching tasks:', error)
return NextResponse.json(
{ error: 'Failed to fetch tasks' },
{ status: 500 }
)
}
}Reading a Single Record
src/app/api/tasks/[id]/route.ts
export async function GET(
request: NextRequest,
{ params }: { params: Promise<{ id: string }> }
) {
try {
const { id } = await params
const task = await prisma.task.findUnique({
where: {
id,
},
include: {
user: {
select: {
id: true,
name: true,
email: true,
},
},
category: {
select: {
id: true,
name: true,
color: true,
},
},
},
})
if (!task) {
return NextResponse.json(
{ error: 'Task not found' },
{ status: 404 }
)
}
return NextResponse.json(task)
} catch (error) {
console.error('Error fetching task:', error)
return NextResponse.json(
{ error: 'Failed to fetch task' },
{ status: 500 }
)
}
}3. Update Operations (PUT)
Update operations modify existing records. It's important to handle partial updates and validate the data before updating.
src/app/api/tasks/[id]/route.ts
export async function PUT(
request: NextRequest,
{ params }: { params: Promise<{ id: string }> }
) {
try {
const { id } = await params
const body = await request.json()
const { title, description, completed, priority, dueDate, categoryId } = body
const task = await prisma.task.update({
where: {
id,
},
data: {
...(title !== undefined && { title }),
...(description !== undefined && { description }),
...(completed !== undefined && { completed }),
...(priority !== undefined && { priority }),
...(dueDate !== undefined && { dueDate: dueDate ? new Date(dueDate) : null }),
...(categoryId !== undefined && { categoryId }),
},
include: {
user: {
select: {
id: true,
name: true,
email: true,
},
},
category: {
select: {
id: true,
name: true,
color: true,
},
},
},
})
return NextResponse.json(task)
} catch (error) {
console.error('Error updating task:', error)
return NextResponse.json(
{ error: 'Failed to update task' },
{ status: 500 }
)
}
}Partial Updates
The spread operator pattern allows you to update only the fields that are provided in the request, leaving other fields unchanged.
4. Delete Operations (DELETE)
Delete operations remove records from your database. Always consider the impact on related data and use appropriate cascade behaviors.
src/app/api/tasks/[id]/route.ts
export async function DELETE(
request: NextRequest,
{ params }: { params: Promise<{ id: string }> }
) {
try {
const { id } = await params
await prisma.task.delete({
where: {
id,
},
})
return NextResponse.json({ message: 'Task deleted successfully' })
} catch (error) {
console.error('Error deleting task:', error)
return NextResponse.json(
{ error: 'Failed to delete task' },
{ status: 500 }
)
}
}Error Handling Best Practices
Validation Errors (400)
Return 400 status codes for missing required fields or invalid data.
if (!email) {
return NextResponse.json(
{ error: 'Email is required' },
{ status: 400 }
)
}Not Found Errors (404)
Return 404 status codes when a requested resource doesn't exist.
if (!task) {
return NextResponse.json(
{ error: 'Task not found' },
{ status: 404 }
)
}Server Errors (500)
Always catch and handle unexpected errors with proper logging.
} catch (error) {
console.error('Error creating user:', error)
return NextResponse.json(
{ error: 'Failed to create user' },
{ status: 500 }
)
}Testing Your CRUD Operations
You can test your API routes using tools like curl, Postman, or the browser:
# Create a new user
curl -X POST http://localhost:3000/api/users \
-H "Content-Type: application/json" \
-d '{"email": "john@example.com", "name": "John Doe"}'
# Get all users
curl http://localhost:3000/api/users
# Get tasks with filters
curl "http://localhost:3000/api/tasks?completed=false&priority=HIGH"
# Update a task
curl -X PUT http://localhost:3000/api/tasks/[task-id] \
-H "Content-Type: application/json" \
-d '{"completed": true}'
# Delete a task
curl -X DELETE http://localhost:3000/api/tasks/[task-id]CRUD Complete!
You now have a complete understanding of CRUD operations with Prisma and Next.js. These patterns form the foundation of most database-driven applications.