Prisma ORM Basics: Schema, Queries and Real-World Patterns

// datasource tells Prisma which database engine to use and where to find it.
// The DATABASE_URL is read from your .env file — never hardcode credentials.
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// generator tells Prisma what to produce when you run `prisma generate`.
// The output is the fully-typed PrismaClient you import in your app.
generator client {
  provider = "prisma-client-js"
}

// A model maps to a database table.
// Field names become the column names (snake_case in DB via @@map if needed).
model User {
  id        Int      @id @default(autoincrement()) // Primary key, auto-incremented
  email     String   @unique                       // Unique constraint on the column
  name      String
  createdAt DateTime @default(now())               // Set automatically on INSERT
  orders    Order[]                                // Relation: one User has many Orders
}

model Order {
  id         Int      @id @default(autoincrement())
  totalPrice Float
  placedAt   DateTime @default(now())
  userId     Int                                   // Foreign key column in the DB
  user       User     @relation(fields: [userId], references: [id]) // Relation scalar
  items      OrderItem[]
}

model OrderItem {
  id        Int    @id @default(autoincrement())
  productName String
  quantity  Int
  unitPrice Float
  orderId   Int
  order     Order  @relation(fields: [orderId], references: [id])
}

import { PrismaClient } from '@prisma/client';

// Instantiate once and reuse — creating a new PrismaClient per request
// opens a new connection pool each time, which will exhaust your DB connections fast.
const prisma = new PrismaClient();

async function runOrderExamples() {

  // ── CREATE ──────────────────────────────────────────────────────────
  // Nested write: creates the User AND their first Order in a single transaction.
  // If the Order insert fails, the User insert is rolled back automatically.
  const newUser = await prisma.user.create({
    data: {
      email: 'alice@example.com',
      name: 'Alice Chen',
      orders: {
        create: [
          {
            totalPrice: 49.99,
            items: {
              create: [
                { productName: 'Mechanical Keyboard', quantity: 1, unitPrice: 49.99 }
              ]
            }
          }
        ]
      }
    },
    // include tells Prisma to JOIN and return the related orders in the response.
    // Without this, `newUser.orders` would be undefined.
    include: { orders: { include: { items: true } } }
  });

  console.log('Created user:', JSON.stringify(newUser, null, 2));

  // ── READ (findUnique) ────────────────────────────────────────────────
  // findUnique requires a @unique or @id field — it throws a runtime error
  // if you try to filter on a non-unique field, which catches logic bugs early.
  const existingUser = await prisma.user.findUnique({
    where: { email: 'alice@example.com' },
    select: {
      id: true,
      name: true,
      // Nested select: only fetch order IDs and totals — no unnecessary data
      orders: {
        select: { id: true, totalPrice: true, placedAt: true }
      }
    }
  });

  console.log('Found user:', existingUser);

  // ── READ (findMany with filter) ──────────────────────────────────────
  // Find all orders over £20 placed in the last 7 days, sorted newest first.
  const recentLargeOrders = await prisma.order.findMany({
    where: {
      totalPrice: { gt: 20 },                            // gt = greater than
      placedAt:   { gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000) } // last 7 days
    },
    orderBy: { placedAt: 'desc' },
    take: 10,                                            // LIMIT 10 — pagination
    include: { user: { select: { name: true, email: true } } }
  });

  console.log(`Found ${recentLargeOrders.length} recent large orders`);

  // ── UPDATE ──────────────────────────────────────────────────────────
  // update requires a unique identifier in `where`. Prisma won't let you
  // run a bulk update without using updateMany — accidental mass-updates
  // are a class of bug Prisma's API actively resists.
  const updatedUser = await prisma.user.update({
    where: { email: 'alice@example.com' },
    data:  { name: 'Alice Zhang' }          // Only this field changes
  });

  console.log('Updated name:', updatedUser.name);

  // ── DELETE ──────────────────────────────────────────────────────────
  // Always clean up in reverse dependency order, or configure cascading deletes
  // in your schema. Prisma won't silently ignore FK constraint violations.
  await prisma.orderItem.deleteMany({ where: { order: { userId: newUser.id } } });
  await prisma.order.deleteMany({     where: { userId: newUser.id } });
  await prisma.user.delete({          where: { id: newUser.id } });

  console.log('Cleanup complete.');
}

runOrderExamples()
  .catch(console.error)
  .finally(() => prisma.$disconnect()); // Always disconnect when the script ends

import { PrismaClient, Prisma } from '@prisma/client';

const prisma = new PrismaClient();

// Real-world checkout: deduct stock and create the order atomically.
// If the stock deduction fails (e.g. stock hits 0), the order is not created.
async function processCheckout(userId: number, productId: number, quantity: number) {

  // Interactive transaction — we need the updated stock value BEFORE creating the order.
  const result = await prisma.$transaction(async (tx) => {
    // Step 1: Check and decrement stock.
    // updateMany with a conditional where clause prevents overselling without a race condition.
    const stockUpdate = await tx.product.updateMany({
      where: {
        id:    productId,
        stock: { gte: quantity }  // Only update if enough stock exists RIGHT NOW
      },
      data: {
        stock: { decrement: quantity }
      }
    });

    // If count is 0, the WHERE clause didn't match — stock was insufficient.
    if (stockUpdate.count === 0) {
      // Throwing inside a transaction automatically triggers a rollback.
      throw new Error(`Insufficient stock for product ${productId}`);
    }

    // Step 2: Fetch the product price (we need it for the order total).
    const product = await tx.product.findUnique({
      where:  { id: productId },
      select: { name: true, price: true }
    });

    if (!product) throw new Error(`Product ${productId} not found`);

    // Step 3: Create the order now that we've confirmed and reserved stock.
    const order = await tx.order.create({
      data: {
        userId,
        totalPrice: product.price * quantity,
        items: {
          create: [{
            productName: product.name,
            quantity,
            unitPrice: product.price
          }]
        }
      },
      include: { items: true }
    });

    return order; // This value becomes the resolved value of the transaction
  });

  console.log('Order placed successfully:', result.id);
  return result;
}

// ── RAW QUERY ────────────────────────────────────────────────────────────────
// Use $queryRaw when you need something Prisma's API can't express —
// here, a revenue report grouped by month using PostgreSQL's date_trunc.
async function getMonthlyRevenue(year: number) {
  // Prisma.sql is a tagged template literal — values are ALWAYS parameterised.
  // Never use string concatenation here: `WHERE year = ${year}` would be safe,
  // but `WHERE name = '${userInput}'` would be an injection vulnerability.
  const rows = await prisma.$queryRaw<{ month: Date; revenue: number }[]>(
    Prisma.sql`
      SELECT
        date_trunc('month', placed_at) AS month,
        SUM(total_price)               AS revenue
      FROM orders
      WHERE EXTRACT(YEAR FROM placed_at) = ${year}
      GROUP BY month
      ORDER BY month ASC
    `
  );

  console.log(`Revenue for ${year}:`);
  rows.forEach(row => {
    const monthLabel = row.month.toLocaleString('default', { month: 'long' });
    console.log(`  ${monthLabel}: £${Number(row.revenue).toFixed(2)}`);
  });

  return rows;
}

// Run examples
processCheckout(1, 42, 2)
  .then(() => getMonthlyRevenue(2024))
  .catch(console.error)
  .finally(() => prisma.$disconnect());

# ── LOCAL DEVELOPMENT ────────────────────────────────────────────────────────

# Step 1: You've added a new `phoneNumber` field to the User model in schema.prisma.
# Run migrate dev to generate and immediately apply the migration to your local DB.
npx prisma migrate dev --name add_phone_number_to_users

# Prisma will output the generated SQL so you can review it:
# ALTER TABLE "User" ADD COLUMN "phoneNumber" TEXT;
# Always read this output — migrations are permanent.

# Step 2: After migrate dev, regenerate the client so your TypeScript types
# include the new `phoneNumber` field immediately.
# (migrate dev actually does this automatically, but it's good to know the standalone command)
npx prisma generate

# Step 3: Explore your data mid-development with Prisma Studio — a local browser UI.
# Useful for manually inspecting rows without writing throwaway scripts.
npx prisma studio
# Opens at http://localhost:5555

# ── SEEDING ───────────────────────────────────────────────────────────────────
# Run your seed script (defined in package.json under prisma.seed) to
# populate the DB with realistic test data after a fresh migration.
npx prisma db seed

# ── CI / PRODUCTION DEPLOYMENT ───────────────────────────────────────────────
# In your Dockerfile or CI pipeline, apply pending migrations non-interactively.
# This command NEVER creates new migrations — it only applies committed ones.
npx prisma migrate deploy

# ── CHECKING SYNC ─────────────────────────────────────────────────────────────
# If you suspect your DB is out of sync with your schema (e.g. a direct DB edit),
# this command shows the diff without making any changes. Read-only diagnostic.
npx prisma migrate status