Skip to content

Prisma and ORM pitfalls: transactions, raw SQL, over-fetching, connections

An ORM call is SQL on a borrowed connection: see the SQL, select what you need, and keep slow work outside the transaction.

Predict first

Before you read: predict

ExamHall's API runs Prisma 7 with a pool of 3 connections. Final submit:

The grading vendor stalls. Three candidates' submits are waiting on notify. A fourth candidate presses Submit.

What happens to the fourth submit?

Code for this question
async function submit(id) {  await prisma.$transaction(async (tx) => {    await tx.submission.update({ where: { id }, data: { status: 'submitted' } });    await gradingVendor.notify(id);  }, { timeout: 30_000 });}
Pick one

The 30-second version

Every Prisma call is SQL: log it, and EXPLAIN what matters. Without select, Prisma reads every column. An interactive $transaction holds a pooled connection from before its first line to its end, so an awaited HTTP call inside it holds that connection and its locks. Prisma bounds both ends: maxWait to start, timeout to finish. One PrismaClient, one pool.

An ORM call is SQL on a borrowed connection: see the SQL, select what you need, and keep slow work outside the transaction.

How it works

Step 1 of 13

A call is SQL. Each Prisma call sends one or more statements. log: [{ emit: 'event', level: 'query' }] with prisma.$on('query', …) hands you each statement and its parameters.
In the kitchen
  • asking at the desk returns a slip saying exactly which court and slot was booked → the query log shows each statement (step 1)

Analogy: A squash club's courts (racket sports)

The club's front desk books courts for members; members never see the court diary.

At the squash club In Prisma
asking at the desk returns a slip saying exactly which court and slot was booked the query log shows each statement (step 1)
asking "who is on court 2?" and getting each player's whole membership file no select reads every column (step 2)
a match booking: the key is yours from the moment you take it until you hand it back, even while you step out to phone someone the interactive callback holds its connection from before its first line (step 5)
a player waiting at the desk for a match court is sent away after two minutes, while someone dropping off a form just queues maxWait on a transaction's start; a plain query only queues (step 7)
at the end of the booked hour the attendant clears the court and wipes the scoreboard, but the phone call you made is still made timeout rolls back; work outside the database stays done (steps 8–9)
each new branch of the club builds its own courts each PrismaClient opens its own pool (step 11)

In real terms: take the connection only for the statements, and do the phoning outside.

Breaks down when: a court is booked for a fixed hour chosen in advance; a transaction lasts exactly as long as the code takes, and the desk cannot see that the player inside has stopped playing.

Examples, run for real

4.1 ExamHall EXH-3850 → MR !4150: final submit and the slow grading vendor

ExamHall's final submit called gradingVendor.notify() inside prisma.$transaction. At 10:00 the vendor slowed down, and all 20 pooled connections sat in transactions waiting on it. MR !4150 commits the status and a pending-notification row with an array $transaction([...]); a worker sends the notifications from that table:

4.1 shipped (notify inside the callback), pool max 20: 20 submits waiting on notify · connections out 20 of 20 · submit 21: P2028 Unable to start a transaction in the given time. · dashboard findMany: still queued in the pool when submit 21 failed, answered (10 rows) after the vendor recovered
4.1 MR !4150 (array $transaction, then a worker calls notify): 21 of 21 submits committed while the vendor stalls · connections out during the stall 0 of 20 · pending notification rows 21 (counted on every connection's database)

With the call inside the callback, every connection waits on the vendor, so the next submit cannot start its transaction and a plain dashboard query queues behind them (steps 5, 7). After MR !4150 the same stall holds no connection: submits commit at once and the notifications wait in a table (steps 4, 10).

4.2 Ticketing: the seat map, as Prisma sends it

The box office logs the statements its seat-map query sends, before and after adding select:

4.2 findMany, no select: SELECT id, event_id, label, status, held_by FROM seats WHERE (event_id = $1 AND status = $2) OFFSET $3 · params ["ev-1","free","0"] · 5 columns
4.2 findMany, select { id, label }: SELECT id, label FROM seats WHERE (event_id = $1 AND status = $2) OFFSET $3 · 2 columns

The first statement reads every column of seats although the page shows two; select names the two (steps 1–2). One findMany is one statement here; its plan is read the same way (step 3).

Try it

Answer first, then check. Nothing is saved.

Question 1 of 7

ExamHall's final submit sets a 2-second transaction timeout:

At 10:00 the vendor is slow: it answers notify OK after 9 seconds.

Afterwards, what is true?

Code for this question
async function submit(id) {  await prisma.$transaction(async (tx) => {    await tx.submission.update({ where: { id }, data: { status: 'submitted' } });    await gradingVendor.notify(id);  }, { timeout: 2_000 });}
Pick one

Question 2 of 7

A box office's API runs Prisma 7 with a pool of 2 connections. An MR moves the payment call to the top of the callback, "so no seat is locked while the provider answers":

The provider stalls with two buyers' holds waiting on it. A third buyer taps a seat.

What happens to the third hold?

Code for this question
async function hold(seatId, buyer) {  await prisma.$transaction(async (tx) => {    await psp.createPaymentIntent(buyer);    await tx.seat.update({ where: { id: seatId }, data: { status: 'held', heldBy: buyer } });  }, { timeout: 30_000 });}
Pick one

Question 3 of 7

Proctors search for candidates whose email contains the text they type. candidates holds 5,000 rows and has a unique index on email:

With query logging on, the team copies the statement Prisma sent and runs EXPLAIN on it with its parameter.

What do they see?

Code for this question
const found = await prisma.candidate.findMany({  where: { email: { contains: typed } },});
Pick one

Question 4 of 7

The proctor dashboard shows each submission's status for exam E2:

submissions has seven columns, answers and feedback among them, each 1,000 characters or more.

Which columns does the statement Prisma sends read?

Code for this question
const rows = await prisma.submission.findMany({ where: { examId: 'E2' } });return rows.map((s) => ({ id: s.id, status: s.status }));
Pick one

Question 5 of 7

A box office's seat-map route, in one Node process:

The route serves 5 requests, one after another.

How many connections does the database accept?

Code for this question
export async function GET() {  const prisma = new PrismaClient({ adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }) });  const free = await prisma.seat.findMany({ where: { eventId: 'ev-1', status: 'free' } });  const held = await prisma.seat.count({ where: { eventId: 'ev-1', status: 'held' } });  return Response.json({ free, held });}
Pick one

Question 6 of 7

A box office's refund must mark the order refunded and write a ledger row together, then call the payment provider's refund.

Which version holds no connection while psp.refund is awaited, and leaves the order unchanged if the ledger insert fails?

Pick one

Question 7 of 7

A box office upgrades from Prisma 6 to Prisma 7.10. DATABASE_URL is set as before, and its client module still reads:

What happens when the module loads and the first seat query runs?

Code for this question
export const prisma = new PrismaClient();
Pick one

Go deeper

Pro tip
  1. In an integration test, wrap the call under test with the query log on and fail when a statement reads a column the endpoint never returns.
    const seen = [];
prisma.$on('query', (e) => seen.push(e.query));
await getDashboard('E2');
expect(seen.join('\n')).not.toMatch(/"answers"/);
    When: a list endpoint over a table with large text or JSON columns (steps 1–2). Status: SELF (Prisma's logging page shows the query event; not run as a graded item).
Source

quote-check.mjs checks every quotation below verbatim in its saved page. Every key in this lesson is executed; these passages support the teaching and name the defaults the scripts confirm. Valid as of 2026-10-13: the Prisma docs now default to Prisma ORM 8 and keep the ORM 7 pages under /orm/v7; this lesson is written for Prisma ORM 7 (the row's version) and runs 7.10.0.

Related lessons

Checked against the tools and docs as of 2026-10-13.