SafeQL & Prisma Client
概述
本页解释了如何改善在 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 的设置,我们假定您的项目已在运行 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. 设置 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
供参考,迁移文件的输出应如下所示:
-- 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 迁移文件的输出:
-- 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 的任何方式。
/** @type {import('eslint').Linter.Config} */
module.exports = {
"plugins": [..., "@ts-safeql/eslint-plugin"],
...
}
3.3 添加 @ts-safeql/check-sql
规则
现在,设置规则,这将使 SafeQL 能够将无效的 SQL 查询标记为 ESLint 错误。
/** @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 配置:
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 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
这也适用于列名 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
的类型转换从 location::geometry
更改为仅 location
,那么在 ST_X
、ST_Y
或 ST_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 操作。