Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 36 ยท Projects and Practice

Project: Inventory and Orders with Transactions

Build an order system where placing an order checks and decrements stock atomically, handles concurrent buyers, is idempotent with a unique key, and rolls back on failure.

Order systems expose every mistake a database tutorial warns about: overselling stock, charging twice, half-saved orders. In this project you build the core of one with Prisma: products with stock, orders with line items, and a placeOrder function that is atomic, safe under concurrency and idempotent.

Prisma cannot run on this site's runner, so the Prisma code is read-only and the runnable parts are labelled stand-ins on node:sqlite. The concepts come from transactions and isolation and retries.

The schema

PRISMA
model Product {
  id    Int         @id @default(autoincrement())
  sku   String      @unique
  name  String
  price Int                                   // cents, never floats for money
  stock Int         @default(0)
  items OrderItem[]
}

model Order {
  id             Int         @id @default(autoincrement())
  idempotencyKey String      @unique
  customerEmail  String
  status         String      @default("placed")
  totalCents     Int
  createdAt      DateTime    @default(now())
  items          OrderItem[]
}

model OrderItem {
  id        Int     @id @default(autoincrement())
  orderId   Int
  productId Int
  quantity  Int
  unitPrice Int                                // copy of the price at purchase time
  order     Order   @relation(fields: [orderId], references: [id], onDelete: Cascade)
  product   Product @relation(fields: [productId], references: [id])

  @@index([orderId])
}

Design decisions worth explaining: money is stored as integer cents; unitPrice is copied onto the item so later price changes do not rewrite history; idempotencyKey is unique so retrying the same request cannot create a second order. Add a database check constraint CHECK (stock >= 0) in a hand-edited migration as the final safety net (Prisma's schema language cannot express it; see migration files).

placeOrder with Prisma

TypeScript
async function placeOrder(input: { key: string; email: string; lines: { productId: number; qty: number }[] }) {
  const existing = await prisma.order.findUnique({ where: { idempotencyKey: input.key } });
  if (existing) return existing;                                   // retry of a finished request

  try {
    return await prisma.$transaction(async (tx) => {
      let total = 0;
      const items = [];
      for (const line of input.lines) {
        // Atomic conditional decrement: succeeds only if enough stock remains
        const updated = await tx.product.updateMany({
          where: { id: line.productId, stock: { gte: line.qty } },
          data: { stock: { decrement: line.qty } },
        });
        if (updated.count === 0) throw new OutOfStockError(line.productId);

        const product = await tx.product.findUniqueOrThrow({ where: { id: line.productId } });
        total += product.price * line.qty;
        items.push({ productId: product.id, quantity: line.qty, unitPrice: product.price });
      }
      return tx.order.create({
        data: { idempotencyKey: input.key, customerEmail: input.email, totalCents: total, items: { create: items } },
      });
    });
  } catch (e: any) {
    if (e.code === 'P2002') return prisma.order.findUniqueOrThrow({ where: { idempotencyKey: input.key } }); // concurrent duplicate
    throw e;
  }
}

Three techniques make it correct:

  1. Conditional update. updateMany with stock: { gte: qty } checks and decrements in one SQL statement, so two buyers can never both take the last item. count === 0 means "not enough stock".
  2. One transaction. If the second product is out of stock, the decrement of the first is rolled back.
  3. Idempotency key. If the client retries after a timeout, the first lookup returns the existing order, and a race between two identical requests is resolved by the unique constraint (P2002).

Stand-in: the whole flow

Node.js Stand-in: atomic orders, rollback and idempotency

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.

Simulating two buyers for the last item

JavaScript is single-threaded here, so the demo interleaves "buyers" step by step. The point is the difference between a check then update and a conditional update.

Node.js Stand-in: check-then-update oversells, conditional update does not

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.

Extending the project

  1. Add a cancelOrder function that restores stock in one transaction and sets status to cancelled; make it idempotent too.
  2. Add GET /orders?cursor= with cursor pagination and item details through select.
  3. Wrap placeOrder in the retry helper for P2034 if you raise the isolation level.
  4. Add a daily revenue report as a raw query grouped by date.
  5. Write a test that launches 20 concurrent placeOrder calls for a product with stock 5 and asserts exactly 5 succeed.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is updateMany with stock: { gte: qty } safer than reading stock and then updating?

    Two concurrent buyers cannot both pass the check, because the database applies the condition while updating.
  2. 2. What does the unique idempotencyKey protect against?

    A retry finds the existing order or fails the unique constraint instead of duplicating it.
  3. 3. Why copy unitPrice onto OrderItem?

    An order records what the customer actually paid at that moment.
  4. 4. What happens to the first product's stock decrement if the second product is out of stock?

    Throwing inside the interactive transaction rolls back every write made in it.

Summary

  • Store money as integer cents and snapshot prices on order items.
  • Use a conditional updateMany to check and decrement stock atomically.
  • Wrap the whole order in one transaction so failures leave nothing half-done.
  • Use a unique idempotency key so retries are safe.
  • A CHECK (stock >= 0) constraint is a cheap last line of defense.

Next: Prisma Practice Exercises