A Tour of PostgREST

March 20, 2016

Web frameworks commonly treat the database as a dumb store, using only the minimal features common among several databases. What would it be like to design a RESTful API server that takes full advantage of Postgres in particular? In this talk I demonstrate PostgREST, a server which does just that and turns any PostgreSQL database into an API.

A Tour of PostgREST


  • Taking the database seriously
  • The traditional web stack
    • What’s in it
    • Three problems: boilerplate, no single soure of truth, hierarchy
  • PostgREST demo
    • Using the Pagila demo database
    • Installing and running PostgREST
    • Inspecting schema for endpoint
    • Retrieving its data
    • Limiting, paginating
    • Row filtering
    • Column filtering
    • Casting values
    • Similarities with GraphQL
    • Embedding data through foreign keys
    • Changing content types for upload, download
    • Creating records
      • Retrieving created record without new request
      • Filtering columns
      • Bulk inserts
    • Calling stored procedures
  • PostgREST security
    • Authentication
      • JWT
    • Authorization
      • The basic roles
    • Demo of logging in and making requests
  • Calling external services
    • LISTEN/NOTIFY demo
    • Discussion of using real queue systems
  • API versioning
    • How to encapsulate an internal schema from the world
    • Using database versions
    • Routing with NGINX
  • How does PostgREST work inside?
    • An investigation of the generated SQL
    • Generating JSON and CSV directly from the DB
  • Q&A
    • Performance
    • Connection pooling?
    • Detecting relations w/o foreign keys
    • Hypermedia and “real rest”
    • Techniques to speed up query - removing CTE, moving LIMIT clause