PostgREST takes a different approach to building REST APIs: instead of writing application code to expose your database, it pushes all API logic into PostgreSQL itself. This means the database is not only the data store but also the engine for serialization, validation, and authorization.
What postgrest does and how it works
PostgREST is a Haskell-based HTTP server that automatically generates a fully RESTful API directly from your existing PostgreSQL database schema. It listens for HTTP requests and translates them into SQL queries that PostgreSQL executes, then streams the results back as JSON.
Under the hood, it uses the Warp HTTP server for handling connections efficiently and the Hasql library to manage connection pooling and leverage PostgreSQL’s binary protocol for speed. This design lets PostgREST serve thousands of requests per second with subsecond latency, even on modest infrastructure like Heroku’s free tier.
The core principle is “database-first API design.” Instead of defining your API routes and logic in application code, you declare tables, views, stored procedures, and roles in PostgreSQL. PostgREST exposes these database objects as RESTful endpoints automatically.
Authentication is handled via JWT tokens, but authorization is pushed down to PostgreSQL roles and policies. This means your security rules live in the database, ensuring a single declarative source of truth for both data and access control.
API versioning is cleverly managed by using database schemas. Different versions of the API can live side-by-side in separate schemas, and PostgREST switches based on the request.
OpenAPI documentation is generated automatically from the database structure, reducing manual upkeep of API docs.
architectural and technical strengths of postgrest
PostgREST’s most distinguishing feature is its architectural decision to delegate nearly all computation and logic to SQL in PostgreSQL. JSON serialization, input validation, authorization checks, and even row counting happen inside the database.
This approach has several advantages:
- No ORM or application logic for CRUD: You avoid the complexity and potential bugs of syncing application code with the database.
- Single source of truth: Your tables, roles, and policies in the database define the API’s behavior and security.
- Performance: Using PostgreSQL’s binary protocol with Hasql and Warp’s efficient HTTP server enables subsecond responses at high throughput (up to 2000 requests/sec on free-tier Heroku).
- Declarative security: Role-based access control is enforced by PostgreSQL’s policies, making security explicit and centralized.
- Schema-based API versioning: Multiple API versions can coexist by leveraging separate database schemas.
The codebase is in Haskell, which is notable because Haskell’s strong static typing and purity can make for reliable and maintainable code, especially in a server context. The server’s core is relatively lean because it delegates most complexity to the database.
There are tradeoffs to this approach:
- SQL expertise required: Developers must be comfortable writing advanced SQL, managing roles, policies, and setting up schemas for API versioning.
- Less flexibility in complex business logic: If your API requires complex workflows or non-database integrations, pushing everything into SQL can get cumbersome.
- Haskell ecosystem: While Haskell gives robustness, it might have a smaller community and fewer libraries compared to mainstream web frameworks.
Overall, PostgREST is well-suited for teams that want a high-performance, declarative API layer directly on top of PostgreSQL and are willing to design their database schema and policies carefully.
explore the project structure and documentation
The repository’s README is comprehensive, covering architecture, usage, and configuration details. The project is primarily Haskell code focused on the server implementation.
The main entry point is the PostgREST executable, which you run with a configuration file or environment variables specifying your database connection and JWT secret.
Key components to explore:
- /src/: Contains the Haskell source code implementing the server logic.
- README.md: Offers detailed documentation on how to configure PostgREST, including authentication, role setup, and API customization.
- Examples and tests: Provide insight into expected database setups and usage patterns.
The quick start is minimal, showing only postgrest --help as a command, so the best way to get started is to read the documentation and set up a test PostgreSQL database with tables and policies.
verdict
PostgREST stands out by fully embracing the database as the heart of the API, pushing RESTful API logic into PostgreSQL with a Haskell server acting as a thin HTTP layer.
This design yields excellent performance and a clean separation of concerns but requires teams to be proficient in SQL and PostgreSQL’s role-based access control to get the most out of it.
It’s an excellent fit for projects that already use PostgreSQL extensively and want a declarative, high-throughput REST API without writing custom backend code.
If your application demands complex business logic outside the database or you prefer more traditional, application-layer API development, PostgREST might feel restrictive.
For those comfortable with SQL and looking for a robust, production-ready API server that minimizes middleware complexity, PostgREST is definitely worth a closer look.
Related Articles
- Supabase: composable open-source backend-as-a-service built around Postgres — Supabase combines specialized open-source tools around Postgres to offer a Firebase-like backend platform. Its modular a
- Jan: a local-first desktop app for large language models with Tauri and Rust — Jan is an open-source desktop app that runs large language models locally using Tauri, Node.js, and Rust. It offers priv
→ GitHub Repo: PostgREST/postgrest ⭐ 27,064 · Haskell