Prisma Database

Q: How is query performance with millions of records?

Prisma's findMany combined with skip / take generates LIMIT / OFFSET pagination, which slows down when the OFFSET value is large for datasets in the millions. For large datasets, use cursor-based pagination ( cursor + take ).

Q: How do I run the seed script?

Add "prisma": { "seed": "npx ts-node prisma/seed.ts" } to package.json, then run npx prisma db seed .

MegaShop has always used mock data—product data stored in JavaScript arrays, which is lost upon restart. Charlie needs a real database. The native SQL written by Bob is prone to errors and lacks type safety. Prisma ORM provides type-safe database queries and generates TypeScript types with just one line of code.

1. What You'll Learn

Prisma Installation and Initialization: schema.prisma + generate + migrate
Data Model Design: Relationships between the Product, Category, User, Order, and CartItem tables
Nuxt Integration: server/utils/prisma.ts Singleton Connection
CRUD operations: findMany/create/update/delete + transactions + aggregates
MegaShop: Paginated Search for Millions of Products + Order Transactions

2. A True Story of an Architect

(1) Pain Point: Mock data cannot be persisted

After Bob restarted the MegaShop development server, all product data disappeared. All the products Alice had added and the orders she had placed were gone. To make matters worse, there was no database in the production environment—there was nowhere to store the data for millions of products.

(2) Prisma ORM Solution

Prisma provides type-safe database operations and automatically generates TypeScript types:

TYPESCRIPT

// Type-safe query - no raw SQL
const products = await prisma.product.findMany({
  where: { category: { slug: 'electronics' } },
  take: 20,
  skip: (page - 1) * 20
})

(3) Benefits: Type Safety + Data Persistence

Product data is stored persistently, and API queries feature full type inference—so Bob will never misspell a field name again, and Alice's order will still be there after a restart.

3. Prisma Installation and Initialization

(1) ▶ Example: Installing Prisma

BASH

npm install prisma @prisma/client
npx prisma init

TEXT

# Command executed successfully

(2) ▶ Example: Prisma Schema

PRISMA

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Product {
  id          Int       @id @default(autoincrement())
  name        String
  slug        String    @unique
  description String?
  price       Decimal   @db.Decimal(10, 2)
  image       String?
  inStock     Boolean   @default(true)
  rating      Float     @default(0)
  reviewCount Int       @default(0)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  categoryId  Int
  category    Category  @relation(fields: [categoryId], references: [id])
  orderItems  OrderItem[]
  cartItems   CartItem[]

  @@index([categoryId])
  @@index([slug])
  @@index([price])
}

model Category {
  id        Int       @id @default(autoincrement())
  name      String
  slug      String    @unique
  parentId  Int?
  parent    Category? @relation("CategoryTree", fields: [parentId], references: [id])
  children  Category[] @relation("CategoryTree")
  products  Product[]

  @@index([slug])
}

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  password  String?
  avatar    String?
  provider  String    @default("email")
  role      Role      @default(CUSTOMER)
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  orders     Order[]
  cartItems  CartItem[]

  @@index([email])
}

model Order {
  id          Int       @id @default(autoincrement())
  userId      Int
  user        User      @relation(fields: [userId], references: [id])
  total       Decimal   @db.Decimal(10, 2)
  status      OrderStatus @default(PENDING)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  items       OrderItem[]

  @@index([userId])
  @@index([status])
}

model OrderItem {
  id        Int     @id @default(autoincrement())
  orderId   Int
  order     Order   @relation(fields: [orderId], references: [id])
  productId Int
  product   Product @relation(fields: [productId], references: [id])
  quantity  Int
  price     Decimal @db.Decimal(10, 2)

  @@index([orderId])
}

model CartItem {
  id        Int     @id @default(autoincrement())
  userId    Int
  user      User    @relation(fields: [userId], references: [id])
  productId Int
  product   Product @relation(fields: [productId], references: [id])
  quantity  Int     @default(1)

  @@unique([userId, productId])
}

enum Role {
  CUSTOMER
  ADMIN
}

enum OrderStatus {
  PENDING
  PAID
  SHIPPED
  DELIVERED
  CANCELLED
}

TEXT

// Execution Successful

(1) MegaShop ER Diagram

erDiagram
    Product ||--o{ OrderItem : "included in"
    Product ||--o{ CartItem : "added to"
    Product }o--|| Category : "belongs to"
    Category ||--o{ Category : "parent-child"
    User ||--o{ Order : "places"
    User ||--o{ CartItem : "has"
    Order ||--o{ OrderItem : "contains"

4. Nuxt Integration

(1) ▶ Example: Prisma Singleton Connection

TYPESCRIPT

// server/utils/prisma.ts
import { PrismaClient } from '@prisma/client'

// Singleton pattern - prevent multiple instances in dev
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient }

export const prisma = globalForPrisma.prisma || new PrismaClient({
  log: process.env.NODE_ENV === 'development' ? ['query', 'error', 'warn'] : ['error']
})

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma
}

TEXT

// Execution Successful

(2) ▶ Example: Nitro Pre-built Prisma Client

TYPESCRIPT

// nuxt.config.ts
export default defineNuxtConfig({
  nitro: {
    // Generate Prisma Client before build
    externals: {
      inline: ['.prisma/client']
    }
  },
  hooks: {
    'build:before': async () => {
      const { execSync } = await import('child_process')
      execSync('npx prisma generate')
    }
  }
})

TEXT

// Execution Successful

5. CRUD Operations

(1) ▶ Example: Paginated Queries for Product Lists

TYPESCRIPT

// server/api/products/index.get.ts
export default defineEventHandler(async (event) => {
  const query = getQuery(event)
  const page = Number(query.page) || 1
  const limit = Number(query.limit) || 20
  const category = query.category as string
  const search = query.search as string
  const minPrice = Number(query.minPrice) || 0
  const maxPrice = Number(query.maxPrice) || Infinity

  const where = {
    AND: [
      category ? { category: { slug: category } } : {},
      search ? { name: { contains: search, mode: 'insensitive' as const } } : {},
      { price: { gte: minPrice } },
      maxPrice < Infinity ? { price: { lte: maxPrice } } : {}
    ]
  }

  const [items, total] = await Promise.all([
    prisma.product.findMany({
      where,
      skip: (page - 1) * limit,
      take: limit,
      include: { category: { select: { name: true, slug: true } } },
      orderBy: { createdAt: 'desc' }
    }),
    prisma.product.count({ where })
  ])

  return { items, total, page, limit }
})

TEXT

// Execution Successful

(2) ▶ Example: Product Details Query

TYPESCRIPT

// server/api/products/[id].get.ts
export default defineEventHandler(async (event) => {
  const id = Number(getRouterParam(event, 'id'))

  const product = await prisma.product.findUnique({
    where: { id },
    include: {
      category: { select: { name: true, slug: true } }
    }
  })

  if (!product) {
    throw createError({ statusCode: 404, message: 'Product not found' })
  }

  return product
})

TEXT

// Execution Successful

(3) ▶ Example: Creating an Order (Transaction)

TYPESCRIPT

// server/api/orders/index.post.ts
export default defineEventHandler(async (event) => {
  const userId = event.context.user?.id
  const { items } = await readBody(event)

  // Transaction: create order + update stock + clear cart
  const order = await prisma.$transaction(async (tx) => {
    // Calculate total
    let total = 0
    const orderItems = []

    for (const item of items) {
      const product = await tx.product.findUnique({ where: { id: item.productId } })
      if (!product || !product.inStock) {
        throw createError({ statusCode: 400, message: `Product ${item.productId} unavailable` })
      }
      total += Number(product.price) * item.quantity
      orderItems.push({
        productId: product.id,
        quantity: item.quantity,
        price: product.price
      })
    }

    // Create order
    const newOrder = await tx.order.create({
      data: {
        userId,
        total,
        items: { create: orderItems }
      },
      include: { items: { include: { product: true } } }
    })

    // Clear user cart
    await tx.cartItem.deleteMany({ where: { userId } })

    return newOrder
  })

  return { order, message: 'Order created successfully' }
})

TEXT

// Execution Successful

(4) ▶ Example: Aggregate Query—Product Statistics

TYPESCRIPT

// server/api/admin/stats.get.ts
export default defineEventHandler(async () => {
  const [
    totalProducts,
    totalUsers,
    totalOrders,
    revenue,
    avgPrice
  ] = await Promise.all([
    prisma.product.count(),
    prisma.user.count(),
    prisma.order.count(),
    prisma.order.aggregate({
      _sum: { total: true },
      where: { status: 'PAID' }
    }),
    prisma.product.aggregate({
      _avg: { price: true }
    })
  ])

  return {
    totalProducts,
    totalUsers,
    totalOrders,
    totalRevenue: revenue._sum.total || 0,
    averagePrice: avgPrice._avg.price || 0
  }
})

TEXT

// Execution Successful

6. Database Migration

(1) ▶ Example: Prisma migration command

BASH

# Create migration from schema changes
npx prisma migrate dev --name init

# Apply migrations in production
npx prisma migrate deploy

# Reset database (dev only!)
npx prisma migrate reset

# Generate Prisma Client
npx prisma generate

# Open Prisma Studio (visual DB browser)
npx prisma studio

# Seed database with test data
npx prisma db seed

TEXT

# Command executed successfully

(2) ▶ Example: Seed Data

TYPESCRIPT

// prisma/seed.ts
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

async function main() {
  // Create categories
  const electronics = await prisma.category.create({
    data: { name: 'Electronics', slug: 'electronics' }
  })
  const clothing = await prisma.category.create({
    data: { name: 'Clothing', slug: 'clothing' }
  })

  // Create products
  for (let i = 1; i <= 1000; i++) {
    await prisma.product.create({
      data: {
        name: `Product ${i}`,
        slug: `product-${i}`,
        price: Math.round(Math.random() * 500 * 100) / 100,
        categoryId: i % 2 === 0 ? electronics.id : clothing.id,
        inStock: Math.random() > 0.2,
        rating: Math.round(Math.random() * 5 * 10) / 10,
        reviewCount: Math.floor(Math.random() * 5000)
      }
    })
  }

  // Create admin user
  await prisma.user.create({
    data: {
      email: 'bob@megashop.com',
      name: 'Bob Admin',
      role: 'ADMIN'
    }
  })

  console.log('Seed completed: 1 thousand products + 2 categories + 1 admin')
}

main()

TEXT

// Execution Successful

7. Comprehensive Example: MegaShop Database Integration

TEXT

# .env
DATABASE_URL="postgresql://megashop:password@localhost:5432/megashop"
REDIS_URL="redis://localhost:6379"
JWT_ACCESS_SECRET="your-access-secret"
JWT_REFRESH_SECRET="your-refresh-secret"

TYPESCRIPT

// server/api/products/search.get.ts - Advanced search
export default defineEventHandler(async (event) => {
  const { q, category, minPrice, maxPrice, sort, page, limit } = getQuery(event)

  const where = {
    AND: [
      q ? { OR: [{ name: { contains: q as string, mode: 'insensitive' } }, { description: { contains: q as string, mode: 'insensitive' } }] } : {},
      category ? { category: { slug: category as string } } : {},
      minPrice ? { price: { gte: Number(minPrice) } } : {},
      maxPrice ? { price: { lte: Number(maxPrice) } } : {}
    ]
  }

  const orderBy: any = sort === 'price-asc' ? { price: 'asc' }
    : sort === 'price-desc' ? { price: 'desc' }
    : sort === 'rating' ? { rating: 'desc' }
    : { createdAt: 'desc' }

  const [items, total] = await Promise.all([
    prisma.product.findMany({
      where, orderBy,
      skip: ((Number(page) || 1) - 1) * (Number(limit) || 20),
      take: Number(limit) || 20,
      include: { category: { select: { name: true, slug: true } } }
    }),
    prisma.product.count({ where })
  ])

  return { items, total, page: Number(page) || 1, limit: Number(limit) || 20 }
})

❓ FAQ

Q What is the difference between Prisma and TypeORM?

A Prisma uses a declarative schema and generates client-side code, offering more comprehensive type inference. TypeORM uses decorators and is closer to a traditional ORM. The Nuxt 3 community recommends Prisma.

Q Why does the Prisma singleton need a global variable?

A In the development environment, Nuxt's hot reload re-executes the server code; without a global variable, multiple PrismaClient instances would be created, leading to connection leaks. This issue does not exist in the production environment.

Q How is query performance with millions of records?

A Prisma's findMany combined with skip/take generates LIMIT/OFFSET pagination, which slows down when the OFFSET value is large for datasets in the millions. For large datasets, use cursor-based pagination (cursor + take).

Q Does Prisma support MongoDB?

A Yes, but it's a preview feature. PostgreSQL is Prisma's best match and offers the most complete set of features. MegaShop recommends PostgreSQL.

Q Can multiple write operations be performed within a transaction?

A Yes. You can perform any number of read and write operations within prisma.$transaction; the transaction is committed only if all operations succeed, and rolled back if any one fails.

Q How do I run the seed script?

A Add "prisma": { "seed": "npx ts-node prisma/seed.ts" } to package.json, then run npx prisma db seed.

📖 Summary

Prisma offers schema definitions, automated migration, type-safe queries, and a visual Studio
server/utils/prisma.ts implements a singleton connection to prevent connection leaks in the development environment
CRUD: findMany with pagination + findUnique for details + create + update + delete
$transaction ensures that order creation, inventory updates, and shopping cart clearance are performed atomically
Data sets in the millions: Index optimization + cursor pagination + aggregate queries for statistics

📝 Exercises

Basic Exercise (Difficulty ⭐): Install Prisma and PostgreSQL, create the Product and Category models, and run migrate and seed.
Advanced Exercise (Difficulty: ⭐⭐): Implement a complete product CRUD API, replacing the previous mock data, and support pagination and category filtering.
Challenge (Difficulty: ⭐⭐⭐): Implement an order transaction—create an order, deduct inventory, and clear the shopping cart—all within a single transaction; if any step fails, roll back the entire transaction.

---|

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

Prisma Database

1. What You'll Learn

2. A True Story of an Architect

(1) Pain Point: Mock data cannot be persisted

(2) Prisma ORM Solution

(3) Benefits: Type Safety + Data Persistence

3. Prisma Installation and Initialization

(1) ▶ Example: Installing Prisma

(2) ▶ Example: Prisma Schema

(1) MegaShop ER Diagram

4. Nuxt Integration

(1) ▶ Example: Prisma Singleton Connection

(2) ▶ Example: Nitro Pre-built Prisma Client

5. CRUD Operations

(1) ▶ Example: Paginated Queries for Product Lists

(2) ▶ Example: Product Details Query

(3) ▶ Example: Creating an Order (Transaction)

(4) ▶ Example: Aggregate Query—Product Statistics

6. Database Migration

(1) ▶ Example: Prisma migration command

(2) ▶ Example: Seed Data

7. Comprehensive Example: MegaShop Database Integration

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好