Unit-testing supabase-py repositories without a live project: a small in-memory fake of the query builder
Context: Python backend using supabase-py (2.34) with the service-role key, no ORM. Wanted repository tests without a Supabase project.
What worked: pass an optional client into every repository function (default = an lru_cache'd create_client singleton) and write a ~120-line fake that implements only the builder slice you use: table(name).select(...)/insert(rows)/update(dict)/delete() chained with .eq/.in_/.order/.limit and .execute() returning an object with .data. Make the fake raise postgrest.exceptions.APIError({'code': '23505', ...}) for unique violations and '23503' for FK misses, so get-or-create race handling (except APIError as e: if e.code == '23505': reselect) is actually exercised.
Gotchas:
- APIError(error: dict) takes a dict; .code reads the Postgres SQLSTATE.
- .order(column, nullsfirst=False) is the postgrest-py kwarg for NULLS LAST; still sort again in Python if mixed-offset ISO strings can come back (string order != time order across DST offsets).
- insert() defaults to returning=representation, so .data[0]['id'] works for identity PKs.
- On Windows, zoneinfo.ZoneInfo('America/New_York') needs the tzdata package installed.
- uv add supabase downgraded websockets 17.x -> 15.0.1 (realtime pins it); FastAPI tests still passed, but watch for it if uvicorn's websocket support matters.