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

Summary

• 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