Raw database access (Reference)

Prisma Client supports the option of sending raw queries to your database. You may wish to use raw queries if:

you want to run a heavily optimized query
you require a feature that Prisma Client does not yet support (please consider raising an issue)

Raw queries are available for all relational databases Prisma supports. In addition, from version 3.9.0 raw queries are supported in MongoDB. For more details, see the relevant sections:

Raw queries with relational databases
Raw queries with MongoDB

Raw queries with relational databases

For relational databases, Prisma Client exposes four methods that allow you to send raw queries. You can use:

$queryRaw to return actual records (for example, using SELECT)
$executeRaw to return a count of affected rows (for example, after an UPDATE or DELETE)
$queryRawUnsafe to return actual records (for example, using SELECT) using a raw string. Potential SQL injection risk
$executeRawUnsafe to return a count of affected rows (for example, after an UPDATE or DELETE) using a raw string. Potential SQL injection risk

$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 method is implemented as a tagged template, which allows you to pass a template literal where you can easily insert your variables. In turn, Prisma creates prepared statements that are safe from SQL injections:


const email = 'emelie@prisma.io'
const result = await prisma.$queryRaw`SELECT * FROM User WHERE email = ${email}`

You can also use the Prisma.sql helper, in fact, the $queryRaw method will only accept a template string or the Prisma.sql helper:


const email = 'emelie@prisma.io'
const result = await prisma.$queryRaw(
  Prisma.sql`SELECT * FROM User WHERE email = ${email}`
)

Be aware that:

Template variables cannot be used inside strings. For example, the following query would not work:


const name = 'Bob'
await prisma.$queryRaw`SELECT 'My name is ${name}';`

Instead, you can either pass the whole string as a variable, or use string concatenation:


const name = 'My name is Bob'
await prisma.$queryRaw`SELECT ${name};`


const name = 'Bob'
await prisma.$queryRaw`SELECT 'My name is ' || ${name};`

Template variables can only be used for data values (such as email in the example above). Variables cannot be used for identifiers such as column names, table names or database names, or for SQL keywords. For example, the following two queries would not work:


const myTable = 'user'
await prisma.$queryRaw`SELECT * FROM ${myTable};`


const ordering = 'desc'
await prisma.$queryRaw`SELECT * FROM Table ORDER BY ${desc};`

Return type

$queryRaw returns an array. Each object corresponds to a database record:


[
  { id: 1, email: 'emelie@prisma.io', name: 'Emelie' },
  { id: 2, email: 'yin@prisma.io', name: 'Yin' },
]

You can also type the results of $queryRaw.

Signature


$queryRaw<T = unknown>(query: TemplateStringsArray | Prisma.Sql, ...values: any[]): PrismaPromise<T>;

Typing `$queryRaw` results

PrismaPromise<T> uses a generic type parameter T. You can determine the type of T when you invoke the $queryRaw method. 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 unknown.

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

Note: The Prisma Client query engine standardizes the return type for all databases. Using the raw queries does not. 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.

$queryRawUnsafe

The $queryRawUnsafe method allows you to pass a raw string (or template string) to the database.

By using this method with user inputs (i.e. SELECT * FROM table WHERE columnx = ${userInput}), you open up the possibility for SQL injection attacks. SQL injection attacks can expose your data, be it confidential or otherwise sensitive, to being modified, or even destroyed.

We strongly advise that you use the $queryRaw query instead. For more information on SQL injection attacks, see the OWASP SQL Injection guide.

The following query returns all fields for each record in the User table:


// import the generated `User` type from the `@prisma/client` module
import { User } from '@prisma/client'

const result = await prisma.$queryRawUnsafe('SELECT * FROM User')

You can also run a parameterized query. The following example returns all users whose email contains the string emelie@prisma.io:


prisma.$queryRawUnsafe(
  'SELECT * FROM users WHERE email = $1',
  'emelie@prisma.io'
)

Signature


$queryRawUnsafe<T = unknown>(query: string, ...values: any[]): PrismaPromise<T>;

Parameterized queries

As an alternative to tagged templates, $queryRawUnsafe supports 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 = 'sarah@prisma.io'
const result = await prisma.$queryRawUnsafe(
  'SELECT * FROM User WHERE (name = ? OR email = ?)',
  userName,
  email
)

Note: MySQL variables are represented by ?

The following example uses a PostgreSQL query:


const userName = 'Sarah'
const email = 'sarah@prisma.io'
const result = await prisma.$queryRawUnsafe(
  'SELECT * FROM User WHERE (name = $1 OR email = $2)',
  userName,
  email
)

Note: PostgreSQL variables are represented by $1 and $2

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.$queryRawUnsafe(
  'SELECT * FROM "User" WHERE (name = $1 OR email ILIKE $2)',
  userName,
  `%${emailFragment}`
)

Note: Using %$2 as an argument would not work

$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`


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. For example, START TRANSACTION is not permitted. You can learn more about the syntax that MySQL allows in Prepared Statements here.
PREPARE does not support ALTER - see the workaround.

Template variables cannot be used inside strings. For example, the following query would not work:


const name = 'Bob'
await prisma.$queryRaw`UPDATE user SET greeting = 'My name is ${name}';`

Instead, you can either pass the whole string as a variable, or use string concatenation:


const name = 'My name is Bob'
await prisma.$queryRaw`UPDATE user SET greeting = ${name};`


const name = 'Bob'
await prisma.$queryRaw`UPDATE user SET greeting = 'My name is ' || ${name};`


const myTable = 'user'
await prisma.$queryRaw`UPDATE ${myTable} SET active = true;`


const ordering = 'desc'
await prisma.$queryRaw`UPDATE User SET active = true ORDER BY ${desc};`

Return type

$executeRaw returns a number.

Signature


$executeRaw<T = unknown>(query: TemplateStringsArray | Prisma.Sql, ...values: any[]): PrismaPromise<number>;

$executeRawUnsafe

The $executeRawUnsafe method allows you to pass a raw string (or template string) to the database. Like $executeRaw, it does not return database records, but returns the number of rows affected.

Note: $executeRawUnsafe 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.

We strongly advise that you use the $executeRaw query instead. For more information on SQL injection attacks, see the OWASP SQL Injection guide.

The following example uses a template string to update records in the database. It then returns a count of the number of records that were updated:


const emailValidated = true
const active = true

const result = await prisma.$executeRawUnsafe(
  `UPDATE User SET active = ${active} WHERE emailValidated = ${emailValidated}`
)

The same can be written as a parameterized query:


const result = prisma.$executeRawUnsafe(
  'UPDATE User SET active = $1 WHERE emailValidated = $2',
  'yin@prisma.io',
  true
)

Signature


$executeRawUnsafe<T = unknown>(query: string, ...values: any[]): PrismaPromise<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 the Prisma Client.

The following example includes a placeholder named ${userId}:


const userId = 42
const result = await prisma.$queryRaw`SELECT * FROM User WHERE id = ${userId};`

✔ 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 { 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!
}`

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}"`
await prisma.$executeRaw(
  Prisma.sql`ALTER USER prisma WITH PASSWORD "${password}"`
)

You can use the following query, but be aware that this is potentially unsafe as ${password} is not escaped:


await prisma.$executeRawUnsafe('ALTER USER prisma WITH PASSWORD "$1"', password})

Unsupported types

Unsupported types need to be cast to Prisma supported types before using them in $queryRaw or $queryRawUnsafe. For example, take the following model, which has a location field with an Unsupported type:


model Country {
  location  Unsupported("point")?
}

The following query on the unsupported field will not work:


await prisma.$queryRaw`SELECT location FROM Country;`

Instead, cast Unsupported fields to any supported Prisma type, if your Unsupported column supports the cast.

The most common type you may want to cast your Unsupported column to is String. For example, on PostgreSQL, this would map to the text type:


await prisma.$queryRaw`SELECT location::text FROM Country;`

The database will thus provide a String representation of your data which Prisma supports.

For details of supported Prisma types, see the Prisma data connector for the relevant database.

SQL injection

Prisma Client mitigates the risk of SQL injection in the following ways:

Prisma Client escapes all variables when you use tagged templates and sends all queries as prepared statements.


$queryRaw`...` // Tagged template
$executeRaw`...` // Tagged template

$executeRaw 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, you can instead use $queryRawUnsafe or $executeRawUnsafe but be aware that your code may be vulnerable to SQL injection.

⚠️ 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 Injection
const query = 'SELECT id, name, email FROM User WHERE name = ' + inputString
const result = await prisma.$queryRawUnsafe(query)

console.log(result)

Raw queries with MongoDB

For MongoDB in versions 3.9.0 and later, Prisma Client exposes three methods that allow you to send raw queries. You can use:

$runCommandRaw to run a command against the database
<model>.findRaw to find zero or more documents that match the filter.
<model>.aggregateRaw to perform aggregation operations on a collection.

$runCommandRaw

$runCommandRaw runs a raw MongoDB command against the database. For example, this query inserts two records with the same _id, bypassing normal document validation:


prisma.$runCommandRaw({
  insert: 'Pets',
  bypassDocumentValidation: true,
  documents: [
    {
      _id: 1,
      name: 'Felinecitas',
      type: 'Cat',
      breed: 'Russian Blue',
      age: 12,
    },
    {
      _id: 1,
      name: 'Nao Nao',
      type: 'Dog',
      breed: 'Chow Chow',
      age: 2,
    },
  ],
})

Note that the $runCommandRaw command should not be used for queries which contain the "find" or "aggregate" commands, as you may be unable to fetch all data. This is because MongoDB returns a cursor that is attached to your MongoDB session, and you may not hit the same MongoDB session every time. For these queries, you should use the specialised findRaw and aggregateRaw methods instead.

Return type

$runCommandRaw returns a JSON object whose shape depends on the inputs.

Signature


$runCommandRaw(command: InputJsonObject): PrismaPromise<JsonObject>;

findRaw

<model>.findRaw returns actual database records. It will find zero or more documents that match the filter on the User collection:


const result = await prisma.user.findRaw({
  filter: { age: { $gt: 25 } },
  options: { projection: { _id: false } },
})

Return type

<model>.findRaw returns a JSON object whose shape depends on the inputs.

Signature


<model>.findRaw(args?: {filter?: InputJsonObject, options?: InputJsonObject}): PrismaPromise<JsonObject>;

filter: The query predicate filter. If unspecified, then all documents in the collection will match the predicate.
options: Additional options to pass to the find command.

aggregateRaw

<model>.aggregateRaw returns aggregated database records. It will perform aggregation operations on the User collection:


const result = await prisma.user.aggregateRaw({
  pipeline: [
    { $match: { status: 'registered' } },
    { $group: { _id: '$country', total: { $sum: 1 } } },
  ],
})

Return type

<model>.aggregateRaw returns a JSON object whose shape depends on the inputs.

Signature


<model>.aggregateRaw(args?: {pipeline?: InputJsonObject[], options?: InputJsonObject}): PrismaPromise<JsonObject>;

pipeline: An array of aggregation stages to process and transform the document stream via the aggregation pipeline.
options: Additional options to pass to the aggregate command.

Was this helpful?

Edit this page on GitHub

Raw database access

Raw queries with relational databases

$queryRaw

Return type

Signature

Typing $queryRaw results

Type caveats when using raw SQL

$queryRawUnsafe

Signature

Parameterized queries

Parameterized PostgreSQL ILIKE query

$executeRaw

Return type

Signature

$executeRawUnsafe

Signature

Transactions

Using variables

Tagged template helpers

ALTER limitation (PostgreSQL)

Unsupported types

SQL injection

⚠️ String concatenation

Raw queries with MongoDB

$runCommandRaw

Return type

Signature

findRaw

Return type

Signature

aggregateRaw

Return type

Signature

Was this helpful?

Products

Resources

Prisma With

Community

Company

Newsletter

Find Us

Typing `$queryRaw` results

Parameterized PostgreSQL `ILIKE` query