query patterns

16 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Query filters on function results with JavaScript SDK

When a function returns a table set, you can chain filter methods on the rpc() call. Example: `const { data, error } = supabase.rpc('get_planets').eq('id', 1)` applies an equality filter to the function results, equivalent to `select * from get_planets() where id = 1;`

Function with parameters using plpgsql example

Example of a function that accepts parameters and uses plpgsql language: ```sql create or replace function add_planet(name text) returns bigint language plpgsql as $$ declare new_row bigint; begin insert into planets(name) values (add_planet.name) returning id into new_row; return new_row; end; $$; ``` Call from JavaScript: `const { data, error } = await supabase.rpc('add_planet', { name: 'Jakku' })` Call from Python: `data = supabase.rpc('add_planet', params={'name': 'Jakku'}).execute()` Call from Dart: `final data = await supabase.rpc('add_planet', params: { 'name': 'Jakku' });` Call from SQL: `select * from add_planet('Jakku');`

Call database functions from client libraries using rpc()

Database functions can be called from client libraries using the rpc() method. JavaScript example: `const { data, error } = await supabase.rpc('function_name')`. Python example: `data = supabase.rpc('function_name').execute()`. Dart example: `final data = await supabase.rpc('function_name');`. Swift example: `try await supabase.rpc("function_name").execute()`. Kotlin example: `val data = supabase.postgrest.rpc("function_name")`. C# example: `await supabase.Rpc("function_name", null);`

Default join behavior is left join

By default, embedded relations use left join semantics from the parent table. Parent rows are returned even if no related rows match. For one-to-many joins, the embedded relation is an empty array [] when nothing matches. For many-to-one joins, the embedded relation is null when nothing matches.

Inner join syntax with !inner

To filter out parent rows that do not match the related table, use the `!inner` modifier on the embedded relation. Example: `instruments!inner(id, name)` will only return parent rows where a matching child row exists.

Join syntax operators: alias, inner, and foreign key specification

Join syntax supports three operators: `alias:relation(columns)` renames the embedded relation in the response (example: `start_scan:scans(id, badge_scan_time)`); `relation!inner(columns)` uses inner join behavior; `relation!foreign_key(columns)` chooses which foreign key relationship to use when multiple foreign keys match the join (example: `scans!scan_id_start(id)`).

Filtering on joined table columns

Use dot notation to filter on joined table columns: `joined_table.column`. Example: `.eq('orchestral_sections.name', 'woodwinds')` filters by a column in the joined table. This works with any filter operator like eq, neq, and in.

Multiple foreign keys to same table require explicit ON clause

When a table has multiple foreign keys pointing to the same target table, you must explicitly specify which foreign key to use in the join. Use the syntax `relation!foreign_key_name(columns)` to disambiguate. Example: if shifts has both scan_id_start and scan_id_end referencing scans, use `scans!scan_id_start(...)` and `scans!scan_id_end(...)`.

TypeScript QueryData type for join results

To get TypeScript types for nested query results, import QueryData from @supabase/supabase-js and create a type from the query object: `type SectionsWithInstruments = QueryData<typeof sectionsWithInstrumentsQuery>`. This provides autocomplete and type safety for nested relationship data.

JavaScript join query example with supabase-js

```js const { data, error } = await supabase.from('orchestral_sections').select(` id, name, instruments ( id, name ) `) ``` This fetches orchestral_sections with nested instruments array. The `data` object contains results, and `error` contains any errors.

Inner join query example

```js const { data, error } = await supabase .from('orchestral_sections') .select(` id, name, instruments!inner ( id, name ) `) .eq('instruments.name', 'flute') ``` This uses `!inner` to only return parent rows that have matching instruments. Result contains only the woodwinds section with the flute instrument.

Multiple foreign key join with alias example

```js const { data, error } = await supabase.from('shifts').select(` *, start_scan:scans!scan_id_start ( id, user_id, badge_scan_time ), end_scan:scans!scan_id_end ( id, user_id, badge_scan_time ) `) ``` This uses aliases (start_scan, end_scan) and explicit foreign key names (scan_id_start, scan_id_end) to distinguish between two foreign keys pointing to the same scans table.

Many-to-many join query example

```js const { data, error } = await supabase.from('teams').select(` id, team_name, users ( id, name ) `) ``` This fetches teams with nested users from a many-to-many relationship through a members junction table. The API automatically handles the junction table without explicit reference.

Left join result with filtered joined field

When using a left join and filtering on a joined field, parent rows are still returned even if they don't match the filter. Example: filtering on `instruments.name = 'flute'` with left join still returns strings and percussion sections with empty instruments arrays, only woodwinds has the flute.

Many-to-many joins with junction tables

Supabase automatically detects many-to-many relationships. You do not need to explicitly reference the junction table in the select statement. If you have users, teams, and a members junction table with foreign keys to both, you can select directly from teams and nest the users without mentioning members.

One-to-many join with foreign keys

The Supabase data APIs automatically detect relationships between Postgres tables based on foreign keys. To query a one-to-many relationship, use nested select syntax with the related table name in parentheses. Example: `select('id, name, instruments(id, name)')` fetches orchestral_sections with their nested instruments.

Give your agent this brain