Querying with Prisma Client

Build an order-history page the obvious way and it works beautifully with four orders in the seed data:

const orders = await prisma.order.findMany({ where: { userId } });
for (const order of orders) {
  order.items = await prisma.orderItem.findMany({ where: { orderId: order.id } });
}

Twenty-five orders means twenty-six queries. At 5 ms of network round trip each, that is ~130 ms of pure latency on a page that should need one round trip. This is the N+1 query problem, and it is the single most common performance question in backend interviews — expect to be asked to name it, spot it, and fix it.

Prisma's fix is include, which loads related rows in the same query:

const orders = await prisma.order.findMany({
  where: { userId, status: { in: ['PAID', 'SHIPPED'] } },
  orderBy: { createdAt: 'desc' },
  take: 20,
  include: {
    items: { select: { title: true, qty: true, price: true } },
  },
});

Turn on query logging (log: [{ emit: 'event', level: 'query' }] on PrismaClient) and you will see one statement with a join rather than twenty-six:

SELECT t0."id", t0."total", t0."createdAt", t1."id", t1."title", t1."qty", t1."price"
FROM "Order" AS t0
LEFT JOIN "OrderItem" AS t1 ON t1."orderId" = t0."id"
WHERE t0."userId" = $1 AND t0."status" IN ($2, $3)
ORDER BY t0."createdAt" DESC
LIMIT $4;

include and select differ in one line: include keeps every scalar field of the parent and adds the relation; select picks exactly what you name. Both accept nested filters, ordering, and pagination inside the relation.

The trap to know for interviews: a take inside an include is applied per parent row, not to the result set overall — include: { items: { take: 5 } } asks for the five items of each order, which Prisma implements with per-parent limiting logic that gets expensive as the parent count grows. Pair it with a where in the relation, and reach for a second query when the parent count is unbounded.

Watch the shape of nested includes too. order → user → orders → items multiplies rows across relations; the join result can exceed what any single relation suggests. Select only the columns you render, order deliberately, and paginate the top level. These queries assume the models declared in Modeling Data with Prisma — if OrderItem has no index on orderId, every join above becomes a sequential scan, and fixing that is a migration.

Relations can be filtered and counted without loading the rows themselves:

const orders = await prisma.order.findMany({
  include: {
    items: { where: { qty: { gt: 0 } }, orderBy: { price: 'desc' } },
    _count: { select: { items: true } },   // "3 items" as a number, zero extra rows
  },
});

const populated = await prisma.order.count({
  where: { items: { some: { qty: { gt: 2 } } } },
});

where: { items: { some: ... } } filters parents by their children — "orders containing a quantity above 2" — without returning a single line item. _count is the fix for the badge on each card: you need "3 items", not the items, and selecting the relation just to call .length in the template is the N+1 in disguise.

Mixing the two is normal: select for the parent's columns plus one full relation gives a narrow object with exactly one expanded edge. And when you are unsure whether a call is really one round trip, count the statements in the query log — the terminal is the ground truth here; neither the ORM nor React will warn you that a loop became N queries.

Split a write across two calls and you have opened a window:

const order = await prisma.order.create({ data: { userId, total } });   // ← crash here
await prisma.orderItem.createMany({ data: items.map(i => ({ ...i, orderId: order.id })) });

If the process dies between them — deploy, OOM, connection drop — you have an order with no line items and no code path that ever repairs it. Data integrity is not a style choice; it is the difference between a bug you revert and a bug you reconcile by hand for a week.

Nested writes close the window. One create declares the whole aggregate, and Prisma runs it inside a single transaction:

const order = await prisma.order.create({
  data: {
    userId,
    total: items.reduce((s, i) => s + i.price * i.qty, 0),
    items: {
      create: items.map(i => ({ sku: i.sku, qty: i.qty, price: i.price })),
    },
  },
  include: { items: true },     // read the inserted rows back without a second round trip
});

Either every row lands or none does. The same nesting works for update: data: { items: { deleteMany: {}, create: { ... } } } replaces a child set atomically, which is how "edit cart" endpoints avoid intermediate states.

When the operations are unrelated models, use an explicit transaction. The array form batches independent operations:

const [order, _] = await prisma.$transaction([
  prisma.order.create({ data: { userId, total } }),
  prisma.inventory.update({ where: { sku }, data: { stock: { decrement: qty } } }),
]);

The interactive form is for logic that branches — checks, conditional writes, anything with an if:

await prisma.$transaction(async (tx) => {
  const stock = await tx.inventory.findUnique({ where: { sku } });
  if (!stock || stock.qty < qty) throw new OutOfStockError(sku);
  await tx.orderItem.create({ data: { orderId, sku, qty } });
  await tx.inventory.update({ where: { sku }, data: { stock: { decrement: qty } } });
});

Inside the callback you must use tx, not the outer client — a query on the outer client runs outside the transaction and silently loses atomicity.

Know the errors you are guarding against: P2002 unique constraint (email already registered), P2003 foreign key violation (the userId does not exist), P2025 record not found (the record you updated was deleted first). Map them to 409/422 responses instead of leaking stack traces.

Finally, keep transactions short. Two concurrent checkouts both running stock: { decrement: 1 } on the same row serialize on a row lock — correct, but a slow query inside the transaction turns that lock into a queue. Batch, avoid network calls inside the transaction, and retry on serialization failures. Compare notes with Modeling Data with Prisma, where these models were declared.

For bulk work there is a faster shape than a loop of creates: createMany issues one INSERT ... VALUES (...) statement, and skipDuplicates maps to ON CONFLICT DO NOTHING for idempotent imports:

await prisma.orderItem.createMany({ data: rows, skipDuplicates: true });

Note what it cannot do — createMany takes no nested writes and returns only a count, so the "create order plus items and read them back" path stays with create. When you need a specific isolation level, pass it explicitly: prisma.$transaction([...], { isolationLevel: 'Serializable' }).

Turn the error mapping into code rather than prose:

catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError) {
    if (e.code === 'P2002') return Response.json({ error: 'email taken' }, { status: 409 });
    if (e.code === 'P2025') return Response.json({ error: 'not found' }, { status: 404 });
  }
  throw e;   // everything else stays a 500 with a logged stack
}

An endpoint that returns prisma.user.findMany() returns passwordHash too — every column, every relation you did not think about, serialized to JSON and shipped to the browser. select is an allowlist, not an optimization you add later. In a Server Component the same mistake leaks through props into client JavaScript.

const members = await prisma.orgMember.findMany({
  where: { orgId },
  select: {
    id: true,
    role: true,
    user: { select: { id: true, displayName: true, avatarUrl: true } },
  },
});

Two columns of noise removed, one nested relation trimmed to three fields, and a type that says exactly what the page may render. Narrow selects also cut database work — less I/O, fewer rows serialized — but the security property is the reason to write it first.

Do aggregation in the database, not in JavaScript:

const stats = await prisma.order.aggregate({
  where: { orgId, createdAt: { gte: monthStart } },
  _count: { _all: true },
  _sum: { total: true },
  _avg: { total: true },
});

Fetching ten thousand rows to .reduce() them transfers all ten thousand and blocks the event loop; _sum/_avg/count() return one row. Use findUnique when a unique index guarantees one row — it asserts the cardinality in the type — and findFirst for the first match of a non-unique filter.

Pagination needs the same discipline:

const page = await prisma.order.findMany({
  where: { orgId },
  orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],   // tiebreaker makes pages stable
  skip: cursor ? 1 : 0,
  take: 25,
  ...(cursor ? { cursor: { id: cursor } } : {}),
});

orderBy without a unique tiebreaker lets two same-timestamp rows shuffle between pages, so records vanish or duplicate as you click. Deep skip offsets scan and discard rows on every request — for long lists switch to cursor (keyset) pagination, where the database seeks directly to the last id you saw.

Measure before you tune: enable the query log event, sort by total duration, and fix the statement that actually dominates. The schema you are querying was declared in Modeling Data with Prisma; the columns you just trimmed become constraints the moment Applying Schema Migrations drops one.

There is a second boundary worth mastering: Prisma's return values are not JSON. DateTime comes back as a JavaScript Date, and Decimal comes back as a class instance from decimal.js:

const order = await prisma.order.findUnique({ where: { id } });
order.createdAt instanceof Date;   // true
order.total.toString();            // "42.50" — JSON.stringify gives the string form

Two consequences follow. A Decimal handed from a Server Component to a Client Component fails React's serialization rule that only plain objects and supported types can cross the boundary — convert it on the server (order.total.toString()) or format it before passing. And an API route that JSON.stringifys whatever the ORM returned has silently chosen its wire format by accident; dates become ISO strings, decimals become strings, and undefined fields vanish. Decide the response shape once at the boundary and validate it with a Zod schema, so a column added to the model later cannot break a consumer that never opted into it.

A quick audit habit closes the topic: search your routes for findMany() and findUnique() calls with no select, then ask what each one actually renders. A list view needs two or three columns per row; a detail page may need a relation. Every unselected query is a guess that the whole row — including whatever gets added next quarter — belongs on the wire.