What is PostgREST
從字面上看 PostgREST 是 PostgreSQL+ RESTful的組合體柳恐,PostgreSQL是一個類似Mysql但比Mysql強大的多的關系性數據庫喘先。
PostgREST是haskell寫的闹瞧,以 postgres 數據庫為基礎绑雄,替你自動生成REST API。你只需要通過sql定義好數據庫的table等奥邮,postgrest就能生成REST API万牺。 PostgREST效率非常高,而且它連json的render 都在postgres中處理洽腺,性能反而還不錯脚粟。
有這樣的一張數據表結構
create table todos (
id serial primary key,
done boolean not null default false,
task text not null,
due timestamptz
);
insert into todos (task) values
('finish tutorial 0'), ('pat self on back');
運行PostgREST ,你就可以通過http訪問數據庫數據了
[
{
"id":1,
"done":false,
"task":"finish tutorial 0",
"due":null
},
{
"id":2,
"done":false,
"task":"pat self on back",
"due":null
}
]
安裝
下載PostgREST打包好的releases版本就好
配置文件 db.conf
# 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 # 端口
設置PostgreSQL訪問權限
在pg_hba.conf
中配置服務端允許的認證方式
#/etc/postgresql/9.x/main/pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 127.0.0.1/0 md5
重啟數據庫 sudo service postgresql restart
啟動PostgREST
postgrest db.conf
根據配置文件中設定的端口啟動http服務
請求數據
以下為真實數據
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",
}
]
查詢條件可以靈活組合蘸朋,生產力暴棚核无,更復雜的查詢請看 PostgREST的API文檔
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的數據
想法
以后出原型,可以這么做:
1 定義表數據結構
2 用postgREST 逆天神器生成RESTful API度液, 支持CURD
3 react 擼頁面與API交互
新工具 pREST
[更新于 2018-07-20]
pREST 跟PostgREST功能類似厕宗。PostgREST 用haskell寫的,pREST用go寫的堕担,文檔也更全面已慢。
pREST 例子
http://<url>/<db>/public/<tabel>?tokens=$notnull&balance=$gt.0&_page_size=20&_page=2
查詢tabel中 tokens不為空 balance>0
的數據,也支持分頁霹购,太棒了佑惠。
[更新于 2020-05-15]
prest.toml
配置文件內容:
# migrations = "./migrations"
# debug = true
# enabling debug mode will disable JWT authorization
[http]
port = 9000
# Port 6000 is blocked on windows. You must change to 8080 or any unblocked port
[jwt]
default = false
[pg]
host = "127.0.0.1"
user = "postgres"
pass = "postgres"
port = 5432
database = "zqw"
## or used cloud factor
# URL = "postgresql://user:pass@localhost/mydatabase/?sslmode=disable"
[access]
restrict = true # can access only the tables listed below
[[access.tables]]
name = "block"
permissions = ["read"]
fields="*"
參考:
http://postgrest.org/zh/latest/index.html
http://www.cnblogs.com/hiloves/archive/2011/08/20/2147043.html
https://github.com/prest/prest/
https://github.com/diogob/postgres-websockets
https://stackoverflow.com/a/24680845
https://github.com/dbohdan/automatic-api