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.

Warning, outdated PostgREST continues to advance and some of the details in this video are no longer accurate. Please see the latest documentation for current information.

Download Video: (HD / SD).

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



Related Posts