[RFC] Query builder immutability and safe branching semantics across Supabase SDKs

[Vishv07]

Motivation & Background

In data-layer development, branching a base query builder instance is a standard pattern for pagination, dynamic filtering, and conditional querying:

# Backend service / worker
base_query = supabase.table("records").select("*").eq("organization_id", org_id)

# Forking for total count vs paginated page
page_query = base_query.range(0, 50).order("created_at")

However, across non-JS Supabase SDKs (such as supabase-py and supabase-swift), filter and modifier methods mutate the internal builder instance in place instead of returning a copy-on-write clone.

When branching as above, subsequent calls mutate base_query, causing race conditions, corrupted query parameters, and unexpected results across asynchronous tasks.

Proposed Design

1. Copy-on-write Builder Chaining: Every filter/query modifier (eq, in_, order, range, limit) should return a new shallow clone of the builder with isolated query parameters and headers.
2. Explicit .clone() Helper: Provide an explicit .clone() method on PostgREST query builders for developers who wish to clone instances ahead of mutation.
3. SDK Cross-Language Alignment: Standardize query builder immutability semantics across supabase-js, supabase-py, supabase-flutter, supabase-swift, and supabase-kt.

Benefits

• Eliminates silent state pollution in backend services and multi-tenant applications.
• Aligns Supabase query builders with modern ORM / query-builder conventions (SQLAlchemy, Prisma, Django QuerySets, Ecto).
• Improves cross-SDK behavioral consistency.

Would love to hear maintainers and community thoughts on adopting standardized immutability across the SDKs!

[edwardsong08]

I think the strongest version of this proposal is to make the contract explicit before trying to make every SDK behave identically. The main question is whether a builder is intended to be a reusable query description or a single-use mutable request. Both models can work, but silently sharing a mutable builder across tasks is the dangerous case.

A practical path could be:

• Document the current mutation and reuse semantics for each SDK.
• Add an explicit clone/fork operation first, with tests proving that filters, modifiers, headers, and pagination state do not leak between branches.
• Treat copy-on-write as a follow-up where the SDK maintainers are comfortable with the allocation and compatibility cost.
• Add a concurrency-focused test that starts from one base query and builds two branches with different filters and ranges, then verifies the generated requests independently.

I would also avoid promising identical method behavior across all languages until the underlying request-builder types have the same lifecycle and ownership model. An explicit clone API plus clear documentation would solve the immediate race/cross-contamination problem while leaving room for copy-on-write in SDKs where it fits naturally.

[Vishv07]

Thanks for the thoughtful and practical breakdown, @edwardsong08!

This phased roadmap makes total sense:

1. Explicit .clone() first: Start with an explicit .clone()/.fork() method on request builders to immediately unblock safe query branching and eliminate cross-task mutation bugs without adding automatic copy overhead or breaking existing semantics.

2. Isolated state guarantees: Ensure .clone() performs deep copies of query params (self.params), headers, and pagination buffers so branched builders never leak state back to the parent.

3. Branching & concurrency test suites: Add comprehensive unit tests covering branched builder execution (e.g., one base query branching into two independent filters/ranges evaluated in parallel).

We can start by prototyping the .clone() implementation and branching test suite in supabase-py as a reference.

[zoubingwu]

Btw, in js sdk builders are mutable in some places as well.

For example, methods like eq(), range(), and order() mutate the current builder's request state and then return this. So branching from the same filtered builder can also cause the branches to affect each other.

It might be useful to include a simple cross-SDK test case where two queries are derived from the same base builder, and verify what the expected branching semantics should be. If a clone() API is introduced, that test could also help define exactly which request state needs to be copied.

[Vishv07]

Thanks for the insight, @zoubingwu! great point postgrest-js behaves similarly by modifying this.url.searchParams in place.

To formalize the branching semantics, here's the reference test specification for what an explicit .clone() must isolate:

Specification: Request State Cloned

When .clone() is called, the new instance receives:

1. URL & Path: Cloned endpoint.
2. Query Parameters: Deep-copied eq, order, limit, offset, etc. — no cross-branch leakage.
3. HTTP Headers: Independent copy of Prefer, Range, Accept, custom headers.
4. Body / JSON Payload: Deep-copied for RPC/mutation builders.
5. Modifier Flags: Preserved builder state (e.g., negate_next).

Cross-SDK Branching Test Case Pattern

const baseQuery = supabase
  .from('items')
  .select('*')
  .eq('tenant_id', 'tenant-123')

// Branch A: Page 1, recent first
const branchA = baseQuery
  .clone()
  .order('created_at', { ascending: false })
  .range(0, 10)

// Branch B: Page 2, alphabetical
const branchB = baseQuery
  .clone()
  .order('title', { ascending: true })
  .range(20, 30)

// Assertions:
// baseQuery params == "select=*&tenant_id=eq.tenant-123"
// branchA params   == "select=*&tenant_id=eq.tenant-123&order=created_at.desc&offset=0&limit=11"
// branchB params   == "select=*&tenant_id=eq.tenant-123&order=title.asc&offset=20&limit=11"

We just opened a reference implementation and complete test suite for supabase-py in PR (supabase/supabase-py#1625) This pattern can be directly translated to postgrest-js and the other SDKs as well!