自定义 & 类型安全查询

概述

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

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

什么是 SafeQL？

SafeQL 允许在原始 SQL 查询中进行高级 linting 和类型安全。设置完成后，SafeQL 与 Prisma Client $queryRaw 和 $executeRaw 协同工作，在需要原始查询时提供类型安全。

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

先决条件

要继续学习，您需要具备以下条件

一个安装了 PostGIS 的 PostgreSQL 数据库
在您的项目中设置 Prisma ORM
在您的项目中设置 ESLint

Prisma ORM 中的地理数据支持

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

具有地理数据列的模型将使用 Unsupported 数据类型存储。具有 Unsupported 类型的字段存在于生成的 Prisma Client 中，并将被类型化为 any。具有必需 Unsupported 类型的模型不会公开写入操作，例如 create 和 update。

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

1. 为使用 PostGIS 设置 Prisma ORM

如果您还没有，请启用 postgresqlExtensions 预览功能并在您的 Prisma 模式中添加 postgis PostgreSQL 扩展

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

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

警告

如果您没有使用托管数据库提供商，您可能需要安装 `postgis` 扩展。请参考 PostGIS 文档了解有关如何开始使用 PostGIS 的更多信息。如果您使用的是 Docker Compose，您可以使用以下代码段来设置一个安装了 PostGIS 的 PostgreSQL 数据库

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:

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

npx prisma migrate dev --name add-postgis

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

migrations/TIMESTAMP_add_postgis/migration.sql

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

您可以通过运行 `prisma migrate status` 来双重检查迁移是否已应用。

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

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

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

您会注意到 `location` 字段使用的是 Unsupported 类型。这意味着我们在使用 `PointOfInterest` 时会失去 Prisma ORM 的许多优势。我们将使用 SafeQL 来解决这个问题。

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

npx prisma migrate dev --name add-poi

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

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

SafeQL 很容易与 Prisma ORM 集成，以便对 `$queryRaw` 和 `$executeRaw` Prisma 操作进行 lint。您可以参考 SafeQL 的集成指南或按照以下步骤操作。

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

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

此 ESLint 插件将允许对查询进行代码检查。

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

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

.eslintrc.js

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

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

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

.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)'。

3.4. 连接到您的数据库

最后，为 SafeQL 设置一个 connectionUrl，以便它可以内省您的数据库并检索您在模式中使用的表和列名称。SafeQL 然后使用此信息来检查您的原始 SQL 语句并突出显示其中的问题。

我们的示例依赖于 dotenv 包来获取与 Prisma ORM 使用的相同的连接字符串。我们建议这样做，以便将您的数据库 URL 保持在版本控制之外。

如果您尚未安装 dotenv，则可以按如下方式安装它

npm install dotenv

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

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

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

在本节中，我们将创建两个 model 扩展，并使用自定义查询来方便地处理 PointOfInterest 模型。

一个 create 查询，允许我们在数据库中创建新的 PointOfInterest 记录。
一个 findClosestPoints 查询，返回距离给定坐标最近的 PointOfInterest 记录。

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

Prisma 模式中的 PointOfInterest 模型使用 Unsupported 类型。因此，Prisma Client 中生成的 PointOfInterest 类型无法用于承载纬度和经度的值。

我们将通过定义两个自定义类型来解决这个问题，这些类型更好地代表了我们在 TypeScript 中的模型。

type MyPoint = {
  latitude: number
  longitude: number
}

type MyPointOfInterest = {
  name: string
  location: MyPoint
}

接下来，您可以将 create 查询添加到 Prisma Client 的 pointOfInterest 属性中。

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"，则会出现以下错误。

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

这同样适用于列名 name 和 location。

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

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

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

现在让我们创建一个 Prisma Client 扩展来查询此模型。我们将创建一个扩展，用于查找距离给定经度和纬度最近的兴趣点。

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 Client，使用在 PointOfInterest 模型上创建的自定义方法来查找距离给定经度和纬度最近的兴趣点。

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

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

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

结论

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

本文介绍了如何使用 SafeQL 和 Prisma Client 扩展创建自定义、类型安全的 Prisma Client 查询，以抽象化目前 Prisma ORM 未原生支持的 PostGIS 操作。

概述​

什么是 SafeQL？​

先决条件​

Prisma ORM 中的地理数据支持​

1. 为使用 PostGIS 设置 Prisma ORM​

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

3. 集成 SafeQL​

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

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

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

3.4. 连接到您的数据库​

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

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

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

结论​

概述

什么是 SafeQL？

先决条件

Prisma ORM 中的地理数据支持

1. 为使用 PostGIS 设置 Prisma ORM

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

3. 集成 SafeQL

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

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

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

3.4. 连接到您的数据库

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

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

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

结论