The Same Old Story
You’ve created a table in PostgreSQL. You’ve inserted a few rows of data. Now you want to display that data on your frontend.
The typical workflow looks like this:
Choose a backend framework (Node.js, Django, Spring Boot, …)
Integrate an ORM (optional)
Write CRUD endpoints
Add authentication and authorization
Deploy everything
Sometimes, just for the sake of quick prototyping, a needlessly complex backend is built, only to fetch a few rows of data. Ironically, this makes prototyping slower instead of faster. With PostgREST, you can expose a secure and production-ready REST API using only your PostgreSQL knowledge, without writing a single line of backend code.
What is PostgREST?
PostgREST is an open-source tool that instantly turns your PostgreSQL database into a RESTful API. Without any additional development effort, it exposes your database as an API endpoint.
Tables → Auto-generated endpoints
SQL functions → Custom actions
PostgreSQL roles → API authorization system
Installation
There’s nothing to install inside your PostgreSQL database. All you need to do is copy the PostgREST executable to your database server (or a separate server, depending on workload) and run it with a configuration file.
postgrest.conf:
# PostgreSQL connection info
db-uri = "postgres://user:password@localhost:5432/database_name"
# Port PostgREST will listen on
server-port = 3000
# Log levels: crit, error, warn, info, debug
log-level = "info"
# PostgreSQL connection info
db-uri = "postgres://user:password@localhost:5432/database_name"
# Port PostgREST will listen on
server-port = 3000
# Log levels: crit, error, warn, info, debug
log-level = "info"
# PostgreSQL connection info
db-uri = "postgres://user:password@localhost:5432/database_name"
# Port PostgREST will listen on
server-port = 3000
# Log levels: crit, error, warn, info, debug
log-level = "info"
Run:
Example: Customer Table
CREATE TABLE customer(
id serial primary key,
full_name text,
email text
)
CREATE TABLE customer(
id serial primary key,
full_name text,
email text
)
CREATE TABLE customer(
id serial primary key,
full_name text,
email text
)
API Examples
Get all customers
curl http://localhost:3000
curl http://localhost:3000
curl http://localhost:3000
Filtering
curl http://localhost:3000
curl http://localhost:3000
curl http://localhost:3000
Insert a new customer
curl -X POST http://localhost:3000/customer \
-H "Content-Type: application/json" \
-d '{"full_name": "Ayşe", "email": "[email protected]"}'curl -X POST http://localhost:3000/customer \
-H "Content-Type: application/json" \
-d '{"full_name": "Ayşe", "email": "[email protected]"}'curl -X POST http://localhost:3000/customer \
-H "Content-Type: application/json" \
-d '{"full_name": "Ayşe", "email": "[email protected]"}'Update a customer
curl -X PATCH "http://localhost:3000/customer?id=eq.1" \
-H "Content-Type: application/json" \
-d '{"full_name": "Mehmet", "email": "[email protected]"}'curl -X PATCH "http://localhost:3000/customer?id=eq.1" \
-H "Content-Type: application/json" \
-d '{"full_name": "Mehmet", "email": "[email protected]"}'curl -X PATCH "http://localhost:3000/customer?id=eq.1" \
-H "Content-Type: application/json" \
-d '{"full_name": "Mehmet", "email": "[email protected]"}'Upsert (insert or update)
curl -X POST "http://localhost:3000/customer" \
-H "Content-Type: application/json" \
-H "Prefer: resolution=merge-duplicates" \
-d '{"id": 1, "full_name": "Ahmet", "email": "[email protected]"}'curl -X POST "http://localhost:3000/customer" \
-H "Content-Type: application/json" \
-H "Prefer: resolution=merge-duplicates" \
-d '{"id": 1, "full_name": "Ahmet", "email": "[email protected]"}'curl -X POST "http://localhost:3000/customer" \
-H "Content-Type: application/json" \
-H "Prefer: resolution=merge-duplicates" \
-d '{"id": 1, "full_name": "Ahmet", "email": "[email protected]"}'Or:
curl -X PUT "http://localhost:3000/customer?id=eq.1" \
-H "Content-Type: application/json" \
-d '{"full_name": "Ahmet", "email": "[email protected]"}'curl -X PUT "http://localhost:3000/customer?id=eq.1" \
-H "Content-Type: application/json" \
-d '{"full_name": "Ahmet", "email": "[email protected]"}'curl -X PUT "http://localhost:3000/customer?id=eq.1" \
-H "Content-Type: application/json" \
-d '{"full_name": "Ahmet", "email": "[email protected]"}'Security
PostgREST offloads security management entirely to PostgreSQL.
anon role → public API access
JWT tokens → user authentication
Row-Level Security (RLS) → fine-grained access control
You can use these features to control who can see which data. Alternatively, you may place PostgREST behind NGINX to handle static token management or introduce your own middleware layer for more advanced authorization logic.
Use Cases
Prototyping → Skip backend setup and start frontend development immediately.
Dashboards / Admin panels → Out-of-the-box CRUD operations.
Microservices → Expose PostgreSQL-powered services as APIs.
Low-code / No-code solutions → Feed frontend apps directly from API.
Advantages
No need to write backend code
Leverages the full power of PostgreSQL
Fast prototyping with production-ready APIs
Security delegated to Postgres roles
Limitations
PostgreSQL dependency (if you know similar tools for other databases, share them!)
Complex business logic may need to be pushed into SQL functions (though PostgreSQL supports multiple languages for function development, which can be a strength).
Limited flexibility in API design customization.
Conclusion
If you’re already using PostgreSQL and often think, “I wish I could just expose this as an API without building a whole backend”, then PostgREST is the right tool for you.
