DEV Community

Alex Spinov
Alex Spinov

Posted on

PostgREST Has a Free API That Turns Your PostgreSQL Into a REST Server

#postgrest #postgres #api #rest

PostgREST instantly turns any PostgreSQL database into a RESTful API. No backend code, no ORM — just point it at your database and get a full CRUD API.

Setup 

# Docker
docker run -p 3000:3000 \
  -e PGRST_DB_URI="postgres://user:pass@host:5432/mydb" \
  -e PGRST_DB_ANON_ROLE="web_anon" \
  postgrest/postgrest
Enter fullscreen mode Exit fullscreen mode

Instant API

Your database tables become REST endpoints:

# List records with filtering
curl "http://localhost:3000/products?price=lt.100&category=eq.electronics&order=price.asc&limit=20"

# Get single record
curl "http://localhost:3000/products?id=eq.1" -H "Accept: application/vnd.pgrst.object+json"

# Insert
curl -X POST http://localhost:3000/products \
  -H "Content-Type: application/json" \
  -d '{"name":"Widget","price":9.99,"category":"tools"}'

# Update
curl -X PATCH "http://localhost:3000/products?id=eq.1" \
  -d '{"price":12.99}'

# Delete
curl -X DELETE "http://localhost:3000/products?id=eq.1"
Enter fullscreen mode Exit fullscreen mode

Advanced Filtering 

# Full text search
curl "http://localhost:3000/articles?title=fts.machine+learning"

# JSON column queries
curl "http://localhost:3000/products?metadata->>'color'=eq.red"

# Array contains
curl "http://localhost:3000/posts?tags=cs.{javascript,api}"

# Range queries
curl "http://localhost:3000/events?date_range=ov.[2026-01-01,2026-12-31]"
Enter fullscreen mode Exit fullscreen mode

Relationships (Embedded) 

# Join related tables automatically
curl "http://localhost:3000/orders?select=id,total,customer:customers(name,email),items:order_items(product:products(name),quantity)"
Enter fullscreen mode Exit fullscreen mode

RPC (Stored Functions) 

CREATE FUNCTION search_products(query text) RETURNS SETOF products AS $$
  SELECT * FROM products WHERE name ILIKE '%' || query || '%'
$$ LANGUAGE sql;
Enter fullscreen mode Exit fullscreen mode 
curl -X POST "http://localhost:3000/rpc/search_products" \
  -d '{"query":"widget"}'
Enter fullscreen mode Exit fullscreen mode

Why This Matters

  • Zero backend code: API generated from database schema
  • PostgreSQL power: Full PG features — functions, views, triggers
  • Performance: Connection pooling, prepared statements
  • Security: Row-level security via PostgreSQL policies

Need custom database APIs or backend automation? I build developer tools. Check out my web scraping actors on Apify or reach out at spinov001@gmail.com for custom solutions.

Top comments (0)