SafeQL 和 Prisma Client

概述

¥Overview

本页介绍了如何改善在 Prisma ORM 中编写原始 SQL 的体验。它使用 Prisma 客户端扩展 和 SafeQL 创建自定义的、类型安全的 Prisma 客户端查询，这些查询抽象你的应用可能需要的自定义 SQL（使用 $queryRaw）。

¥This page explains how to improve the experience of writing raw SQL in Prisma ORM. It uses Prisma Client extensions and SafeQL to create custom, type-safe Prisma Client queries which abstract custom SQL that your app might need (using $queryRaw).

该示例将使用 PostGIS 和 PostgreSQL，但适用于你的应用中可能需要的任何原始 SQL 查询。

¥The example will be using PostGIS and PostgreSQL, but is applicable to any raw SQL queries that you might need in your application.

注意

此页面基于 Prisma Client 中提供的 传统的原始查询方法。虽然 TypedSQL 涵盖了 Prisma Client 中原始 SQL 的许多用例，但使用这些旧方法仍然是处理 Unsupported 字段的推荐方法。

¥This page builds on the legacy raw query methods available in Prisma Client. While many use cases for raw SQL in Prisma Client are covered by TypedSQL, using these legacy methods is still the recommended approach for working with Unsupported fields.

什么是 SafeQL？

¥What is SafeQL?

SafeQL 允许在原始 SQL 查询中进行高级 linting 和类型安全。设置后，SafeQL 与 Prisma 客户端 $queryRaw 和 $executeRaw 配合使用，在需要原始查询时提供类型安全。

¥SafeQL allows for advanced linting and type safety within raw SQL queries. After setup, SafeQL works with Prisma Client $queryRaw and $executeRaw to provide type safety when raw queries are required.

SafeQL 作为 ESLint 插件运行，并使用 ESLint 规则进行配置。本指南不包括设置 ESLint，我们假设你已经在项目中运行了它。

¥SafeQL runs as an ESLint plugin and is configured using ESLint rules. This guide doesn't cover setting up ESLint and we will assume that you already having it running in your project.

先决条件

¥Prerequisites

为了跟随，你将需要：

¥To follow along, you will be expected to have:

• 安装了 PostGIS 的 PostgreSQL 数据库

  ¥A PostgreSQL database with PostGIS installed

• 在你的项目中设置 Prisma ORM

  ¥Prisma ORM set up in your project

• 在你的项目中设置 ESLint

  ¥ESLint set up in your project

Prisma ORM 中的地理数据支持

¥Geographic data support in Prisma ORM

在撰写本文时，Prisma ORM 不支持处理地理数据，特别是使用 PostGIS。

¥At the time of writing, Prisma ORM does not support working with geographic data, specifically using PostGIS.

具有地理数据列的模型将使用 Unsupported 数据类型进行存储。具有 Unsupported 类型的字段出现在生成的 Prisma 客户端中，并将键入为 any。具有所需 Unsupported 类型的模型不会公开 create 和 update 等写入操作。

¥A model that has geographic data columns will be stored using the Unsupported data type. Fields with Unsupported types are present in the generated Prisma Client and will be typed as any. A model with a required Unsupported type does not expose write operations such as create, and update.

Prisma 客户端支持使用 $queryRaw 和 $executeRaw 对具有必填 Unsupported 字段的模型进行写入操作。在原始查询中处理地理数据时，你可以使用 Prisma 客户端扩展和 SafeQL 来提高类型安全性。

¥Prisma Client supports write operations on models with a required Unsupported field using $queryRaw and $executeRaw. You can use Prisma Client extensions and SafeQL to improve the type-safety when working with geographical data in raw queries.

1. 设置 Prisma ORM 以与 PostGIS 一起使用

¥ Set up Prisma ORM for use with PostGIS

如果你还没有启用 postgresqlExtensions 预览功能，并在 Prisma 架构中添加 postgis PostgreSQL 扩展：

¥If you haven't already, enable the postgresqlExtensions Preview feature and add the postgis PostgreSQL extension in your Prisma schema:

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

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [postgis]
}

warning

如果你不使用托管数据库提供商，则可能需要安装 postgis 扩展。请参阅 PostGIS 的文档 了解有关如何开始使用 PostGIS 的更多信息。如果你使用 Docker Compose，则可以使用以下代码片段来设置安装了 PostGIS 的 PostgreSQL 数据库：

¥If you are not using a hosted database provider, you will likely need to install the postgis extension. Refer to PostGIS's docs to learn more about how to get started with PostGIS. If you're using Docker Compose, you can use the following snippet to set up a PostgreSQL database that has PostGIS installed:

version: '3.6'
services:
  pgDB:
    image: postgis/postgis:13-3.1-alpine
    restart: always
    ports:
      - '5432:5432'
    volumes:
      - db_data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: password
      POSTGRES_DB: geoexample
volumes:
  db_data:

接下来，创建迁移并执行迁移以启用扩展：

¥Next, create a migration and execute a migration to enable the extension:

npx prisma migrate dev --name add-postgis

作为参考，迁移文件的输出应如下所示：

¥For reference, the output of the migration file should look like the following:

migrations/TIMESTAMP_add_postgis/migration.sql

-- CreateExtension
CREATE EXTENSION IF NOT EXISTS "postgis";

你可以通过运行 prisma migrate status 来仔细检查迁移是否已应用。

¥You can double-check that the migration has been applied by running prisma migrate status.

2. 创建使用地理数据列的新模型

¥ Create a new model that uses a geographic data column

应用迁移后，添加一个包含 geography 数据类型列的新模型。在本指南中，我们将使用名为 PointOfInterest 的模型。

¥Add a new model with a column with a geography data type once the migration is applied. For this guide, we'll use a model called PointOfInterest.

model PointOfInterest {
  id       Int                                   @id @default(autoincrement())
  name     String
  location Unsupported("geography(Point, 4326)")
}

你会注意到 location 字段使用 Unsupported 类型。这意味着我们在使用 PointOfInterest 时失去了 Prisma ORM 的很多好处。我们将使用 SafeQL 来解决这个问题。

¥You'll notice that the location field uses an Unsupported type. This means that we lose a lot of the benefits of Prisma ORM when working with PointOfInterest. We'll be using SafeQL to fix this.

与之前一样，使用 prisma migrate dev 命令创建并执行迁移，以在数据库中创建 PointOfInterest 表：

¥Like before, create and execute a migration using the prisma migrate dev command to create the PointOfInterest table in your database:

npx prisma migrate dev --name add-poi

作为参考，以下是 Prisma Migrate 生成的 SQL 迁移文件的输出：

¥For reference, here is the output of the SQL migration file generated by Prisma Migrate:

migrations/TIMESTAMP_add_poi/migration.sql

-- CreateTable
CREATE TABLE "PointOfInterest" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,
    "location" geography(Point, 4326) NOT NULL,

    CONSTRAINT "PointOfInterest_pkey" PRIMARY KEY ("id")
);

3. 集成 SafeQL

¥ Integrate SafeQL

SafeQL 可轻松与 Prisma ORM 集成，以检查 $queryRaw 和 $executeRaw Prisma 操作。你可以参考 SafeQL 的集成指南 或按照以下步骤操作。

¥SafeQL is easily integrated with Prisma ORM in order to lint $queryRaw and $executeRaw Prisma operations. You can reference SafeQL's integration guide or follow the steps below.

3.1.安装 @ts-safeql/eslint-plugin npm 包

¥3.1. Install the @ts-safeql/eslint-plugin npm package

npm install -D @ts-safeql/eslint-plugin libpg-query

这个 ESLint 插件将允许对查询进行 linted。

¥This ESLint plugin is what will allow for queries to be linted.

3.2.将 @ts-safeql/eslint-plugin 添加到你的 ESLint 插件中

¥3.2. Add @ts-safeql/eslint-plugin to your ESLint plugins

接下来，将 @ts-safeql/eslint-plugin 添加到你的 ESLint 插件列表中。在我们的示例中，我们使用 .eslintrc.js 文件，但这可以应用于你 配置 ESLint。

¥Next, add @ts-safeql/eslint-plugin to your list of ESLint plugins. In our example we are using an .eslintrc.js file, but this can be applied to any way that you configure ESLint.

.eslintrc.js

/** @type {import('eslint').Linter.Config} */
module.exports = {
  "plugins": [..., "@ts-safeql/eslint-plugin"],
  ...
}

3.3 添加 @ts-safeql/check-sql 条规则

¥3.3 Add @ts-safeql/check-sql rules

现在，设置规则，使 SafeQL 能够将无效的 SQL 查询标记为 ESLint 错误。

¥Now, setup the rules that will enable SafeQL to mark invalid SQL queries as ESLint errors.

.eslintrc.js

/** @type {import('eslint').Linter.Config} */
module.exports = {
  plugins: [..., '@ts-safeql/eslint-plugin'],
  rules: {
    '@ts-safeql/check-sql': [
      'error',
      {
        connections: [
          {
            // The migrations path:
            migrationsDir: './prisma/migrations',
            targets: [
              // This makes `prisma.$queryRaw` and `prisma.$executeRaw` commands linted
              { tag: 'prisma.+($queryRaw|$executeRaw)', transform: '{type}[]' },
            ],
          },
        ],
      },
    ],
  },
}

注意：如果你的 PrismaClient 实例的名称与 prisma 不同，你需要相应地调整 tag 的值。例如，如果名称为 db，则 tag 的值应为 'db.+($queryRaw|$executeRaw)'。

¥Note: If your PrismaClient instance is called something different than prisma, you need to adjust the value for tag accordingly. For example, if it is called db, the value for tag should be 'db.+($queryRaw|$executeRaw)'.

3.4.连接到你的数据库

¥3.4. Connect to your database

最后，为 SafeQL 设置 connectionUrl，以便它可以内省你的数据库并检索你在架构中使用的表和列名称。然后，SafeQL 使用此信息来检查和高亮原始 SQL 语句中的问题。

¥Finally, set up a connectionUrl for SafeQL so that it can introspect your database and retrieve the table and column names you use in your schema. SafeQL then uses this information for linting and highlighting problems in your raw SQL statements.

我们的示例依赖于 dotenv 包来获取 Prisma ORM 使用的相同连接字符串。我们建议这样做是为了使你的数据库 URL 不受版本控制。

¥Our example relies on the dotenv package to get the same connection string that is used by Prisma ORM. We recommend this in order to keep your database URL out of version control.

如果你还没有安装 dotenv，可以按如下方式安装：

¥If you haven't installed dotenv yet, you can install it as follows:

npm install dotenv

然后更新你的 ESLint 配置，如下所示：

¥Then update your ESLint config as follows:

.eslintrc.js

require('dotenv').config()

/** @type {import('eslint').Linter.Config} */
module.exports = {
  plugins: ['@ts-safeql/eslint-plugin'],
  // exclude `parserOptions` if you are not using TypeScript
  parserOptions: {
    project: './tsconfig.json',
  },
  rules: {
    '@ts-safeql/check-sql': [
      'error',
      {
        connections: [
          {
            connectionUrl: process.env.DATABASE_URL,
            // The migrations path:
            migrationsDir: './prisma/migrations',
            targets: [
              // what you would like SafeQL to lint. This makes `prisma.$queryRaw` and `prisma.$executeRaw`
              // commands linted
              { tag: 'prisma.+($queryRaw|$executeRaw)', transform: '{type}[]' },
            ],
          },
        ],
      },
    ],
  },
}

SafeQL 现已完全配置，可帮助你使用 Prisma Client 编写更好的原始 SQL。

¥SafeQL is now fully configured to help you write better raw SQL using Prisma Client.

4. 创建扩展以使原始 SQL 查询类型安全

¥ Creating extensions to make raw SQL queries type-safe

在本节中，我们将使用自定义查询创建两个 model 扩展，以便能够方便地使用 PointOfInterest 模型：

¥In this section, we'll create two model extensions with custom queries to be able to work conveniently with the PointOfInterest model:

1. create 查询允许我们在数据库中创建新的 PointOfInterest 记录

   ¥A create query that allows us to create new PointOfInterest records in the database

2. 返回最接近给定坐标的 PointOfInterest 记录的 findClosestPoints 查询

   ¥A findClosestPoints query that returns the PointOfInterest records that are closest to a given coordinate

4.1.添加扩展以创建 PointOfInterest 记录

¥4.1. Adding an extension to create PointOfInterest records

Prisma 架构中的 PointOfInterest 模型使用 Unsupported 类型。因此，Prisma 客户端中生成的 PointOfInterest 类型不能用于携带纬度和经度值。

¥The PointOfInterest model in the Prisma schema uses an Unsupported type. As a consequence, the generated PointOfInterest type in Prisma Client can't be used to carry values for latitude and longitude.

我们将通过定义两个自定义类型来解决这个问题，它们可以更好地在 TypeScript 中表示我们的模型：

¥We will resolve this by defining two custom types that better represent our model in TypeScript:

type MyPoint = {
  latitude: number
  longitude: number
}

type MyPointOfInterest = {
  name: string
  location: MyPoint
}

接下来，你可以将 create 查询添加到 Prisma 客户端的 pointOfInterest 属性：

¥Next, you can add a create query to the pointOfInterest property of your Prisma Client:

const prisma = new PrismaClient().$extends({
  model: {
    pointOfInterest: {
      async create(data: {
        name: string
        latitude: number
        longitude: number
      }) {
        // Create an object using the custom types from above
        const poi: MyPointOfInterest = {
          name: data.name,
          location: {
            latitude: data.latitude,
            longitude: data.longitude,
          },
        }

        // Insert the object into the database
        const point = `POINT(${poi.location.longitude} ${poi.location.latitude})`
        await prisma.$queryRaw`
          INSERT INTO "PointOfInterest" (name, location) VALUES (${poi.name}, ST_GeomFromText(${point}, 4326));
        `

        // Return the object
        return poi
      },
    },
  },
})

请注意，代码片段中高亮的行中的 SQL 已由 SafeQL 检查！例如，如果将表名从 "PointOfInterest" 更改为 "PointOfInterest2"，则会出现以下错误：

¥Notice that the SQL in the line that's highlighted in the code snippet gets checked by SafeQL! For example, if you change the name of the table from "PointOfInterest" to "PointOfInterest2", the following error appears:

error  Invalid Query: relation "PointOfInterest2" does not exist  @ts-safeql/check-sql

这也适用于列名 name 和 location。

¥This also works with the column names name and location.

你现在可以在代码中创建新的 PointOfInterest 记录，如下所示：

¥You can now create new PointOfInterest records in your code as follows:

const poi = await prisma.pointOfInterest.create({
  name: 'Berlin',
  latitude: 52.52,
  longitude: 13.405,
})

4.2.添加扩展以查询最接近 PointOfInterest 的记录

¥4.2. Adding an extension to query for closest to PointOfInterest records

现在让我们创建一个 Prisma 客户端扩展来查询该模型。我们将进行扩展，找到距离给定经度和纬度最近的兴趣点。

¥Now let's make a Prisma Client extension in order to query this model. We will be making an extension that finds the closest points of interest to a given longitude and latitude.

const prisma = new PrismaClient().$extends({
  model: {
    pointOfInterest: {
      async create(data: {
        name: string
        latitude: number
        longitude: number
      }) {
        // ... same code as before
      },

      async findClosestPoints(latitude: number, longitude: number) {
        // Query for clostest points of interests
        const result = await prisma.$queryRaw<
          {
            id: number | null
            name: string | null
            st_x: number | null
            st_y: number | null
          }[]
        >`SELECT id, name, ST_X(location::geometry), ST_Y(location::geometry) 
            FROM "PointOfInterest" 
            ORDER BY ST_DistanceSphere(location::geometry, ST_MakePoint(${longitude}, ${latitude})) DESC`

        // Transform to our custom type
        const pois: MyPointOfInterest[] = result.map((data) => {
          return {
            name: data.name,
            location: {
              latitude: data.st_x || 0,
              longitude: data.st_y || 0,
            },
          }
        })

        // Return data
        return pois
      },
    },
  },
})

现在，你可以正常使用我们的 Prisma 客户端，使用在 PointOfInterest 模型上创建的自定义方法来查找给定经度和纬度的附近兴趣点。

¥Now, you can use our Prisma Client as normal to find close points of interest to a given longitude and latitude using the custom method created on the PointOfInterest model.

const closestPointOfInterest = await prisma.pointOfInterest.findClosestPoints(
  53.5488,
  9.9872
)

与之前类似，我们再次受益于 SafeQL 为我们的原始查询添加额外的类型安全性。例如，如果我们通过将 location::geometry 更改为 location 来删除对 location 的 geometry 的强制转换，则我们将分别在 ST_X、ST_Y 或 ST_DistanceSphere 函数中出现 linting 错误。

¥Similar to before, we again have the benefit of SafeQL to add extra type safety to our raw queries. For example, if we removed the cast to geometry for location by changing location::geometry to just location, we would get linting errors in the ST_X, ST_Y or ST_DistanceSphere functions respectively.

error  Invalid Query: function st_distancesphere(geography, geometry) does not exist  @ts-safeql/check-sql

结论

¥Conclusion

虽然在使用 Prisma ORM 时有时可能需要直接使用原始 SQL，但你可以使用各种技术来改善使用 Prisma ORM 编写原始 SQL 查询的体验。

¥While you may sometimes need to drop down to raw SQL when using Prisma ORM, you can use various techniques to make the experience of writing raw SQL queries with Prisma ORM better.

在本文中，你使用 SafeQL 和 Prisma 客户端扩展来创建自定义、类型安全的 Prisma 客户端查询，以抽象目前 Prisma ORM 本身不支持的 PostGIS 操作。

¥In this article, you have used SafeQL and Prisma Client extensions to create custom, type-safe Prisma Client queries to abstract PostGIS operations which are currently not natively supported in Prisma ORM.