LLM Best Practices

Prisma Raw Queries

Last updated 3 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Use raw SQL when the query builder cannot express the query, and keep the tagged-template form so values stay parameterized. $queryRaw returns rows and $executeRaw returns the affected row count. Reach for them deliberately: for the same table, a missing index or a materialized view often removes the need.

Use executeRaw for writes

const rows = await prisma.$queryRaw<Array<{ id: string; rank: number }>>`
  SELECT id, ts_rank(search, websearch_to_tsquery('english', ${q})) AS rank
  FROM articles
  WHERE search @@ websearch_to_tsquery('english', ${q})
  ORDER BY rank DESC
  LIMIT ${limit}
`
 
const count = await prisma.$executeRaw`
  UPDATE jobs SET status = 'running', started_at = now()
  WHERE id = ${jobId} AND status = 'pending'
`
  • Each ${value} becomes a $1, $2, … placeholder. Parameters protect values only; identifiers (table, column, keyword) cannot be parameters.
  • The type argument on $queryRaw<T> is not checked at runtime. Prisma casts the rows to T, so define it accurately.
  • Inside a transaction, call these on the tx client; see prisma-transactions.

Never build SQL by string concatenation

// WRONG: injection risk
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${userInput}'`)
 
// RIGHT
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${userInput}`

$queryRawUnsafe and $executeRawUnsafe take plain strings. Use them only when the query structure must be dynamic, and validate the dynamic part against a hardcoded allowlist first. Never pass user input as a column name, table name, or keyword.

Compose fragments with Prisma.sql

import { Prisma } from "./generated/prisma/client"
 
const filter = status ? Prisma.sql`WHERE status = ${status}` : Prisma.empty
const rows = await prisma.$queryRaw<Array<{ id: string }>>`
  SELECT id FROM jobs ${filter} ORDER BY created_at DESC LIMIT 50
`
  • Fragments keep their own parameters, so composition does not reopen injection.
  • Prisma.join(ids) builds a parameter list for IN: WHERE id IN (${Prisma.join(ids)}).
  • Prisma.raw(str) injects an unparameterized literal. Use it only for allowlisted identifiers.
  • In Prisma 7, import Prisma from the generator’s output path, not @prisma/client.

Know what needs raw SQL

  • Recursive CTEs, window functions, and lateral joins.
  • Full-text ranking and highlighting (ts_rank, ts_headline); see postgres-full-text-search.
  • JSONB operators beyond Prisma’s Json filters (path, equals, string_contains, array_contains): jsonb_path_query, @?, #>>; see postgres-jsonb.
  • EXPLAIN ANALYZE; see postgres-explain.
  • SELECT ... FOR UPDATE, COPY, and INSERT ... ON CONFLICT with complex SET expressions.

Convert raw results when needed

$queryRaw bypasses the model layer, so rows carry database types, not Prisma conversions.

  • Postgres count(*) and other bigint aggregates arrive as JavaScript BigInt. Cast with Number(...) before JSON serialization, or cast in SQL (count(*)::int).
  • timestamp columns arrive as Date, matching client behavior.
  • When a raw query returns the same shape as a model, prefer the ORM query unless profiling shows a clear gain.

Backlinks