PostgREST REST API 用法总结

发表于 2024-07-17 分类于技术评论：阅读次数：

通过 PostgREST 我们可以将数据库表映射成 REST API，避免繁琐的增删改查或 ORM 过程。本篇总结了 PostgREST REST API 的基本用法。

接上一篇《PostgREST 使用小记》，本篇来了解一下 PostgREST REST API 的基本用法。

PostgREST 提供 REST API 供客户端调用。它将客户端 HTTP 请求转换成 SQL 语句来操作 DB，并将对应的操作结果作为 HTTP 响应。

SQL 非常灵活和强大，尤其是 SELECT 语句强大到甚至有些复杂；而对外提供的 REST API 应当简单好用。那 PostgREST 是如何在两者之间取舍和平衡的呢？答案就是一个设计良好的路由规则。

本篇关注的正是 PostgREST REST API 的路由规则及其用户，即我们如何使用这些规则来读写 DB。

一个不那么好的规则

之前我的个人项目是用 Node.js soul 库将数据库映射成 REST API，最近一边看 PostgREST 文档一边将 soul 替换成 PostgREST。个人感觉 PostgREST 的 API 路径比 soul 设计得更合理，也更易用。以 soul 中的这个 url 为例，

1	/invoices/rows?_filters=InvoiceId__neq:1,Total__gte:5,BillingPostalCode__notnull

在 PostgREST 中对应接口的 url 是，

1	/invoices/InvoiceId=neq.1&Total=gte.5&BillingPostalCode=not.is.null

PostgREST API url 可读性明显更好。

首先，soul 中的 rows, _filters 存在的必要性并不大
其次，__ 前缀让人看着很难受，比如 __neq、__gte

多看几个例子来感受一下两种 url 规则的可读性差异：

# soul
/people/rows?_schema=first_name,age"
# PostgREST
/people?select=first_name,age"

# soul
?_ordering=-Title
# PostgREST
?order=Title.desc

# soul
curl --request POST \
  --url http://localhost:8000/api/tables/Employee/rows \
  --header 'Content-Type: application/json' \
  --data '{
	"fields": {
		"FirstName": "Damien",
		"LastName": "Rice"
	}
}'
# PostgREST
curl --request POST \
  --url http://localhost:3000/Employee \
  --header 'Content-Type: application/json' \
  --data '{
		"FirstName": "Damien",
		"LastName": "Rice"
}'

soul 的另一个问题是 API 的 url 的表达能力目前仍有缺失。当然考虑到 soul 是一个较新的库，还在开发和完善中，所以这一点是可以理解的。举个例子，PostgREST 可以查询年龄小于18或大于21的人，对应的 url 如下：

/people?or=(age.lt.18,age.gt.21)

soul 暂时无法直接支持该查询，只能自定义 extension 来实现同样的效果。

PostgREST 的规则

PostgREST 只使用单层路由，不使用嵌套的路由。这意味着 PostgREST 中只支持以下风格的路由

1	/people?id=1

而不支持以下这种更有 RESTful 风格的路由：

/people/1

根据权限的不同，每个路由提供 GET、POST、PATCH、 DELETE、OPTIONS 以及 HEAD 方法。其中 GET、POST、PATCH、 DELETE 使用较多。

读数据

PostgREST 提供多种操作用于读数据 (READ)，而其中又以”水平过滤”相关的操作最多。水平过滤操作的完整列表见这里，我们这里只看以下较重要的几个操作：

水平过滤
- 基本操作: eq, gt, gte, lt, lte, neq, in, is
- 逻辑操作: not, or, and, all, any
- 模式匹配: like, ilike, match, imatch,
- 全文检索: fts, plfts, phfts, wfts
垂直过滤
- 指定列: select,
- 重命名: :
- JSON: ->, –>
排序: order, desc, asc
分页: limit, offset

水平过滤数据时，基本操作、模式匹配以及全文检索形成查询条件，而逻辑操作则用来将多个查询条件组合起来形成更复杂的查询条件。

另外，水平过滤时我们还会考虑分页和计数以避免单次请求返回过多的数据。

分页: 最简单的方式是使用 limit 和 offset 查询参数
计数: 通过添加请求头 Prefer: count=<value> 来让 PostgREST 返回总数。
- 有三种不同的计数方式：
  - exact - 准确的总数
  - planned - 预估的总数
  - estimated - 在”准确”和”预估”之间折衷。当表中的数据量较小时，返回准确值。当表中数据量较大时，返回预估值。表大小的阈值由 db-max-rows 参数指定
- 响应头 Content-Range 代表总数，其格式类似 0-24/321

来看几个具体的例子：

# 查询年龄大于18且小于21的人
# 注意 and 是默认的逻辑操作符
curl "http://localhost:3000/people?age.gt.18&age.lt.21

# 查询年龄小于18或大于21的人
curl "http://localhost:3000/people?or=(age.lt.18,age.gt.21)"

# 分页查询
curl "http://localhost:3000/people?limit=15&offset=30"

# 查询 last_name 以 O 开头或以 P 结尾的人
curl "http://localhost:3000/people?last_name=like(any).{O*,P*}"

# 查询 last_name 以 O 开头且以 n 结尾的人
curl -g "http://localhost:3000/people?last_name=like(all).{O*,*n}"

# tsearch 表中有一个名为 my_tsv 的列, 类型为 tsvector
# 按法语进行分词检索, my_tsv 列包含字符串 amusant
curl "http://localhost:3000/tsearch?my_tsv=fts(french).amusant"

# 仅取 full_name 和 birth_date 两列数据
# 并且分别重命名为 fullName 和 birthDate
curl "http://localhost:3000/people?select=fullName:full_name,birthDate:birth_date"

# 排序方式：按年龄降序且按身高升序
curl "http://localhost:3000/people?order=age.desc,height.asc"

PostgreSQL 数据库索引(index)对于读数据过程是透明的。我们可以通过添加 Accept: application/vnd.pgrst.plan 请求头来观察 Execution plan ，以判断索引的生效情况。

1 2	curl "http://localhost:3000/users?select=name&order=id" \ -H "Accept: application/vnd.pgrst.plan"

写数据

PostgREST 写数据相关的操作少一些，主要包括以下这些：

操作	Method	单条	多条	注意事项
Insert	POST	Y	Y	状态码，缺省值，指定列，响应
Upsert	POST	Y	Y	指定PK，冲突处理(duplicate，UNIQUE)
Upsert	PUT	Y	N	指定PK
Update	PATCH	Y	Y	水平过滤，指定列，响应，limit
Delete	DELETE	Y	Y	水平过滤，响应，limit

使用以上写操作时应有以下注意事项：

Insert 操作成功时 HTTP 状态码是 201
Insert 操作时默认的缺省值是 null。可以添加 Prefer: missing=default 请求头将 null 调整成 SQL 中的 DEFAULT 值
Insert 操作时使用 columns 查询参数指定待插入的列
Upsert 操作可以理解 Update or Insert。使用这个操作时要指定明确的冲突处理策略(这里的冲突指的是值重复)
- 判断冲突的依据
  - 默认根据主键来判断
  - 也可以用有 UNIQUE 约束的列来判断是否冲突 (通过 on_conflict 查询参数来指定列)
- 解决冲突的办法
  - 合并重复值：添加请求头 Prefer: resolution=merge-duplicates
  - 忽略重复值：添加请求头 Prefer: resolution=ignore-duplicates
Method 为 PUT 的 Upsert 只针对单条数据进行操作，所以必须指定主键
Update 和 Delete 是两个危险的操作，参数指定不当的话可能修改或删除整个表中的数据。实践中可以结合使用 limit 和 order 两个查询参数来限制影响的数据范围，以避免误操作。
添加请求头 Prefer: return=representation 可以让接口返回响应体，以满足实际开发需求。具体可以 Return Representation

来看几个例子：

# 单条插入
curl "http://localhost:3000/table_name" \
  -X POST -H "Content-Type: application/json" \
  -d '{ "col1": "value1", "col2": "value2" }'

# 批量插入
curl "http://localhost:3000/people" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  [
    { "name": "J Doe", "age": 62, "height": 70 },
    { "name": "Janus", "age": 10, "height": 55 }
  ]
EOF

# 插入时使用 SQL `DEFAULT` 值作为缺失的值
curl "http://localhost:3000/foo?columns=id,bar,baz" \
  -H "Content-Type: application/json" \
  -H "Prefer: missing=default" \
  -d @- << EOF
    [
      { "bar": "val1" },
      { "bar": "val2", "baz": 15 }
    ]
EOF

# 只插入指定列 souce 和 figure
curl "http://localhost:3000/datasets?columns=source,figure" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  {
    "source": "Natural Disaster Prevention and Control",
    "publication_date": "2015-09-11",
    "figure": 1100,
  }
EOF

# Update 多行数据，只更新指定的列 category
curl "http://localhost:3000/people?age=lt.13" \
  -X PATCH -H "Content-Type: application/json" \
  -d '{ "category": "child" }'

# Update 单行数据，需要指定主键
curl "http://localhost/employees?id=eq.4" \
  -X PUT -H "Content-Type: application/json" \
  -d '{ "id": 4, "name": "Sara B.", "salary": 60000 }'

# Delete 数据，被删除的数据作为响应结果返回  
curl "http://localhost:3000/user?id=eq.1" -X DELETE \
  -H "Prefer: return=representation"

# 最多只删除10条数据
curl -X DELETE "http://localhost:3000/users?limit=10&order=id
 &status=eq.inactive"

总结

PostgREST REST API 的路由设计良好，简单易用。读几遍文档后我能借助 PostgREST 轻松高效地搞定个人项目后端接口，完全无需手写 SQL 或使用 ORM 框架。

PostgREST 的问题是它用较为冷门 Haskell 语言来开发。我有些担心以后遇到 bug 没那么容易动手解决。不过考虑到只是个人项目在用，目前看风险不大。

一个不那么好的规则

PostgREST 的规则

读数据

写数据

总结

参考