Automatic REST APIs with


Jett Durham
@thejettdurham
Who I Am...
Lead Software Developer
What I Am...
full stack and then some

What I Am Not...
postgREST contributor
SQL or PostgreSQL expert
Network Security Expert



Talk Overview
-
Building REST APIs (the hard way)
-
Enter PostgREST
-
DEMO: Exploration
-
Security with PostgREST
-
DEMO: Hospitable Takeover
-
Final Thoughts
Building REST APIs
The hard way
The Layer Cake Pattern
Your Application

Web Server
Database
Request
Response
What's in an App?
HTTP Request Handling
ERROR HANDLING
Authentication
Authorization
Request Parsing
Request Validation
Database Communication
Database Response Handling
HTTP Response Building
Layers in Layers
Your Application
Your Code
Data Handling Library
Object Relational Mapper
Authentication Framework
Application Framework
Much Better!
Active Record cut my lines of code in half!
oAuth lets me stop worrying about my API security!
Play Framework makes writing web apps so easy!



Much Better?
I updated Active Record and the API broke. How to fix?
The last version of oAuth had a security flaw that leaked our customer emails to spambots
Play Framework updated again, time for another rewrite! And updating to Java 8. And updating to Gradle 2.8. And...



Libraries are great!
But know the tradeoffs
A Baker's (Half)-Dozen
Mark's Social Network






Gordon's Coffee Shop
David's DVD Store
Larry's Search Engine
Jeff's Warehouse
Daniel's Streaming Service
Most APIs kinda look the same...
2 Sides of a Coin
GET
POST
PATCH
DELETE
SELECT
INSERT
UPDATE
DELETE








What do?
Enter PostgREST


Where it fits
Your Application

Web Server
Database
Request
Response

Is It Fast?
TL;DR: subsecond response times for up to 2000 requests/sec on Heroku free tier.
TL;DR;TL;DR: Wicked Fast!

What is this sorcery?!



Haskell
PostgreSQL
Warp Web Server
Need to Scale?

















DEMO
Exploration
Demo System



10.0.3.2

:5432
/dvdrental

:3000
Demo Database

Sakila Sample Database
- Tables
- Views
- Functions
- Triggers
Security with PostgREST
SSL?


No...

...but wait!
It Doesn't Need To






Authentication?

Authorization?

JSON Web Tokens
{
"user": "seth",
"role": "administrator"
}


+
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6InNldGgiLCJyb2xlIjoiYWRtaW5pc3RyYXRvciJ9.3KPWRfCmFTsMN7D8p8Uz0s4Xsxxuuu9QOB83TkSnLq0
Special DB Users


Anonymous
Authenticator
Unauthenticated

{
"user": "seth",
"role": "administrator"
}
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6InNldGgiLCJyb2xlIjoiYWRtaW5pc3RyYXRvciJ9.3KPWRfCmFTsMN7D8p8Uz0s4Xsxxuuu9QOB83TkSnLq0
Authenticated


DEMO
Hospitable Takeover
The DVD store has been assimilated

One API to rule them all
Internal Admin Panel
Public Website: Film search


President
All Access
Michael Bluth

Accountant
List & modifies payments
Also has Worker permissions
Gob Bluth

Worker
Lists films, categories, actors, languages, & customers
List & Creates Rentals
George Michael Bluth


John Q Public
Lists films
John Q

Final Thoughts
What is it really good for?
Domain Model
Database Tables

Old Databases
public
apiv1














Public-facing API?
Probably not...

Private APIs!

Competition?
Third Party Alternatives
mongodb-rest



Third Party Alternatives
Automatic API REST



Third Party Alternatives
phprestsql



Third Party Alternatives
arrest-db






Third Party Alternatives
restSQL




First-Party Alternatives
HTTP API


First-Party Alternatives
HTTP API





The Future of PostgREST?

In Review...
REST APIs are important
Building & Maintaining APIs can be hard!
PostgREST can set you free!
We're at the start of the auto API trend
fin
Jett Durham
@thejettdurham
Essential Concepts

Tables, Rows, Columns
0 | michael | develop... | 3 | 56500 | ... |
1 | john | analyst | 6 | 47900 | ... |
user_id | name | job_title | dept_id | salary | notes |
---|
employee |
---|
Keys: Primary, Foreign
0 | michael | develop... | 3 | 56500 | ... |
1 | john | analyst | 6 | 47900 | ... |
user_id | name | job_title | dept_id | salary | notes |
---|
employee |
---|



Views

Functions & Triggers
-- SAMPLE FUNCTION
CREATE FUNCTION tf1 (acct_no integer, debit numeric)
RETURNS numeric AS $$
UPDATE bank
SET balance = balance - $2
WHERE accountno = $1
RETURNING balance;
$$ LANGUAGE SQL;
-- SAMPLE TRIGGER
CREATE TRIGGER emp_stamp
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
Schemas











Databases









Database Server








REST API Recap
REST APIs power the modern web





Why REST APIs?
Interoperability
Ease of Consumption
Ease of Development
?
REST Nuts & Bolts

HTTP Request
https://
api.twitter.com
/1.1
/users/suggestions.json
?id=foo
- GET
- POST
- PUT
- DELETE
- PATCH
- OPTIONS
- HEAD
HTTP Method
KEY | VALUE |
---|---|
Authorization | ... |
Content-Type | application/json |
Request Headers
Request Body
{
"glossary": {
"title": "example glossary",
"GlossDiv": {
"title": "S",
"GlossList": {
"GlossEntry": {
"ID": "SGML",
"SortAs": "SGML",
"GlossTerm": "Standard Generalized Markup Language",
"Acronym": "SGML",
"Abbrev": "ISO 8879:1986",
"GlossDef": {
"para": "A meta-markup language, used to create markup languages such as DocBook.",
"GlossSeeAlso": ["GML", "XML"]
},
"GlossSee": "markup"
}
}
}
}
}
URL
HTTP Response
- 200
- 301
- 403
- 404
- 451
- 500
Status Code
KEY | VALUE |
---|---|
Connection | keep-alive |
Content-Type | application/json |
Response Headers
Response Body
{
"glossary": {
"title": "example glossary",
"GlossDiv": {
"title": "S",
"GlossList": {
"GlossEntry": {
"ID": "SGML",
"SortAs": "SGML",
"GlossTerm": "Standard Generalized Markup Language",
"Acronym": "SGML",
"Abbrev": "ISO 8879:1986",
"GlossDef": {
"para": "A meta-markup language, used to create markup languages such as DocBook.",
"GlossSeeAlso": ["GML", "XML"]
},
"GlossSee": "markup"
}
}
}
}
}
JSON
{
"Count": 1670,
"Username": "jdurham",
"IsPresent": true,
"JobTitles": [
"Software Developer",
"Software Engineer",
"Sysadmin",
"Supervisor"
],
"Addresses": [
{
"Name": "home",
"Street": "123 Main St.",
"City": "Wichita",
"State": "Kansas",
"Zip": 67212
},
{
"Name": "work",
"Street": "266 N Main St.",
"City": "Wichita",
"State": "Kansas",
"Zip": 67202
}
]
}

-
Lightweight
-
Expressive
-
Readable
-
Portable
Automatic REST APIs with PostgREST
By Jett Durham
Automatic REST APIs with PostgREST
- 1,497