Advanced Prisma Patterns: Relations, Transactions, and Raw Queries

Nested writes

Prisma lets you create related records in a single call. This is atomic — if any part fails, nothing is written:

const order = await prisma.order.create({
  data: {
    userId: user.id,
    status: 'pending',
    items: {
      create: [
        { productId: 'prod_1', quantity: 2, price: 29.99 },
        { productId: 'prod_2', quantity: 1, price: 49.99 },
      ],
    },
    payment: {
      create: {
        method: 'card',
        amount: 109.97,
      },
    },
  },
  include: { items: true, payment: true },
});

This replaces three separate inserts and a manual transaction. Prisma wraps it in a database transaction automatically.

Interactive transactions

Sometimes you need to read, compute, then write — all atomically. Nested writes cannot do this. Interactive transactions can:

const result = await prisma.$transaction(async (tx) => {
  const account = await tx.account.findUniqueOrThrow({
    where: { id: accountId },
  });

  if (account.balance < amount) {
    throw new Error('Insufficient balance');
  }

  const updated = await tx.account.update({
    where: { id: accountId },
    data: { balance: { decrement: amount } },
  });

  await tx.transaction.create({
    data: { accountId, amount: -amount, type: 'withdrawal' },
  });

  return updated;
});

The tx client runs every query inside the same database transaction. If the function throws, everything rolls back.

When to use raw queries

Prisma's query builder covers 90 percent of use cases. The other 10 percent — complex aggregations, window functions, recursive CTEs — need raw SQL:

const topCategories = await prisma.$queryRaw<{ category: string; total: number }[]>`
  SELECT category, COUNT(*)::int AS total
  FROM posts
  WHERE published = true
  GROUP BY category
  ORDER BY total DESC
  LIMIT 5
`;

Use tagged template literals, not string concatenation. The template literal syntax parameterizes inputs automatically and prevents SQL injection.

Connection pooling in serverless

Prisma opens a connection pool per process. In serverless (Lambda, Vercel), every cold start creates a new pool. At scale, this exhausts your database connections. Use Prisma Accelerate or PgBouncer to proxy connections:

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  directUrl = env("DIRECT_URL") // for migrations
}

DATABASE_URL points to the pooler. DIRECT_URL points to the database directly for migrations that need DDL access.

The N+1 trap

Prisma does not lazy-load relations. But if you run queries in a loop, you get the same N+1 problem:

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

// Good: one query with include
const users = await prisma.user.findMany({
  include: { posts: true },
});

Always check the Prisma query log in development (prisma.$on('query')) to catch these before they hit production.