TypedSQL

TypedSQL 入门

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

1. 确保您已安装 `@prisma/client` 和 `prisma`，并将其更新到至少版本 `5.19.0`。

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

2. 将 `typedSql` 预览特性标志添加到您的 `schema.prisma` 文件中

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

3. 在您的 `prisma` 目录中创建一个 `sql` 目录。您将在此处编写 SQL 查询。

   mkdir -p prisma/sql

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

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

   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 函数和类型

   警告

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

   prisma generate --sql

   如果您不想在每次更改后重新生成客户端，此命令也支持现有的 `--watch` 标志

   prisma generate --sql --watch

7. 现在您可以在 TypeScript 代码中导入和使用您的 SQL 查询了

   /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 查询传递参数

要将参数传递给您的 TypedSQL 查询，您可以使用参数化查询。这使您能够编写灵活可复用的 SQL 语句，同时保持类型安全。方法如下

1. 在您的 SQL 文件中，为您想传递的参数使用占位符。占位符的语法取决于您的数据库引擎

   PostgreSQL

   对于 PostgreSQL，使用位置占位符 `$1`、`$2` 等。

   prisma/sql/getUsersByAge.sql

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

   MySQL

   对于 MySQL，使用位置占位符 `?`

   prisma/sql/getUsersByAge.sql

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

   SQLite

   在 SQLite 中，您可以使用多种不同的占位符。位置占位符（`$1`、`$2` 等）、通用占位符（`?`）和命名占位符（`:minAge`、`:maxAge` 等）都可用。在本例中，我们将使用命名占位符 `:minAge` 和 `:maxAge`

   prisma/sql/getUsersByAge.sql

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

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

2. 在 TypeScript 代码中使用生成的函数时，将参数作为额外的参数传递给 `$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 类型，为查询结果和输入参数提供全面的类型检查。

向 TypedSQL 传递数组参数

TypedSQL 支持为 PostgreSQL 传递数组作为参数。使用 PostgreSQL 的 `ANY` 运算符和数组参数。

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 类型，确保输入和查询结果的类型安全。

注意

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

在您的 SQL 文件中定义参数类型

TypedSQL 中的参数类型定义是通过 SQL 文件中的特定注释来实现的。这些注释的形式如下

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

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

例如，如果您需要为一个别名为 `name` 且描述为“用户的名称”的字符串参数指定类型，您可以在 SQL 文件中添加以下注释

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

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

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

当前接受的类型有 `Int`、`BigInt`、`Float`、`Boolean`、`String`、`DateTime`、`Json`、`Bytes`、`null` 和 `Decimal`。

以上面的示例为例，SQL 文件将如下所示

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

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

注意

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

示例

有关如何在各种场景中使用 TypedSQL 的实际示例，请参阅Prisma Examples 仓库。此仓库包含一系列可直接运行的 Prisma 示例项目，展示了最佳实践和常见用例，包括 TypedSQL 的实现。

TypedSQL 的局限性

支持的数据库

TypedSQL 支持现代版本的 MySQL 和 PostgreSQL，无需额外配置。对于 MySQL 8.0 之前的版本和所有 SQLite 版本，您需要在 SQL 文件中手动描述参数类型。在所有受支持的 PostgreSQL 版本以及 MySQL 8.0 及更高版本中，输入的类型是推断出来的。

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

需要活动的数据库连接

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

带有动态列的动态 SQL 查询

TypedSQL 不原生支持构建带有动态添加列的 SQL 查询。当您需要创建一个列在运行时确定的查询时，您必须使用 `$queryRaw` 和 `$executeRaw` 方法。这些方法允许执行原生 SQL，其中可以包含动态列选择。

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

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 的类型安全性和开发者体验。

致谢

此特性深受 PgTyped 和 SQLx 的启发。此外，SQLite 的解析由 SQLx 处理。