Working with Raw SQL Queries
1. Executing Raw SELECT
const rows = await prisma.$queryRaw`SELECT id, email FROM "User" WHERE active = true`;| API | Use |
|---|---|
$queryRaw | Tagged template, parameterized |
$queryRawUnsafe | Plain string (dangerous) |
$queryRawTyped<T> | Explicit return type |
2. Using Parameterized Queries
const id = 5;
const user = await prisma.$queryRaw`SELECT * FROM "User" WHERE id = ${id}`;| Aspect | Detail |
|---|---|
| Safe | Values auto-escaped as bind params |
| Type | JS values map to DB types |
3. Executing Raw Write Operations
const rowsAffected = await prisma.$executeRaw`UPDATE "User" SET active = false WHERE id = ${id}`;| API | Returns |
|---|---|
$executeRaw | Affected row count |
$executeRawUnsafe | Affected row count (no escape) |
4. Using Unsafe Raw Queries
Warning:
$queryRawUnsafe and $executeRawUnsafe interpolate strings directly. Validate inputs against an allowlist before use to prevent SQL injection.| Use | When |
|---|---|
| Dynamic columns | Column allowlisting required |
| Dynamic table names | Otherwise impossible with tagged template |
5. Typing Raw Query Results
type Row = { id: number; total: number };
const rows = await prisma.$queryRaw<Row[]>`SELECT id, total FROM "Order"`;| Note | Detail |
|---|---|
| Numbers | BigInt columns return bigint |
| Decimals | Return Prisma.Decimal |
6. Using Tagged Template Literals
| Element | Detail |
|---|---|
| Backticks required | Distinguishes from string call |
| Identifiers | Use Prisma.sql + Prisma.raw for dynamic parts |
import { Prisma } from "@prisma/client";
const col = Prisma.raw('"createdAt"');
await prisma.$queryRaw(Prisma.sql`SELECT ${col} FROM "Post" WHERE id = ${id}`);7. Escaping Values Safely
| Helper | Use |
|---|---|
Prisma.sql | Compose parameterized fragments |
Prisma.join(arr, ',') | Join arrays safely |
Prisma.empty | No-op fragment |
Prisma.raw | Inserts unsanitized literal (dangerous) |
8. Executing Multiple Statements
| Aspect | Detail |
|---|---|
| Single statement | Default for raw helpers |
| Multi-statement | Use $transaction with multiple $executeRaw |
9. Using Database-Specific Functions
| Use | Example |
|---|---|
| PostGIS | ST_Distance |
| Window functions | ROW_NUMBER() OVER |
| JSON ops | jsonb_set, JSON_EXTRACT |
10. Combining Raw and Type-Safe Queries
| Pattern | Detail |
|---|---|
| Hybrid | Use raw for analytics, ORM for CRUD |
| In transaction | Both share tx client |