PostgREST doesn’t just serve your data; it lets you query it with incredible flexibility using standard URL parameters, turning your database tables into RESTful resources.
Let’s see this in action. Imagine you have a products table with columns like id, name, price, and category_id. You can fetch all products with a simple GET request to /products.
curl "https://your-supabase-url.supabase.co/rest/v1/products" \
-H "apiKey: YOUR_ANON_KEY"
But what if you only want products in the "Electronics" category? PostgREST translates URL query parameters into SQL WHERE clauses. For categories, you’d likely have a categories table and a foreign key category_id in your products table. To filter by category name, you’d join the tables implicitly.
curl "https://your-supabase-url.supabase.co/rest/v1/products?category_id=eq.1" \
-H "apiKey: YOUR_ANON_KEY"
Here, category_id=eq.1 tells PostgREST: "Filter the products table where the category_id column is equal to 1." The eq is an operator. PostgREST supports a rich set of operators: eq (equal), neq (not equal), lt (less than), lte (less than or equal), gt (greater than), gte (greater than or equal), like (SQL LIKE), ilike (case-insensitive LIKE), is (SQL IS NULL/TRUE/FALSE), in (SQL IN), cs (contains, for arrays), cd (contained by, for arrays), ov (overlaps, for arrays), pl (precedes, for ranges), and sl (succeeds, for ranges).
You can also filter on related tables. If you want electronics products and you know the id for the "Electronics" category is 1, you’d use the above. If you don’t know the ID but know the name, you can use a foreign relationship filter. PostgREST automatically exposes relationships.
curl "https://your-supabase-url.supabase.co/rest/v1/products?categories.name=eq.Electronics" \
-H "apiKey: YOUR_ANON_KEY"
This asks for products where the related categories table has a name that equals "Electronics". PostgREST performs the necessary JOIN behind the scenes.
Beyond simple equality, you can combine filters. To get "Electronics" products priced below $500:
curl "https://your-supabase-url.supabase.co/rest/v1/products?categories.name=eq.Electronics&price=lt.500" \
-H "apiKey: YOUR_ANON_KEY"
This is equivalent to SELECT * FROM products WHERE price < 500 AND category_id = (SELECT id FROM categories WHERE name = 'Electronics').
What if you want products whose names start with "Smart"?
curl "https://your-supabase-url.supabase.co/rest/v1/products?name=like.Smart%" \
-H "apiKey: YOUR_ANON_KEY"
Note the % wildcard. For case-insensitive matching, use ilike.
You can also select specific columns using the select parameter. To get only the id and name of electronics products under $500:
curl "https://your-supabase-url.supabase.co/rest/v1/products?categories.name=eq.Electronics&price=lt.500&select=id,name" \
-H "apiKey: YOUR_ANON_KEY"
This translates to SELECT id, name FROM products WHERE price < 500 AND category_id = (SELECT id FROM categories WHERE name = 'Electronics').
PostgREST can also handle ordering and pagination. To order products by price, descending, and get the first 10:
curl "https://your-supabase-url.supabase.co/rest/v1/products?order=price.desc&limit=10" \
-H "apiKey: YOUR_ANON_KEY"
The order parameter takes a column name and an optional asc (ascending) or desc (descending) modifier. limit sets the maximum number of rows. For deeper pagination, use offset.
The filtering mechanism is incredibly powerful because it maps directly to your database schema. You don’t need to write specific API endpoints for every conceivable query. Instead, PostgREST dynamically generates SQL based on the URL. This means that adding a new column to your products table automatically makes it available for filtering via the API, without any backend code changes. The select parameter, in particular, is crucial for performance as it prevents over-fetching data, ensuring only the necessary columns are retrieved from the database.
When you use operators like cs (contains) on array columns, PostgREST constructs SQL queries that leverage PostgreSQL’s native array functions, such as && for overlap or @> for containment. This means you can filter based on whether an array column includes specific values or if it’s entirely contained within another array, directly from your API.
You can also perform range queries with cs and cd for array columns, or use pl and sl for range columns. For instance, to find products with prices between $100 and $500:
curl "https://your-supabase-url.supabase.co/rest/v1/products?price=cs.{100,500}" \
-H "apiKey: YOUR_ANON_KEY"
This query is interpreted by PostgREST as SELECT * FROM products WHERE price <@ ARRAY[100, 500]::numrange.
The next step is often exploring how to handle complex logical operations like AND, OR, and NOT when combining filters.