Prisma CRUD & Queries

Once PrismaService is wired into your NestJS application, every model in your schema.prisma becomes a fully typed property on the client — prisma.user, prisma.post, and so on. Each exposes the same small, predictable set of methods for creating, reading, updating, and deleting rows. Because the types are generated from your schema, the compiler knows exactly which fields exist, which are required, and what shape a query returns, so a typo or a missing relation is a build error rather than a 2 a.m. production incident. This page shows the everyday CRUD patterns plus filtering, ordering, pagination, relation loading, and aggregation.

The model delegate API

Prisma generates a delegate per model. Inject PrismaService into a service and call the delegate methods directly. The example below assumes a User with many Post records.

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  published Boolean @default(false)
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

// users.service.ts
import { Injectable, NotFoundException } from '@nestjs/common';
import { Prisma, User } from '@prisma/client';
import { PrismaService } from '../prisma/prisma.service';

@Injectable()
export class UsersService {
  constructor(private readonly prisma: PrismaService) {}

  // CREATE
  create(data: Prisma.UserCreateInput): Promise<User> {
    return this.prisma.user.create({ data });
  }

  // READ one
  async findOne(id: number): Promise<User> {
    const user = await this.prisma.user.findUnique({ where: { id } });
    if (!user) throw new NotFoundException(`User ${id} not found`);
    return user;
  }
}

Always type your inputs with the generated Prisma.UserCreateInput / Prisma.UserUpdateInput helpers rather than hand-rolled interfaces. They stay in sync with the schema automatically and reject unknown fields at compile time.

Reading with filters, ordering, and pagination

findMany accepts a single options object. where filters, orderBy sorts, and skip/take paginate. String filters support operators like contains, startsWith, and in, and you can combine conditions with AND, OR, and NOT.

async findPublished(search: string, page = 1, pageSize = 20) {
  return this.prisma.post.findMany({
    where: {
      published: true,
      title: { contains: search, mode: 'insensitive' },
    },
    orderBy: { id: 'desc' },
    skip: (page - 1) * pageSize,
    take: pageSize,
  });
}

The most common option keys are summarized below.

Option	Purpose	Example
`where`	Filter rows	`{ published: true }`
`orderBy`	Sort results	`{ createdAt: 'desc' }`
`skip` / `take`	Offset pagination	`skip: 20, take: 10`
`cursor`	Cursor pagination	`cursor: { id: 50 }`
`select`	Pick specific fields	`{ id: true, email: true }`
`include`	Eager-load relations	`{ posts: true }`
`distinct`	De-duplicate by field	`['authorId']`

For large datasets, prefer cursor pagination over skip/take: it stays fast because the database seeks directly to the cursor row instead of counting past skipped rows.

Loading relations with include and select

Relations are not fetched unless you ask. Use include to attach related records, or select to return a precise subset of fields (the two are mutually exclusive at the same level). You can nest both to shape exactly the payload your API returns.

async userWithRecentPosts(id: number) {
  return this.prisma.user.findUnique({
    where: { id },
    select: {
      id: true,
      email: true,
      posts: {
        where: { published: true },
        orderBy: { id: 'desc' },
        take: 5,
        select: { id: true, title: true },
      },
    },
  });
}

Output:

{
  "id": 1,
  "email": "[email protected]",
  "posts": [
    { "id": 42, "title": "Type-safe queries in NestJS" },
    { "id": 39, "title": "Why I switched to Prisma" }
  ]
}

Creating with nested writes

Prisma can create a parent and its relations in one atomic statement using nested create or connect. Use create to insert new related rows and connect to link existing ones by a unique field.

createWithPosts(email: string) {
  return this.prisma.user.create({
    data: {
      email,
      name: 'Grace',
      posts: {
        create: [
          { title: 'First post', published: true },
          { title: 'Draft idea' },
        ],
      },
    },
    include: { posts: true },
  });
}

Updating and deleting

update targets a single row by a unique where and applies a partial data patch. updateMany patches every matching row and returns a count. delete removes one row; upsert updates if present or creates if absent.

publishAll(authorId: number) {
  return this.prisma.post.updateMany({
    where: { authorId, published: false },
    data: { published: true },
  });
}

upsertByEmail(email: string, name: string) {
  return this.prisma.user.upsert({
    where: { email },
    update: { name },
    create: { email, name },
  });
}

remove(id: number) {
  return this.prisma.post.delete({ where: { id } });
}

update and delete throw Prisma.PrismaClientKnownRequestError with code P2025 when no row matches. Catch it to return a clean 404 instead of leaking a 500.

Aggregations and grouping

For counts and statistics, use count, aggregate, and groupBy rather than pulling rows into memory and reducing them in Node.

async stats() {
  const total = await this.prisma.post.count({ where: { published: true } });

  const byAuthor = await this.prisma.post.groupBy({
    by: ['authorId'],
    _count: { _all: true },
    orderBy: { _count: { authorId: 'desc' } },
  });

  return { total, byAuthor };
}