TypeScript SelectQueryError when selecting embedding resources after rpc()

[stefan-girlich]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

When referencing embedded resources in .select() after .rpc(), the resolved type of data contains an error for the embedded resources.

To Reproduce

1. Find a resources/table which has a relationship with another resource. In my case, batches has a many-to-one relationship to collections.
2. Implement an SQL function which returns a SETOF a resource/table of this resource (see code below)
3. Invoke the SQL function via .rpc() in TypeScript (see below).
4. Observe that the type of data returned from the RPC call is incorrectly inferred as SelectQueryError<"column 'collections' does not exist on 'get_batches_by_ids'.">[] | null instead of the expected type., but the code works as expected at runtime.

SQL function

CREATE OR REPLACE FUNCTION get_batches_by_ids(batch_ids UUID[])
RETURNS SETOF batches AS $$  SELECT *
  FROM batches
  WHERE (array_length(batch_ids, 1) IS NULL OR id = ANY(batch_ids));$$ LANGUAGE sql;

RPC invokation ❌

const supabase = createServerClient<Database>(/*...*/)
const { data } = await supabase
  .rpc('get_batches_by_ids', { batch_ids })
  .select('*, collections!public_batches_collection_id_fkey(id)') 
// SelectQueryError<"column 'collections' does not exist on 'get_batches_by_ids'.">[] | null

Conventional .select() invokation ✅

const { data } = await supabase
    .from('batches')
    .select('*, collections!public_batches_collection_id_fkey(id)')
    .in('id', batch_ids)

Workaround

Note that this workaround runs an extra query and needs to be improved for production usage.

  const query = '*, collections!public_batches_collection_id_fkey(id)'

  const { data: rawDataTypeDummy } = await supabase
    .from('batches')
    .select(query)

  const { data, error } = await supabase.rpc('get_batches_by_ids', { batch_ids }).select(query)
  return data as typeof rawDataTypeDummy

Expected behavior

The data type returned from the RPC call should be correctly inferred based on the table schema and selected fields/resources. The type should match what would be expected from a standard .select() query.

System information

• OS: MacOS
• Browser (if applies): -
• Version of supabase-js: 2.49.4
• Version of postgrest-js: 1.19.4
• Version of Node.js: 21.6.2

Additional context

I am using the RPC-based approach to avoid exceeding URI length limits with a large number of UUIDs in the in() clause (see this comment).

[masda70]

I've been dealing with this same issue and have developed an improved workaround that doesn't require an extra query. The key is using the overrideTypes method with proper type extraction and storing the query in a variable for reusability.

Improved Pattern

Instead of running an extra query, you can extract the correct type using a helper function and then override the RPC return type:

// Store the query in a variable for reusability
const batchesQuery = '*, collections!public_batches_collection_id_fkey(id)'

// Helper function to extract the correct type from the table query
// This addresses PostgREST RPC typing issues with embedded resources
function getBatchesTypeDefinition() {
  return supabase.from('batches').select(batchesQuery)
}
type BatchesType = NonNullable<Awaited<ReturnType<typeof getBatchesTypeDefinition>>['data']>

const { data, error } = await supabase
  .rpc('get_batches_by_ids', { batch_ids })
  .select(batchesQuery)
  .overrideTypes<BatchesType, {merge: false}>()

Bumping this issue as it's still likely affecting other developers working with complex RPC functions. The overrideTypes workaround is solid, but it would be great to have this fixed at the library level so we don't need these workarounds.