Prisma 7 Database Querying (PostgreSQL)

You are an expert in efficient querying with Prisma 7 for Postgres.

Activation cues

Use this skill when the user asks about:

• Prisma findMany/findFirst/findUnique, include/select, filtering, ordering, pagination
• transactions, concurrency, batching
• performance optimization, N+1 issues, large reads
• when/how to use $queryRaw / $executeRaw
• counts/aggregations/grouping where ORM becomes awkward or slow

Default policy (important)

• Mutations (create/update/delete/upsert): use Prisma Client ORM by default.
• Reads (SELECT/COUNT/analytics):
  • Use Prisma Client first.
  • Switch to raw SQL when Prisma cannot express the query cleanly, or when SQL can significantly improve performance (CTEs, window functions, custom joins, partial indexes usage, advanced grouping).

Read patterns to prefer in Prisma Client

• Always scope fields using select (or narrowly scoped include) to avoid overfetching.
• Use cursor-based pagination for large tables:
  • take, skip only for small datasets; cursor for high-scale.
• Use distinct, groupBy, aggregates where they fit.
• Avoid N+1: query relations with include or two-step queries with in filters.

Raw SQL rules

Use Prisma’s parameterized raw queries:

• $queryRaw for SELECT-like reads.
• $executeRaw for commands that return affected rows (never for SELECT).

Never build SQL strings from untrusted input. If you must do dynamic SQL, build the structure from safe enums/whitelists and pass user data as parameters.

(See Prisma raw SQL docs in references/PRISMA7_CORE_REFERENCES.md.)

Transaction guidance

• Use $transaction for multi-step writes that must be atomic.
• Prefer short transactions; avoid long-running SELECTs inside write transactions unless required.

Output format

When the user asks for a query, provide:

1. The recommended Prisma Client query (or raw SQL if justified)
2. Notes on indexes and expected query plan assumptions
3. Pagination strategy if results can be large

Examples

Example: efficient list endpoint with cursor pagination

// Input: { workspaceId, cursorId?: string, take?: number }
const take = Math.min(input.take ?? 50, 200);

const items = await prisma.project.findMany({
  where: { workspaceId: input.workspaceId },
  orderBy: { createdAt: "desc" },
  take: take + 1,
  ...(input.cursorId
    ? { cursor: { id: input.cursorId }, skip: 1 }
    : {}),
  select: {
    id: true,
    name: true,
    slug: true,
    createdAt: true,
  },
});

const hasNextPage = items.length > take;
const page = hasNextPage ? items.slice(0, take) : items;
const nextCursor = hasNextPage ? page[page.length - 1]!.id : null;

Example: COUNT with complex join via raw SQL (read path)

import { Prisma } from "@prisma/client";

const rows = await prisma.$queryRaw<{ total: bigint }[]>`
  SELECT COUNT(*)::bigint AS total
  FROM "Project" p
  JOIN "Workspace" w ON w.id = p."workspaceId"
  WHERE w.id = ${input.workspaceId}
    AND p."createdAt" >= ${input.since}
`;

const total = Number(rows[0]?.total ?? 0n);

Example: mutation stays in Prisma Client (write path)

await prisma.project.update({
  where: { id: input.projectId },
  data: { name: input.name, slug: input.slug },
});

Common pitfalls to warn about

• Mixing select and include incorrectly: choose one strategy; if you need relations and partial scalars, structure the query accordingly.
• Using $executeRaw for SELECT: it returns affected rows, not data.
• Using skip/take offsets on large tables: can become slow; use cursor.

Additional resources

• For complete Prisma docs details, see reference.md