使用PostgREST的RestAPI操作PostgreSQL数据库教程

注意

为什么不提供嵌套路线&＃xff1f;许多API允许嵌套检索相关信息&＃xff0c;例如/films/1/director。我们提供了一种更灵活的机制&＃xff08;受GraphQL启发&＃xff09;来嵌入相关信息。它可以处理一对多和多对多关系。关于资源嵌入的部分对此进行了介绍。

水平过滤&＃xff08;行&＃xff09;

您可以通过在列上添加条件来过滤结果行&＃xff0c;每个条件都有一个查询字符串参数。例如&＃xff0c;要返回13岁以下的人&＃xff1a;

多个参数可以通过以下方式逻辑组合&＃xff1a;

多个参数可以通过以下方式在逻辑上脱节&＃xff1a;

也可以应用复杂的逻辑&＃xff1a;

垂直过滤&＃xff08;列&＃xff09;

当某些列很宽时&＃xff08;例如那些保存二进制数据的列&＃xff09;&＃xff0c;服务器在响应中保留它们会更有效。客户端可以使用select参数指定需要哪些列。

默认值为*&＃xff0c;表示所有列。在“ 资源嵌入”中&＃xff0c;此值将变得更加重要。

Unicode支持

PostgREST支持模式&＃xff0c;表&＃xff0c;列和值中的unicode。要访问具有Unicode名称的表&＃xff0c;请使用百分比编码。

要求此&＃xff1a;

做这个&＃xff1a;

订购

保留字order对响应行进行重新排序。它使用逗号分隔的列和方向列表&＃xff1a;

如果未指定方向&＃xff0c;则默认为升序&＃xff1a;

如果您关心null的排序位置&＃xff0c;请先添加nullsfirst或nullslast&＃xff1a;

您也可以使用“ 计算列”对结果进行排序&＃xff0c;即使计算列不会出现在输出中。

限制和分页

PostgREST使用HTTP范围标头来描述结果的大小。每个响应均包含当前范围以及&＃xff08;如果要求&＃xff09;结果总数&＃xff1a;

这里返回零到十四的项目。该信息在每个响应中都可用&＃xff0c;并且可以帮助您在客户端上呈现分页控件。这是一个符合RFC7233的解决方案&＃xff0c;可以使响应JSON更干净。

应用限制行和偏移行的方式有两种&＃xff1a;通过请求标头或查询参数。使用标题时&＃xff0c;请指定所需的行范围。该请求将获得前20个人。

请注意&＃xff0c;如果无法满足您的请求&＃xff0c;服务器的响应可能会更少&＃xff1a;

您也可以要求无限制范围的开放范围&＃xff0c;例如。Range: 10-

请求限制或偏移量的另一种方法是使用查询参数。例如

此方法对于嵌入式资源也很有用&＃xff0c;我们将在另一部分中介绍。即使您使用查询参数来限制查询&＃xff0c;服务器也会始终以范围标头作为响应。

为了获得表或视图的总大小&＃xff08;例如在分页控件中呈现最后一个页面链接时&＃xff09;&＃xff0c;请在请求标头中指定您的首选项&＃xff1a;

请注意&＃xff0c;表越大&＃xff0c;查询在数据库中运行的速度就越慢。服务器将响应所选范围和总计

响应格式

PostgREST使用适当的HTTP内容协商&＃xff08;RFC7231&＃xff09;来传递所需的资源表示形式。也就是说&＃xff0c;相同的API终结点可以根据客户端请求以JSON或CSV等不同格式进行响应。

使用接受请求标头为响应指定可接受的格式&＃xff1a;

当前的可能性是

• * / *
• 文字/ csv
• 应用程序/ json
• 应用程序/ openapi &＃43; json
• 应用/八位字节流

对于API端点和根目录上的OpenAPI&＃xff0c;服务器将默认为JSON。

单数或复数

默认情况下&＃xff0c;即使只有一项&＃xff0c;PostgREST也会以数组形式返回所有JSON结果。例如&＃xff0c;要求/items?id&＃61;eq.1退货

这对于客户端代码可能很不方便。要将第一个结果作为数组未包含的对象返回&＃xff0c;请指定vnd.pgrst.object为Accept标头的一部分

这返回

当请求单个响应但未找到任何条目时&＃xff0c;服务器将以错误消息和406 Not Acceptable状态码响应&＃xff0c;而不是通常的空数组和200状态&＃xff1a;

重要

PostgREST需要FOREIGN KEY约束才能进行资源嵌入。

重要

每当数据库模式中的FOREIGN KEY约束发生更改时&＃xff0c;都必须刷新PostgREST的模式缓存&＃xff0c;以使“资源嵌入”正常工作。请参阅“ 模式重新加载 ”部分。

通过联接表嵌入

PostgREST还可以检测通过联接表进行的关系。因此&＃xff0c;您可以请求电影演员&＃xff08;在这种情况下&＃xff0c;通过角色来查找信息&＃xff09;。您还可以反转包容的方向&＃xff0c;要求所有导演&＃xff0c;包括其电影列表&＃xff1a;

嵌入式过滤器

嵌入式资源的形状可以与其顶级资源相似。为此&＃xff0c;请在查询参数前面加上嵌入式资源的名称。例如&＃xff0c;要订购每部电影中的演员&＃xff1a;

这个排序在每一部电影的演员名单&＃xff0c;但并没有改变电影本身的顺序。要过滤每部电影返回的角色&＃xff1a;

再一次&＃xff0c;这将角色包括在某些角色中&＃xff0c;但不以任何方式过滤电影。没有这些字符的电影将与空字符列表一起包括在内。

一个or过滤器可以用于类似的操作&＃xff1a;

限制和偏移操作是可能的&＃xff1a;

可以为嵌入式资源添加别名&＃xff0c;并可以对这些别名应用过滤器&＃xff1a;

嵌入浏览

如果视图包含在源表中定义了外键的列&＃xff0c;则可以嵌入视图。

作为示例&＃xff0c;让我们nominations_view基于提名表创建一个名为的视图。

由于其中包含competition_id和film_id&＃xff0c;并且每个人的源表中都有一个外键&＃xff0c;因此我们可以嵌入竞赛和电影&＃xff1a;

也可以嵌入物化视图。

重要

每当您创建或更改函数时&＃xff0c;都必须刷新PostgREST的架构缓存。请参阅“ 模式重新加载 ”部分。

如果未刷新架构缓存&＃xff0c;则PostgREST将假定text为函数参数的默认类型。这可能会导致获得错误响应&＃xff0c;例如&＃xff1a;

注意

对于PostgreSQL 10之前的版本&＃xff0c;要传递PostgreSQL本机数组&＃xff0c;您需要将其引用为字符串&＃xff1a;

在这些版本中&＃xff0c;我们建议使用json类型的函数参数来接受来自客户端的数组&＃xff1a;

从PostgreSQL 10开始&＃xff0c;来自客户端的json数组通常会映射到PostgreSQL本机数组。

注意

为什么使用/ rpc前缀&＃xff1f;原因之一是避免视图和过程之间的名称冲突。它还有助于向API使用者强调这些功能不是正常的烦人的事情。这些功能可能具有任意和令人惊讶的行为&＃xff0c;而不是用户期望从其他途径获得的标准“后期创建资源”。

稳定的功能

PostgreSQL中标有stable或易失immutable 性的过程只能读取&＃xff0c;不能修改数据库&＃xff0c;而PostgREST在与只读副本兼容的只读事务中执行它们。如果需要&＃xff0c;可以使用HTTP GET动词调用稳定和不可变的函数。

因为add_them被声明为IMMUTABLE&＃xff0c;所以我们可以用GET请求交替调用该函数&＃xff1a;

函数参数名称在POST情况下与JSON对象键匹配&＃xff0c;对于GET情况&＃xff0c;它们与查询参数匹配?a&＃61;1&b&＃61;2。

标量函数

PostgREST将检测该函数是标量的还是表值的&＃xff0c;并将相应地调整响应格式&＃xff1a;

大话务量

类似于Bulk Insert&＃xff0c;可以批量调用函数。

结果&＃xff1a;

也可以在函数调用中指定列。

功能过滤器

返回表类型响应的函数可以使用与用于表和视图的过滤器相同的过滤器来整形&＃xff1a;

功能权限

默认情况下&＃xff0c;函数以调用它的用户的特权执行。这意味着用户必须具有所有权限才能执行该过程执行的操作。

另一种选择是使用该选项定义功能。然后&＃xff0c;将只进行一次权限检查&＃xff0c;即调用该功能的权限&＃xff0c;并且该功能中的操作将拥有拥有该功能本身的用户的权限。有关更多详细信息&＃xff0c;请参见PostgreSQL文档。SECURITY DEFINER

重载函数

您可以使用不同数量的参数调用重载函数。

访问请求头&＃xff0c;COOKIE和JWT声明

通过读取每个请求的PostgREST设置的GUC变量&＃xff0c;存储过程可以访问请求标头&＃xff0c;COOKIE和jwt声明。它们被命名为request.header.XYZ&＃xff0c;request.COOKIE.XYZ和request.jwt.claim.XYZ。

设置响应头

PostgREST读取response.headersSQL变量以将额外的标头添加到HTTP响应。存储过程可以修改此变量。例如&＃xff0c;此语句将在响应中添加缓存头&＃xff1a;

请注意&＃xff0c;该变量应设置为单键对象数组&＃xff0c;而不是单个多键对象。这是因为设置多个值时&＃xff0c;必须重复标头&＃xff08;例如Cache-Control或&＃xff09;Set-COOKIE&＃xff0c;并且对象不允许重复的键。

错误和HTTP状态代码

通过引发SQL异常&＃xff0c;存储过程可以返回非200 HTTP状态代码。例如&＃xff0c;这是一个伪函数&＃xff0c;总是会返回错误&＃xff1a;

调用该函数将返回带有正文的HTTP 400

定制HTTP状态代码的一种方法是根据PostgREST 错误到状态代码的映射引发特定异常。例如&＃xff0c;将使用HTTP 401/403进行响应。RAISE insufficient_privilege

为了更好地控制HTTP状态代码&＃xff0c;请引发PTxyz类型的异常。例如&＃xff0c;使用HTTP 402进行响应&＃xff0c;请举起&＃39;PT402&＃39;&＃xff1a;

返回值&＃xff1a;

注意

插入行时&＃xff0c;您必须发布一个JSON对象&＃xff0c;而不是用引号引起来的JSON。

如果您不小心&＃xff0c;某些Javascript库会错误地发布数据。为了获得最佳结果&＃xff0c;请尝试为PostgREST构建的客户端库之一。

警告

当心意外更新表中的每一行。要了解如何防止这种情况&＃xff0c;请参阅“ 阻止全表操作”。

警告

使用PostgREST可能无法在具有复杂规则的VIEW上插入。建议您使用触发器而不是RULEs。如果要继续使用RULE&＃xff0c;一种解决方法是将VIEW插入包装在存储过程中&＃xff0c;然后通过“ 存储过程”界面调用它。

批量插入

批量插入的工作原理与单行插入完全相同&＃xff0c;不同之处在于&＃xff0c;您可以提供具有统一键的对象的JSON数组或CSV格式的行。这不仅使所需的HTTP请求最小化&＃xff0c;而且在后端使用单个INSERT语句以提高效率。请注意&＃xff0c;使用CSV需要较少的服务器解析&＃xff0c;并且速度更快。

要批量插入CSV&＃xff0c;只需将发布到的表格路由中&＃xff0c;并在第一行中包含列名即可。例如Content-Type: text/csv

空字段&＃xff08;,,&＃xff09;强制为空字符串&＃xff0c;保留字NULL映射为SQL空值。请注意&＃xff0c;列名和逗号之间不应有空格。

要批量插入JSON&＃xff0c;请发布具有所有匹配键的对象数组

指定列

通过使用columns查询参数&＃xff0c;可以指定将要插入/更新的有效负载密钥&＃xff0c;而忽略其余的有效负载。

在这种情况下&＃xff0c;将仅插入source&＃xff0c;publication_date和Figure。其余JSON键将被忽略。

使用此方法还具有提高批量插入效率的副作用&＃xff0c;因为PostgREST不会处理JSON&＃xff0c;而是将其直接发送到PostgreSQL。

UPSERT 

您可以使用POST和标头制作UPSERT &＃xff1a;Prefer: resolution&＃61;merge-duplicates

UPSERT基于主键列进行操作&＃xff0c;您必须指定所有这些键。您还可以选择使用忽略重复项。当主键是自然键时&＃xff0c;UPSERT效果最好&＃xff0c;但是如果主键是替代键&＃xff0c;则还可以使用它&＃xff08;例如&＃xff1a;“ id串行主键”&＃xff09;。有关更多详细信息&＃xff0c;请阅读此问题。Prefer: resolution&＃61;ignore-duplicates

可以通过以下方式使用PUT和过滤主键列来完成单行UPSERT eq&＃xff1a;

必须在请求正文中指定所有列&＃xff0c;包括主键列。

警告

当心意外删除表中的所有行。要了解如何防止这种情况&＃xff0c;请参阅“ 阻止全表操作”。

注意

如果将返回多于一行&＃xff0c;则二进制结果将被串联&＃xff0c;并且没有定界符。

纯文本输出

您可以text使用来从列中获取原始输出。Accept: text/plain

这遵循与Binary Output相同的规则。

重要

随着架构在运行的服务器下更改&＃xff0c;OpenAPI信息可能会过时。要了解如何刷新缓存&＃xff0c;请参阅Schema Reloading。