Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 36 ยท Transactions and Raw SQL

Raw Queries with $queryRaw

Run raw SQL from Prisma with $queryRaw and $executeRaw, bind parameters safely with tagged templates, avoid SQL injection, and use Prisma.sql, Prisma.join and Prisma.raw correctly.

Prisma's query API covers most needs, but SQL is more expressive. Window functions, recursive queries, full-text search, reporting aggregates and database-specific features are easier to write as SQL. Prisma lets you do that without leaving the client: $queryRaw for statements that return rows, and $executeRaw for statements that change data and return a count.

The Prisma calls are read-only here. The TryIts are labelled stand-ins: a small tagged-template function that behaves like $queryRaw (it turns ${value} into bound parameters) running on node:sqlite.

$queryRaw: reading rows

TypeScript
const minAge = 18;
const users = await prisma.$queryRaw<{ id: number; email: string }[]>`
  SELECT id, email FROM "User" WHERE age >= ${minAge} ORDER BY id
`;

Notice there are backticks, not parentheses. $queryRaw is a tagged template literal. Prisma receives the SQL text pieces and the interpolated values separately, sends the values as bound parameters ($1, $2 on PostgreSQL, ? on MySQL and SQLite), and the database never mixes them into the SQL text. That is what makes it safe from SQL injection.

The type argument is a promise to TypeScript, not a check. Prisma returns whatever columns the database sent. Two things to remember:

  • Column names are as the database names them (created_at, not createdAt) unless you alias them in SQL.
  • Some types come back differently from the model API: COUNT(*) is a bigint in PostgreSQL, so convert it (Number(row.count)) or JSON serialization fails.

$executeRaw: writing and counting

TypeScript
const affected: number = await prisma.$executeRaw`
  UPDATE "Post" SET "published" = true WHERE "authorId" = ${authorId}
`;

It returns the number of affected rows. Use it for UPDATE, DELETE, INSERT and DDL where the model API is awkward, for example one set-based update that touches a million rows.

Why the tagged template is safe

Node.js Stand-in: bound parameters vs string concatenation

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The injected text changed the meaning of the unsafe query. In the safe version it is only compared as a value, and no row has that email.

What you cannot parameterize

Values can be parameters. Identifiers (table and column names), keywords and sort directions cannot. This fails:

TypeScript
await prisma.$queryRaw`SELECT * FROM ${tableName}`; // error: table names cannot be parameters

For dynamic identifiers, validate against a fixed allow-list and build the SQL with Prisma.raw, which inserts text verbatim:

TypeScript
import { Prisma } from '@prisma/client';

const sortable = { newest: 'created_at', title: 'title' } as const;
const column = sortable[sortKey as keyof typeof sortable] ?? 'created_at';

const rows = await prisma.$queryRaw`
  SELECT * FROM "Post" ORDER BY ${Prisma.raw(column)} DESC
`;

Prisma.raw is dangerous with user input: only ever feed it values you chose from an allow-list, never the raw request value. (In Prisma 7 the import path is your generated client, for example import { Prisma } from './generated/client'.)

Building queries piece by piece: Prisma.sql, join, empty

Prisma.sql creates a reusable fragment that stays parameterized. Prisma.join turns an array into a comma-separated list of parameters for IN. Prisma.empty is a no-op fragment for optional pieces:

TypeScript
const ids = [3, 5, 8];
const onlyPublished = true;

const posts = await prisma.$queryRaw`
  SELECT id, title FROM "Post"
  WHERE id IN (${Prisma.join(ids)})
  ${onlyPublished ? Prisma.sql`AND published = true` : Prisma.empty}
`;

Passing an array directly (IN (${ids})) does not do what you want; that is what join is for. Watch the database's parameter limit (about 32,000 on PostgreSQL): a million IDs will not fit.

Node.js Stand-in: sql, join and empty fragments

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The unsafe variants

$queryRawUnsafe(sqlString, ...params) and $executeRawUnsafe take an ordinary string. They exist for SQL you build dynamically, but string concatenation with user input is exactly how SQL injection happens. If you must use them, pass values through placeholders and never concatenate:

TypeScript
await prisma.$queryRawUnsafe('SELECT * FROM "User" WHERE email = $1', email);   // PostgreSQL placeholder

Prefer the tagged template, and read TypedSQL for checked raw SQL.

Raw queries in transactions

Both methods work on the transaction client, so they commit or roll back with everything else: call tx.$queryRaw or tx.$executeRaw inside the callback (see transactions).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is prisma.$queryRaw... WHERE id = ${id} safe from SQL injection?

    The tagged template splits the SQL and the values; the database receives values as parameters and never parses them as SQL.
  2. 2. What does $executeRaw return?

    $executeRaw is for statements that change data; it returns a count. $queryRaw returns rows.
  3. 3. How should you build ORDER BY with a user-chosen column?

    Identifiers cannot be bound parameters. Only text you chose yourself from a fixed list is safe to insert with Prisma.raw.
  4. 4. What is Prisma.join(ids) for?

    Prisma.join expands an array to a comma-separated list of bound parameters.

Summary

  • $queryRaw returns rows, $executeRaw returns a count; both are tagged templates with bound parameters.
  • The type argument is not verified at runtime; watch for bigint and snake_case columns.
  • Values can be parameters; identifiers cannot. Use an allow-list with Prisma.raw.
  • Prisma.sql, Prisma.join and Prisma.empty compose queries safely.
  • Avoid the Unsafe variants unless you pass values through placeholders.

Next: TypedSQL and Raw SQL Safety