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:

1;[
2 { id: 1, email: 'sarah@prisma.io', name: 'Sarah' },
3 { id: 2, email: 'alice@prisma.io', name: 'Alice' },
4]

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[]:

1// import the generated `User` type from the `@prisma/client` module
2import { User } from '@prisma/client'
3
4const result = await prisma.queryRaw<User[]>('SELECT * FROM User;')
5// 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:

1model Post {
2 id Int @default(autoincrement()) @id
3 published Boolean @default(false)
4 title String
5 content String?
6}

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

1const result = await prisma.queryRaw<Post[]>`SELECT * FROM Post`
2
3result.forEach(x => {
4 console.log(x.published)
5})

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:

1const result: number = await prisma.$executeRaw(
2 'UPDATE User SET active = true WHERE emailValidated = true;'
3)

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:

1const emailValidated = true
2const active = true
3
4const 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

1executeRaw<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 ():

1const userId = 42
2const 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:

1import { join } from "@prisma/client";
2
3const ids = [1, 3, 5, 10, 20];
4const result = await prisma.$queryRaw`SELECT * FROM User WHERE id IN (${join(
5 ids
6)})`;

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

1import { sql, empty } from "@prisma/client";
2
3const userName = "";
4const result = await prisma.$queryRaw`SELECT * FROM User ${
5 userName ? sql`WHERE name = ${userName}` : empty // Cannot use "" or NULL here!
6}`;

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):

1const userName = 'Sarah'
2const email = 'alice@prisma.io'
3const result = await prisma.queryRaw(
4 'SELECT * FROM User WHERE (name = ? OR email = ?)', // MySQL variable, represented by ?
5 userName,
6 email
7)

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.

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:

1const inputString = '"Sarah" UNION SELECT id, title, content FROM Post' // SQL Injection
2const query = 'SELECT id, name, email FROM User WHERE name = ' + inputString
3const result = await prisma.queryRaw(query)
4
5console.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:

1const inputString = '"Sarah" UNION SELECT id, title, content FROM Post' // SQL Injection
2const result = await prisma.queryRaw(`SELECT id, name, email FROM User WHERE name = ${inputString}`)
3
4console.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 ``:

1const inputString = '"Sarah" UNION SELECT id, title, content FROM Post'
2const result = await prisma.queryRaw`SELECT id, name, email FROM User WHERE name = ${inputString}`
3
4console.log(result)
Edit this page on Github