Help us improve
Share bugs, ideas, or general feedback.
From opinionated-prisma
Decision logic for when to use $queryRaw vs Prisma's query API. Use when deciding whether raw SQL is warranted, writing window functions, CTEs, full-text search, JSONB operators, or complex aggregations. Triggers on "$queryRaw", "raw sql", "window function", "CTE", "full-text search", "JSONB", "ROW_NUMBER", "RANK", "Prisma.sql", "EXPLAIN ANALYZE".
npx claudepluginhub esot321c/opinionated-prismaHow this skill is triggered — by the user, by Claude, or both
Slash command
/opinionated-prisma:raw-sql-boundaryThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Use Prisma's query API by default. Reach for `$queryRaw` only when Prisma's generated SQL is measurably worse or cannot express the operation at all. The threshold is not "raw SQL would be faster" (it almost always is, marginally). The threshold is "Prisma cannot do this, or Prisma's approach changes the algorithmic complexity."
Guides Payload CMS config (payload.config.ts), collections, fields, hooks, access control, APIs. Debugs validation errors, security, relationships, queries, transactions, hook behavior.
Builds scalable data pipelines, modern data warehouses, and real-time streaming architectures using Apache Spark, dbt, Airflow, and cloud-native platforms.
Share bugs, ideas, or general feedback.
Use Prisma's query API by default. Reach for $queryRaw only when Prisma's generated SQL is measurably worse or cannot express the operation at all. The threshold is not "raw SQL would be faster" (it almost always is, marginally). The threshold is "Prisma cannot do this, or Prisma's approach changes the algorithmic complexity."
| Pattern | Why Prisma Falls Short |
|---|---|
Window functions (ROW_NUMBER, RANK, LAG/LEAD) | Not expressible in Prisma query API |
| CTEs (recursive or not) | Not supported. Needed for hierarchical data like org charts, matter trees, threaded comments |
| Full-text search with ranking | Prisma's search filter has no ts_rank, no custom dictionaries, no control over tsvector columns |
JSONB containment queries (@>, ?, ?&) | Prisma filters JSONB but cannot use GIN-aware operators |
Bulk upsert (INSERT ... ON CONFLICT) | Prisma's upsert is one query per row |
Conditional bulk update (UPDATE ... CASE) | Prisma's updateMany applies uniform data |
Aggregation beyond groupBy | No HAVING with complex expressions, no rollups, no cube |
EXPLAIN ANALYZE | Cannot run through Prisma API |
| Materialized view creation/refresh | DDL, not a query |
relationJoins enabled.If you find yourself writing raw SQL for basic CRUD, the problem is probably a missing index or a schema design issue, not a Prisma limitation.
// WRONG: Untyped
const results = await prisma.$queryRaw`SELECT * FROM matters`;
// CORRECT: Typed
interface MatterWithRank {
id: string;
title: string;
rank: number;
}
const results = await prisma.$queryRaw<MatterWithRank[]>`
SELECT id, title,
ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY created_at DESC) as rank
FROM matters
WHERE deleted_at IS NULL
`;
src/
modules/
matters/
matters.service.ts # Prisma client queries
matters.queries.ts # $queryRaw queries for matters
matters.controller.ts
Don't scatter raw SQL across services. Keep it next to the Prisma operations it supplements so the next developer (or Claude) knows where to look.
// WRONG: String interpolation (SQL injection risk)
const results = await prisma.$queryRaw`
SELECT * FROM matters WHERE client_id = '${clientId}'
`;
// CORRECT: Parameterized
const results = await prisma.$queryRaw<Matter[]>`
SELECT * FROM matters WHERE client_id = ${clientId}::uuid
`;
Prisma's tagged template literal handles parameterization automatically. Do not use string concatenation or template literals outside the $queryRaw tag.
// A matter can have sub-matters (e.g. a corporate reorganization with sub-files)
const hierarchy = await prisma.$queryRaw<MatterNode[]>`
WITH RECURSIVE matter_tree AS (
SELECT id, title, parent_id, 0 as depth
FROM matters
WHERE id = ${rootMatterId}::uuid
UNION ALL
SELECT m.id, m.title, m.parent_id, mt.depth + 1
FROM matters m
JOIN matter_tree mt ON m.parent_id = mt.id
WHERE m.deleted_at IS NULL
)
SELECT * FROM matter_tree ORDER BY depth, title
`;
// No reason for raw SQL here
const clients = await prisma.$queryRaw<Client[]>`
SELECT * FROM clients
WHERE deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 50
`;
// Prisma does this correctly:
const clients = await prisma.client.findMany({
where: { deletedAt: null },
orderBy: { createdAt: 'desc' },
take: 50
});