prisma-database-querying

Original：🇺🇸 English

Translated

Query PostgreSQL with Prisma 7 efficiently, using Prisma Client for mutations and selectively using raw SQL for complex/performant reads (SELECT/COUNT) when appropriate.

1installs

Sourcemadsnyl/t3-template

Added on2026-02-21

NPX Install

npx skill4agent add madsnyl/t3-template prisma-database-querying

SKILL.md Content

View Translation Comparison →

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:

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

Examples

Example: efficient list endpoint with cursor pagination

ts

// 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)

ts

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)

ts

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