原始查询

警告

借助 Prisma ORM 5.19.0，我们发布了 TypedSQL。TypedSQL 是一种编写类型安全的 SQL 查询的新方法，甚至更容易添加到你的工作流程中。

¥With Prisma ORM 5.19.0, we have released TypedSQL. TypedSQL is a new way to write SQL queries that are type-safe and even easier to add to your workflow.

我们强烈建议尽可能使用 TypedSQL 查询而不是下面描述的旧式原始查询。

¥We strongly recommend using TypedSQL queries over the legacy raw queries described below whenever possible.

Prisma 客户端支持将原始查询发送到数据库的选项。如果出现以下情况，你可能希望使用原始查询：

¥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

• 你需要 Prisma 客户端尚不支持的功能（请 考虑提出问题）

  ¥you require a feature that Prisma Client does not yet support (please consider raising an issue)

原始查询适用于 Prisma ORM 支持的所有关系数据库。此外，从版本 3.9.0 开始，MongoDB 支持原始查询。有关更多详细信息，请参阅相关部分：

¥Raw queries are available for all relational databases Prisma ORM 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

• 使用 MongoDB 进行原始查询

  ¥Raw queries with MongoDB

使用关系数据库的原始查询

¥Raw queries with relational databases

对于关系数据库，Prisma Client 公开了四种允许你发送原始查询的方法。你可以使用：

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

• $queryRaw 返回实际记录（例如，使用 SELECT）。

  ¥$queryRaw to return actual records (for example, using SELECT).

• $executeRaw 返回受影响行的计数（例如，在 UPDATE 或 DELETE 之后）。

  ¥$executeRaw to return a count of affected rows (for example, after an UPDATE or DELETE).

• $queryRawUnsafe 使用原始字符串返回实际记录（例如，使用 SELECT）。

  ¥$queryRawUnsafe to return actual records (for example, using SELECT) using a raw string.

• $executeRawUnsafe 使用原始字符串返回受影响行的计数（例如，在 UPDATE 或 DELETE 之后）。

  ¥$executeRawUnsafe to return a count of affected rows (for example, after an UPDATE or DELETE) using a raw string.

名称中带有 "不安全" 的方法更加灵活，但存在使代码容易受到 SQL 注入攻击的巨大风险。

¥The methods with "Unsafe" in the name are a lot more flexible but are at significant risk of making your code vulnerable to SQL injection.

其他两种方法可以安全地使用简单的模板标记，无需构建字符串，也无需连接。但是，对于更复杂的用例需要谨慎，因为如果以某些方式使用这些方法，仍然可能引入 SQL 注入。有关更多详细信息，请参阅下面的 SQL 注入预防 部分。

¥The other two methods are safe to use with a simple template tag, no string building, and no concatenation. However, caution is required for more complex use cases as it is still possible to introduce SQL injection if these methods are used in certain ways. For more details, see the SQL injection prevention section below.

注意：上面列表中的所有方法一次只能运行一个查询。你无法追加第二个查询 - 例如，用 select 1; select 2; 调用其中任何一个都不起作用。

¥Note: All methods in the above list can only run one query at a time. You cannot append a second query - for example, calling any of them with select 1; select 2; will not work.

$queryRaw

$queryRaw 返回实际的数据库记录。例如，以下 SELECT 查询返回 User 表中每条记录的所有字段：

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

该方法作为 标记模板 实现，它允许你传递模板字面量，你可以在其中轻松插入 variables。反过来，Prisma 客户端会创建免受 SQL 注入攻击的准备好的语句：

¥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 Client 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}`;

你还可以使用 Prisma.sql 辅助程序，事实上，$queryRaw 方法只接受模板字符串或 Prisma.sql 辅助程序：

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

warning

如果你使用字符串构建将不受信任的输入合并到传递给此方法的查询中，那么你就有可能遭受 SQL 注入攻击。SQL 注入攻击可能会使你的数据遭到修改或删除。首选机制是在运行此方法时包含查询的文本。有关此风险的更多信息以及如何预防此风险的示例，请参阅下面的 SQL 注入预防 部分。

¥If you use string building to incorporate untrusted input into queries passed to this method, then you open up the possibility for SQL injection attacks. SQL injection attacks can expose your data to modification or deletion. The preferred mechanism would be to include the text of the query at the point that you run this method. For more information on this risk and also examples of how to prevent it, see the SQL injection prevention section below.

注意事项

¥Considerations

意识到：

¥Be aware that:

• 模板变量不能在 SQL 字符串字面量内使用。例如，以下查询将不起作用：

  ¥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};`;

• 模板变量只能用于数据值（例如上例中的 email）。变量不能用于标识符，例如列名、表名或数据库名，也不能用于 SQL 关键字。例如，以下两个查询将不起作用：

  ¥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 将 $queryRaw 和 $queryRawUnsafe 返回的任何数据库值映射到其相应的 JavaScript 类型。了解更多。

  ¥Prisma maps any database values returned by $queryRaw and $queryRawUnsafe to their corresponding JavaScript types. Learn more.

• $queryRaw 不支持 PostgreSQL 数据库中的动态表名称。了解更多

  ¥$queryRaw does not support dynamic table names in PostgreSQL databases. Learn more

返回类型

¥Return type

$queryRaw 返回一个数组。每个对象对应一条数据库记录：

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

你也可以 输入 $queryRaw 的结果。

¥You can also type the results of $queryRaw.

签名

¥Signature

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

输入 $queryRaw 结果

¥Typing $queryRaw results

PrismaPromise<T> 使用 泛型类型参数 T。你可以在调用 $queryRaw 方法时确定 T 的类型。在以下示例中，$queryRaw 返回 User[]：

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

注意：如果你不提供类型，则 $queryRaw 默认为 unknown。

¥Note: If you do not provide a type, $queryRaw defaults to unknown.

如果你选择模型的特定字段或想要包含关系，请参阅有关 利用 Prisma Client 生成的类型 的文档并确保结果输入正确。

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

使用原始 SQL 时的输入注意事项

¥Type caveats when using raw SQL

当你输入 $queryRaw 的结果时，原始数据可能并不总是与建议的 TypeScript 类型匹配。例如，以下 Prisma 模型包含名为 published 的 Boolean 字段：

¥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     String
  content   String?
}

以下查询返回所有帖子。然后它打印出每个 Post 的 published 字段的值：

¥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);
});

对于常规 CRUD 查询，Prisma 客户端查询引擎标准化了所有数据库的返回类型。使用原始查询则不会。如果数据库提供者是 MySQL，则返回值为 1 或 0。但是，如果数据库提供程序是 PostgreSQL，则值为 true 或 false。

¥For regular CRUD queries, 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 returned values are 1 or 0. However, if the database provider is PostgreSQL, the values are true or false.

注意：Prisma 将 JavaScript 整数作为 INT8 发送到 PostgreSQL。这可能与仅接受 INT4 作为输入的用户定义函数冲突。如果你将 $queryRaw 与 PostgreSQL 数据库结合使用，请将输入类型更新为 INT8，或将查询参数转换为 INT4。

¥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 $queryRaw in conjunction with a PostgreSQL database, update the input types to INT8, or cast your query parameters to INT4.

PostgreSQL 中的动态表名称

¥Dynamic table names in PostgreSQL

无法插入表名。这意味着你不能将动态表名与 $queryRaw 一起使用。相反，你必须使用 $queryRawUnsafe，如下所示：

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

请注意，如果将 $queryRawUnsafe 与用户输入结合使用，则会面临 SQL 注入攻击的风险。了解更多。

¥Note that if you use $queryRawUnsafe in conjunction with user inputs, you risk SQL injection attacks. Learn more.

$queryRawUnsafe()

$queryRawUnsafe() 方法允许你将原始字符串（或模板字符串）传递到数据库。

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

warning

如果你将此方法与用户输入（换句话说，SELECT * FROM table WHERE columnx = ${userInput}）一起使用，那么你就有可能遭受 SQL 注入攻击。SQL 注入攻击可能会使你的数据遭到修改或删除。

¥If you use this method with user inputs (in other words, 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 方法。正确使用时，$queryRaw 方法明显更安全，但请注意，$queryRaw 方法在某些情况下也可能容易受到攻击。有关详细信息，请参阅下面的 SQL 注入预防 部分。

¥Wherever possible you should use the $queryRaw method instead. When used correctly $queryRaw method is significantly safer but note that the $queryRaw method can also be made vulnerable in certain circumstances. For more information, see the SQL injection prevention section below.

以下查询返回 User 表中每条记录的所有字段：

¥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");

你还可以运行参数化查询。以下示例返回电子邮件包含字符串 emelie@prisma.io 的所有用户：

¥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");

注意：Prisma 将 JavaScript 整数作为 INT8 发送到 PostgreSQL。这可能与仅接受 INT4 作为输入的用户定义函数冲突。如果你将参数化 $queryRawUnsafe 查询与 PostgreSQL 数据库结合使用，请将输入类型更新为 INT8，或将查询参数转换为 INT4。

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

有关使用参数化查询的更多详细信息，请参阅下面的 参数化查询 部分。

¥For more details on using parameterized queries, see the parameterized queries section below.

签名

¥Signature

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

$executeRaw

$executeRaw 返回受数据库操作影响的行数，例如 UPDATE 或 DELETE。该函数不返回数据库记录。以下查询更新数据库中的记录并返回已更新的记录数：

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

该方法作为 标记模板 实现，它允许你传递模板字面量，你可以在其中轻松插入 variables。反过来，Prisma 客户端会创建免受 SQL 注入攻击的准备好的语句：

¥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 Client 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};`;

warning

如果你使用字符串构建将不受信任的输入合并到传递给此方法的查询中，那么你就有可能遭受 SQL 注入攻击。SQL 注入攻击可能会使你的数据遭到修改或删除。首选机制是在运行此方法时包含查询的文本。有关此风险的更多信息以及如何预防此风险的示例，请参阅下面的 SQL 注入预防 部分。

¥If you use string building to incorporate untrusted input into queries passed to this method, then you open up the possibility for SQL injection attacks. SQL injection attacks can expose your data to modification or deletion. The preferred mechanism would be to include the text of the query at the point that you run this method. For more information on this risk and also examples of how to prevent it, see the SQL injection prevention section below.

注意事项

¥Considerations

意识到：

¥Be aware that:

• $executeRaw 不支持单个字符串中的多个查询（例如，ALTER TABLE 和 CREATE TABLE 在一起）。

  ¥$executeRaw does not support multiple queries in a single string (for example, ALTER TABLE and CREATE TABLE together).

• Prisma Client 提交准备好的语句，准备好的语句仅允许 SQL 语句的子集。例如，不允许使用 START TRANSACTION。你可以了解更多有关 MySQL 在准备语句中允许使用的语法 的信息。

  ¥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 不支持 ALTER - 参见 workaround。

  ¥PREPARE does not support ALTER - see the workaround.

• 模板变量不能在 SQL 字符串字面量内使用。例如，以下查询将不起作用：

  ¥Template variables cannot be used inside SQL string literals. For example, the following query would not work:

  const name = "Bob";
  await prisma.$executeRaw`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.$executeRaw`UPDATE user SET greeting = ${name};`;

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

• 模板变量只能用于数据值（例如上例中的 email）。变量不能用于标识符，例如列名、表名或数据库名，也不能用于 SQL 关键字。例如，以下两个查询将不起作用：

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

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

返回类型

¥Return type

$executeRaw 返回 number。

¥$executeRaw returns a number.

签名

¥Signature

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

$executeRawUnsafe()

$executeRawUnsafe() 方法允许你将原始字符串（或模板字符串）传递到数据库。与 $executeRaw 一样，它不返回数据库记录，而是返回受影响的行数。

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

warning

如果你将此方法与用户输入（换句话说，SELECT * FROM table WHERE columnx = ${userInput}）一起使用，那么你就有可能遭受 SQL 注入攻击。SQL 注入攻击可能会使你的数据遭到修改或删除。

¥If you use this method with user inputs (in other words, 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 方法。正确使用时，$executeRaw 方法明显更安全，但请注意，$executeRaw 方法在某些情况下也可能容易受到攻击。有关详细信息，请参阅下面的 SQL 注入预防 部分。

¥Wherever possible you should use the $executeRaw method instead. When used correctly $executeRaw method is significantly safer but note that the $executeRaw method can also be made vulnerable in certain circumstances. For more information, see the SQL injection prevention section below.

以下示例使用模板字符串来更新数据库中的记录。然后它返回已更新的记录数：

¥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
);

有关使用参数化查询的更多详细信息，请参阅下面的 参数化查询 部分。

¥For more details on using parameterized queries, see the parameterized queries section below.

签名

¥Signature

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

原始查询类型映射

¥Raw query type mapping

Prisma 将 $queryRaw 和 $queryRawUnsafe 返回的任何数据库值映射到其相应的 JavaScript 类型。此行为与 findMany() 等常规 Prisma 查询方法相同。

¥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().

info

功能可用性：

¥Feature availability:

• 在 v3.14.x 和 v3.15.x 中，预览功能 improvedQueryRaw 提供了原始查询类型映射。我们在 4.0.0 版本中做了原始查询类型映射 一般可用，因此你不需要在 4.0.0 或更高版本中使用 improvedQueryRaw。

  ¥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 use improvedQueryRaw in version 4.0.0 or later.

• 在 4.0.0 版本之前，原始查询类型映射不适用于 SQLite。

  ¥Before version 4.0.0, raw query type mapping was not available for SQLite.

例如，采用一个从表中选择 BigInt、Bytes、Decimal 和 Date 类型的列的原始查询：

¥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);

Show CLI results

{ bigint: BigInt("123"), bytes: <Buffer 01 02>), decimal: Decimal("12.34"), date: Date("<some_date>") }

在 result 对象中，数据库值已映射到相应的 JavaScript 类型。

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

下表显示了数据库中使用的类型与原始查询返回的 JavaScript 类型之间的转换：

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

数据库类型	JavaScript 类型
文本	String
32 位整数	Number
32 位无符号整数	BigInt
浮点数	Number
双精度数	Number
64 位整数	BigInt
小数/数字	Decimal
字节	Uint8Array(v6 之前: Buffer)
杰森	Object
DateTime	Date
日期	Date
时间	Date
Uuid	String
XML	String

请注意，每种数据库类型的确切名称因数据库而异，例如，布尔类型在 PostgreSQL 中称为 boolean，在 CockroachDB 中称为 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.

原始查询类型转换行为

¥Raw query typecasting behavior

使用 Prisma Client 的原始查询可能需要参数符合 SQL 函数或查询的预期类型。Prisma Client 不会进行微妙的隐式转换。

¥Raw queries with Prisma Client might require parameters to be in the expected types of the SQL function or query. Prisma Client does not do subtle, implicit casts.

例如，使用 PostgreSQL 的 LENGTH 函数进行以下查询，该函数仅接受 text 类型作为输入：

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

此查询返回错误：

¥This 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.

在这种情况下，解决方案是将 42 明确转换为 text 类型：

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

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

信息

功能可用性：自 4.0.0 版以来，此功能为 一般可用。在 v3.14.x 和 v3.15.x 中，它与预览功能 improvedQueryRaw 一起提供。

¥Feature availability: This funtionality is Generally Available since version 4.0.0. In v3.14.x and v3.15.x, it was available with the preview feature improvedQueryRaw.

对于上述 4.0.0 版本之前的示例，Prisma ORM 会默默地将 42 强制转换为 text，并且不需要显式转换。

¥For the example above before version 4.0.0, Prisma ORM silently coerces 42 to text and does not require the explicit cast.

另一方面，以下原始查询现在可以正常工作，返回整数结果，之前失败：

¥On the other hand the following raw query now works correctly, returning an integer result, and failed before:

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

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

事务

¥Transactions

在 2.10.0 及更高版本中，你可以在 transaction 中使用 .$executeRaw() 和 .$queryRaw()。

¥In 2.10.0 and later, you can use .$executeRaw() and .$queryRaw() inside a transaction.

使用变量

¥Using variables

$executeRaw 和 $queryRaw 作为 标记模板 实现。标记模板是在 Prisma 客户端中使用原始 SQL 变量的推荐方法。

¥$executeRaw and $queryRaw are implemented as tagged templates. Tagged templates are the recommended way to use variables with raw SQL in the Prisma Client.

以下示例包含名为 ${userId} 的占位符：

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

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

✔ 使用 $queryRaw 和 $executeRaw 的标记模板版本的好处包括：

¥✔ Benefits of using the tagged template versions of $queryRaw and $executeRaw include:

• Prisma 客户端转义所有变量。

  ¥Prisma Client escapes all variables.

• 标记模板与数据库无关 - 你不需要记住变量是否应该写为 $1 (PostgreSQL) 还是 ? (MySQL)。

  ¥Tagged templates are database-agnostic - you do not need to remember if variables should be written as $1 (PostgreSQL) or ? (MySQL).

• SQL 模板标签 允许你访问 有用的帮手。

  ¥SQL Template Tag give you access to useful helpers.

• 嵌入式命名变量更易于阅读。

  ¥Embedded, named variables are easier to read.

注意：你无法将表或列名称传递到带标记的模板占位符中。例如，你不能根据某些条件 SELECT ? 并传入 * 或 id, name。

¥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 特别使用 SQL 模板标签，它公开了许多辅助程序。例如，以下查询使用 join() 传入 ID 列表：

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

以下示例使用 empty 和 sql 辅助程序根据 userName 是否为空来更改查询：

¥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 限制 (PostgreSQL)

¥ALTER limitation (PostgreSQL)

PostgreSQL 不支持在准备好的语句中使用 ALTER，这意味着以下查询将不起作用：

¥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}"`);

你可以使用以下查询，但请注意，这可能不安全，因为 ${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 型 需要先转换为 Prisma Client 支持的类型，然后才能在 $queryRaw 或 $queryRawUnsafe 中使用它们。例如，采用以下模型，其中具有 location 字段和 Unsupported 类型：

¥Unsupported types need to be cast to Prisma Client 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;`;

相反，如果你的 Unsupported 列支持转换，则将 Unsupported 字段转换为任何支持的 Prisma 客户端类型。

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

你可能希望将 Unsupported 列转换为 String 的最常见类型。例如，在 PostgreSQL 上，这将映射到 text 类型：

¥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;`;

因此，数据库将提供 Prisma 客户端支持的数据的 String 表示形式。

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

有关支持的 Prisma 类型的详细信息，请参阅相关数据库的 Prisma 连接器概述。

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

SQL 注入预防

¥SQL injection prevention

在 Prisma Client 中避免 SQL 注入的理想方法是尽可能使用 ORM 模型执行查询。

¥The ideal way to avoid SQL injection in Prisma Client is to use the ORM models to perform queries wherever possible.

如果这是不可能的并且需要原始查询，Prisma Client 提供了各种原始方法，但安全地使用这些方法非常重要。

¥Where this is not possible and raw queries are required, Prisma Client provides various raw methods, but it is important to use these methods safely.

本节将提供安全和不安全地使用这些方法的各种示例。你可以在 Prisma 在线运行 中测试这些示例。

¥This section will provide various examples of using these methods safely and unsafely. You can test these examples in the Prisma Playground.

在 $queryRaw 和 $executeRaw 中

¥In $queryRaw and $executeRaw

$queryRaw 和 $executeRaw 的使用简单、安全

¥Simple, safe use of $queryRaw and $executeRaw

当你使用标记模板并将所有查询作为准备好的语句发送时，这些方法可以通过转义所有变量来降低 SQL 注入的风险。

¥These methods can mitigate the risk of SQL injection by escaping all variables when you use tagged templates and sends all queries as prepared statements.

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

以下示例对于 SQL 注入是安全的：

¥The following example is safe ✅ from SQL Injection:

const inputString = `'Sarah' UNION SELECT id, title FROM "Post"`;
const result = await prisma.$queryRaw`SELECT id, name FROM "User" WHERE name = ${inputString}`;

console.log(result);

$queryRaw 和 $executeRaw 的不安全使用

¥Unsafe use of $queryRaw and $executeRaw

然而，也有可能以不安全的方式使用这些方法。

¥However, it is also possible to use these methods in unsafe ways.

一种方法是人为生成标记模板，该模板不安全地连接用户输入。

¥One way is by artificially generating a tagged template that unsafely concatenates user input.

以下示例容易受到 SQL 注入攻击：

¥The following example is vulnerable ❌ to SQL Injection:

// Unsafely generate query text
const inputString = `'Sarah' UNION SELECT id, title FROM "Post"`; // SQL Injection
const query = `SELECT id, name FROM "User" WHERE name = ${inputString}`;

// Version for Typescript
const stringsArray: any = [...[query]];

// Version for Javascript
const stringsArray = [...[query]];

// Use the `raw` property to impersonate a tagged template
stringsArray.raw = [query];

// Use queryRaw
const result = await prisma.$queryRaw(stringsArray);
console.log(result);

使这些方法容易受到攻击的另一种方法是滥用 Prisma.raw 函数。

¥Another way to make these methods vulnerable is misuse of the Prisma.raw function.

以下示例都容易受到 SQL 注入攻击：

¥The following examples are all vulnerable ❌ to SQL Injection:

const inputString = `'Sarah' UNION SELECT id, title FROM "Post"`;
const result = await prisma.$queryRaw`SELECT id, name FROM "User" WHERE name = ${Prisma.raw(
  inputString
)}`;
console.log(result);

const inputString = `'Sarah' UNION SELECT id, title FROM "Post"`;
const result = await prisma.$queryRaw(
  Prisma.raw(`SELECT id, name FROM "User" WHERE name = ${inputString}`)
);
console.log(result);

const inputString = `'Sarah' UNION SELECT id, title FROM "Post"`;
const query = Prisma.raw(`SELECT id, name FROM "User" WHERE name = ${inputString}`);
const result = await prisma.$queryRaw(query);
console.log(result);

在更复杂的场景中安全地使用 $queryRaw 和 $executeRaw

¥Safely using $queryRaw and $executeRaw in more complex scenarios

构建与查询执行分开的原始查询

¥Building raw queries separate to query execution

如果你想在其他地方构建原始查询或与参数分开，则需要使用以下方法之一。

¥If you want to build your raw queries elsewhere or separate to your parameters you will need to use one of the following methods.

在此示例中，sql 辅助方法用于通过安全地包含变量来构建查询文本。它对于 SQL 注入是安全的：

¥In this example, the sql helper method is used to build the query text by safely including the variable. It is safe ✅ from SQL Injection:

// inputString can be untrusted input
const inputString = `'Sarah' UNION SELECT id, title FROM "Post"`;

// Safe if the text query below is completely trusted content
const query = Prisma.sql`SELECT id, name FROM "User" WHERE name = ${inputString}`;

const result = await prisma.$queryRaw(query);
console.log(result);

在此示例中，可以安全地防止 SQL 注入，sql 辅助方法用于构建查询文本，其中包括输入值的参数标记。每个变量都由一个标记符号表示（MySQL 为 ?，PostgreSQL 为 $1、$2 等）。请注意，这些示例仅显示 PostgreSQL 查询。

¥In this example which is safe ✅ from SQL Injection, the sql helper method is used to build the query text including a parameter marker for the input value. Each variable is represented by a marker symbol (? for MySQL, $1, $2, and so on for PostgreSQL). Note that the examples just show PostgreSQL queries.

// Version for Typescript
const query: any;

// Version for Javascript
const query;

// Safe if the text query below is completely trusted content
query = Prisma.sql`SELECT id, name FROM "User" WHERE name = $1`;

// inputString can be untrusted input
const inputString = `'Sarah' UNION SELECT id, title FROM "Post"`;
query.values = [inputString];

const result = await prisma.$queryRaw(query);
console.log(result);

注意：PostgreSQL 变量用 $1 等表示

¥Note: PostgreSQL variables are represented by $1, etc

在其他地方或分阶段构建原始查询

¥Building raw queries elsewhere or in stages

如果你想在查询执行位置以外的位置构建原始查询，则理想的方法是从查询段创建 Sql 对象并向其传递参数值。

¥If you want to build your raw queries somewhere other than where the query is executed, the ideal way to do this is to create an Sql object from the segments of your query and pass it the parameter value.

在下面的示例中，我们有两个要参数化的变量。只要传递给 Prisma.sql 的查询字符串仅包含可信内容，该示例就不会受到 SQL 注入的攻击：

¥In the following example we have two variables to parameterize. The example is safe ✅ from SQL Injection as long as the query strings being passed to Prisma.sql only contain trusted content:

// Example is safe if the text query below is completely trusted content
const query1 = `SELECT id, name FROM "User" WHERE name = `; // The first parameter would be inserted after this string
const query2 = ` OR name = `; // The second parameter would be inserted after this string

const inputString1 = "Fred";
const inputString2 = `'Sarah' UNION SELECT id, title FROM "Post"`;

const query = Prisma.sql([query1, query2, ""], inputString1, inputString2);
const result = await prisma.$queryRaw(query);
console.log(result);

注意：请注意，作为第一个参数 Prisma.sql 传递的字符串数组需要在末尾有一个空字符串，因为 sql 函数需要比参数数量多一个查询段。

¥Note: Notice that the string array being passed as the first parameter Prisma.sql needs to have an empty string at the end as the sql function expects one more query segment than the number of parameters.

如果你想将原始查询构建为一个大字符串，这仍然是可能的，但需要小心，因为它使用潜在危险的 Prisma.raw 方法。你还需要使用数据库的正确参数标记来构建查询，因为 Prisma 无法像通常那样为相关数据库提供标记。

¥If you want to build your raw queries into one large string, this is still possible but requires some care as it is uses the potentially dangerous Prisma.raw method. You also need to build your query using the correct parameter markers for your database as Prisma won't be able to provide markers for the relevant database as it usually is.

只要传递给 Prisma.raw 的查询字符串仅包含可信内容，以下示例就可以安全地防止 SQL 注入：

¥The following example is safe ✅ from SQL Injection as long as the query strings being passed to Prisma.raw only contain trusted content:

// Version for Typescript
const query: any;

// Version for Javascript
const query;

// Example is safe if the text query below is completely trusted content
const query1 = `SELECT id, name FROM "User" `;
const query2 = `WHERE name = $1 `;

query = Prisma.raw(`${query1}${query2}`);

// inputString can be untrusted input
const inputString = `'Sarah' UNION SELECT id, title FROM "Post"`;
query.values = [inputString];

const result = await prisma.$queryRaw(query);
console.log(result);

在 $queryRawUnsafe 和 $executeRawUnsafe 中

¥In $queryRawUnsafe and $executeRawUnsafe

不安全地使用 $queryRawUnsafe 和 $executeRawUnsafe

¥Using $queryRawUnsafe and $executeRawUnsafe unsafely

如果你不能使用标记模板，则可以改用 $queryRawUnsafe 或 $executeRawUnsafe。但是，请注意，这些函数会显著增加代码中 SQL 注入漏洞的风险。

¥If you cannot use tagged templates, you can instead use $queryRawUnsafe or $executeRawUnsafe. However, be aware that these functions significantly increase the risk of SQL injection vulnerabilities in your code.

以下示例连接 query 和 inputString。Prisma Client ❌ 在本例中无法转义 inputString，这使其容易受到 SQL 注入攻击：

¥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);

参数化查询

¥Parameterized queries

作为标记模板的替代方案，$queryRawUnsafe 支持标准参数化查询，其中每个变量都由一个符号表示（MySQL 为 ?，PostgreSQL 为 $1、$2 等）。请注意，这些示例仅显示 PostgreSQL 查询。

¥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). Note that the examples just show PostgreSQL queries.

以下示例对于 SQL 注入是安全的：

¥The following example is safe ✅ from SQL Injection:

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

注意：PostgreSQL 变量由 $1 和 $2 表示

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

与标记模板一样，Prisma Client 会转义以这种方式提供的所有变量。

¥As with tagged templates, Prisma Client escapes all variables when they are provided in this way.

注意：你不能将表名或列名作为变量传递到参数化查询中。例如，你不能根据某些条件 SELECT ? 并传入 * 或 id, name。

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

参数化 PostgreSQL ILIKE 查询

¥Parameterized PostgreSQL ILIKE query

当你使用 ILIKE 时，% 通配符应包含在变量本身中，而不是查询中 (string)。此示例对于 SQL 注入是安全的。

¥When you use ILIKE, the % wildcard character(s) should be included in the variable itself, not the query (string). This example is safe ✅ from SQL Injection.

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

注意：使用 %$2 作为参数是行不通的

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

使用 MongoDB 进行原始查询

¥Raw queries with MongoDB

对于版本 3.9.0 及更高版本的 MongoDB，Prisma 客户端公开了三种允许你发送原始查询的方法。你可以使用：

¥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 对数据库运行命令

  ¥$runCommandRaw to run a command against the database

• <model>.findRaw 查找零个或多个与过滤器匹配的文档。

  ¥<model>.findRaw to find zero or more documents that match the filter.

• <model>.aggregateRaw 对集合执行聚合操作。

  ¥<model>.aggregateRaw to perform aggregation operations on a collection.

$runCommandRaw()

$runCommandRaw() 针对数据库运行原始 MongoDB 命令。作为输入，它接受所有 MongoDB 数据库命令，但以下例外：

¥$runCommandRaw() runs a raw MongoDB command against the database. As input, it accepts all MongoDB database commands, with the following exceptions:

• find（使用 findRaw() 代替）

  ¥find (use findRaw() instead)

• aggregate（使用 aggregateRaw() 代替）

  ¥aggregate (use aggregateRaw() instead)

使用 $runCommandRaw() 执行 MongoDB 数据库命令时，需要注意以下几点：

¥When you use $runCommandRaw() to run a MongoDB database command, note the following:

• 调用 $runCommandRaw() 时传递的对象必须遵循 MongoDB 数据库命令的语法。

  ¥The object that you pass when you invoke $runCommandRaw() must follow the syntax of the MongoDB database command.

• 你必须使用 MongoDB 数据库命令的适当角色连接到数据库。

  ¥You must connect to the database with an appropriate role for the MongoDB database command.

在以下示例中，查询插入具有相同 _id 的两条记录。这绕过了正常的文档验证。

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

警告

不要将 $runCommandRaw() 用于包含 "find" 或 "aggregate" 命令的查询，因为你可能无法获取所有数据。这是因为 MongoDB 返回附加到你的 MongoDB 会话的 cursor，并且你可能不会每次都访问相同的 MongoDB 会话。对于这些查询，你应该使用专门的 findRaw() 和 aggregateRaw() 方法。

¥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() 返回一个 JSON 对象，其形状取决于输入。

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

签名

¥Signature

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

findRaw()

<model>.findRaw() 返回实际的数据库记录。它将在 User 集合上找到零个或多个与过滤器匹配的文档：

¥<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() 返回一个 JSON 对象，其形状取决于输入。

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

签名

¥Signature

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

• filter：查询谓词过滤器。如果未指定，则集合中的所有文档都将与 predicate 匹配。

  ¥filter: The query predicate filter. If unspecified, then all documents in the collection will match the predicate.

• options：传递给 find 命令 的附加选项。

  ¥options: Additional options to pass to the find command.

aggregateRaw()

<model>.aggregateRaw() 返回聚合的数据库记录。它将对 User 集合执行聚合操作：

¥<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() 返回一个 JSON 对象，其形状取决于输入。

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

签名

¥Signature

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

• pipeline：一系列聚合阶段，用于通过 聚合管道 处理和转换文档流。

  ¥pipeline: An array of aggregation stages to process and transform the document stream via the aggregation pipeline.

• options：传递给 aggregate 命令 的附加选项。

  ¥options: Additional options to pass to the aggregate command.

注意事项

¥Caveats

当使用自定义对象（如 ObjectId 或 Date,）时，你必须根据 MongoDB 扩展 JSON 规范 传递它们。示例：

¥When working with custom objects like ObjectId or Date, you will have to pass them according to the MongoDB extended JSON Spec. Example:

const result = await prisma.user.aggregateRaw({
  pipeline: [
    { $match: { _id: { $oid: id } } }
//                     ^ notice the $oid convention here 
  ],
});