Prisma Client exposes two methods that allow you to send raw SQL queries to your database:

  • Use $queryRaw to return actual records (for example, using SELECT)
  • Use $executeRaw to return a count of affected rows (for example, after an UPDATE or DELETE)

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` module
import { 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 to any.

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()) @id
published Boolean @default(false)
title String
content String?
}

The following query returns all posts and prints out the value of each post's published property:

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 standardises 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 = true
const active = true
const 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 and CREATE TABLE together).
  • Prisma Client submits prepared statements, and prepared statements only allow a subset of SQL statements (START TRANSACTIONis not permitted). For example, "MySQL: SQL Syntax Allowed in Prepared Statements".

Return type

$executeRaw returns a number.

Signature

executeRaw<T = any>(query: string | TemplateStringsArray, ...values: any[]): Promise<number>;

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 = 42
const 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 * or id, 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 { join } from "@prisma/client";
const ids = [1, 3, 5, 10, 20];
const result = await prisma.$queryRaw`SELECT * FROM User WHERE id IN (${join(
ids
)})`;

The following example uses the empty and sql helpers to change the query depending on whether userName is empty:

import { sql, empty } from "@prisma/client";
const userName = "";
const result = await prisma.$queryRaw`SELECT * FROM User ${
userName ? sql`WHERE name = ${userName}` : 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,
email
)

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 $2
userName,
email
)

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 * or id, 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 work
userName,
`%${emailFragment}`
);

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`` or executeRaw`` ) 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, adding DROP bobby_tables to the end of an ALTER.

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 concatanates queryand 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 Injection
const query = 'SELECT id, name, email FROM User WHERE name = ' + inputString
const 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 Injection
const 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)
Edit this page on GitHub