PostgREST serves a fully RESTful API from any existing PostgreSQL
database. It provides a cleaner, more standards-compliant, faster
API than you are likely to write from scratch.
Demo postgrest.herokuapp.com | Watch Video
Try making requests to the live demo server with an HTTP client
such as postman. The structure of the
demo database is defined by
You can use it as inspiration for test-driven server migrations in
your own projects.
postgrest --db-host localhost --db-port 5432 \ --db-name my_db --db-user postgres \ --db-pass foobar --db-pool 200 \ --anonymous postgres --secure \ --port 3000
TLDR; subsecond response times for up to 2000 requests/sec on Heroku free tier. (see the load test)
If you're used to servers written in interpreted languages (or named
after precious gems), prepare to be pleasantly surprised by PostgREST
Three factors contribute to the speed. First the server is written
in Haskell using the
HTTP server (aka a compiled language with lightweight threads).
Next it delegates as much calculation as possible to the database
- Serializing JSON responses directly in SQL
- Data validation
- Combined row counting and retrieval
- Data post in single command (
Finally it uses the database efficiently with the
- Reusing prepared statements
- Keeping a pool of db connections
- Using the Postgres binary protocol
- Being stateless to allow horizontal scaling
Ultimately the server (when load balanced) is constrained by database
performance. This may make it inappropriate for very large traffic
load. To learn more about scaling with Heroku and Amazon RDS see
the performance guide.
Other optimizations are possible, and some are outlined in the
PostgREST handles authentication (HTTP Basic over SSL) and delegates
authorization to the role information defined in the database. This
ensures there is a single declarative source of truth for security.
When dealing with the database the server assumes the identity of
the currently authenticated user, and for the duration of the
connection cannot do anything the user themselves couldn't.
Postgres 9.5 will soon support true row-level
In the meantime what isn't yet implemented can be simulated with
triggers and security-barrier views. Because the possible queries
to the database are limited to certain templates using
functions, the trigger workaround does not compromise row-level
For example security patterns see the security
A robust long-lived API needs the freedom to exist in multiple
versions. PostgREST supports versioning through HTTP content
negotiation. Requests for a certain version translate into switching
which database schema to search for tables. PostgreSQL schema search
paths allow tables from earlier versions to be reused verbatim in
To learn more, see the guide to versioning.
Rather than writing and maintaining separate docs yourself let the
API explain its own affordances using HTTP. All PostgREST endpoints
respond to the OPTIONS verb and explain what they support as well
as the data format of their JSON payload.
The number of rows returned by an endpoint is reported by - and
limited with - range headers. More about
There are more opportunities for self-documentation listed in Future
Rather than relying on an Object Relational Mapper and custom
imperative coding, this system requires you put declarative constraints
directly into your database. Hence no application can corrupt your
data (including your API server).
The PostgREST exposes HTTP interface with safeguards to prevent
surprises, such as enforcing idempotent PUT requests, and
- Watching endpoint changes with sockets and Postgres pubsub
- Specifying per-view HTTP caching
- Inferring good default caching policies from the Postgres stats collector
- Generating mock data for test clients
- Maintaining separate connection pools per role to avoid "set/reset
role" performance penalty
- Describe more relationships with Link headers
- Depending on accept headers, render OPTIONS as RAML or a
- Add two-legged auth with OAuth 1.0a(?)
- ... the other issues
Thanks to Adam Baker for code
contributions and many fundamental design discussions.