Raw database access
Prisma Client exposes two methods that allow you to send raw SQL queries to your database:
- Use
$queryRaw
to return actual records (for example, usingSELECT
) - Use
$executeRaw
to return a count of affected rows (for example, after anUPDATE
orDELETE
)
Use cases for raw SQL include:
- You are a strong SQL user and want to run a heavily optimized query
- Prisma Client does not yet support the feature that you require (please consider raising an issue)
queryRaw
$queryRaw
returns actual database records. For example, the following SELECT
query returns all fields for each record in the User
table:
const result = await prisma.$queryRaw('SELECT * FROM User;')
The same method is implemented as a tagged template, which makes it easier to use variables by providing placeholders. The following example includes an ${email}
placeholder:
const email = 'edna@prisma.io'const result = await prisma.$queryRaw`SELECT * FROM User WHERE email = ${email};`
Return type
$queryRaw
returns an array. Each object corresponds to a database record:
[{ id: 1, email: 'sarah@prisma.io', name: 'Sarah' },{ id: 2, email: 'alice@prisma.io', name: 'Alice' },]
You can also type the results of $queryRaw
.
Signature
$queryRaw<T = any>(query: string | TemplateStringsArray, ...values: any[]): Promise<T>;
Typing $queryRaw
results
You can adjust the return type of $queryRaw
with a TypeScript Generic. $queryRaw
has the following signature:
$queryRaw<T = any>(query: string | TemplateStringsArray): Promise<T>;
Promise<T>
uses generic type parameter T
. You can determine the type of T
when you invoke the $queryRaw
command. In the following example, $queryRaw
returns User[]
:
// import the generated `User` type from the `@prisma/client` moduleimport { User } from '@prisma/client'const result = await prisma.$queryRaw<User[]>('SELECT * FROM User;')// result is of type: `User[]`
Note: If you do not provide a type,
$queryRaw
defaults toany
.
result
is now User[]
and intellisense is available:
If you are selecting specific fields of the model or want to include relations, refer to the documentation about leveraging Prisma Client's generated types if you want to make sure that the results are properly typed.
Type caveats when using raw SQL
When you type the results of $queryRaw
, the raw data does not always match the suggested TypeScript type. For example, the following Prisma model includes a Boolean
field named published
:
model Post {id Int @default(autoincrement()) @idpublished Boolean @default(false)title Stringcontent String?}
The following query returns all posts and prints out the value of published
field of each Post
:
const result = await prisma.$queryRaw<Post[]>`SELECT * FROM Post`result.forEach(x => {console.log(x.published)})
If the database provider is MySQL, the values are 1
or 0
. However, if the database provider is PostgreSQL, the values are true
, false
, or NULL
.
Note: The Prisma Client query engine standardizes the return type for all databases. Using the raw queries does not.
executeRaw
$executeRaw
returns the number of rows affected by a database operation, such as UPDATE
or DELETE
. This function does not return database records. The following query updates records in the database and returns a count of the number of records that were updated:
const result: number = await prisma.$executeRaw('UPDATE User SET active = true WHERE emailValidated = true;')
The same method is implemented as a tagged template, which makes it easier to use variables by providing placeholders. The following example includes ${emailValidated}
and ${active}
placeholders:
const emailValidated = trueconst active = trueconst result: number = await prisma.$executeRaw`UPDATE User SET active = ${active} WHERE emailValidated = ${emailValidated};`
Be aware that:
$executeRaw
does not support multiple queries in a single string (for example,ALTER TABLE
andCREATE TABLE
together).- Prisma Client submits prepared statements, and prepared statements only allow a subset of SQL statements (
START TRANSACTION
is not permitted). For example, "MySQL: SQL Syntax Allowed in Prepared Statements". PREPARE
does not supportALTER
- see workaround.
Return type
$executeRaw
returns a number
.
Signature
$executeRaw<T = any>(query: string | TemplateStringsArray, ...values: any[]): Promise<number>;
Transactions
In 2.10.0 and later, you can use .$executeRaw()
and .$queryRaw()
inside a transaction.
Using variables
$executeRaw
and $queryRaw
are implemented as tagged templates. Tagged templates are the recommended way to use variables with raw SQL in Prisma Client.
The following example includes a placeholder named ${userId}
. Note the double backticks (``
) instead of ()
:
const userId = 42const result = await prisma.$queryRaw`SELECT * FROM User WHERE id = ${userId};`
Important: You must use double backticks - the same query using
$queryRaw()
is not secure because${userId}
is not escaped.
✔ Benefits of using the tagged template versions of $queryRaw
and $executeRaw
include:
- Prisma Client escapes all variables.
- Tagged templates are database-agnostic - you do not need to remember if variables should be written as
$1
(PostgreSQL) or?
(MySQL). - SQL Template Tag give you access to useful helpers.
- Embedded, named variables are easier to read.
Note: You cannot pass a table or column name into a tagged template placeholder. For example, you cannot
SELECT ?
and pass in*
orid, name
based on some condition.
Tagged template helpers
Prisma Client specifically uses SQL Template Tag, which exposes a number of helpers. For example, the following query uses join()
to pass in a list of IDs:
import { Prisma } from "@prisma/client";const ids = [1, 3, 5, 10, 20];const result = await prisma.$queryRaw`SELECT * FROM User WHERE id IN (${Prisma.join(ids)})`;
The following example uses the empty
and sql
helpers to change the query depending on whether userName
is empty:
import { Prisma } from "@prisma/client";const userName = ''const result = await prisma.$queryRaw`SELECT * FROM User ${userName ? Prisma.sql`WHERE name = ${userName}` : Prisma.empty // Cannot use "" or NULL here!}`;
Parameterized queries
As an alternative to tagged templates, $queryRaw()
and $executeRaw()
support standard parameterized queries where each variable is represented by a symbol (?
for mySQL, $1
, $2
, and so on for PostgreSQL). The following example uses a MySQL query:
const userName = 'Sarah'const email = 'alice@prisma.io'const result = await prisma.$queryRaw('SELECT * FROM User WHERE (name = ? OR email = ?)', // MySQL variable, represented by ?userName,)
The following example uses a PostgreSQL query:
const userName = 'Sarah'const email = 'alice@prisma.io'const result = await prisma.$queryRaw('SELECT * FROM User WHERE (name = $1 OR email = $2)', // PostgreSQL variables, represented by $1 and $2userName,)
As with tagged templates, Prisma Client escapes all variables.
Note: You cannot pass a table or column name as a variable into a parameterized query. For example, you cannot
SELECT ?
and pass in*
orid, name
based on some condition.
Parameterized PostgreSQL ILIKE
query
When you use ILIKE
, the %
wildcard character(s) should be included in the variable itself, not the query (string
):
const userName = 'Sarah'const emailFragment = 'prisma.io'const result = await prisma.$queryRaw('SELECT * FROM "User" WHERE (name = $1 OR email = $2)', // Using %$2 here would not workuserName,`%${emailFragment}`)
ALTER limitation (PostgreSQL)
PostgreSQL does not support using ALTER
in a prepared statement, which means that the following queries will not work:
await prisma.$executeRaw`ALTER USER prisma WITH PASSWORD '${password}'` // Tagged templateawait prisma.$executeRaw(`ALTER USER prisma WITH PASSWORD $1`, newPassword) // Parameterized query
You can use the following query to work around the issue, but be aware that this is potentially unsafe as ${password}
is not escaped:
await prisma.$executeRaw(`ALTER USER prisma WITH PASSWORD '${password}'`)
SQL injection
Prisma Client mitigates the risk of SQL injection in the following ways:
- Prisma Client escapes all variables when you use tagged templates (
queryRaw``
orexecuteRaw``
) or parameterized queries ($queryRaw(query, variables)
or$executeRaw(query, variables)
), and sends all queries as prepared statements. - Both versions of
$executeRaw
(standard and tagged template) can only run one query at a time. You cannot append a second query - for example, addingDROP bobby_tables
to the end of anALTER
.
If you cannot use tagged templates or parameterized queries (for example, you need to pass in table or column names as variables), be aware that your code may be vulnerable to SQL injection.
Warning: String concatenation
The following example concatenates query
and inputString
. Prisma Client ❌ cannot escape inputString
in this example, which makes it vulnerable to SQL injection:
const inputString = '"Sarah" UNION SELECT id, title, content FROM Post' // SQL Injectionconst query = 'SELECT id, name, email FROM User WHERE name = ' + inputStringconst result = await prisma.$queryRaw(query)console.log(result)
Warning: Template literals
The following example uses a template literal inside $queryRaw()
and returns all users where name
equals ${inputString}
. Prisma Client ❌ cannot escape inputString
in this example, which makes it vulnerable to SQL injection:
const inputString = '"Sarah" UNION SELECT id, title, content FROM Post' // SQL Injectionconst result = await prisma.$queryRaw(`SELECT id, name, email FROM User WHERE name = ${inputString}`)console.log(result)
By contrast, the same query using tagged templates escapes inputString
and the query does not return results. The only difference between the two examples is that ()
have been replaced by ``
:
const inputString = '"Sarah" UNION SELECT id, title, content FROM Post'const result = await prisma.$queryRaw`SELECT id, name, email FROM User WHERE name = ${inputString}`console.log(result)