PostgREST REST API 用法总结
通过 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 规则的可读性差异:
1 | # soul |
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 风格的路由:
1 | /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
- 有三种不同的计数方式:
来看几个具体的例子:
1 | # 查询年龄大于18且小于21的人 |
PostgreSQL 数据库索引(index)对于读数据过程是透明的。我们可以通过添加 Accept: application/vnd.pgrst.plan
请求头来观察 Execution plan ,以判断索引的生效情况。
1 | curl "http://localhost:3000/users?select=name&order=id" \ |
写数据
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
来看几个例子:
1 | # 单条插入 |
总结
PostgREST REST API 的路由设计良好,简单易用。读几遍文档后我能借助 PostgREST 轻松高效地搞定个人项目后端接口,完全无需手写 SQL 或使用 ORM 框架。
PostgREST 的问题是它用较为冷门 Haskell 语言来开发。我有些担心以后遇到 bug 没那么容易动手解决。不过考虑到只是个人项目在用,目前看风险不大。