神器PostgREST

新工具 pREST

[更新于 2018-07-20]
pREST is a way to serve a RESTful APIs for any database
PostgREST 用haskell写的, pREST用go写的, 会haskell的都是怪胎

pREST 例子 http://<url>/<db>/public/<tabel>?tokens=$notnull&balance=$gt.0&_page_size=20&_page=2
查询tabel中 tokens不为空 balance>0的数据, 还分页了

What is PostgREST

REST API for any Postgres database

PostgREST 可以在定义的数据表结构上自动生成 RESTful风格的CURD,更可以查询, 排序...

查询条件可以灵活组合, 比如

http://localhost:4000/blocks?limit=10 #获取前10条数据
http://localhost:4000/blocks?limit=10&select=height #只返回height字段
http://localhost:4000/blocks?limit=10&select=height,hash&id.gte.99999 # 获取id>99999的数据

生产力暴棚

安装

PostgREST 用出名的学院派计算机语言 Haskell写的
本人可不想编译 haskell, 使用PostgREST打包好的releases版本就好了

配置文件

#db.conf
db-uri = "postgres://postgres:postgres@localhost:5432/eth"
db-schema = "public" # postgre default schema public
db-anon-role = "postgres"
server-host = "0.0.0.0"
server-port = 4000

设置访问权限

在pg_hba.conf中配置服务端允许的认证方式

#/etc/postgresql/9.x/main/pg_hba.conf
# TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
#host  all  all 0.0.0.0/0 md5

host all all 127.0.0.1/32  md5

sudo service postgresql restart

启动

postgrest db.conf

请求数据

curl http://localhost:4000/blocks #直接请求表 `blocks`

[
  {
    "id":1,
    "height":10,
    "timestamp":1516894509,
    "txs_num":500,
    "hash":"0x40b8e073a6196d330a35c4b41f8ec9e7a00aa4898d580ee360a703119517d986",
    "parent_hash":"0x53d898401c6c7900cc0b14da33c2d25b1d4a83db36e7faefc998f597682ea708",
    "uncle_hash":"0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347",
    "coinbase":"0x5a0b54d5dc17e0aadc383d2db43b0a0d3e029c4c",
    "difficulty":236596,
    "size":29216,
    "gas_used":7965081,
    "gas_limit":7988374,
    "nonce":61634,
    "reward":5193,
    "extra_data":"sparkpool-cn-node-2",
  }
]


http://localhost:4000/blocks?limit=10 #获取前10条数据
http://localhost:4000/blocks?limit=10&offset=30 #分页
http://localhost:4000/blocks?limit=10&order=height.desc #倒序
http://localhost:4000/blocks?limit=10&select=height #只获取height字段
http://localhost:4000/blocks?limit=10&select=height,hash&id.gte.99999 # 获取id>99999的数据

更复杂的查询请看 PostgREST的API文档

想法

以后出原型

1 定义表数据结构
2 用postgREST 逆天神器
3 react 撸页面

---

参考:

http://www.cnblogs.com/hiloves/archive/2011/08/20/2147043.html
https://stackoverflow.com/questions/7695962/postgresql-password-authentication-failed-for-user-postgres/24680845#24680845