15.2 Prisma CRUD — Complete Type-Safe Queries

Create

import { prisma } from './lib/prisma'

// Create single record
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
  },
})
// Return type: User (auto-inferred)

// Create with relations (nested writes)
const userWithPost = await prisma.user.create({
  data: {
    email: 'bob@example.com',
    name: 'Bob',
    posts: {
      create: [
        { title: 'First Post', content: 'Content...' },
        { title: 'Second Post' },
      ],
    },
    profile: {
      create: { bio: 'Hello!' },
    },
  },
  include: {
    posts: true,
    profile: true,
  },
})

// Create multiple records at once
const result = await prisma.user.createMany({
  data: [
    { email: 'charlie@example.com', name: 'Charlie' },
    { email: 'diana@example.com', name: 'Diana' },
  ],
  skipDuplicates: true,  // Ignore duplicates
})
console.log(result.count)  // Number of created records

---

Read

// Single record — returns null if not found
const user = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
})

// Single record — throws if not found
const user2 = await prisma.user.findUniqueOrThrow({
  where: { id: 1 },
})

// First matching record
const firstUser = await prisma.user.findFirst({
  where: { name: { contains: 'Alice' } },
  orderBy: { createdAt: 'desc' },
})

// Multiple records
const users = await prisma.user.findMany({
  where: {
    AND: [
      { email: { contains: '@example.com' } },
      { createdAt: { gte: new Date('2024-01-01') } },
    ],
  },
  orderBy: [
    { name: 'asc' },
    { createdAt: 'desc' },
  ],
  skip: 0,   // Pagination
  take: 10,
})

// Select specific fields
const userNames = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
  },
})
// Return type: { id: number; name: string | null; email: string }[]

// Include relations
const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
    profile: true,
  },
})

---

Update

// Update single record
const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: {
    name: 'Alice Updated',
  },
})

// Atomic numeric operations
const post = await prisma.post.update({
  where: { id: 1 },
  data: {
    viewCount: { increment: 1 },
    likeCount: { decrement: 1 },
  },
})

// Update with relations
const userWithNewPost = await prisma.user.update({
  where: { id: 1 },
  data: {
    posts: {
      create: { title: 'New Post' },
      connect: { id: 5 },       // Connect existing post
      disconnect: { id: 3 },    // Disconnect
      deleteMany: { published: false },  // Conditional delete
    },
  },
  include: { posts: true },
})

// Update multiple records
const result = await prisma.user.updateMany({
  where: { email: { contains: '@old-domain.com' } },
  data: { name: 'Migrated User' },
})
console.log(result.count)

// upsert — update if exists, create if not
const upserted = await prisma.user.upsert({
  where: { email: 'new@example.com' },
  update: { name: 'Updated Name' },
  create: { email: 'new@example.com', name: 'New User' },
})

---

Delete

// Delete single record
const deleted = await prisma.user.delete({
  where: { id: 1 },
})

// Delete multiple records
const result = await prisma.post.deleteMany({
  where: {
    published: false,
    createdAt: { lt: new Date('2023-01-01') },
  },
})
console.log(result.count)

---

Aggregate

// Count
const count = await prisma.user.count()
const publishedCount = await prisma.post.count({
  where: { published: true },
})

// Numeric aggregations
const stats = await prisma.post.aggregate({
  _count: { id: true },
  _avg: { viewCount: true },
  _max: { viewCount: true },
  _min: { viewCount: true },
  _sum: { viewCount: true },
})

// Group by
const postsByUser = await prisma.post.groupBy({
  by: ['authorId'],
  _count: { id: true },
  _avg: { viewCount: true },
  having: {
    viewCount: { _avg: { gt: 100 } },  // Average viewCount > 100
  },
  orderBy: {
    _count: { id: 'desc' },
  },
})

---

Pro Tips

Type-safe Dynamic Filter Pattern

import { Prisma } from '@prisma/client'

// Build dynamic filters
function buildUserFilter(params: {
  search?: string
  role?: 'ADMIN' | 'USER'
  active?: boolean
}): Prisma.UserWhereInput {
  return {
    ...(params.search && {
      OR: [
        { name: { contains: params.search, mode: 'insensitive' } },
        { email: { contains: params.search, mode: 'insensitive' } },
      ],
    }),
    ...(params.role && { role: params.role }),
    ...(params.active !== undefined && { isActive: params.active }),
  }
}

const users = await prisma.user.findMany({
  where: buildUserFilter({ search: 'alice', role: 'USER' }),
})