- full stack developer
- didn’t take Databases 101
- always finding faster ways to ship apps.
Your mission, if you choose to accept it, is to build a REST API on top of your database.
- easy to get started
- sensible defaults, avoid decision fatigue
- hard to customize
- lot of code
Cleaner and a more standards compliant API than the one you’d write from scratch
Quick to get started, all you need to do is create the database.
Simplicity
Easy to scale
“Civilization advances by extending the number of important operations which we can perform without thinking of them.” Alfred North Whitehead.
WARNING: this does not imply that we run without any backend.
- we take a lot of backend stuff for granted
- trend: backend becoming more and more easy to setup, ex. GraphQL(content)
- frontend is getting more ready to consume REST API, ex. flavour of the month js frameworks popping up(form)
- more separation bet. form and content
Generic interface between application and webserver
Usage: postgrest DB_URL (-a|--anonymous ROLE) [-s|--schema NAME]
[-p|--port PORT] [-j|--jwt-secret SECRET] [-o|--pool COUNT]
[-m|--max-rows COUNT]
PostgREST 0.3.1.1 / create a REST API to an existing Postgres database
Available options:
-h,--help Show this help text
DB_URL (REQUIRED) database connection string, e.g.
postgres://user:pass@host:port/db
-a,--anonymous ROLE (REQUIRED) postgres role to use for non-authenticated
requests
-s,--schema NAME schema to use for API routes (default: "public")
-p,--port PORT port number on which to run HTTP
server (default: 3000)
-j,--jwt-secret SECRET secret used to encrypt and decrypt JWT
tokens (default: "secret")
-o,--pool COUNT max connections in database pool (default: 10)
-m,--max-rows COUNT max rows in response (default: "infinity")
Follows the UNIX philosophy
tables/views map to routes
- POST ~ INSERT
- GET ~ SELECT
- GET /1?foo=bar ~ SELECT WHERE foo=bar
- PATCH ~ UPDATE
- PUT ~ UPSERT
- DELETE ~ DELETE
Pagination/range headers ~ LIMIT and OFFSET
Auth ~ user roles
“Postgres is the Emacs of databases.” Craig Kerstiens https://speakerdeck.com/craigkerstiens/postgres-demystified-1
A lot less verbose than XML
Native javascript!
- plsql, plv8 etc
- fat models, thin controllers.
Always use ssl to serve rest apis, no exceptions!
Use Nginx
APIs keep changing
Avoid hair splitting
Was done initially using schemas
Now offloaded to Nginx
- different schemas running in different ports
- nginx configured to route accordingly
Open standard for passing claims between 2 parties.
User does HTTP requests with a role claim
Postgrest will switch to that role for the duration of the request
One key to rule them all.
Pushes everything to client.
ALTER TABLE todos ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_todos ON todos
USING (author = basic_auth.current_email());
CREATE POLICY authors_eigencreate ON todos FOR INSERT
WITH CHECK (
author = basic_auth.current_email()
);
CREATE POLICY authors_eigenedit ON todos FOR update
USING (author = basic_auth.current_email())
WITH CHECK (
author = basic_auth.current_email()
);
CREATE POLICY authors_eigendelete ON todos FOR delete
USING (author = basic_auth.current_email());
http://blog.2ndquadrant.com/emulating-row-security-in-postgresql-9-4/
GET /
Rudimentary HATEOS
You can customize which columns are returned using the select parameter:
GET /people?select=age,height,weight
JSON drill-down
GET /people?employees->3->>id=eq.2
Set Content-Type: text/csv
and do:
POST /people name,age,height J Doe,62,70 Jonas,10,55
Mark all people whose age < 13 as “child”.
PATCH /people?age=lt.13 { “person_type”: “child” }
- convert your legacy DB to an API
- single page apps
- mobile backends
- poor man’s Parse, using
pg_notify
PostGraphQL https://github.com/calebmer/postgraphql
PgREST http://pgre.st/
No nested resources
GET /people/students/1
not allowed.
Relational data is flat by nature.
RPCs as POST requests
Docker image https://hub.docker.com/r/begriffs/postgrest/
AWS Hint: AWS Lambda.
Heroku
- https://www.flickr.com/photos/pong/288491653/
- https://www.flickr.com/photos/hinnosaar/3932759035/
- https://www.flickr.com/photos/sidelong/3878741556/
- https://www.flickr.com/photos/72562013@N06/8415307629/
- https://www.flickr.com/photos/ajmexico/3281139507/
- https://www.flickr.com/photos/kylemay/1470605905/
- https://www.flickr.com/photos/matski_98/21938357/
- https://www.flickr.com/photos/danramarch/4565233602/
- https://www.flickr.com/photos/chrisandhilleary/191838649/
- https://www.flickr.com/photos/natethemagi/8674074864/
- https://www.flickr.com/photos/cactusbeetroot/5876761252/
- https://www.flickr.com/photos/micronova/4518582224/