Raw database access

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

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

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

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

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

// 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[]`

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

const result = await prisma.$queryRaw<Post[]>`SELECT * FROM Post`

result.forEach((x) => {
  console.log(x.published)
})

let userTable = 'User'
let result = await prisma.$queryRawUnsafe(`SELECT * FROM ${userTable}`)

$queryRawUnsafe

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

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

Note: Prisma sends JavaScript integers to PostgreSQL as INT8. This might conflict with your user-defined functions that accept only INT4 as input. If you use a parameterized $queryRawUnsafe query in conjunction with a PostgreSQL database, update the input types to INT8, or cast your query parameters to INT4.

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

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

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

const userName = 'Sarah'
const emailFragment = 'prisma.io'
const result = await prisma.$queryRawUnsafe(
  'SELECT * FROM "User" WHERE (name = $1 OR email ILIKE $2)',
  userName,
  `%${emailFragment}`
)

$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 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 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 SQL string literals. 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};`

• 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`UPDATE ${myTable} SET active = true;`

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

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

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
)

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

Raw query type mapping

Prisma maps any database values returned by $queryRaw and $queryRawUnsafeto their corresponding JavaScript types. This behavior is the same as for regular Prisma query methods like findMany.

As an example, take a raw query that selects columns with BigInt, Bytes, Decimal and Date types from a table:

const result =
  await prisma.$queryRaw`SELECT bigint, bytes, decimal, date FROM "Table";`

console.log(result)

${ bigint: BigInt("123"), bytes: Buffer.from([1, 2]), decimal: Decimal("12.34"), date: Date("<some_date>") }

In the result object, the database values have been mapped to the corresponding JavaScript types.

The following table shows the conversion between types used in the database and the JavaScript type returned by the raw query:

Note that the exact name for each database type will vary between databases – for example, the boolean type is known as boolean in PostgreSQL and STRING in CockroachDB. See the Scalar types reference for full details of type names for each database.

PostgreSQL typecasting fixes

Prisma resolves a number of issues with typecasting in PostgreSQL.

For example, the following raw query now works correctly, returning an integer result:

await prisma.$queryRaw`SELECT ${1.5}::int as int`

// Before: db error: ERROR: incorrect binary data format in bind parameter 1
// After: [{ int: 2 }]

A consequence of this fix is that some subtle implicit casts are now handled more strictly, so some queries that previously were allowed will now fail. As an example, take the following query using PostgreSQL's LENGTH function, which only accepts the text type as an input:

await prisma.$queryRaw`SELECT LENGTH(${42});`

Before version 4.0.0, Prisma silently coerces 42 to text. From version 4.0.0, the query returns an error:

$// ERROR: function length(integer) does not exist
$// HINT: No function matches the given name and argument types. You might need to add explicit type casts.

The fix in this case is to explicitly cast 42 to the text type:

await prisma.$queryRaw`SELECT LENGTH(${42}::text);`

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.

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

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

await prisma.$executeRaw`ALTER USER prisma WITH PASSWORD "${password}"`
await prisma.$executeRaw(
  Prisma.sql`ALTER USER prisma WITH PASSWORD "${password}"`
)

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.

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)

$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,
    },
  ],
})

$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 } },
})

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

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 } } },
  ],
})

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