From SQL to a PostgREST call
supabase-js does not speak SQL. Every .from().select().eq() chain is assembled into a URL and sent to PostgREST, the HTTP layer in front of your Postgres database, and PostgREST's query grammar is deliberately smaller than SQL. That gap is where most Supabase questions live: the query is easy to say in SQL and the client syntax for it is not obvious, or does not exist at all.
This generator works from the SQL side, because that is the side you already know. Paste a statement, and it parses it the way Postgres would (quoted identifiers, case folding, '' escapes in strings, comments) and emits the equivalent supabase-js v2 call. Switch the target and the same statement comes out as supabase-py for Python or as a plain curl against the REST API, which is worth reading at least once: seeing ?status=eq.active&order=created_at.desc demystifies what the client library actually does.
The part we care most about is what happens when the SQL does not fit. A GROUP BY, a subquery, a window function or a CROSS JOIN has no PostgREST form, and a converter that silently drops the clause would hand you a query that runs and returns the wrong rows. Here the output switches to the RPC route instead: a create function statement wrapping your original SQL, ready for the SQL editor, plus the one-line .rpc() call. Parsing and generation run as JavaScript in your tab, so table names, schema details and any literal values in your WHERE clause stay on your machine.
How to use this generator
- Paste a statement. SELECT, INSERT, UPDATE and DELETE are supported, in Postgres syntax. The code appears while you type; parse errors point at the exact line and column. No query at hand? The
try:chips under the input hold one starter per statement type, including one that runs into the RPC fallback on purpose. - Pick a target.
supabase-js(default),pythonfor supabase-py, orcurlfor the raw PostgREST request with apikey and Authorization headers already in place. - Read the notes. Under the code, lines marked
!need action before running (a missing WHERE on a DELETE, a placeholder to fill in) and lines markediare background: the foreign key an embed depends on, or how an embed's shape differs from the SQL join.
Habits from MySQL do not produce a shrug: backtick identifiers, the LIMIT 20, 40 form and double-quoted strings each get an error that names the Postgres way to write it.
--setup
Prepends the client boilerplate as its own block with its own copy button: the createClient lines for supabase-js and supabase-py, the two export lines for curl. For the first query in a new project, the output is then complete rather than assuming a supabase variable from somewhere.
--explain
Annotates every generated line with the SQL clause it came from (// WHERE status = 'active'). Useful while the PostgREST method names are still new, or when the output goes into a code review where not everyone reads filter syntax.
--maybe-single
Turns LIMIT 1 into .maybeSingle(), so you get one object or null instead of a one-element array. Off by default because it also changes error behaviour: two matching rows become an error rather than a truncated list.
A few conveniences fire automatically, without a flag. $1-style placeholders become variables named after their column, so a parameterized query from your codebase pastes straight in. now() becomes new Date().toISOString(), computed client-side. And SELECT COUNT(*) becomes a head request that transfers no rows at all.
How SQL maps to supabase-js
| SQL | supabase-js | REST URL |
|---|---|---|
WHERE status = 'active' | .eq('status', 'active') | status=eq.active |
WHERE price != 0 | .neq('price', 0) | price=neq.0 |
WHERE total >= 100 | .gte('total', 100) | total=gte.100 |
WHERE name ILIKE 'ada%' | .ilike('name', 'ada%') | name=ilike.ada* |
WHERE role IN ('a', 'b') | .in('role', ['a', 'b']) | role=in.(a,b) |
WHERE deleted_at IS NULL | .is('deleted_at', null) | deleted_at=is.null |
WHERE x BETWEEN 1 AND 9 | .gte('x', 1).lte('x', 9) | x=gte.1&x=lte.9 |
WHERE a = 1 OR b = 2 | .or('a.eq.1,b.eq.2') | or=(a.eq.1,b.eq.2) |
ORDER BY x DESC NULLS LAST | .order('x', { ascending: false, nullsFirst: false }) | order=x.desc.nullslast |
LIMIT 25 OFFSET 50 | .range(50, 74) | limit=25&offset=50 |
JOIN orders ON … | .select('*, orders!inner(*)') | select=*,orders!inner(*) |
Chained filters always combine with AND, which is exactly how a WHERE clause with ANDs reads, so the generated chain keeps your conditions in order. The full operator vocabulary is in PostgREST's tables and views reference; the subset above covers what SQL statements actually contain. Note the wildcard difference in the URL column: PostgREST uses * where SQL uses %, because a bare percent sign starts an escape sequence in a URL. The client libraries accept % and translate.
Negations use the not. prefix rather than separate methods: NOT LIKE becomes .not('name', 'like', 'test%'), NOT IN becomes .not('role', 'in', '(bot,spam)') with the value list in PostgREST's parenthesised form, and IS NOT NULL becomes .not('deleted_at', 'is', null).
JOINs become embedded resources
PostgREST has no ON clause. Related tables are pulled in by naming them inside select(), and the server figures out the join condition from the foreign key between the two tables. This is the biggest mental shift coming from SQL, and it has three consequences worth spelling out.
The foreign key is the join condition. select('username, orders(total)') only works because a constraint links orders.user_id to users.id. No foreign key, no embed: you get PGRST200, "could not find a relationship". The generator reads your ON clause and names the exact constraint the embed depends on, so you can check it exists before anything runs. When two foreign keys connect the same tables (an order with user_id and billed_user_id, say), the embed is ambiguous and must name the constraint: orders!orders_user_id_fkey(total).
JOIN and LEFT JOIN differ in one modifier. A plain embed keeps every parent row, like a LEFT JOIN; !inner drops parents without a match, like an inner join. Your SQL already states which one you meant, so the generator emits !inner exactly when you wrote JOIN and leaves it off for LEFT JOIN. Related trap: a filter on an embedded column (.eq('orders.status', 'paid')) trims the nested rows, not the parent list, unless the embed is !inner. That single missing modifier is behind a large share of "my filter does nothing" questions.
The result shape is nested, not flat. SQL returns one flat row per user-order pair, repeating the user columns; an embed returns each user once with an orders array inside. Usually the nested shape is what application code wanted anyway, but it is a difference, not an equivalence, and the notes under the output say so rather than pretending the translation is lossless.
OR groups and the .or() string
OR is where the fluent chain ends. Every chained filter ANDs with the previous ones, so WHERE plan = 'pro' OR credits > 100 cannot be written as two method calls; it becomes one .or() carrying the whole group in PostgREST filter syntax:
.or('plan.eq.pro,credits.gt.100')
.or('status.eq.new,and(status.eq.open,priority.eq.high)')The syntax is compact and easy to get almost right. Commas separate the OR branches, and(…) and or(…) nest, and each branch is column.operator.value with no spaces around the dots. The failure mode is values: a string containing a comma, parenthesis or space changes the meaning of the filter unless it is wrapped in double quotes inside the string, which is why the generator quotes note.eq."a, (b)" for you. Mixed AND/OR nesting from your WHERE clause, including parenthesised groups and NOT BETWEEN, is restructured into the string automatically, which is exactly the transformation that goes wrong when done by hand at 6 pm.
One structural limit remains: a single .or() belongs to one table. A group that mixes a parent column with an embedded column has no URL form (an .or() on the embed exists, via the referencedTable option, but it filters the embed). When your SQL contains such a group, the generator says so and routes the query through an RPC instead of quietly narrowing it.
When the answer is an RPC
A real slice of everyday SQL has no PostgREST translation at all: GROUP BY and HAVING, aggregates beyond a bare count, DISTINCT, window functions, CTEs, subqueries, set operations, RIGHT and CROSS joins, expressions in SET, comparisons between two columns. Supabase's answer for all of them is the same: put the SQL into a Postgres function and call it with .rpc(). The function runs inside the database, where the full language is available, and PostgREST exposes it at /rest/v1/rpc/name.
The annoying part is normally the function signature, because returns table (…) wants every output column typed by hand. The generated wrapper sidesteps that:
create or replace function orders_query()
returns setof json
language sql stable
as $$
select to_json(q)
from (
-- your query, unchanged
) q
$$;Each row comes back as a JSON object with the column names as keys, which is the same shape supabase-js hands you anyway, so the calling code does not care that a function is involved. Two cautions belong next to this pattern. Functions run with the caller's permissions by default; resist security definer until you have understood that it bypasses the caller's RLS. And take parameters as function arguments (.rpc('orders_query', { min_total: 100 })) rather than concatenating values into the SQL, otherwise the function reintroduces the injection risk the API had solved.
For a query your app runs constantly, a view is the calmer alternative: create it once and the query builder treats it like a table, filters, embeds and all. View for reusable read shapes, RPC for anything parameterized or write-heavy.
LIMIT, OFFSET and counting
LIMIT 25 OFFSET 50 becomes .range(50, 74), and the endpoints being inclusive is a detail that produces real off-by-one bugs: .range(0, 25) is 26 rows. The generator does that arithmetic from your SQL, which is a small thing that removes a recurring mistake. Two server-side defaults matter here. Supabase caps any single response at 1000 rows unless configured otherwise, so an uncapped SELECT does not actually return the whole table, and deep OFFSETs get slower linearly because Postgres reads and discards the skipped rows; past a few thousand, keyset pagination (.order('id').gt('id', lastId)) keeps response times flat.
SELECT COUNT(*) gets a translation many hand-written clients miss: .select('*', { count: 'exact', head: true }) performs a HEAD request, so the count arrives in the Content-Range header and zero row data is transferred. For a count alongside the rows, drop head and read count next to data. On big tables count: 'planned' or 'estimated' trade exactness for speed by asking the query planner instead of scanning; a pager that shows "about 12,000 results" rarely needs more.
Related tools on this site: the SQL formatter makes a query readable before you translate it, and the CSV to SQL INSERT generator covers the step before this one, when the data is still in a spreadsheet export.
Where PostgREST stops
How do I do a JOIN in Supabase without writing SQL?
You embed the related table inside select(): supabase.from("users").select("username, orders(total)") returns each user with their orders nested, provided a foreign key links orders.user_id to users.id, because PostgREST discovers the relationship through the foreign key rather than through an ON clause. Plain embedding behaves like a LEFT JOIN (parents without a match stay, with an empty array); appending !inner, as in orders!inner(total), behaves like an INNER JOIN and drops parents without a match. If two foreign keys connect the same pair of tables, PostgREST answers with error PGRST201 and you disambiguate with the constraint name: orders!orders_billing_user_id_fkey(total).
How do I write OR conditions in a Supabase query?
With the .or() method, which takes one string in PostgREST filter syntax: .or("plan.eq.pro,credits.gt.100") means plan = 'pro' OR credits > 100. Each element is column.operator.value, commas mean OR, and nesting works with and(…) inside the string: .or("status.eq.new,and(status.eq.open,priority.eq.high)"). Two things break it in practice: values containing commas, parentheses or spaces must be wrapped in double quotes inside the string, and the whole .or() applies to one table only, so a condition on an embedded table needs its own .or() with the referencedTable option. Chained methods like .eq().gt() always combine with AND; .or() is the only way to get OR.
Why does filtering on a joined table still return all the parent rows?
Because a filter on an embedded resource trims the embedded rows, not the parent list: .select("*, orders(*)").eq("orders.status", "paid") returns every user, each with only their paid orders, and users without paid orders arrive with an empty orders array. To drop the parents too, mark the embed !inner: .select("*, orders!inner(*)").eq("orders.status", "paid") behaves like an SQL inner join plus WHERE. This is one of the most common Supabase surprises, and the reason a generated query uses !inner exactly when your SQL said JOIN rather than LEFT JOIN.
Why does my Supabase query return an empty array even though the rows exist?
Almost always row level security. When RLS is enabled on a table and no policy grants SELECT to the role you are calling with, PostgREST returns 200 with an empty array, not an error, so the query looks correct while the policy silently filters out every row. Check by running the same query in the SQL editor (which bypasses RLS as postgres) and comparing; if the editor sees rows and the API does not, you are missing a policy like create policy "read own rows" on orders for select using (auth.uid() = user_id). The anon key also only sees what anon policies allow, logged-in users need policies for the authenticated role.
How do I use GROUP BY or aggregates like SUM in Supabase?
Not through the query builder: PostgREST's URL grammar has no GROUP BY, and on Supabase the aggregate functions PostgREST 12 introduced (sum, avg, max, min, count inside select) are disabled by default because an unindexed aggregate over a big table is an easy denial-of-service. The reliable routes are a view (create view sales_by_status as select status, sum(total) …, then query the view like a table) or a function called via .rpc(). Wrapping the query in a function that returns setof json spares you writing out a returns table (…) signature; this generator produces exactly that wrapper when it sees GROUP BY.
Can I run raw SQL from supabase-js?
No, supabase-js has no method that accepts an SQL string, by design: the client talks to PostgREST, which only exposes tables, views and functions. Your options are to express the query in the builder, to create a view for it, or to wrap it in a Postgres function and call it with supabase.rpc("fn_name", args). From a trusted server environment you can also skip the API entirely and connect with a normal Postgres driver through the connection pooler; that is a direct database connection with your service credentials, not something to ship in a browser or app bundle.
What is the difference between .single() and .maybeSingle() in supabase-js?
.single() demands exactly one row and returns an error (code PGRST116, "JSON object requested, multiple (or no) rows returned") when the query yields zero or several. .maybeSingle() allows zero and gives you data: null in that case, which is what a lookup by unique key usually wants. Neither adds LIMIT 1 semantics to a broad query: if two rows match, both fail the same way, so keep the filter itself unique. Data-shape-wise both replace the usual array with a single object.
How do I paginate results in Supabase?
With .range(from, to), where both ends are inclusive and zero-based: page three of 25-row pages is .range(50, 74), which maps to LIMIT 25 OFFSET 50. For a total, request it in the same call with .select("*", { count: "exact" }) and read count next to data; "planned" and "estimated" are cheaper variants that read the query planner's guess, good enough for a page indicator on large tables. Offset pagination degrades on deep pages because Postgres still scans the skipped rows, so past a few thousand rows switch to keyset pagination: order by a unique column and filter .gt("id", lastSeenId) instead of increasing the offset. Also relevant: Supabase caps a single response at 1000 rows by default (the max-rows setting), so a query without a limit does not actually return everything.
Is a Supabase query built from user input open to SQL injection?
Classic SQL injection, no: filter values travel as URL parameters and PostgREST binds them as parameters, so a value like '; drop table users;-- is compared as a literal string and matches nothing. The two real risks sit elsewhere. Interpolating user input into a .or() string lets the user inject filter syntax (a crafted value with commas and dots can widen the condition), so validate or avoid string-built filters from input. And a Postgres function you call via .rpc() executes whatever SQL you wrote inside it; if that function concatenates its arguments into dynamic SQL with execute, you have reinvented injection behind an API. RLS is the safety net either way: a query can only ever see what policies allow.
What is the difference between like and ilike in a PostgREST filter?
Case: like is case-sensitive, ilike is not, exactly as in Postgres. In supabase-js you write the pattern with % as usual (.ilike("email", "%@gmail.com")); in a raw PostgREST URL the wildcard is *, because % starts an URL escape sequence, so the same filter reads email=ilike.*@gmail.com. Two practical notes: a pattern with a leading wildcard cannot use a normal b-tree index, which makes %term% scans slow on big tables unless you add a trigram index (create extension pg_trgm), and for a plain equality check eq beats like on both clarity and the planner.
How do I upsert rows in Supabase?
.upsert() is INSERT … ON CONFLICT: supabase.from("users").upsert({ email: "a@x.com", plan: "pro" }, { onConflict: "email" }) inserts the row or, when a row with the same email exists, overwrites it with the incoming values. onConflict must name a column (or comma-separated columns) with a unique or primary-key constraint, otherwise Postgres answers 42P10 "no unique or exclusion constraint matching the ON CONFLICT specification". ignoreDuplicates: true turns it into DO NOTHING. The limitation compared to SQL: ON CONFLICT DO UPDATE SET with its own expressions (increment a counter, keep the older date) has no upsert equivalent, that variant needs a function.