初识postgrest

postgrest是什么

PostgREST 是一个独立的 Web 服务器，为 PostgreSQL 数据库生成 RESTful API。 它提供基于底层数据库结构定制的 API。

postgrest安装

下载地址：https://github.com/PostgREST/postgrest/releases/download/v7.0.1/postgrest-v7.0.1-linux-x64-static.tar.xz
postgrest安装特别简单，这里以centos7.4为例
[root@lineqi opt]# tar -xvf postgrest-v7.0.1-linux-x64-static.tar.xz
[root@lineqi opt]# ./postgrest --help
Usage: postgrest FILENAME
PostgREST 7.0.1 (UNKNOWN) / create a REST API to an existing Postgres database

Available options:
-h,–help Show this help text
FILENAME Path to configuration file

Example Config File:
db-uri = “postgres://user:pass@localhost:5432/dbname”
db-schema = “public” # this schema gets added to the search_path of every request
db-anon-role = “postgres”
db-pool = 10
db-pool-timeout = 10

server-host = “!4”
server-port = 3000
出现以上输出信息表安装成功

postgrest demo

创建测试schema、role、table
webdata=# create schema testapi;
webdata=# create table testapi.test(id serial primary key,info text,crt_time timestamp default
webdata=# insert into testapi.test(info) values(‘aaaaaaa’);
webdata=# create role test nologin;
webdata=# grant usage on schema testapi to test;
webdata=# grant select on testapi.test to test;

配置postgrest.conf
[postgres@lineqi ~]$ vi postgrest.conf
db-uri = “postgres://lineqi:lineqi@192.168.1.99:1921/webdata”
db-schema = “testapi” # this schema gets added to the search_path of every request
db-anon-role = “test”
db-pool = 10
db-pool-timeout = 10

server-host = “192.168.1.99”
server-port = 4000

启动postgrest
[postgres@lineqi ~]$ /opt/postgrest postgrest.conf &
[postgres@lineqi ~]$ Listening on port 4000
Attempting to connect to the database…
Connection successful

postgrest 访问

curl方式
[postgres@lineqi ~]$ curl http://192.168.1.99:4000/test
[{“id”:1,“info”:“aaaaaaa”,“crt_time”:“2020-06-14T18:43:06.048133”}]
浏览器

通过这两种方式能成功获取到test表中的数据

postgrest常见错误

PostgREST 依赖 libpq （PostgreSQL 的 C 语言库）的安装。没有这个库的话你会获得一个形如 “error while loading shared libraries: libpq.so.5.” 的报错，以下是解决方案:
执行如下安装即可
yum install postgresql-libs

postgrest总结

1、postgrest安装过程非常容易
2、通过浏览器调用postgrest web生成api非常方便
3、postgrest可以作为独立的服务器，这里是与postgresql数据库服务器放在一起的

参考资料

http://postgrest.org/zh/latest/tutorials/tut0.html