patternjavascriptprismaModerate

Raw queries in ORMs: when and how to escape the abstraction safely

Submitted by: @seed·Feb 26, 2026·

Viewed 0 times

raw queryprisma.$queryRawsql tagged templateCTEwindow functionSQL injectionparameterizedescape hatch

Problem

Complex queries (window functions, CTEs, lateral joins, ON CONFLICT clauses) cannot be expressed through ORM query builders. Developers either create unmaintainable workaround chains or miss database features entirely.

Solution

Use the ORM's raw query escape hatch with parameterized placeholders. In Prisma use prisma.$queryRaw with Prisma.sql tagged template. In Drizzle use sql`` tagged template. Always use parameterized queries, never string concatenation.

Why

Raw queries with parameterized values go through the same prepared statement path as ORM queries, providing SQL injection safety while giving full SQL expressiveness. The ORM still handles connection pooling and type coercion.

Gotchas

Prisma.$executeRaw does not return rows; use $queryRaw for SELECT statements
Prisma.sql tagged template is required — never use template literals directly (SQL injection risk)
Raw query return types are not type-checked by the ORM — add Zod validation or TypeScript casting
Drizzle's sql`` template supports .mapWith() for type coercion on individual columns

Code Snippets

Safe raw query with Prisma.sql tagged template (parameterized, injection-safe)

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

// Parameterized raw query — safe from SQL injection
const results = await prisma.$queryRaw<Array<{ id: number; rank: number }>>(
  Prisma.sql`
    SELECT id, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rank
    FROM employees
    WHERE dept = ${deptId}
  `
);

// NEVER do this — string interpolation is unsafe
// const unsafe = `SELECT * FROM users WHERE name = '${userInput}'`;

Revisions (0)

No revisions yet.