Prisma ORM Advanced Patterns for Production Applications

Soft Delete Pattern

Never permanently delete records. Add a deletedAt field and filter it:

// Soft delete
async function softDeleteUser(id: string) {
  return prisma.user.update({
    where: { id },
    data: { deletedAt: new Date() },
  });
}

// Query with soft delete filter
async function getActiveUsers() {
  return prisma.user.findMany({
    where: { deletedAt: null }, // Only active records
    orderBy: { createdAt: "desc" },
  });
}

// Restore soft-deleted record
async function restoreUser(id: string) {
  return prisma.user.update({
    where: { id },
    data: { deletedAt: null },
  });
}

We use this pattern across all 60+ models in our platform.

Dynamic Query Building

Build queries dynamically based on user input:

async function searchProducts(filters: {
  search?: string;
  category?: string;
  minPrice?: number;
  maxPrice?: number;
  sortBy?: "price" | "name" | "date";
  page?: number;
}) {
  const where: Prisma.ProductWhereInput = {
    deletedAt: null,
    published: true,
    ...(filters.search && {
      OR: [
        { name: { contains: filters.search, mode: "insensitive" } },
        { description: { contains: filters.search, mode: "insensitive" } },
      ],
    }),
    ...(filters.category && { categoryId: filters.category }),
    ...(filters.minPrice && { price: { gte: filters.minPrice } }),
    ...(filters.maxPrice && { price: { lte: filters.maxPrice } }),
  };

  const [products, total] = await Promise.all([
    prisma.product.findMany({
      where,
      orderBy: { [filters.sortBy || "createdAt"]: "desc" },
      skip: ((filters.page || 1) - 1) * 20,
      take: 20,
      include: { category: true, images: true },
    }),
    prisma.product.count({ where }),
  ]);

  return { products, total, pages: Math.ceil(total / 20) };
}

Transaction Patterns

Use transactions for operations that must succeed together:

async function processOrder(orderData: OrderInput) {
  return prisma.$transaction(async (tx) => {
    // 1. Create the order
    const order = await tx.order.create({
      data: {
        userId: orderData.userId,
        total: orderData.total,
        status: "PENDING",
      },
    });

    // 2. Create order items and generate license keys
    for (const item of orderData.items) {
      const orderItem = await tx.orderItem.create({
        data: {
          orderId: order.id,
          productId: item.productId,
          price: item.price,
          quantity: item.quantity,
        },
      });

      // Generate license key for digital products
      if (item.isDigital) {
        await tx.licenseKey.create({
          data: {
            orderItemId: orderItem.id,
            key: generateLicenseKey(),
            activationsLimit: 1,
          },
        });
      }
    }

    // 3. Apply coupon if provided
    if (orderData.couponCode) {
      await tx.coupon.update({
        where: { code: orderData.couponCode },
        data: { usageCount: { increment: 1 } },
      });
    }

    return order;
  });
}

Connection Pooling

In production with serverless functions:

// lib/prisma.ts
import { PrismaClient } from "@prisma/client";

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === "development" ? ["query"] : [],
  });

if (process.env.NODE_ENV !== "production") {
  globalForPrisma.prisma = prisma;
}

For high-traffic applications, consider external connection poolers like PgBouncer. See our PostgreSQL Performance Tuning guide.

Select Only What You Need

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

// ✅ Good — only fetches needed columns
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
    role: true,
    // No password hash, no full profile data
  },
});

Related Data Loading Strategies

// Strategy 1: Include (JOIN) — good for 1:1 or small 1:many
const order = await prisma.order.findUnique({
  where: { id: orderId },
  include: {
    items: { include: { product: true } },
    user: { select: { name: true, email: true } },
  },
});

// Strategy 2: Separate queries — good for large 1:many
const [order, comments] = await Promise.all([
  prisma.order.findUnique({ where: { id: orderId } }),
  prisma.comment.findMany({
    where: { orderId },
    take: 20,
    orderBy: { createdAt: "desc" },
  }),
]);

Middleware for Audit Logging

prisma.$use(async (params, next) => {
  const start = Date.now();
  const result = await next(params);
  const duration = Date.now() - start;

  // Log slow queries
  if (duration > 1000) {
    console.warn(`Slow query: ${params.model}.${params.action} took ${duration}ms`);
  }

  return result;
});

All These Patterns, Built In

Every pattern in this guide is implemented in our Developer Portfolio & SaaS Platform. With 60+ Prisma models and $299 starting price, you get a production-ready database architecture without months of setup.

Related reads:

• PostgreSQL Performance Tuning — optimize the database layer
• TypeScript Tips for Type Safety — maximize Prisma's type generation
• Database Migration Strategies — manage schema evolution

Follow Hardik on Reddit for database tips and discussions.

Get our production Prisma setup with 60+ models — Developer Portfolio & SaaS Platform starting at $299.