TypedSQL

开始使用 TypedSQL

¥Getting started with TypedSQL

要开始在 Prisma 项目中使用 TypedSQL，请按照以下步骤操作：

¥To start using TypedSQL in your Prisma project, follow these steps:

1. 确保你已安装 @prisma/client 和 prisma 并至少更新到 5.19.0 版本。

   ¥Ensure you have @prisma/client and prisma installed and updated to at least version 5.19.0.

   npm install @prisma/client@latest
   npm install -D prisma@latest

2. 将 typedSql 预览功能标志添加到你的 schema.prisma 文件：

   ¥Add the typedSql preview feature flag to your schema.prisma file:

   generator client {
     provider = "prisma-client-js"
     previewFeatures = ["typedSql"]
   }

3. 在你的 prisma 目录中创建一个 sql 目录。这是你编写 SQL 查询的地方。

   ¥Create a sql directory inside your prisma directory. This is where you'll write your SQL queries.

   mkdir -p prisma/sql

4. 在你的 prisma/sql 目录中创建一个新的 .sql 文件。例如，getUsersWithPosts.sql。请注意，文件名必须是有效的 JS 标识符，并且不能以 $ 开头。

   ¥Create a new .sql file in your prisma/sql directory. For example, getUsersWithPosts.sql. Note that the file name must be a valid JS identifier and cannot start with a $.

5. 在新的 .sql 文件中编写你的 SQL 查询。例如：

   ¥Write your SQL queries in your new .sql file. For example:

   prisma/sql/getUsersWithPosts.sql

   SELECT u.id, u.name, COUNT(p.id) as "postCount"
   FROM "User" u
   LEFT JOIN "Post" p ON u.id = p."authorId"
   GROUP BY u.id, u.name

6. 使用 sql 标志生成 Prisma Client，以确保为你的 SQL 查询创建 TypeScript 函数和类型：

   ¥Generate Prisma Client with the sql flag to ensure TypeScript functions and types for your SQL queries are created:

   警告

   在使用 sql 标志生成客户端之前，请确保已应用所有待处理的迁移。

   ¥Make sure that any pending migrations are applied before generating the client with the sql flag.

   prisma generate --sql

   如果你不想在每次更改后重新生成客户端，此命令也可以与现有的 --watch 标志一起使用：

   ¥If you don't want to regenerate the client after every change, this command also works with the existing --watch flag:

   prisma generate --sql --watch

7. 现在，你可以在 TypeScript 代码中导入和使用 SQL 查询：

   ¥Now you can import and use your SQL queries in your TypeScript code:

   /src/index.ts

   import { PrismaClient } from '@prisma/client'
   import { getUsersWithPosts } from '@prisma/client/sql'
   
   const prisma = new PrismaClient()
   
   const usersWithPostCounts = await prisma.$queryRawTyped(getUsersWithPosts())
   console.log(usersWithPostCounts)

将参数传递给 TypedSQL 查询

¥Passing Arguments to TypedSQL Queries

要将参数传递给 TypedSQL 查询，可以使用参数化查询。这允许你编写灵活且可重用的 SQL 语句，同时保持类型安全。操作方法如下：

¥To pass arguments to your TypedSQL queries, you can use parameterized queries. This allows you to write flexible and reusable SQL statements while maintaining type safety. Here's how to do it:

1. 在你的 SQL 文件中，使用占位符来表示要传递的参数。占位符的语法取决于你的数据库引擎：

   ¥In your SQL file, use placeholders for the parameters you want to pass. The syntax for placeholders depends on your database engine:

PostgreSQL

For PostgreSQL, use the positional placeholders $1, $2, etc.:

prisma/sql/getUsersByAge.sql

SELECT id, name, age
FROM users
WHERE age > $1 AND age < $2

MySQL

For MySQL, use the positional placeholders ?:

prisma/sql/getUsersByAge.sql

SELECT id, name, age
FROM users
WHERE age > ? AND age < ?

SQLite

In SQLite, there are a number of different placeholders you can use. Postional placeholders ($1, $2, etc.), general placeholders (?), and named placeholders (:minAge, :maxAge, etc.) are all available. For this example, we'll use named placeholders :minAge and :maxAge:

prisma/sql/getUsersByAge.sql

SELECT id, name, age
FROM users
WHERE age > :minAge AND age < :maxAge

注意

有关如何 在你的 SQL 文件中定义参数类型 的信息，请参阅下文。

¥See below for information on how to define argument types in your SQL files.

1. 在 TypeScript 代码中使用生成的函数时，将参数作为附加参数传递给 $queryRawTyped：

   ¥When using the generated function in your TypeScript code, pass the arguments as additional parameters to $queryRawTyped:

   /src/index.ts

   import { PrismaClient } from '@prisma/client'
   import { getUsersByAge } from '@prisma/client/sql'
   
   const prisma = new PrismaClient()
   
   const minAge = 18
   const maxAge = 30
   const users = await prisma.$queryRawTyped(getUsersByAge(minAge, maxAge))
   console.log(users)

通过使用参数化查询，你可以确保类型安全并防止 SQL 注入漏洞。TypedSQL 生成器将根据你的 SQL 查询为参数创建适当的 TypeScript 类型，为查询结果和输入参数提供完整的类型检查。

¥By using parameterized queries, you ensure type safety and protect against SQL injection vulnerabilities. The TypedSQL generator will create the appropriate TypeScript types for the parameters based on your SQL query, providing full type checking for both the query results and the input parameters.

将数组参数传递给 TypedSQL

¥Passing array arguments to TypedSQL

TypedSQL 支持将数组作为 PostgreSQL 的参数传递。将 PostgreSQL 的 ANY 运算符与数组参数一起使用。

¥TypedSQL supports passing arrays as arguments for PostgreSQL. Use PostgreSQL's ANY operator with an array parameter.

prisma/sql/getUsersByIds.sql

SELECT id, name, email
FROM users
WHERE id = ANY($1)

/src/index.ts

import { PrismaClient } from '@prisma/client'
import { getUsersByIds } from '@prisma/client/sql'

const prisma = new PrismaClient()

const userIds = [1, 2, 3]
const users = await prisma.$queryRawTyped(getUsersByIds(userIds))
console.log(users)

TypedSQL 将为数组参数生成适当的 TypeScript 类型，确保输入和查询结果的类型安全。

¥TypedSQL will generate the appropriate TypeScript types for the array parameter, ensuring type safety for both the input and the query results.

注意

传递数组参数时，请注意数据库在单个查询中支持的最大占位符数量。对于非常大的数组，你可能需要将查询拆分为多个较小的查询。

¥When passing array arguments, be mindful of the maximum number of placeholders your database supports in a single query. For very large arrays, you may need to split the query into multiple smaller queries.

在 SQL 文件中定义参数类型

¥Defining argument types in your SQL files

TypedSQL 中的参数输入是通过 SQL 文件中的特定注释完成的。这些注释的形式为：

¥Argument typing in TypedSQL is accomplished via specific comments in your SQL files. These comments are of the form:

-- @param {Type} $N:alias optional description

其中 Type 是有效的数据库类型，N 是参数在查询中的位置，alias 是 TypeScript 类型中使用的参数的可选别名。

¥Where Type is a valid database type, N is the position of the argument in the query, and alias is an optional alias for the argument that is used in the TypeScript type.

例如，如果你需要输入一个别名为 name 和描述为 "用户的名称" 的字符串参数，则应在 SQL 文件中添加以下注释：

¥As an example, if you needed to type a single string argument with the alias name and the description "The name of the user", you would add the following comment to your SQL file:

-- @param {String} $1:name The name of the user

要指示参数可为空，请在别名后添加问号：

¥To indicate that a parameter is nullable, add a question mark after the alias:

-- @param {String} $1:name? The name of the user (optional)

目前接受的类型为 Int、BigInt、Float、Boolean、String、DateTime、Json、Bytes、null 和 Decimal。

¥Currently accepted types are Int, BigInt, Float, Boolean, String, DateTime, Json, Bytes, null, and Decimal.

使用 来自上述示例，SQL 文件将如下所示：

¥Taking the example from above, the SQL file would look like this:

-- @param {Int} $1:minAge
-- @param {Int} $2:maxAge
SELECT id, name, age
FROM users
WHERE age > $1 AND age < $2

无论数据库引擎如何，参数类型定义的格式都是相同的。

¥The format of argument type definitions is the same regardless of the database engine.

注意

不支持手动为数组参数定义参数类型。对于这些参数，你将需要依赖 TypedSQL 提供的类型推断。

¥Manually argument type definitions are not supported for array arguments. For these arguments, you will need to rely on the type inference provided by TypedSQL.

示例

¥Examples

有关如何在各种场景中使用 TypedSQL 的实际示例，请参阅 Prisma 示例 repo。此 repo 包含一组可立即运行的 Prisma 示例项目，这些项目演示了最佳实践和常见用例，包括 TypedSQL 实现。

¥For practical examples of how to use TypedSQL in various scenarios, please refer to the Prisma Examples repo. This repo contains a collection of ready-to-run Prisma example projects that demonstrate best practices and common use cases, including TypedSQL implementations.

TypedSQL 的局限性

¥Limitations of TypedSQL

支持数据库

¥Supported Databases

TypedSQL 支持现代版本的 MySQL 和 PostgreSQL，无需任何进一步配置。对于 MySQL 8.0 之前的版本和所有 SQLite 版本，你需要在 SQL 文件中手动 描述参数类型。在 PostgreSQL 和 MySQL 8.0 及更高版本的所有受支持版本中都会推断输入的类型。

¥TypedSQL supports modern versions of MySQL and PostgreSQL without any further configuration. For MySQL versions older than 8.0 and all SQLite versions, you will need to manually describe argument types in your SQL files. The types of inputs are inferred in all supported versions of PostgreSQL and MySQL 8.0 and later.

TypedSQL 不适用于 MongoDB，因为它是专门为 SQL 数据库设计的。

¥TypedSQL does not work with MongoDB, as it is specifically designed for SQL databases.

需要活动数据库连接

¥Active Database Connection Required

TypedSQL 需要活动的数据库连接才能正常运行。这意味着你需要有一个正在运行的数据库实例，Prisma 在使用 --sql 标志生成客户端时可以连接到该实例。如果在 Prisma 配置中提供了 directUrl，TypedSQL 将使用它进行连接。

¥TypedSQL requires an active database connection to function properly. This means you need to have a running database instance that Prisma can connect to when generating the client with the --sql flag. If a directUrl is provided in your Prisma configuration, TypedSQL will use that for the connection.

具有动态列的动态 SQL 查询

¥Dynamic SQL Queries with Dynamic Columns

TypedSQL 本身不支持构建具有动态添加列的 SQL 查询。当你需要创建在运行时确定列的查询时，必须使用 $queryRaw 和 $executeRaw 方法。这些方法允许执行原始 SQL，其中可以包括动态列选择。

¥TypedSQL does not natively support constructing SQL queries with dynamically added columns. When you need to create a query where the columns are determined at runtime, you must use the $queryRaw and $executeRaw methods. These methods allow for the execution of raw SQL, which can include dynamic column selections.

使用动态列选择的查询示例：

¥Example of a query using dynamic column selection:

const columns = 'name, email, age'; // Columns determined at runtime
const result = await prisma.$queryRawUnsafe(
  `SELECT ${columns} FROM Users WHERE active = true`
);

在此示例中，要选择的列是动态定义的，并包含在 SQL 查询中。虽然这种方法提供了灵活性，但它需要仔细注意安全性，尤其是 避免 SQL 注入漏洞。此外，使用原始 SQL 查询意味着放弃 TypedSQL 的类型安全性和 DX。

¥In this example, the columns to be selected are defined dynamically and included in the SQL query. While this approach provides flexibility, it requires careful attention to security, particularly to avoid SQL injection vulnerabilities. Additionally, using raw SQL queries means foregoing the type-safety and DX of TypedSQL.

致谢

¥Acknowledgements

此功能受到 PgTyped 和 SQLx 的很大启发。此外，SQLite 解析由 SQLx。

¥This feature was heavily inspired by PgTyped and SQLx. Additionally, SQLite parsing is handled by SQLx.