LLM Best Practices

Prisma Raw Queries

4 min read

View markdown source

Overview

Prisma’s query builder covers the vast majority of CRUD operations. It falls short for recursive CTEs, window functions, full-text search ranking, lateral joins, and any query that needs plan hints or custom operators. $queryRaw and $executeRaw give you a direct lane to the database while keeping the TypeScript type system and parameterized query safety intact. Drop down to raw SQL deliberately, not as a first resort.

Use $queryRaw for SELECT and $executeRaw for writes

The two methods differ in return value, not in safety.

// $queryRaw: returns typed rows.
const rows = await prisma.$queryRaw<Array<{ id: string; rank: number }>>`
  SELECT id, ts_rank(search_tsv, plainto_tsquery(${query})) AS rank
  FROM articles
  WHERE search_tsv @@ plainto_tsquery(${query})
  ORDER BY rank DESC
  LIMIT ${limit};
`
 
// $executeRaw: returns affected row count.
const count = await prisma.$executeRaw`
  UPDATE jobs SET status = 'running', started_at = now()
  WHERE id = ${jobId} AND status = 'pending';
`
  • The tagged template syntax (backtick with interpolation) automatically parameterizes values. Each ${variable} becomes a $1, $2, … placeholder in the final SQL.
  • The type parameter on $queryRaw<T> is not validated at runtime. Define it accurately; Prisma casts the returned rows directly to T.
  • Both methods work inside $transaction. Pass the transaction client (tx) instead of the global prisma. See prisma-transactions.

Always use the tagged template form, never string concatenation

String concatenation produces injection vulnerabilities. The tagged template form is the safe path.

// WRONG: SQL injection risk. Never do this.
const raw = `SELECT * FROM users WHERE email = '${userInput}'`
await prisma.$queryRawUnsafe(raw)
 
// CORRECT: parameterized.
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${userInput}`
  • $queryRawUnsafe and $executeRawUnsafe accept a plain string. Use them only when the query structure itself must be dynamic (for example, a column name you build from an allowlist). In that case, validate the dynamic part against a hardcoded list before interpolating.
  • Never pass user input as a column name, table name, or SQL keyword. Parameterization only protects values, not identifiers.

Use Prisma.sql to compose query fragments

When a query has conditional clauses, use Prisma.sql to assemble fragments safely.

import { Prisma } from "./generated/prisma/client"
 
function buildFilter(status?: string): Prisma.Sql {
  return status ? Prisma.sql`WHERE status = ${status}` : Prisma.sql``
}
 
const filter = buildFilter(req.query.status)
const rows = await prisma.$queryRaw<Array<{ id: string }>>`
  SELECT id FROM jobs ${filter} ORDER BY created_at DESC LIMIT 50;
`
  • Prisma.sql fragments compose without re-opening injection surfaces. Each fragment’s interpolated values are parameterized independently.
  • Prisma.join(array, separator) produces comma-separated parameter lists for IN clauses: WHERE id IN (${Prisma.join(ids)}).
  • Prisma.raw(string) injects a literal SQL fragment with no parameterization. Use only for identifiers validated against an allowlist.
  • In Prisma 7, import Prisma from the generator’s output path (for example ./generated/prisma/client), not @prisma/client. The $queryRaw, $executeRaw, Prisma.sql, Prisma.join, and Prisma.raw APIs are otherwise unchanged. See prisma-v6-to-v7-upgrade.

Know when to drop to raw SQL

The ORM is appropriate for most CRUD. Drop down to raw SQL when the query builder cannot express the query without a workaround.

Queries that require raw SQL in Prisma:

  • Recursive CTEs (WITH RECURSIVE).
  • Window functions (ROW_NUMBER() OVER (PARTITION BY ...)).
  • Full-text search with ranking (ts_rank, ts_headline). See full-text search.
  • JSONB path operators (@>, #>>, jsonb_path_query). See postgres-jsonb.
  • EXPLAIN or EXPLAIN ANALYZE output. See postgres-explain.
  • Upsert with conflict target expressions that go beyond @unique fields.
  • Bulk insert via COPY or INSERT ... ON CONFLICT DO UPDATE with complex SET expressions.

If you find yourself calling $queryRaw frequently for the same table, consider whether the schema lacks an index or a materialized view that would let the ORM query handle it.

Map raw results back to Prisma types when possible

$queryRaw bypasses the Prisma model layer. The result rows will not include DateTime conversion or nested relation objects.

type RankedArticle = {
  id: string
  title: string
  rank: number
}
 
const results = await prisma.$queryRaw<RankedArticle[]>`
  SELECT id, title, ts_rank(search_tsv, plainto_tsquery(${q})) AS rank
  FROM articles
  WHERE search_tsv @@ plainto_tsquery(${q})
  ORDER BY rank DESC
  LIMIT 10;
`
  • Postgres returns BigInt for count(*) and similar aggregates. Cast to Number explicitly if your JSON serializer cannot handle BigInt.
  • DateTime columns come back as JavaScript Date objects. This matches the Prisma client behavior, so downstream code is compatible.
  • When a raw query returns the same shape as a Prisma model, prefer the ORM query unless performance profiling shows a clear gain. See postgres-explain for query plan analysis.

Graph View

Backlinks