PostgREST: Your Database IS the API

Complete working examples: All code from this article is available at https://github.com/peterkracik/postgrest-example

The Problem We Keep Solving

Picture this: You’re starting a new project. PostgreSQL database? Check. Schema designed with foreign keys, constraints, indexes? Check. Now comes the part where you spend the next two weeks writing REST endpoints — controllers, routes, serializers, pagination, filtering, sorting, error handling, documentation…

What if that entire two weeks could become two hours?

Enter PostgREST — a standalone web server that transforms your PostgreSQL database directly into a RESTful API. Your database schema becomes your API specification. No controllers. No serializers. No boilerplate.

# Your database table…
CREATE TABLE products (
 id SERIAL PRIMARY KEY,
 name TEXT NOT NULL,
 price NUMERIC,
 in_stock BOOLEAN DEFAULT true
);
# …instantly becomes this API:
GET /products # List all products
GET /products?id=eq.5 # Get product with id=5
POST /products # Create a new product
PATCH /products?id=eq.5 # Update product
DELETE /products?id=eq.5 # Delete product

No backend code. Your database already knows everything — tables, relationships, constraints, permissions. PostgREST just exposes it through HTTP.

What is PostgREST?

PostgREST is a standalone web server that sits between your PostgreSQL database and HTTP clients. It automatically generates a REST API from your database schema, with built-in support for:

• CRUD operations on all tables

• Complex filtering via URL parameters

• Nested resources (automatic joins)

• Pagination and sorting

• Authentication via JWT

• Authorization via PostgreSQL’s Row-Level Security

• OpenAPI documentation (auto-generated)

Created by Joe Nelson in 2013 and maintained for over 10 years, PostgREST is battle-tested in production at thousands of companies. It’s not a prototype — it’s a mature tool that powers platforms serving millions of users.

How It Works: The Dual API Pattern

Here’s the key insight: PostgREST doesn’t have to replace your entire API. Most successful implementations use a hybrid approach:

┌─────────────────────────────────┐
│ Your Application                │
└────────┬──────────────┬─────────┘
         │              │
     PostgREST     Custom API
     (~80% ops)    (~20% ops)
         │              │
 ┌───────▼──────────────▼─────────┐
 │          PostgreSQL            │
 └────────────────────────────────┘

PostgREST handles:

• Product catalog queries

• Order history

• User data reads

• Admin dashboard CRUD

• Any standard data operations

Custom API handles:

• Payment processing

• Email notifications

• External service integrations

• Complex multi-step workflows

They’re not competing — they’re complementary. Your custom API also talks to PostgreSQL, but handles business logic that doesn’t belong in the database.

Query Power: Beyond Basic CRUD

Let’s see what makes PostgREST special. Start with a simple e-commerce schema:

CREATE TABLE products (
 id SERIAL PRIMARY KEY,
 sku TEXT UNIQUE,
 product_name TEXT NOT NULL,
 brand TEXT,
 price NUMERIC(10,2),
 quantity_in_stock INT,
 is_featured BOOLEAN DEFAULT false
);

> See complete schema: database/init.sql

Basic Queries

# Get all products
curl http://localhost:3000/products
# Filter by brand
curl "http://localhost:3000/products?brand=eq.TechCorp"
# Range query (products $100-$500)
curl "http://localhost:3000/products?price=gte.100&price=lte.500"
# Pattern matching (find 'wireless' products)
curl "http://localhost:3000/products?product_name=ilike.*wireless*"
# Complex conditions (featured OR low stock)
curl "http://localhost:3000/products?or=(is_featured.eq.true,quantity_in_stock.lt.5)"

Nested Resources

Add related tables:

CREATE TABLE orders (
 id SERIAL PRIMARY KEY,
 order_number TEXT UNIQUE,
 customer_name TEXT,
 status TEXT,
 total_amount NUMERIC(10,2)
);
CREATE TABLE order_items (
 id SERIAL PRIMARY KEY,
 order_id INT REFERENCES orders(id),
 product_name TEXT,
 quantity INT,
 unit_price NUMERIC(10,2)
);

Now query with automatic joins:

# Get orders with items in a single request
curl "http://localhost:3000/orders?select=*,items:order_items(*)"
# With filtering
curl "http://localhost:3000/orders?\
select=*,items:order_items(*)&\
status=eq.pending"

One HTTP request. One database query. Zero N+1 problems.

More examples: examples/01-basic-queries.sh through examples/04-authentication.sh

Getting Started in 10 Minutes

Clone the example repository and start the stack:

git clone https://github.com/peterkracik/postgrest-example
cd postgrest-example
docker-compose up -d

That’s it. Your API is live at http://localhost:3000.

What’s included:

• PostgreSQL with sample e-commerce data

• PostgREST configured with JWT authentication

• Pre-configured roles (anonymous, customer, admin)

• Row-Level Security policies

• OpenAPI documentation at `http://localhost:3000/`

Test it immediately:

# View products (public access)
curl http://localhost:3000/products
# View OpenAPI docs
open http://localhost:3000/

> Full setup guide at README.md

Security: Authentication & Authorization

This is where PostgREST shines. Instead of writing authentication middleware in your application, you leverage PostgreSQL’s built-in security.

Role-Based Access

Define roles in PostgreSQL:

- Anonymous role (public access)
CREATE ROLE web_anon NOLOGIN;
GRANT SELECT ON products TO web_anon;
 - Customer role (authenticated users)
CREATE ROLE customer NOLOGIN;
GRANT SELECT, INSERT, UPDATE ON orders TO customer;
 - Admin role (full access)
CREATE ROLE admin NOLOGIN;
GRANT ALL ON ALL TABLES TO admin;

Complete role setup: database/init.sql#L19-L47

JWT Authentication

PostgREST uses JWT tokens to switch PostgreSQL roles:

# Generate a test JWT
cd jwt-generator
node generate-token.js admin
# Use it in requests
curl "http://localhost:3000/orders" \
 -H "Authorization: Bearer eyJhbGc…"

JWT generator: jwt-generator

Row-Level Security

The real magic: multi-tenant data isolation at the database level.

- Enable RLS on orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
 - Customers see only their own orders
CREATE POLICY customer_own_orders ON orders
 FOR SELECT TO customer
 USING (
 vendor_id = current_setting('request.jwt.claims')::json->>'vendor_id'
 );
 - Admins see everything
CREATE POLICY admin_all_orders ON orders
 FOR ALL TO admin
 USING (true);

Now, when a customer queries:

curl "http://localhost:3000/orders" \
 -H "Authorization: Bearer <customer-jwt>"

PostgreSQL automatically filters to only their orders. The customer can’t forget to add the filter — it’s impossible to bypass. Security is enforced at the database level.

Admin with the same query? Sees all orders. Different role, different permissions.

Full RLS examples: database/init.sql#L193-L224

TypeScript Client (Optional)

While curl is perfect for testing, production apps typically use a client library:

import { PostgrestClient } from '@supabase/postgrest-js'
const client = new PostgrestClient('http://localhost:3000')
// Set auth token
client.headers = { 'Authorization': `Bearer ${token}` }
// Fetch orders with nested items
const { data, error } = await client
 .from('orders')
 .select(`
 *,
 items:order_items(*)
 `)
 .eq('status', 'pending')
 .order('order_date', { ascending: false })

The client is just syntactic sugar over HTTP requests. Everything you can do with curl, you can do with the TypeScript client.

> Full TypeScript examples: client-typescript

When to Use PostgREST

✅ Perfect For:

Admin Dashboards:

• React Admin + PostgREST = instant admin UI

• Zero backend code for CRUD operations

• Auto-generated forms from the database schema

Internal Tools:

• Employee management portals

• Inventory systems

• Configuration interfaces

• Reporting dashboards

Multi-Tenant SaaS:

• Row-Level Security handles tenant isolation

• No application-level filtering code

• Security bugs become impossible

Rapid Prototypes:

• MVP in hours, not weeks

• Database schema = API specification

• Add custom API endpoints later as needed

❌ Not Ideal For:

Complex Business Logic:

• Payment processing workflows

• Multi-step orchestrations

• Heavy data transformations

• Extensive external API calls

Real-Time Requirements:

• WebSocket connections

• Live collaborative editing

• Chat applications (Note: Supabase adds realtime on top of PostgREST)

Non-PostgreSQL Databases:

• PostgREST only works with PostgreSQL

• MySQL, MongoDB, SQL Server need different solutions

Teams Unfamiliar with SQL:

• Requires PostgreSQL expertise

• Understanding of RLS and database security

• Comfort with SQL performance tuning

The Hybrid Approach (Recommended)

Most successful implementations use PostgREST for ~80% of endpoints:

┌─────────────────────────────────────────────────────┐
│              Your Application                       │
└────────────────────┬────────────────────────────────┘
                     │
 ┌───────────────────▼────────────────────────────────┐
 │             Which API layer?                       │
 └───────────┬─────────────────────┬──────────────────┘
             │                     │
            Simple CRUD Complex Logic
             │                     │
 ┌───────────▼──────┐ ┌────────────▼──────────┐
 │       PostgREST  │ │       Custom API      │
 │       (No code)  │ │      (Express/        │
 │                  │ │           NestJS).    │
 └───────────┬──────┘ └────────────┬──────────┘
             │                     │
 ┌───────────▼─────────────────────▼──────────┐
 │                 PostgreSQL                 │
 └────────────────────────────────────────────┘

Decision Matrix:

| Operation                     | Use PostgREST  | Use Custom API  |
|- - - - - - - - - - - - - - - -| - - - - - - - -| - - - - - - - - |
| List products with filters.   |       ✅       |      ❌         |
| Get order history             |       ✅       |      ❌         |
| Update user profile.          |       ✅       |      ❌         |
| Process payment               |       ❌       |      ✅         |
| Send order confirmation email |       ❌       |      ✅         |
| Call external shipping API    |       ❌       |      ✅         |
| Generate PDF invoice          |       ❌       |      ✅         |

Best of both worlds: Speed where you need it (PostgREST), flexibility where it matters (custom API).

PostgREST in the Wild

While PostgREST is powerful on its own, it’s worth noting how major platforms use it:

Supabase

Supabase built their entire platform on PostgREST, handling billions of requests per month. They added:

• Realtime subscriptions (via PostgreSQL LISTEN/NOTIFY)

• Auto-generated TypeScript types

• Integrated authentication

• File storage

Every Supabase project gets a PostgREST API automatically. It’s proof that PostgREST scales to production at massive volume.

Neon

Neon built a PostgREST-compatible API in Rust for their serverless Postgres. The fact that they chose PostgREST’s API design (rather than inventing their own) shows how well-designed the protocol is.

Self-Hosted

Many enterprises run PostgREST with their existing PostgreSQL infrastructure:

• No vendor lock-in

• Full control over deployment

• Can use with AWS RDS, Google Cloud SQL, or self-hosted Postgres

• No per-request pricing

The point? PostgREST is production-ready, whether you use it standalone or via a platform.

What Problems Does PostgREST Solve?

1. Eliminates Boilerplate

Every REST API starts the same way: controllers, routes, serializers, pagination, and filtering. PostgREST eliminates this entirely. Your database migration **is** your API deployment.

2. Complex Querying Without Custom Endpoints

Traditional APIs force you to predict every query pattern. PostgREST gives your frontend SQL-like power through URL parameters. The frontend specifies exactly what it needs — no custom endpoints required.

3. N+1 Problem Solved

Nested resource embedding uses PostgreSQL’s query planner. One HTTP request, one database query. The N+1 problem doesn’t exist.

4. Security at the Source

Authorization bugs happen when you forget to check permissions in one endpoint. With PostgreSQL RLS, the database enforces security — forgetting is impossible.

5. Self-Documenting

OpenAPI documentation auto-generated from your schema. Change your schema? Docs update automatically.

What PostgREST Does NOT Solve

Be honest about limitations:

• ❌ Complex business logic (use custom API)

• ❌ Custom response formats (use views or custom API)

• ❌ Real-time WebSockets (need additional layer)

• ❌ Non-PostgreSQL databases (only works with Postgres)

• ❌ Learning curve (requires PostgreSQL knowledge)

Solution: Use the hybrid approach. PostgREST for data, custom API for logic.

Try It Now

Three ways to start:

1. Self-Hosted (15 minutes)

git clone https://github.com/peterkracik/postgrest-example
cd postgrest-example
docker-compose up -d
curl http://localhost:3000/products

Includes:

• Complete e-commerce schema

• JWT authentication

• Row-Level Security examples

• Bash query examples

• TypeScript client examples

2. Supabase (5 minutes)

• Create a free project at supabase.com

• Instant PostgREST API with real-time features

• Managed hosting, auto-scaling

3. Neon (10 minutes)

• Sign up at neon.tech

• PostgREST-compatible API

• Serverless Postgres with branching

All three give you the same core experience: your database becomes an API.

Conclusion

PostgREST changes the game for API development. By transforming your PostgreSQL database directly into a REST API, it eliminates weeks of boilerplate, reduces bugs, and provides powerful features like automatic documentation, complex querying, and database-level security.

The paradigm shift: Stop writing code for what your database already knows. Your schema defines structure, constraints, and relationships. PostgreSQL enforces security and optimizes queries. Why duplicate all of that in application code?

Is it a silver bullet? No. PostgREST isn’t for complex business logic or real-time WebSockets. But for the 80% of your API that’s pure data access? It’s transformative.

The hybrid approach works: Use PostgREST for data operations, use a custom API for business logic. Best of both worlds.

It’s production-ready: Battle-tested for 10+ years, powering platforms that serve millions of users. The question isn’t whether PostgREST can handle production — it’s whether your use case fits its strengths.

Resources

• Complete Working Example: github.com/peterkracik/postgrest-example

• PostgREST Documentation: docs.postgrest.org

• PostgreSQL RLS Guide: postgresql.org/docs/current/ddl-rowsecurity.html

web: https://kracik.sk
X: https://x.com/peterkracik
LinkedIn: https://www.linkedin.com/in/peterkracik/