Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 36 ยท Projects and Practice

Prisma Practice Exercises

Practice Prisma with graded exercises on migrations, transactions, raw SQL, N+1, pagination and indexes, with hints, runnable stand-in checks and worked solutions.

Reading about an ORM is not the same as using it. These exercises cover the second half of this course: migrations, transactions, raw SQL, performance, testing and deployment. Try each one before opening the answer. Prisma needs the npm package and a database, so the Prisma answers are read-only; the runnable checks use node:sqlite stand-ins to prove the underlying idea.

Exercise 1: write the right migration (easy)

You renamed Post.body to Post.content in schema.prisma and ran prisma migrate dev --name rename_body. Prisma generated a drop and an add. What do you do?

Answer

Re-create the migration with --create-only and replace the generated SQL with ALTER TABLE "Post" RENAME COLUMN "body" TO "content"; before applying. See migration files and drift.

Exercise 2: choose the command (easy)

For each situation, name the command: (a) deploy to production, (b) throw away a messy local database and rebuild it from migrations, (c) prototype quickly with no history, (d) read an existing database into models.

Answer

(a) prisma migrate deploy; (b) prisma migrate reset; (c) prisma db push; (d) prisma db pull.

Exercise 3: idempotent seed (medium)

Make this seed safe to run repeatedly, then verify it by running it three times.

Node.js Exercise 3: fix the seed

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Answer

Use prisma.plan.upsert({ where: { slug }, update: { price }, create: { slug, price } }). In SQL: INSERT ... ON CONFLICT(slug) DO UPDATE SET price = excluded.price. Run the TryIt again with that statement and you get three ok lines and two rows.

Exercise 4: spot the N+1 (medium)

What is wrong with this code, and how many queries does it run for 200 orders?

TypeScript
const orders = await prisma.order.findMany();
const result = await Promise.all(
  orders.map(async (o) => ({
    ...o,
    customer: await prisma.customer.findUnique({ where: { id: o.customerId } }),
  })),
);
Answer

201 queries (1 plus 200). Prisma batches same-tick findUnique calls, which softens the cost, but the clean fix is prisma.order.findMany({ include: { customer: true } }), or select only the customer fields you display. See solving N+1.

Exercise 5: transaction or not? (medium)

Which of these need a transaction? (a) create a user and their profile with a nested write, (b) decrement stock and create an order line, (c) read one post by slug, (d) debit one account and credit another.

Answer

(a) already atomic because it is one nested write; (b) yes, use an interactive transaction with a conditional update; (c) no; (d) yes, array or interactive $transaction. See transactions.

Exercise 6: stop the lost update (hard)

Two admins edit the same document. Implement optimistic locking so the second save fails instead of overwriting. Complete the TODO and make the second save return false.

Node.js Exercise 6: optimistic locking

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Answer

UPDATE doc SET body = ?, version = version + 1 WHERE id = 1 AND version = ? with .run(body, seenVersion). In Prisma: updateMany({ where: { id, version: seenVersion }, data: { body, version: { increment: 1 } } }) and check count. See isolation and retries.

Exercise 7: safe raw SQL (medium)

This endpoint is vulnerable. Why, and how do you fix it?

TypeScript
const rows = await prisma.$queryRawUnsafe(
  `SELECT * FROM "Post" WHERE title LIKE '%${req.query.q}%' ORDER BY ${req.query.sort}`,
);
Answer

Both parts are injectable. Bind the search value (LIKE ${'%' + q + '%'} in a tagged $queryRaw) and map sort through an allow-list inserted with Prisma.raw. See raw queries.

Exercise 8: indexes (medium)

A page filters where: { status: 'published' } and orders by createdAt desc, paging with a cursor. Which index would you add, and what would you run to confirm it is used?

Answer

@@index([status, createdAt(sort: Desc)]) (add id as the tiebreaker if you page on it). Confirm with EXPLAIN or EXPLAIN ANALYZE that the plan shows an index scan instead of a sequential scan plus sort. See indexes and query plans.

Exercise 9: fix the pagination (hard)

Complete nextPage so it returns items and a nextCursor using keyset pagination without a second count query.

Node.js Exercise 9: keyset pagination

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Answer
JavaScript
const rows = db.prepare('SELECT * FROM note WHERE id > ? ORDER BY id LIMIT ?').all(cursor, limit + 1);
const hasMore = rows.length > limit;
const items = hasMore ? rows.slice(0, limit) : rows;
return { items, nextCursor: hasMore ? items.at(-1).id : null };

Expected pages: 1,2,3, 4,5,6, 7. See pagination.

Exercise 10: ship it (hard)

Write the ordered list of commands your CI pipeline runs for a Prisma app, and say which one must never run in production.

Answer

npm ci, npx prisma generate, build, test against a fresh database after npx prisma migrate deploy, then deploy and run npx prisma migrate deploy as the release step. Never in production: migrate dev, migrate reset, db push --accept-data-loss. See deploying Prisma apps.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which tool tells you whether a query uses an index?

    EXPLAIN shows the plan; look for index scans rather than sequential scans.
  2. 2. What makes a seed script safe to run repeatedly?

    Upsert updates or creates by unique key, so repeated runs give the same result.
  3. 3. Which approach prevents two buyers from taking the last item?

    The check and the decrement are one atomic statement.
  4. 4. Which command must never run against production?

    reset drops the database and replays migrations; it is a development tool.

Summary

  • Practice the full loop: change the schema, write a safe migration, query efficiently, write atomically, test and deploy.
  • Prefer atomic, set-based operations (upsert, conditional updateMany, include) over loops with read-then-write.
  • Verify performance claims with EXPLAIN and query logs, not intuition.
  • Return to the lessons linked above whenever an answer felt shaky.

Next: Back to the Prisma ORM course