PostgREST — Instant RESTful API from Any PostgreSQL Database

Introduction

PostgREST is a standalone Haskell web server that generates a RESTful API directly from your PostgreSQL database schema. Instead of writing boilerplate CRUD endpoints, you define your data model in Postgres and PostgREST serves it instantly. It leverages PostgreSQL's built-in role system for authentication and row-level security for authorization.

What PostgREST Does

• Serves any PostgreSQL table or view as a REST endpoint with full CRUD support
• Provides advanced filtering, ordering, pagination, and full-text search via URL query parameters
• Supports embedded resources through foreign key relationships for nested JSON responses
• Uses PostgreSQL roles and row-level security policies for fine-grained access control
• Exposes stored functions as RPC endpoints for custom business logic

Architecture Overview

PostgREST connects to PostgreSQL as a single connection-pooled process. It reads the database catalog at startup to discover tables, views, columns, relationships, and permissions. Incoming HTTP requests are translated into optimized SQL queries using the database's own planner. Authentication is handled via JWT tokens that map to PostgreSQL roles, so all authorization logic lives in the database itself through grants and RLS policies.

Self-Hosting & Configuration

• Requires PostgreSQL 9.6 or later; works best with PostgreSQL 12+ for full feature support
• Configuration via a simple key-value file or environment variables (db-uri, db-schemas, db-anon-role)
• Runs as a single static binary with no runtime dependencies
• Pair with connection poolers like PgBouncer for high-concurrency production deployments
• Integrate with nginx or Caddy as a reverse proxy for TLS termination and rate limiting

Key Features

• Zero-code API generation from existing PostgreSQL schemas
• Automatic OpenAPI (Swagger) documentation generation for every endpoint
• Resource embedding that resolves foreign key joins in a single request
• Upsert and bulk insert support with conflict resolution via ON CONFLICT
• Built-in content negotiation supporting JSON, CSV, and GeoJSON output formats

Comparison with Similar Tools

• Hasura — Full GraphQL engine with subscriptions; heavier runtime but richer real-time features
• pREST — Go-based REST API for Postgres; simpler but fewer query features than PostgREST
• Supabase — Wraps PostgREST with auth, storage, and a dashboard; managed platform layer on top
• Directus — Headless CMS with visual admin panel; database-agnostic but more opinionated

FAQ

Q: Does PostgREST support authentication? A: Yes. It validates JWT tokens and maps the token's role claim to a PostgreSQL role, so all permissions are enforced by the database.

Q: Can I use PostgREST with an existing database? A: Absolutely. Point it at any Postgres database and it will expose the schemas you specify. No migrations or code changes needed.

Q: How does it handle performance at scale? A: PostgREST generates efficient SQL and relies on PostgreSQL's query planner. It supports connection pooling and horizontal scaling behind a load balancer.

Q: Is it suitable for production use? A: Yes. PostgREST is used in production by many organizations and is the API engine behind Supabase.

Sources

• https://github.com/PostgREST/postgrest
• https://postgrest.org/en/stable/