PostgREST API
Auto-generated REST API from your PostgreSQL schema. No backend code required.
Enabling PostgREST
PostgREST is enabled per PostgreSQL database server. To enable it:
- Navigate to Database Servers in the sh0 dashboard.
- Select an existing PostgreSQL server (or create one).
- Click Enable PostgREST in the server settings.
- sh0 deploys a PostgREST instance, connects it to your database, and provisions an SSL domain.
Configuration
After enabling PostgREST, you can configure the following settings from the dashboard:
Anonymous Role
The anon_role is the PostgreSQL role used for unauthenticated requests. By default, sh0 creates a role named anon with read-only access to the public schema.
Use PostgreSQL GRANT and REVOKE statements to control what unauthenticated users can access.
Exposed Schemas
By default, PostgREST exposes only the public schema. You can configure additional schemas to expose via the dashboard settings. Tables in non-exposed schemas are never accessible through the API.
pg_catalog or information_schema schemas. Only expose schemas that contain tables you want to be accessible via the API.Query Syntax
PostgREST uses query parameters to filter, sort, paginate, and shape the response. All endpoints follow the pattern:
GET https://<your-domain>/<table_name>?select=<columns>&<filters>
Filtering
Use column-level filters with operators:
| Operator | Meaning | Example |
|---|---|---|
| eq | Equal | ?status=eq.active |
| neq | Not equal | ?role=neq.admin |
| gt / gte | Greater than / or equal | ?age=gte.18 |
| lt / lte | Less than / or equal | ?price=lt.100 |
| like / ilike | Pattern match (case sensitive / insensitive) | ?name=ilike.*john* |
| in | In list | ?id=in.(1,2,3) |
| is | IS (null, true, false) | ?deleted_at=is.null |
Pagination
Use the Range header or limit and offset query parameters:
GET /users?limit=20&offset=40&order=created_at.desc
Resource Embedding (Joins)
PostgREST automatically detects foreign-key relationships and allows embedding related resources:
GET /orders?select=id,total,customer(name,email)
This returns each order with the related customer's name and email embedded in the response.
Authentication with JWT
PostgREST verifies JWT tokens passed in the Authorization header. When a valid JWT is provided, PostgREST switches to the role specified in the token's role claim and sets all claims as PostgreSQL session variables.
This integrates with PostgreSQL Row-Level Security (RLS) policies. For example, a policy that checks current_setting('request.jwt.claims')::json->>'sub' can restrict rows based on the authenticated user.
OpenAPI Specification
Every PostgREST instance exposes a live OpenAPI 3.0 specification at its root URL (GET /). Use this to:
- Import into Postman or Insomnia for testing.
- Generate typed client SDKs with openapi-generator.
- Feed into documentation tools like Swagger UI.