Prisma provides type-safe database access for Node.js. Here's how to use it effectively in production applications.
Schema Design#
1// prisma/schema.prisma
2generator client {
3 provider = "prisma-client-js"
4 previewFeatures = ["fullTextSearch"]
5}
6
7datasource db {
8 provider = "postgresql"
9 url = env("DATABASE_URL")
10}
11
12// Use meaningful model names
13model User {
14 id String @id @default(cuid())
15 email String @unique
16 name String
17 role Role @default(USER)
18 createdAt DateTime @default(now())
19 updatedAt DateTime @updatedAt
20
21 // Relations
22 posts Post[]
23 comments Comment[]
24 profile Profile?
25
26 // Indexes for common queries
27 @@index([email])
28 @@index([createdAt])
29}
30
31enum Role {
32 USER
33 ADMIN
34 MODERATOR
35}
36
37model Post {
38 id String @id @default(cuid())
39 title String
40 content String?
41 published Boolean @default(false)
42 publishedAt DateTime?
43 createdAt DateTime @default(now())
44 updatedAt DateTime @updatedAt
45
46 author User @relation(fields: [authorId], references: [id])
47 authorId String
48 comments Comment[]
49 tags Tag[]
50
51 // Composite index for common query
52 @@index([authorId, published])
53 @@index([publishedAt])
54}
55
56// Many-to-many with explicit join table
57model Tag {
58 id String @id @default(cuid())
59 name String @unique
60 posts Post[]
61}Client Initialization#
1// lib/prisma.ts
2import { PrismaClient } from '@prisma/client';
3
4// Prevent multiple instances in development
5const globalForPrisma = globalThis as unknown as {
6 prisma: PrismaClient | undefined;
7};
8
9export const prisma = globalForPrisma.prisma ?? new PrismaClient({
10 log: process.env.NODE_ENV === 'development'
11 ? ['query', 'error', 'warn']
12 : ['error'],
13});
14
15if (process.env.NODE_ENV !== 'production') {
16 globalForPrisma.prisma = prisma;
17}
18
19// With custom logging
20const prisma = new PrismaClient({
21 log: [
22 { level: 'query', emit: 'event' },
23 { level: 'error', emit: 'stdout' },
24 ],
25});
26
27prisma.$on('query', (e) => {
28 if (e.duration > 100) {
29 console.warn(`Slow query (${e.duration}ms):`, e.query);
30 }
31});Efficient Queries#
1// Select only needed fields
2const users = await prisma.user.findMany({
3 select: {
4 id: true,
5 name: true,
6 email: true,
7 },
8});
9
10// Include relations selectively
11const post = await prisma.post.findUnique({
12 where: { id },
13 include: {
14 author: {
15 select: { id: true, name: true },
16 },
17 comments: {
18 take: 10,
19 orderBy: { createdAt: 'desc' },
20 },
21 },
22});
23
24// Avoid N+1 queries
25// ❌ Bad - N+1 queries
26const posts = await prisma.post.findMany();
27for (const post of posts) {
28 const author = await prisma.user.findUnique({
29 where: { id: post.authorId },
30 });
31}
32
33// ✓ Good - single query with include
34const posts = await prisma.post.findMany({
35 include: { author: true },
36});
37
38// ✓ Or use select for specific fields
39const posts = await prisma.post.findMany({
40 select: {
41 id: true,
42 title: true,
43 author: {
44 select: { name: true },
45 },
46 },
47});Pagination#
1// Cursor-based pagination (preferred)
2async function getPosts(cursor?: string, limit = 20) {
3 const posts = await prisma.post.findMany({
4 take: limit + 1,
5 cursor: cursor ? { id: cursor } : undefined,
6 skip: cursor ? 1 : 0,
7 orderBy: { createdAt: 'desc' },
8 });
9
10 const hasMore = posts.length > limit;
11 const data = hasMore ? posts.slice(0, -1) : posts;
12
13 return {
14 data,
15 nextCursor: hasMore ? data[data.length - 1].id : null,
16 };
17}
18
19// Offset pagination (for page numbers)
20async function getPostsOffset(page = 1, limit = 20) {
21 const [posts, total] = await Promise.all([
22 prisma.post.findMany({
23 skip: (page - 1) * limit,
24 take: limit,
25 orderBy: { createdAt: 'desc' },
26 }),
27 prisma.post.count(),
28 ]);
29
30 return {
31 data: posts,
32 pagination: {
33 page,
34 limit,
35 total,
36 totalPages: Math.ceil(total / limit),
37 },
38 };
39}Transactions#
1// Sequential operations
2const [user, post] = await prisma.$transaction([
3 prisma.user.create({ data: userData }),
4 prisma.post.create({ data: postData }),
5]);
6
7// Interactive transaction (more control)
8const result = await prisma.$transaction(async (tx) => {
9 const user = await tx.user.findUnique({
10 where: { id: userId },
11 });
12
13 if (!user) {
14 throw new Error('User not found');
15 }
16
17 if (user.balance < amount) {
18 throw new Error('Insufficient balance');
19 }
20
21 const [sender, recipient] = await Promise.all([
22 tx.user.update({
23 where: { id: userId },
24 data: { balance: { decrement: amount } },
25 }),
26 tx.user.update({
27 where: { id: recipientId },
28 data: { balance: { increment: amount } },
29 }),
30 ]);
31
32 return { sender, recipient };
33}, {
34 maxWait: 5000,
35 timeout: 10000,
36 isolationLevel: 'Serializable',
37});Raw Queries#
1// Raw SQL when needed
2const users = await prisma.$queryRaw<User[]>`
3 SELECT * FROM "User"
4 WHERE email LIKE ${`%${domain}`}
5 ORDER BY "createdAt" DESC
6 LIMIT ${limit}
7`;
8
9// Unsafe raw (use carefully)
10const tableName = 'User';
11const users = await prisma.$queryRawUnsafe(
12 `SELECT * FROM "${tableName}" WHERE id = $1`,
13 userId
14);
15
16// Execute without return
17await prisma.$executeRaw`
18 UPDATE "Post"
19 SET "viewCount" = "viewCount" + 1
20 WHERE id = ${postId}
21`;Middleware#
1// Soft delete middleware
2prisma.$use(async (params, next) => {
3 if (params.model === 'Post') {
4 if (params.action === 'delete') {
5 params.action = 'update';
6 params.args.data = { deletedAt: new Date() };
7 }
8
9 if (params.action === 'deleteMany') {
10 params.action = 'updateMany';
11 params.args.data = { deletedAt: new Date() };
12 }
13
14 // Filter out soft-deleted in reads
15 if (params.action === 'findMany' || params.action === 'findFirst') {
16 params.args.where = {
17 ...params.args.where,
18 deletedAt: null,
19 };
20 }
21 }
22
23 return next(params);
24});
25
26// Logging middleware
27prisma.$use(async (params, next) => {
28 const start = Date.now();
29 const result = await next(params);
30 const duration = Date.now() - start;
31
32 console.log(`${params.model}.${params.action} - ${duration}ms`);
33
34 return result;
35});Migrations#
1# Create migration
2npx prisma migrate dev --name add_user_role
3
4# Apply migrations in production
5npx prisma migrate deploy
6
7# Reset database (dev only)
8npx prisma migrate reset
9
10# Generate client after schema changes
11npx prisma generate1// Safe migration patterns
2// 1. Add nullable column
3model User {
4 phone String? // Nullable first
5}
6
7// 2. Backfill data
8await prisma.user.updateMany({
9 where: { phone: null },
10 data: { phone: '' },
11});
12
13// 3. Make required (in separate migration)
14model User {
15 phone String @default("")
16}Connection Management#
1// Connection pooling (in connection string)
2// DATABASE_URL="postgresql://...?connection_limit=5"
3
4// Graceful shutdown
5async function shutdown() {
6 await prisma.$disconnect();
7 process.exit(0);
8}
9
10process.on('SIGINT', shutdown);
11process.on('SIGTERM', shutdown);
12
13// Health check
14async function checkDatabase(): Promise<boolean> {
15 try {
16 await prisma.$queryRaw`SELECT 1`;
17 return true;
18 } catch {
19 return false;
20 }
21}Testing#
1// Mock Prisma for unit tests
2import { mockDeep, DeepMockProxy } from 'jest-mock-extended';
3import { PrismaClient } from '@prisma/client';
4
5export const prismaMock = mockDeep<PrismaClient>();
6
7// In tests
8beforeEach(() => {
9 prismaMock.user.findUnique.mockResolvedValue({
10 id: '1',
11 email: 'test@example.com',
12 name: 'Test User',
13 });
14});
15
16// Integration tests with test database
17beforeAll(async () => {
18 await prisma.$connect();
19});
20
21afterAll(async () => {
22 await prisma.$disconnect();
23});
24
25beforeEach(async () => {
26 // Clean database
27 await prisma.post.deleteMany();
28 await prisma.user.deleteMany();
29});Best Practices#
Schema:
✓ Use meaningful model names
✓ Add indexes for query patterns
✓ Use enums for fixed values
✓ Consider soft deletes
Queries:
✓ Select only needed fields
✓ Use includes/selects wisely
✓ Avoid N+1 queries
✓ Use transactions for consistency
Performance:
✓ Enable query logging in dev
✓ Monitor slow queries
✓ Use connection pooling
✓ Index frequently queried columns
Conclusion#
Prisma provides excellent developer experience with type safety. Design schemas thoughtfully, use selective queries to avoid over-fetching, implement proper pagination, and use transactions for data integrity. Monitor query performance and add indexes based on actual usage patterns.