Prisma ORM Advanced Patterns: Transactions, Middleware, and Raw Queries

Prisma Beyond the Basics

Most guides cover basic Prisma CRUD. Here are the patterns you need for production applications.

Interactive Transactions

// Ensure atomicity across multiple operations
const [order, payment] = await prisma.$transaction(async (tx) => {
  // Check inventory
  const product = await tx.product.findUniqueOrThrow({
    where: { id: productId },
  });
  
  if (product.stock < quantity) {
    throw new Error('Insufficient stock');
  }
  
  // Deduct stock
  await tx.product.update({
    where: { id: productId },
    data: { stock: { decrement: quantity } },
  });
  
  // Create order
  const order = await tx.order.create({
    data: { userId, productId, quantity, total: product.price * quantity },
  });
  
  // Create payment record
  const payment = await tx.payment.create({
    data: { orderId: order.id, amount: order.total, status: 'PENDING' },
  });
  
  return [order, payment];
}, {
  maxWait: 5000, // Max time waiting for a connection
  timeout: 10000, // Max time for the transaction
  isolationLevel: 'Serializable',
});

Middleware for Soft Deletes

// prisma/middleware.ts
export function addSoftDeleteMiddleware(prisma: PrismaClient) {
  // Intercept delete operations
  prisma.$use(async (params, next) => {
    if (params.action === 'delete') {
      params.action = 'update';
      params.args.data = { deletedAt: new Date() };
    }
    if (params.action === 'deleteMany') {
      params.action = 'updateMany';
      if (params.args.data !== undefined) {
        params.args.data.deletedAt = new Date();
      } else {
        params.args.data = { deletedAt: new Date() };
      }
    }
    return next(params);
  });
  
  // Filter out soft-deleted records from queries
  prisma.$use(async (params, next) => {
    const softDeleteModels = ['User', 'Post', 'Comment'];
    
    if (softDeleteModels.includes(params.model ?? '')) {
      if (params.action === 'findUnique' || params.action === 'findFirst') {
        params.action = 'findFirst';
        params.args.where = { ...params.args.where, deletedAt: null };
      }
      if (params.action === 'findMany') {
        params.args.where = { ...params.args.where, deletedAt: null };
      }
    }
    return next(params);
  });
}

Prisma Schema Design Patterns

// Polymorphic relations
model Notification {
  id          String   @id @default(cuid())
  userId      String
  user        User     @relation(fields: [userId], references: [id])
  type        String
  
  // Polymorphic reference
  resourceId   String
  resourceType String   // 'Post' | 'Comment' | 'Order'
  
  read        Boolean  @default(false)
  createdAt   DateTime @default(now())
  
  @@index([userId, read])
  @@index([resourceId, resourceType])
}

// Full-text search (PostgreSQL)
model Post {
  id      String @id @default(cuid())
  title   String
  content String
  
  @@index([title, content], type: Gin)  // PostgreSQL GIN index
}

Full-Text Search

// PostgreSQL full-text search with Prisma
const posts = await prisma.$queryRaw`
  SELECT id, title, content,
    ts_rank(search_vector, query) AS rank
  FROM posts,
    to_tsquery('english', ${searchTerm}) query
  WHERE search_vector @@ query
  ORDER BY rank DESC
  LIMIT 20
`;

// Or using Prisma's built-in (MySQL/PostgreSQL)
const posts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { search: searchTerm } },
      { content: { search: searchTerm } },
    ],
  },
});

Efficient Pagination with Cursors

async function paginatePosts(cursor?: string, take = 20) {
  const posts = await prisma.post.findMany({
    take: take + 1,
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { createdAt: 'desc' },
    where: { published: true },
  });
  
  const hasMore = posts.length > take;
  const items = hasMore ? posts.slice(0, -1) : posts;
  
  return {
    items,
    nextCursor: hasMore ? items[items.length - 1].id : undefined,
  };
}

Raw Queries for Complex Operations

// Type-safe raw queries
const result = await prisma.$queryRaw<Array<{
  id: string;
  name: string;
  orderCount: bigint;
}>>`
  SELECT u.id, u.name, COUNT(o.id) as "orderCount"
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  WHERE u.created_at > ${new Date('2026-01-01')}
  GROUP BY u.id, u.name
  HAVING COUNT(o.id) > 5
  ORDER BY "orderCount" DESC
`;

// Note: BigInt from COUNT — convert if needed
const users = result.map(r => ({
  ...r,
  orderCount: Number(r.orderCount),
}));

Connection Pool Configuration

// DATABASE_URL with connection pool settings
const url = 'postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=20';

// Or via PrismaClient
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
  log: process.env.NODE_ENV === 'development'
    ? ['query', 'info', 'warn', 'error']
    : ['error'],
});

Avoiding N+1 with Include

// Bad: N+1
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } }); // N queries!
}

// Good: Single query with include
const users = await prisma.user.findMany({
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
    _count: { select: { posts: true } },
  },
});

// For complex selections, use select
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
    posts: {
      select: { id: true, title: true, publishedAt: true },
      where: { published: true },
    },
  },
});

Migration Best Practices

# Development workflow
npx prisma migrate dev --name add_user_role

# Production — review SQL first
npx prisma migrate diff --from-migrations ./prisma/migrations --to-schema-datamodel ./prisma/schema.prisma

# Apply in production
npx prisma migrate deploy