Skip to main content

编写你自己的 SQL

虽然 Prisma Client API 旨在使所有数据库查询直观、类型安全和方便，但在某些情况下，原始 SQL 仍然是完成这项工作的最佳工具。

¥While the Prisma Client API aims to make all your database queries intuitive, type-safe, and convenient, there may still be situations where raw SQL is the best tool for the job.

这种情况可能由于各种原因而发生，例如需要优化特定查询的性能，或者因为 Prisma Client 的查询 API 无法完全表达你的数据需求。

¥This can happen for various reasons, such as the need to optimize the performance of a specific query or because your data requirements can't be fully expressed by Prisma Client's query API.

在大多数情况下，TypedSQL 允许你用 SQL 表达查询，同时仍然受益于 Prisma Client 的出色用户体验。但是，由于 TypedSQL 是静态类型的，它可能无法处理某些场景，例如动态生成的 WHERE 子句。在这些情况下，你将需要使用 $queryRaw 或 $executeRaw，或它们的不安全对应物。

¥In most cases, TypedSQL allows you to express your query in SQL while still benefiting from Prisma Client's excellent user experience. However, since TypedSQL is statically typed, it may not handle certain scenarios, such as dynamically generated WHERE clauses. In these cases, you will need to use $queryRaw or $executeRaw, or their unsafe counterparts.

使用 Prisma Client 和 TypedSQL 编写类型安全查询​

¥Writing type-safe queries with Prisma Client and TypedSQL

信息

TypedSQL 在 Prisma ORM 5.19.0 及更高版本中可用。有关以前版本中的原始数据库访问，请参阅 我们的原始查询文档。

¥TypedSQL is available in Prisma ORM 5.19.0 and later. For raw database access in previous versions, see our raw queries documentation.

什么是 TypedSQL？​

¥What is TypedSQL?

TypedSQL 是 Prisma ORM 的一项新功能，它允许你在 .sql 文件中编写查询，同时仍然享受 Prisma Client 的出色开发者体验。你可以编写自己熟悉的代码，并从完全类型的输入和输出中受益。

¥TypedSQL is a new feature of Prisma ORM that allows you to write your queries in .sql files while still enjoying the great developer experience of Prisma Client. You can write the code you're comfortable with and benefit from fully-typed inputs and outputs.

使用 TypedSQL，你可以：

¥With TypedSQL, you can:

  1. 使用熟悉的语法编写复杂的 SQL 查询

    ¥Write complex SQL queries using familiar syntax

  2. 受益于完整的 IDE 支持和 SQL 语法高亮

    ¥Benefit from full IDE support and syntax highlighting for SQL

  3. 将你的 SQL 查询作为完全类型化的函数导入到你的 TypeScript 代码中

    ¥Import your SQL queries as fully typed functions in your TypeScript code

  4. 通过 Prisma 类型系统的安全性保持原始 SQL 的灵活性

    ¥Maintain the flexibility of raw SQL with the safety of Prisma's type system

TypedSQL 特别适用于：

¥TypedSQL is particularly useful for:

  • 使用 Prisma 的查询 API 难以表达的复杂报告查询

    ¥Complex reporting queries that are difficult to express using Prisma's query API

  • 需要微调 SQL 的性能关键型操作

    ¥Performance-critical operations that require fine-tuned SQL

  • 利用 Prisma API 中尚不支持的数据库特定功能

    ¥Leveraging database-specific features not yet supported in Prisma's API

通过使用 TypedSQL，你可以编写高效、类型安全的数据库查询，而无需牺牲原始 SQL 的功能和灵活性。此功能允许你将自定义 SQL 查询无缝集成到 Prisma 驱动的应用中，确保类型安全并提高开发者的工作效率。

¥By using TypedSQL, you can write efficient, type-safe database queries without sacrificing the power and flexibility of raw SQL. This feature allows you to seamlessly integrate custom SQL queries into your Prisma-powered applications, ensuring type safety and improving developer productivity.

有关如何开始使用 TypedSQL 的详细指南，包括设置说明和使用示例，请参阅我们的 TypedSQL 文档。

¥For a detailed guide on how to get started with TypedSQL, including setup instructions and usage examples, please refer to our TypedSQL documentation.

原始查询​

¥Raw queries

在 5.19.0 版本之前，Prisma Client 仅支持非类型安全的原始 SQL 查询，并且需要手动将查询结果映射到所需类型。

¥Prior to version 5.19.0, Prisma Client only supported raw SQL queries that were not type-safe and required manual mapping of the query result to the desired type.

虽然不像 TypedSQL 那样符合人机工程学，但这些查询仍然受支持，并且在由于 TypedSQL 中尚不支持的功能或动态生成查询而无法进行 TypedSQL 查询时很有用。

¥While not as ergonomic as TypedSQL, these queries are still supported and are useful when TypedSQL queries are not possible either due to features not yet supported in TypedSQL or when the query is dynamically generated.

关系数据库中原始 SQL 查询的替代方法​

¥Alternative approaches to raw SQL queries in relational databases

Prisma ORM 支持四种在关系数据库中执行原始 SQL 查询的方法：

¥Prisma ORM supports four methods to execute raw SQL queries in relational databases:

这些命令类似于使用 TypedSQL，但它们不是类型安全的，并且在你的代码中而不是在专用的 .sql 文件中以字符串形式编写。

¥These commands are similar to using TypedSQL, but they are not type-safe and are written as strings in your code rather than in dedicated .sql files.

文档数据库中原始查询的替代方法​

¥Alternative approaches to raw queries in document databases

对于 MongoDB，Prisma ORM 支持三种执行原始查询的方法：

¥For MongoDB, Prisma ORM supports three methods to execute raw queries:

这些方法允许你执行原始 MongoDB 命令和查询，在你需要使用特定于 MongoDB 的功能或优化时提供灵活性。

¥These methods allow you to execute raw MongoDB commands and queries, providing flexibility when you need to use MongoDB-specific features or optimizations.

$runCommandRaw 用于执行数据库命令，<model>.findRaw 用于查找与过滤器匹配的文档，<model>.aggregateRaw 用于聚合操作。Prisma 3.9.0 及更高版本提供所有三种方法。

¥$runCommandRaw is used to execute database commands, <model>.findRaw is used to find documents that match a filter, and <model>.aggregateRaw is used for aggregation operations. All three methods are available from Prisma version 3.9.0 and later.

与关系数据库中的原始查询类似，这些方法不是类型安全的，需要手动处理查询结果。

¥Similar to raw queries in relational databases, these methods are not type-safe and require manual handling of the query results.