Prisma Client Queries

Prisma Client Queries

Query data with Prisma Client findUnique/findMany, create/update/delete, upsert, select, include

When to Use

• Reading data from the database with type-safe queries
• Creating, updating, or deleting records
• Selecting specific fields or including related records
• Performing upserts (create-or-update) operations

Instructions

1. Instantiate the client once and reuse it. Never create a new PrismaClient per request:

// lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
export const prisma = globalForPrisma.prisma ?? new PrismaClient();

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

2. Find a single record — use findUnique for lookups by @id or @unique fields. Use findFirst when filtering by non-unique fields:

const user = await prisma.user.findUnique({ where: { id: userId } });
const admin = await prisma.user.findFirst({ where: { role: 'ADMIN' } });

3. Find multiple records — findMany returns an array. Always paginate large result sets:

const users = await prisma.user.findMany({
  where: { role: 'USER' },
  take: 20,
  skip: 0,
  orderBy: { createdAt: 'desc' },
});

4. Select specific fields to avoid over-fetching:

const names = await prisma.user.findMany({
  select: { id: true, name: true, email: true },
});
// Type: { id: string; name: string | null; email: string }[]

5. Include related records with include:

const userWithPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: { posts: { where: { published: true }, take: 10 } },
});

6. Create a record — all required fields must be provided:

const user = await prisma.user.create({
  data: { email: '[email protected]', name: 'New User' },
});

7. Update a record — requires a unique where clause:

const updated = await prisma.user.update({
  where: { id: userId },
  data: { name: 'Updated Name' },
});

8. Upsert — create if not found, update if found:

const user = await prisma.user.upsert({
  where: { email: '[email protected]' },
  create: { email: '[email protected]', name: 'New' },
  update: { name: 'Existing' },
});

9. Delete — use delete for single records, deleteMany for bulk:

await prisma.user.delete({ where: { id: userId } });
await prisma.post.deleteMany({ where: { authorId: userId } });

10. Nested writes — create or connect related records in one operation:

const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    posts: { create: [{ title: 'First Post' }, { title: 'Second Post' }] },
  },
  include: { posts: true },
});

Details

Prisma Client is auto-generated from the schema and provides full TypeScript types for every query. The generated types enforce that you only pass valid field names, filter operators, and relation includes.

select vs include: These are mutually exclusive at the same level. select returns only the specified fields (plus any nested select/include). include returns all scalar fields plus the specified relations.

findUnique vs findFirst: findUnique can only filter by @id or @unique fields and benefits from Prisma's internal query deduplication (DataLoader batching). findFirst can filter by any field but does not deduplicate.

findUniqueOrThrow / findFirstOrThrow: These variants throw a PrismaClientKnownRequestError with code P2025 instead of returning null. Use them when absence is an error condition.

Nested writes are atomic: create with nested create/connect/connectOrCreate runs in a single implicit transaction. If any nested operation fails, the entire write is rolled back.

Return types narrow automatically: When you use select, the return type includes only the selected fields. This is enforced at the TypeScript level, so your code stays type-safe.

Common mistakes:

• Using findFirst where findUnique would work — loses batching optimization
• Deeply nesting include — each level adds a separate database query. Three levels deep on a list page causes N+1 problems
• Not handling null from findUnique — always check or use findUniqueOrThrow
• Forgetting that updateMany/deleteMany do not return the affected records — only a count

Source

https://prisma.io/docs/orm/prisma-client/queries

Process

1. Read the instructions and examples in this document.
2. Apply the patterns to your implementation, adapting to your specific context.
3. Verify your implementation against the details and edge cases listed above.

Harness Integration

• Type: knowledge — this skill is a reference document, not a procedural workflow.
• No tools or state — consumed as context by other skills and agents.

Success Criteria

• The patterns described in this document are applied correctly in the implementation.
• Edge cases and anti-patterns listed in this document are avoided.