---
title: "Prisma Raw Queries"
slug: "prisma-raw-queries"
category: "backend"
tags: ["backend", "prisma", "sql", "postgres", "raw-queries", "orm", "typescript"]
status: "stable"
last_updated: 2026-05-29
summary: "Use $queryRaw and $executeRaw with Prisma.sql tagged templates for safe raw SQL; know when the ORM is insufficient and when to drop down."
related:
  [
    "[[backend/prisma]]",
    "[[backend/postgres]]",
    "[[backend/prisma-client]]",
    "[[backend/prisma-transactions]]",
    "[[backend/prisma-schema]]",
    "[[comparisons/prisma-vs-drizzle]]",
    "[[backend/postgres-explain]]",
  ]
---

## 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.

```ts
// $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 [[backend/prisma-transactions]].

## Always use the tagged template form, never string concatenation

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

```ts
// 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.

```ts
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 [[backend/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 [[backend/postgres-full-text-search|full-text search]].
- `JSONB` path operators (`@>`, `#>>`, `jsonb_path_query`). See [[backend/postgres-jsonb]].
- `EXPLAIN` or `EXPLAIN ANALYZE` output. See [[backend/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.

```ts
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 [[backend/postgres-explain]] for query plan analysis.

## Related

- [[backend/prisma]]
- [[backend/postgres]]
- [[backend/prisma-client]]
- [[backend/prisma-transactions]]
- [[backend/prisma-schema]]
- [[comparisons/prisma-vs-drizzle]]
- [[backend/postgres-explain]]