Prisma with Express — Building REST APIs with Prisma
In this tutorial, you will learn about Prisma with Express. We cover key concepts, practical examples, and best practices to help you master this topic.
Using Prisma with Express combines the type-safe database client with a flexible HTTP framework, enabling rapid development of REST APIs with structured routes, validation, and error handling.
What You'll Learn
By the end of this lesson you will create Express routes that use Prisma for database operations, implement request validation, handle pagination and filtering from query parameters, and structure a maintainable API layer.
Real-World Use
DodaZIP's REST API is built with Express and Prisma. Each resource has dedicated routes (GET, POST, PUT, DELETE) that use Prisma's type-safe methods. The API layer is separated into routes, controllers, and services.
Express Route Setup
Create Express routes with Prisma integration.
// routes/users.ts
import { Router, Request, Response } from 'express';
import { prisma } from '../db';
const router = Router();
// GET /users - List users with pagination
router.get('/', async (req: Request, res: Response) => {
const page = parseInt(req.query.page as string) || 1;
const pageSize = parseInt(req.query.pageSize as string) || 20;
const [users, total] = await Promise.all([
prisma.user.findMany({
skip: (page - 1) * pageSize,
take: pageSize,
orderBy: { createdAt: 'desc' },
}),
prisma.user.count(),
]);
res.json({
data: users,
pagination: {
page,
pageSize,
total,
totalPages: Math.ceil(total / pageSize),
},
});
});
// GET /users/:id - Get single user
router.get('/:id', async (req: Request, res: Response) => {
const user = await prisma.user.findUnique({
where: { id: parseInt(req.params.id) },
include: { posts: true },
});
if (!user) {
res.status(404).json({ error: 'User not found' });
return;
}
res.json({ data: user });
});
# express_setup.py
# Express + Prisma route structure
def route_structure():
print("Express Route Structure:")
print()
print("routes/")
print(" users.ts - User CRUD endpoints")
print(" posts.ts - Post CRUD endpoints")
print(" files.ts - File endpoints")
print()
print("Each route file:")
print(" GET / → List with pagination/filters")
print(" POST / → Create resource")
print(" GET /:id → Get single resource")
print(" PUT /:id → Update resource")
print(" DELETE /:id → Delete resource")
route_structure()
CRUD Controller
Organize logic into controller functions.
// controllers/post.controller.ts
import { prisma } from '../db';
export class PostController {
async create(data: { title: string; content?: string; authorId: number }) {
return prisma.post.create({
data: {
title: data.title,
content: data.content,
authorId: data.authorId,
},
include: { author: { select: { id: true, name: true } } },
});
}
async findById(id: number) {
return prisma.post.findUnique({
where: { id },
include: { author: true, comments: true },
});
}
async findAll(filters: { page?: number; authorId?: number; search?: string }) {
const where: any = {};
if (filters.authorId) {
where.authorId = filters.authorId;
}
if (filters.search) {
where.OR = [
{ title: { contains: filters.search, mode: 'insensitive' } },
{ content: { contains: filters.search, mode: 'insensitive' } },
];
}
const page = filters.page || 1;
const pageSize = 20;
const [data, total] = await Promise.all([
prisma.post.findMany({
where,
skip: (page - 1) * pageSize,
take: pageSize,
orderBy: { createdAt: 'desc' },
include: { author: { select: { name: true } } },
}),
prisma.post.count({ where }),
]);
return { data, total, page, pageSize };
}
async update(id: number, data: { title?: string; content?: string }) {
return prisma.post.update({ where: { id }, data });
}
async delete(id: number) {
return prisma.post.delete({ where: { id } });
}
}
# controller.py
# Controller pattern
def controller_pattern():
print("Controller Pattern:")
print()
print("class PostController {")
print(" async create(data) → return created post")
print(" async findById(id) → return post or null")
print(" async findAll(filters) → return paginated list")
print(" async update(id, data) → return updated post")
print(" async delete(id) → return deleted post")
print("}")
print()
print("Benefits:")
print(" - Separates business logic from routes")
print(" - Reusable across different route types")
print(" - Easier to test (unit test controllers)")
print(" - Clean route files (thin routes)")
controller_pattern()
Request Validation
Validate incoming data before database operations.
// middleware/validate.ts
import { z } from 'zod';
const createUserSchema = z.object({
email: z.string().email(),
name: z.string().min(2).max(100),
password: z.string().min(8),
});
// Validation middleware
function validate(schema: z.ZodSchema) {
return (req: Request, res: Response, next: NextFunction) => {
const result = schema.safeParse(req.body);
if (!result.success) {
res.status(400).json({
error: 'Validation failed',
details: result.error.errors.map(e => ({
field: e.path.join('.'),
message: e.message,
})),
});
return;
}
req.validatedBody = result.data;
next();
};
}
// Route using validation
router.post('/', validate(createUserSchema), async (req, res) => {
const user = await prisma.user.create({
data: req.validatedBody,
});
res.status(201).json({ data: user });
});
# validation.py
# Request validation patterns
def validation_pattern():
print("Request Validation Patterns:")
print()
print("Validate at three levels:")
print()
print("1. Route-level (Zod/Joi):")
print(" - Validate request body shape")
print(" - Type conversions (string → number)")
print(" - Sanitize inputs")
print()
print("2. Service-level:")
print(" - Business rule validation")
print(" - Check uniqueness, permissions")
print()
print("3. Database-level (Prisma schema):")
print(" - Required fields, unique constraints")
print(" - Foreign key integrity")
validation_pattern()
Service Layer
Abstract business logic behind a service layer.
// services/file.service.ts
import { prisma } from '../db';
export class FileService {
async uploadFile(userId: number, fileData: { name: string; sizeBytes: number }) {
// Check quota
const userStats = await prisma.user.aggregate({
where: { id: userId },
_sum: { storageUsed: true },
});
const used = userStats._sum.storageUsed || 0;
const maxStorage = 100 * 1024 * 1024; // 100MB
if (used + fileData.sizeBytes > maxStorage) {
throw new Error('Storage quota exceeded');
}
// Create file record and processing job
return prisma.$transaction(async (tx) => {
const file = await tx.file.create({
data: { ...fileData, userId, status: 'UPLOADED' },
});
await tx.processingJob.create({
data: { fileId: file.id, status: 'QUEUED' },
});
return file;
});
}
}
# service_layer.py
# Service layer pattern
def service_pattern():
print("Service Layer Pattern:")
print()
print("Services contain business logic:")
print(" - Validation and authorization checks")
print(" - Complex queries and transactions")
print(" - Integration with external services")
print(" - Error handling and logging")
print()
print("Layered architecture:")
print(" Route → Controller → Service → Prisma → Database")
print(" (validation) (orchestration) (business logic) (data access)")
service_pattern()
Common Mistakes
Putting Prisma queries directly in route handlers: Fat routes are hard to test and maintain. Extract logic into controllers or services.
Not handling null from findUnique: Express routes crash when accessing properties on null results. Always check for null.
Exposing Prisma query parameters to clients: Never pass req.query directly to Prisma. Validate and sanitize all user input.
Missing error boundaries: Unhandled Prisma errors crash the Express Process. Add a global error handler middleware.
N+1 queries in route handlers: Using findMany without include and then making separate queries for each related record. Use include or batch queries.
Practice Questions
What is the purpose of the service layer? To encapsulate business logic, validation, and complex operations, keeping route handlers thin.
Why should you validate request data? To prevent invalid data from reaching the database, ensure type safety, and provide user-friendly error messages.
How do you handle Prisma errors in Express? Use a global error handler middleware that matches error codes to HTTP status codes and messages.
What is the problem with passing req.query to Prisma? It exposes the database structure and allows malicious query manipulation. Always extract and validate specific parameters.
Challenge: Create a complete REST API for a file management system with Express, Prisma, validation, pagination, error handling, and a service layer.
FAQ
Mini Project
Create a REST API for a blogging platform with Express and Prisma: user CRUD, post CRUD with pagination, comment CRUD, and search endpoints with proper validation and error handling.
def rest_api_structure():
print("Blog REST API Structure:")
print()
print("GET /api/users - List users")
print("POST /api/users - Create user")
print("GET /api/users/:id - Get user with posts")
print("PUT /api/users/:id - Update user")
print("DELETE /api/users/:id - Delete user")
print()
print("GET /api/posts - List posts (paginated)")
print("POST /api/posts - Create post")
print("GET /api/posts/:id - Get post with comments")
print("PUT /api/posts/:id - Update post")
print("DELETE /api/posts/:id - Delete post")
print()
print("GET /api/posts/search?q= - Full-text search")
rest_api_structure()
What's Next
Next: Prisma with Next.js for full-stack apps.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro