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
如果你还没有这样做,请在你的 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
作为参考,迁移文件的输出应如下所示:
-- 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
}
接下来,你可以将一个 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::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 操作。