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 "@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.

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