Une API REST sans code grâce à Postgrest, Sub0 et PostgreSQL

François-Guillaume Ribreau

—

François-Guillaume Ribreau

—Ex-Bringr cofounder & CTO

François-Guillaume Ribreau

—Ex-Bringr cofounder & CTO

Ex-Architect @iAdvize

François-Guillaume Ribreau

—Ex-Bringr cofounder & CTO

Ex-Architect @iAdvizeArchitect & Head of development @Ouest-France

François-Guillaume Ribreau

—Ex-Bringr cofounder & CTO

Ex-Architect @iAdvizeArchitect & Head of development @Ouest-France

🌟 Founded @imagecharts @Redsmin @mailpopin

François-Guillaume Ribreau

—Ex-Bringr cofounder & CTO

Ex-Architect @iAdvizeArchitect & Head of development @Ouest-France

🌟 Founded @imagecharts @Redsmin @mailpopin

🚀 Trainer @EPSI_Nantes @UnivNantes

François-Guillaume Ribreau

—Ex-Bringr cofounder & CTO

Ex-Architect @iAdvizeArchitect & Head of development @Ouest-France

🌟 Founded @imagecharts @Redsmin @mailpopin

🚀 Trainer @EPSI_Nantes @UnivNantes📢 Twitter/Github: @FGRibreau

API?

API?

Persistence API?

Single Source of Truth?

API?

Persistence API?

Single Source of Truth?

DRY?

API?

Persistence API?

3-tier

Database

API

Frontend

3-tier

Database(Tables/Views

Schema)

API(Models / ORM)

Front

Validation

Database(Schema (constraint))

API(Models / ORM

(validation))

Front(validation)

Relation

Database(Schema (referential

integrity))

API(Models / ORM

(relations))

Front

Authorization

Database(Users, roles,

policies)

API(Authorization middleware)

Front

Etc… 🕰

Database…

API…

Front

API

DB

• HTTP request handling • Authentication• Authorization• Request Parsing • Request Validation • Database Communication • Database Response Handling • HTTP Response Building

Persistence API

your job

API

SQLHTTP DB

• HTTP request handling • Authentication• Authorization• Request Parsing • Request Validation • Database Communication • Database Response Handling • HTTP Response Building

Persistence API

your job

API

SQLHTTP DB

Persistence API

TL;DR: HTTP <-> SQL mapping

… with a lot of space for potential mistakes. your job

Postgrest DB

Persistence API

your job#SSoT #DRY

Are we serious?

Are we serious?

Postgrest DB

Persistence API

your job#SSoT #DRY

schema

Postgrest

Read / Write requests

(read) GET /projects (read) GET /account

(write) POST /rpc/signUp (write) POST /rpc/logIn

How do you manage projection, filtering, ordering?

GET /people?age=gte.18&isStudent=is.true

How do you manage projection, filtering, ordering?

GET /people?age=gte.18&isStudent=is.true

GET /people?select=age::text,height,weight

How do you manage projection, filtering, ordering?

GET /people?age=gte.18&isStudent=is.true

GET /people?select=age::text,height,weight

GET /stuff?metadata->a->>b=eq.2

How do you manage projection, filtering, ordering?

GET /people?age=gte.18&isStudent=is.true

GET /people?select=age::text,height,weight

GET /stuff?metadata->a->>b=eq.2

GET /projects?select=id,name,tasks{id,name}

&order=id.asc&tasks.order=name.asc

How do you manage projection, filtering, ordering?

How do you manage versioning?

How do you manage versioning?

public private

How do you manage versioning?

public private

authentication schema

i18n schema

app schema

….

schema

How do you manage versioning?

public private

v1_0 schema authentication schema

i18n schema

app schema

….

view schemastoredfn

projects signIn signUp

How do you manage versioning?

public private

v1_0 schema

v2_0 schema

authentication schema

i18n schema

app schema

….

view schemastoredfn

projects signIn signUp

projects logIn signUp

How do you manage authentication?

How do you manage authentication?

How do you manage authorization?

How do you manage authorization?

CREATE ROLE authenticator NOINHERIT LOGIN; CREATE ROLE anonymous; CREATE ROLE authenticated_user; GRANT anonymous, authenticated_user TO authenticator;

How do you manage authorization?

How do you manage authorization?

Row Level Security (PG 9.5+)

How do you manage authorization?

Row Level Security (PG 9.5+)ALTER TABLE app.project ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_can_only_access_its_own_project on app.project — Any rows for which the expression returns false or null will not be visible to the user (in a SELECT), and will not be available for modification (in an UPDATE or DELETE)

using (user_id = current_setting('request.jwt.claim.user_id'))

How do you manage authorization?

Row Level Security (PG 9.5+)ALTER TABLE app.project ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_can_only_access_its_own_project on app.project — Any rows for which the expression returns false or null will not be visible to the user (in a SELECT), and will not be available for modification (in an UPDATE or DELETE)

using (user_id = current_setting('request.jwt.claim.user_id'))

2 lines of SQLReliable security model (closed by default)

Declarative Expressive

How do you manage emails/3rd parties?

http://bit.ly/2oNbaKy

How do you manage emails/3rd parties?pg_notify (PG 9.2+)

http://bit.ly/2oNbaKy

How do you manage documentation?

How do you manage documentation?

OpenAPI (Swagger) formatautomatically extracted from schema

How do you manage code-reviews, tests, migrations?

Pivotal/trilogy pg_tag pg_unit pg_unit2

How do you manage code-reviews, tests, migrations?

It’s just SQL.

Pivotal/trilogy pg_tag pg_unit pg_unit2

One more thing

PostgraphQL A GraphQL API created by reflection over a PostgreSQL schema. (NodeJS)

Sub0 ❤ GraphQL & REST API for your database

Free plans for Redis administration & monitoring

at redsmin.com

Questions?

@FGRibreau

No more server-side rendering pain, 1 url = 1 chart

image-charts.com

Free plans for Redis administration & monitoring

at redsmin.com

We are looking for Front-end Developers

twitter.com/iadvizetech

Questions?

@FGRibreau

No more server-side rendering pain, 1 url = 1 chart

image-charts.com