跳到主要内容

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

如果你还没有这样做,请在你的 Prisma schema 中启用 postgresqlExtensions 预览特性 并添加 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
}

接下来,你可以将一个 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

这也适用于列名 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,它为我们的原始查询增加了额外的类型安全性。例如,如果我们移除 locationgeometry 类型转换,将 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 操作。