Run a custom read query
A SELECT needs SQL features or shape not conveniently expressed through Prisma's standard query API.
Prisma · Module
$queryRaw
$queryRaw runs a raw SQL query and returns its records as an array. Prefer its tagged-template form with interpolated parameters, which Prisma sends as a prepared statement.
How it works
$queryRaw executes a raw SQL statement through Prisma Client and returns the resulting records as an array. Use its tagged-template form to interpolate values safely; Prisma turns the statement into a prepared query rather than requiring string concatenation.
Syntax
prisma.$queryRaw`SQL query ${value}`Parameters
| Name | Type | Description |
|---|---|---|
query | Prisma.Sql | A tagged SQL template containing the raw query and interpolated values. |
Returns
A promise resolving to an array of objects representing the returned database records.
Key points
- Use
$queryRawfor raw read queries when the result rows themselves are needed. - The method is intended to retrieve records rather than report an affected-row count.
- Expect the resolved value to be an array.
- Each array element represents one returned database record.
- Tagged-template interpolation is converted into prepared statements, reducing SQL-injection risk.
- The raw-query API is documented for supported relational databases and MongoDB.
Common mistakes
- A single call cannot contain multiple SQL statements; split separate statements into separate calls.
- Do not append another statement with a semicolon, because the call will fail.
- Use the tagged-template API for
$queryRaw; do not treat it as an ordinary string-taking function.
Example: Parameterized record query
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
const email = 'ada@example.com'
const users = await prisma.$queryRaw<{ id: number; email: string }[]>`
SELECT id, email FROM "User" WHERE email = ${email}
`
console.log(users)
await prisma.$disconnect()Runs a parameterized SELECT and receives the matching database rows.
When to use it
Retrieve records
A query must return database rows rather than only an affected-row count.
Use database-specific querying
An application targets a supported relational database or MongoDB and needs raw-query access.
Interview questions
QWhat does $queryRaw return, and how does it differ from $executeRaw?
$queryRaw sends raw SQL and returns the actual database records, unlike $executeRaw, which returns the number of affected rows. The result is an array whose objects represent database records.
Follow-up: How should interpolated values be handled? Use the tagged-template form so Prisma creates a prepared statement and escapes the variables.
QHow can $queryRaw be used safely with variables?
The preferred form is a tagged template such as prisma.$queryRaw\SELECT ... WHERE email = ${email}\. Interpolated variables are handled as parameters, helping protect against SQL injection.
Follow-up: What should be avoided when incorporating user input? Avoid concatenating input into SQL; use parameterized queries or tagged templates.
QWhen is $queryRaw appropriate, and what execution limitation applies?
It is useful when a query needs raw SQL, such as a SELECT that Prisma's query API does not express conveniently. The method is available for relational databases supported by Prisma ORM and MongoDB.
Follow-up: What execution limitation applies to one call? Each method call can execute only one query, so multiple statements must not be appended.
Related
$executeRaw$queryRawUnsafe$executeRawUnsafePrisma.SqlTypedSQL