Raw database access
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
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, usingSELECT
)$executeRaw
to return a count of affected rows (for example, after anUPDATE
orDELETE
)$queryRawUnsafe
to return actual records (for example, usingSELECT
) using a raw string. Potential SQL injection risk$executeRawUnsafe
to return a count of affected rows (for example, after anUPDATE
orDELETE
) 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}`)
Considerations
Be aware that:
Template variables cannot be used inside SQL string literals. 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 ${ordering};`Prisma maps any database values returned by
$queryRaw
and$queryRawUnsafe
to their corresponding JavaScript types. Learn more.$queryRaw
does not support dynamic table names in PostgreSQL databases. Learn more
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` moduleimport { 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 tounknown
.
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 might 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 Stringcontent String?}
The following query returns all posts. It then prints out the value of the published
field for 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
or0
. However, if the database provider is PostgreSQL, the values aretrue
,false
, orNULL
.
Note: Prisma sends JavaScript integers to PostgreSQL as
INT8
. This might conflict with your user-defined functions that accept onlyINT4
as input. If you use$queryRaw
in conjunction with a PostgreSQL database, update the input types toINT8
, or cast your query parameters toINT4
.
Dynamic table names in PostgreSQL
It is not possible to interpolate table names. This means that you cannot use dynamic table names with $queryRaw
. Instead, you must use $queryRawUnsafe
, as follows:
let userTable = 'User'let result = await prisma.$queryRawUnsafe(`SELECT * FROM ${userTable}`)
Note that if you use $queryRawUnsafe
in conjunction with user inputs, you risk SQL injection attacks. Learn more.
$queryRawUnsafe
The $queryRawUnsafe
method allows you to pass a raw string (or template string) to the database.
SELECT * FROM table WHERE columnx = ${userInput}
), then you open up the possibility for SQL injection attacks. SQL injection attacks can expose your data to modification or deletion.$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` moduleimport { 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 onlyINT4
as input. If you use a parameterized$queryRawUnsafe
query in conjunction with a PostgreSQL database, update the input types toINT8
, or cast your query parameters toINT4
.
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,)
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,)
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*
orid, 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`
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 = trueconst active = trueconst 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
andCREATE 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 supportALTER
- 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};`
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, addingDROP bobby_tables
to the end of anALTER
.
SELECT * FROM table WHERE columnx = ${userInput}
), then you open up the possibility for SQL injection attacks. SQL injection attacks can expose your data to modification or deletion.$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 = trueconst active = trueconst 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>;
Raw query type mapping
Prisma maps any database values returned by $queryRaw
and $queryRawUnsafe
to their corresponding JavaScript types. This behavior is the same as for regular Prisma query methods like findMany
.
- In v3.14.x and v3.15.x, raw query type mapping was available with the preview feature
improvedQueryRaw
. We made raw query type mapping Generally Available in version 4.0.0, so you do not need to useimprovedQueryRaw
in version 4.0.0 or later. - Before version 4.0.0, raw query type mapping was not available for SQLite.
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:
Database type | JavaScript type |
---|---|
Text | String |
32-bit integer | Number |
Floating point number | Number |
Double precision number | Number |
64-bit integer | BigInt |
Decimal / numeric | Decimal |
Bytes | Buffer |
Json | Object |
DateTime | Date |
Date | Date |
Time | Date |
Uuid | String |
Xml | String |
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.
Feature availability: In v3.14.x and v3.15.x, these PostgreSQL fixes were available with the preview feature improvedQueryRaw
. We made these fixes Generally Available in version 4.0.0, so you do not need to use improvedQueryRaw
in version 4.0.0 or later.
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 = 42const 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*
orid, 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, addingDROP bobby_tables
to the end of anALTER
.
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 Injectionconst query = 'SELECT id, name, email FROM User WHERE name = ' + inputStringconst 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. As input, it accepts all MongoDB database commands, with the following exceptions:
find
(usefindRaw
instead)aggregate
(useaggregateRaw
instead)
When you use $runCommandRaw
to run a MongoDB database command, note the following:
- The object that you pass when you invoke
$runCommandRaw
must follow the syntax of the MongoDB database command. - You must connect to the database with an appropriate role for the MongoDB database command.
In the following example, a query inserts two records with the same _id
. This bypasses 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,},],})
Do not use $runCommandRaw
for queries which contain the "find"
or "aggregate"
commands, because you might be unable to fetch all data. This is because MongoDB returns a cursor that is attached to your MongoDB session, and you might 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 thefind
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 theaggregate
command.