PostgREST

Kevin Li

Created: 2020-05-06 Wed 18:57

1 The Future

  • Restfull will the past.
  • A new standard will rise.
    • More about data, one data model everywhere.
    • It'll answer your question.
    • Data is the API.
  • something like
    • GraphQL
    • Falcor
    • and Postgrest

2 什么是PostgREST: PostgreSQL + restful

  • 从PostgreSQL数据库生成restful接口
  • restful的endpoint的来源
    • 表/视图
    • 外键
    • 存储过程/函数
  • 使用JWT做认证, 数据库用户权限映射到JWT

3 Tutorial

安装PostgreSQL和Postgrest, 使用pgloader导入部分数据进行测试.

3.1 Install

3.1.1 PostgreSQL

安装并且作为一个系统服务 

brew doctor
brew update
brew install postgresql
brew services start postgresql

3.1.2 Postgrest

brew install postgrest

3.1.3 pgloader

brew install pgloader

3.2 prepare data

# default user postgres
# psql DBNAME USERNAME
psql postgres

CREATE USER dbuser WITH PASSWORD 'password';
CREATE DATABASE demodb OWNER dbuser;
GRANT ALL PRIVILEGES ON DATABASE demodb to dbuser;
\z

\dt

\d

\?

\conninfo

\d+

\l

\dn

\e

3.3 mysql config

Add to [mysqld] section of my.cnf file (found in /usr/local/etc/ for Homebrew's installation):
default-authentication-plugin=mysql_native_password

mysql -u root -p

ALTER USER 'root'@'localhost'
   IDENTIFIED WITH mysql_native_password
   BY 'password';


# reboot service
brew services restart mysql

3.4 import data

pgloader mysql://root:007a007b@localhost/pdb pgsql:///demodb

3.5 start service

create tutorial.conf 

db-uri = "postgres://lizy:lizy@localhost:5433/demodb"
db-schema = "pdb"
db-anon-role = "lizy"

run with conf: 

postgrest tutorial.conf

3.6 user authoriation/authentation

grant select on  pdb.t_index_category to lizy;

grant select on  pdb.t_activity_product to lizy;

grant select on  pdb.t_category to lizy;


ALTER TABLE pdb.t_index_category
DROP CONSTRAINT constraint_fk;

ALTER TABLE pdb.t_index_category
ADD CONSTRAINT constraint_fk
FOREIGN KEY (category_id)
REFERENCES t_category(category_id);


\d pdb.t_index_category

\d pdb.t_category

\d+

3.7 examples

3.7.1 query

  • 返回值类型可定义
  • 联合多个条件
  • 部分字段返回
  • 排序
  • 返回条数
  • 分页
GET http://localhost:3000/t_index_category?select=index_category_id&order=index_category_id.desc

3.7.2 with json

GET http://localhost:3000/t_activity_product

3.7.3 relations

GET http://localhost:3000/t_index_category?select=index_category_id, company_id, app_id, t_category(category_id)

4 references