跳到主要内容

SafeQL & Prisma Client

概述

本页解释了如何改善在 Prisma ORM 中编写原始 SQL 的体验。它使用Prisma Client 扩展SafeQL来创建自定义的、类型安全的 Prisma Client 查询,这些查询能够抽象应用程序可能需要的自定义 SQL(使用 $queryRaw)。

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

注意

本页基于 Prisma Client 中提供的旧版原始查询方法。尽管 Prisma Client 中许多原始 SQL 的用例都由TypedSQL涵盖,但对于处理 Unsupported 字段,使用这些旧版方法仍然是推荐的方式。

什么是 SafeQL?

SafeQL允许在原始 SQL 查询中进行高级 Linting 和类型安全检查。设置完成后,SafeQL 可与 Prisma Client 的 $queryRaw$executeRaw 配合使用,在需要原始查询时提供类型安全。

SafeQL 作为 ESLint 插件运行,并使用 ESLint 规则进行配置。本指南不涉及 ESLint 的设置,我们假定您的项目已在运行 ESLint。

先决条件

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

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

Prisma ORM 中的地理数据支持

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

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

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

1. 设置 Prisma ORM 以与 PostGIS 配合使用

如果您尚未启用 postgresqlExtensions 预览功能,请在您的 Prisma schema 中添加 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 操作进行 Linting。您可以参考 SafeQL 的集成指南 或按照以下步骤操作。

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

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

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

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,以便它可以内省您的数据库并检索您在 schema 中使用的表名和列名。SafeQL 随后将使用此信息对您的原始 SQL 语句进行 Linting 和问题高亮。

我们的示例依赖于 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 模型。

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

4.1. 添加一个扩展来创建 PointOfInterest 记录

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

我们将通过定义两种自定义类型来解决这个问题,这两种类型可以更好地代表 TypeScript 中的模型。

type MyPoint = {
latitude: number
longitude: number
}

type MyPointOfInterest = {
name: string
location: MyPoint
}

接下来,您可以向 Prisma Client 的 pointOfInterest 属性添加一个 create 查询。

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

这也适用于列名 namelocation

您现在可以在代码中按如下方式创建新的 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 的类型转换从 location::geometry 更改为仅 location,那么在 ST_XST_YST_DistanceSphere 函数中就会出现 Linting 错误。

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 操作。

© . All rights reserved.