Prisma Best Practices for Production Applications

Query Optimization

Select Only What You Need

// ❌ Bad - fetches all columns
const users = await prisma.user.findMany();

// ✅ Good - fetches only needed columns
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
  },
});

Use Pagination

async function getUsers(page: number, pageSize: number = 20) {
  const [users, total] = await prisma.$transaction([
    prisma.user.findMany({
      skip: (page - 1) * pageSize,
      take: pageSize,
      orderBy: { createdAt: "desc" },
    }),
    prisma.user.count(),
  ]);

  return {
    users,
    total,
    pages: Math.ceil(total / pageSize),
  };
}

Avoid N+1 Queries

// ❌ Bad - N+1 query
const posts = await prisma.post.findMany();
for (const post of posts) {
  const author = await prisma.user.findUnique({
    where: { id: post.authorId },
  });
}

// ✅ Good - Single query with include
const posts = await prisma.post.findMany({
  include: {
    author: {
      select: { id: true, name: true },
    },
  },
});

Transaction Patterns

Use transactions for related operations:

async function createOrder(userId: string, items: OrderItem[]) {
  return prisma.$transaction(async (tx) => {
    // Create order
    const order = await tx.order.create({
      data: { userId, status: "PENDING" },
    });

    // Create order items
    await tx.orderItem.createMany({
      data: items.map((item) => ({
        orderId: order.id,
        productId: item.productId,
        quantity: item.quantity,
      })),
    });

    // Update inventory
    for (const item of items) {
      await tx.product.update({
        where: { id: item.productId },
        data: { stock: { decrement: item.quantity } },
      });
    }

    return order;
  });
}

Migration Best Practices

1. Never edit existing migrations - Create new ones instead
2. Test migrations on staging first - Always verify before production
3. Use meaningful names - npx prisma migrate dev --name add_user_roles
4. Backup before migrating - Always have a restore point

# Development
npx prisma migrate dev --name your_migration_name

# Production (CI/CD)
npx prisma migrate deploy

Index Strategy

Add indexes for frequently queried columns:

model Post {
  id          String   @id @default(cuid())
  slug        String   @unique
  title       String
  published   Boolean  @default(false)
  authorId    String
  createdAt   DateTime @default(now())
  
  author      User     @relation(fields: [authorId], references: [id])

  @@index([authorId])
  @@index([published, createdAt])
  @@index([slug])
}

Type-Safe Raw Queries

When you need raw SQL, stay type-safe:

const result = await prisma.$queryRaw<{ id: string; count: bigint }[]>`
  SELECT author_id as id, COUNT(*) as count
  FROM posts
  WHERE published = true
  GROUP BY author_id
  ORDER BY count DESC
  LIMIT 10
`;

Conclusion

Prisma is powerful, but production readiness requires attention to connection management, query optimization, and proper patterns. Follow these best practices to build scalable, maintainable applications.

Remember: always profile your queries in development and monitor them in production!