Raw SQL

Prisma Intermediate/Advanced

Chapter 3 · Raw SQL

Prisma Client covers most everyday queries, but not all of them. Window functions, full-text search, common table expressions, database-specific features and some bulk updates are easier — or only possible — in plain SQL. Prisma lets you drop down to SQL for those queries without giving up its connection handling or, if you write the SQL carefully, its protection against SQL injection.

Examples use PostgreSQL. Prisma names each table after its model, so the Post model is the table "Post". PostgreSQL needs the double quotes because the name has a capital letter. (If your schema uses @@map, use the mapped name instead.)

$queryRaw and $executeRaw

Method	Use for	Returns
$queryRaw	SELECT (anything that returns rows)	An array of row objects
$executeRaw	UPDATE, DELETE, INSERT without RETURNING	The number of rows affected

// Rank each author's posts by views, using a window function
type RankedPost = { title: string; authorId: number; viewCount: number; rank: bigint };

const ranked = await prisma.$queryRaw<RankedPost[]>`
  SELECT "title", "authorId", "viewCount",
         RANK() OVER (PARTITION BY "authorId" ORDER BY "viewCount" DESC) AS "rank"
  FROM "Post"
  WHERE "published" = true
`;

// Archive old drafts in one statement
const cutoff = new Date("2026-01-01");
const deleted = await prisma.$executeRaw`
  DELETE FROM "Post" WHERE "published" = false AND "createdAt" < ${cutoff}
`;
console.log(`Removed ${deleted} old drafts`);

Notice these are tagged templates: backticks directly after the method name, with no parentheses. That detail is what keeps them safe.

Why Tagged Templates Stop SQL Injection

When you write ${cutoff} inside a tagged template, Prisma does not paste the value into the SQL text. It sends the SQL with a placeholder and passes the value separately, as a parameter. The database treats a parameter as data, never as SQL, whatever characters it contains.

const search = "x' OR '1'='1";   // hostile input

// SAFE: the whole string is one parameter; matches nothing
await prisma.$queryRaw`SELECT * FROM "Post" WHERE "title" = ${search}`;

// DANGEROUS: the string becomes part of the SQL; matches EVERY post
await prisma.$queryRawUnsafe(`SELECT * FROM "Post" WHERE "title" = '${search}'`);

The Unsafe methods mean exactly that

$queryRawUnsafe and $executeRawUnsafe take an ordinary string. If you build that string with + or a normal template literal, user input becomes SQL. Prisma's docs describe this as a significant risk of SQL injection. If you must use them, pass values as extra arguments with placeholders ($queryRawUnsafe('... WHERE "title" = $1', search)) — never inside the string.

Building Queries in Pieces: Prisma.sql

Search endpoints often need optional conditions. Prisma.sql builds a safe SQL fragment you can combine with others, and Prisma.empty stands for "nothing here":

import { Prisma } from "./generated/prisma/client";

function searchPosts(opts: { text?: string; authorIds?: number[] }) {
  const textFilter = opts.text
    ? Prisma.sql`AND "title" ILIKE ${"%" + opts.text + "%"}`
    : Prisma.empty;

  const authorFilter = opts.authorIds?.length
    ? Prisma.sql`AND "authorId" IN (${Prisma.join(opts.authorIds)})`
    : Prisma.empty;

  return prisma.$queryRaw`
    SELECT "id", "title", "slug" FROM "Post"
    WHERE "published" = true ${textFilter} ${authorFilter}
    ORDER BY "createdAt" DESC
    LIMIT 20
  `;
}

Helper	Does	Safe with user input?
Prisma.sql`...`	A reusable, parameterised fragment	Yes
Prisma.join(list)	A comma-separated list of parameters, for `IN (...)`	Yes
Prisma.empty	An empty fragment, for optional parts	Yes
Prisma.raw(text)	Inserts text directly into the SQL	No — trusted text only

Column and table names can't be parameters

Parameters stand for values. Column names, table names and keywords like ASC/DESC can't be passed as parameters. If a user chooses the sort column, check it against a fixed list first:

const SORTABLE = { newest: '"createdAt" DESC', popular: '"viewCount" DESC' } as const;

const key = req.query.sort in SORTABLE ? (req.query.sort as keyof typeof SORTABLE) : "newest";
const orderBy = Prisma.raw(SORTABLE[key]);   // safe: only our own fixed strings reach Prisma.raw

await prisma.$queryRaw`SELECT "title" FROM "Post" ORDER BY ${orderBy} LIMIT 10`;

What Comes Back: Types

Raw results skip Prisma's model types. The generic in $queryRaw<T> is a promise you make to TypeScript — Prisma doesn't check it. And the database's own types come through:

Database type	JavaScript value
64-bit integers, including COUNT(*) on PostgreSQL	`bigint`
Numeric / decimal	`Decimal`
Dates and timestamps	`Date`
Bytes	`Uint8Array`

res.json() can't send a bigint

JSON.stringify throws TypeError: Do not know how to serialize a BigInt. The rank column above and any COUNT(*) will trigger it. Convert with Number(row.rank) before sending, or cast in SQL: COUNT(*)::int.

TypedSQL: Real Types for Raw Queries

TypedSQL fixes the "trust me" typing problem. You write each query in its own .sql file, and prisma generate --sql connects to your database, works out the real parameter and column types, and generates a typed function. It's a preview feature, so enable it first:

// schema.prisma
generator client {
  provider        = "prisma-client"
  output          = "../generated/prisma"
  previewFeatures = ["typedSql"]
}

-- prisma/sql/topPostsByAuthor.sql
-- @param {Int} $1:authorId
-- @param {Int} $2:limit
SELECT "title", "slug", "viewCount"
FROM "Post"
WHERE "authorId" = $1 AND "published" = true
ORDER BY "viewCount" DESC
LIMIT $2;

npx prisma generate --sql

import { topPostsByAuthor } from "./generated/prisma/sql";

const rows = await prisma.$queryRawTyped(topPostsByAuthor(3, 5));
// rows is typed from the real columns: title, slug, viewCount

The file name becomes the function name, so it must be a valid JavaScript identifier.
Placeholders follow your database: $1 for PostgreSQL, ? for MySQL; SQLite accepts $1, ? or named parameters.
Generating needs a live database connection, which matters in CI.
It can't handle dynamic column lists or optional clauses — use $queryRaw with Prisma.sql for those.
Type detection is weaker on SQLite and MySQL before 8.0, where you may need @param annotations for every parameter.

When to Reach for Raw SQL

Situation	Recommendation
Normal CRUD, filtering, relations	Prisma Client
A fixed complex report or window function	TypedSQL
A query with optional parts	`$queryRaw` with `Prisma.sql` and `Prisma.empty`
Dynamic sort column	Allow-list, then `Prisma.raw` on your own strings
Anything built from user input by string concatenation	Never

Raw SQL can still be transactional

tx.$queryRaw and tx.$executeRaw work inside an interactive transaction, and raw queries can go in a $transaction([...]) list too, so Chapter 1's guarantees still apply.

Hands-On Exercises

Exercise 1

Using $queryRaw, return the number of published posts per month of the current year, then send it from an Express route without the BigInt error.

📄 View solution

Exercise 2

This code is vulnerable. Explain the attack, then rewrite it safely:
prisma.$queryRawUnsafe(`SELECT * FROM "User" WHERE "email" = '${req.query.email}'`)

📄 View solution

Exercise 3

Build GET /search?q=&tag=&sort= with raw SQL where every part is optional: a title search, a tag name, and sort of newest or popular.

📄 View solution

Chapter 3 Quick Reference

$queryRaw`...` returns rows; $executeRaw`...` returns the number of affected rows
Tagged template values become parameters — safe from SQL injection
*Unsafe methods take a plain string — never put user input in it
Prisma.sql, Prisma.join, Prisma.empty are safe; Prisma.raw is for trusted text only
Identifiers and keywords can't be parameters — use an allow-list
64-bit integers come back as bigint; convert before res.json()
TypedSQL (preview): prisma/sql/*.sql, prisma generate --sql, $queryRawTyped(fn(args))