Raw database access

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.

model Post {
  id         Int     @default(autoincrement()) @id
 published  Boolean @default(false)
  title      String
  content    String?
}

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

Return type

$executeRaw returns a number.

Signature

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

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,
  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.

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}`
)

await prisma.$executeRaw`ALTER USER prisma WITH PASSWORD '${password}'` // Tagged template
await prisma.$executeRaw(`ALTER USER prisma WITH PASSWORD $1`, newPassword) // Parameterized query

await prisma.$executeRaw(`ALTER USER prisma WITH PASSWORD '${password}'`)

Warning: String concatenation

The following example concatenates 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)