Read sets and $many

An app shell that shows an unread badge, a customer count and the latest note makes three requests on every navigation. Even in parallel, each costs a connection and a trip through PostgREST. db.$many runs them together: ad-hoc specs as one parallel wave, and registered read sets as a single request.

Ad-hoc readsCopy Anchor Link

Pass an array of query specs. The result is a tuple, typed per entry:

const [customers, notes] = await db
  .$many([
    betterSupabase.spec.customers.findMany({
      select: ["id", "name"],
      limit: 10,
    }),
    betterSupabase.spec.notes.count(),
  ])
  .orThrow();

Over PostgREST the specs run in parallel, as one wave. Over better-supabase/postgres they run on one connection in one transaction, through Executor.batch. Plugins apply to each spec as usual. The first error fails the whole call.

Registered read setsCopy Anchor Link

A read set names its reads once, with typed placeholders for parameters and for the caller's id:

import { defineReadSet } from "better-supabase";

import { betterSupabase } from "./supabase/index.ts";

export const workspaceSummary = defineReadSet(
  betterSupabase,
  "workspace_summary",
  {},
  (s, _p, auth) => ({
    customers: s.customers.count(),
    mine: s.customers.count({ where: { createdBy: auth.uid } }),
    latestNote: s.notes.findFirst({
      select: ["body", "createdAt"],
      orderBy: { createdAt: "desc" },
    }),
  }),
);

List the module in the config and add the read-sets SQL module:

export default defineConfig({
  readSets: ["src/lib/read-sets.ts"],
  sql: { modules: ["read-sets"] },
});

better-supabase gen compiles each set into one function in the read-sets module, then you create a migration with supabase db schema declarative sync (supabase db diff on the legacy migra engine):

create or replace function public.rs_workspace_summary(p jsonb)
  returns jsonb
  language sql stable security invoker set search_path = ''
as $rs$
  select jsonb_build_object(
    'customers', ...,
    'mine', ... where t0."created_by" = (select auth.uid()) ...,
    'latestNote', ...
  )
$rs$;
grant execute on function public.rs_workspace_summary(jsonb) to authenticated;

Then run it:

const summary = await db.$many(workspaceSummary, {}).orThrow();
// { customers: number; mine: number; latestNote: { body: string; createdAt: string } | null }

Rows come back exactly as db.$run(spec) would return them: same casing, codecs and aggregates.

ParametersCopy Anchor Link

p holds placeholders, not values. Use them where a value goes in where, including in lists and string filters such as contains. The function reads each one from its p jsonb argument and casts it to the declared type, so nothing is built as dynamic SQL.

Name enums and domains with their schema: the function runs with an empty search_path. An enum column expects its literal union, so cast the placeholder (p.kind as 'call').

Every parameter is required. A missing one fails with invalid_request before anything reaches the database. A spec taken from a read set still holds placeholders, so db.$run(readSet.specs.x) fails too; run the set.

The caller's idCopy Anchor Link

The third builder argument, auth, stands for the signed-in user. auth.uid goes wherever a uuid value does, so the set needs no userId parameter and a caller can't pass someone else's id. Inside a string filter such as contains it is compared as text. It is a single value, so it can't go in an in list; compare with it directly instead.

Over PostgREST the function calls (select auth.uid()), which reads the sub claim of the request's JWT. Over better-supabase/postgres, and on executors that run the reads in parallel, $many binds the sub claim of the connection's claims instead, and fails with invalid_request when there is none. Connect with the user's verified claims (connect(client, { claims }); the server's db does this for you). For an anon caller auth.uid() is null, so a comparison with it matches nothing.

SecurityCopy Anchor Link

The function is security invoker, so RLS decides what each caller reads, as for any other query. Execute is granted to authenticated only; pass roles: ['anon', 'authenticated'] to open it up.

Read sets run without query plugins, on both executors: the function is compiled once, and plugins such as tenant or softDelete add filters at request time. Scope read sets with RLS, or with explicit parameters and where filters. defineReadSet logs a warning when the definition has query plugins and the set reads a table with a tenant or soft-delete flag.

LimitsCopy Anchor Link

• Each entry is one select: findMany, findFirst, findUnique, findById, count, exists, aggregate or an offset paginate.
• A set has between 1 and 50 entries.
• Names are snake*case, at most 60 characters. The function is public.rs*<name>.
• gen imports the module with Node, so relative imports need their .ts extension (allowImportingTsExtensions in tsconfig.json), and the module can't import server-only.

CachingCopy Anchor Link

In Next.js, tag a "use cache" scope with every table a set (or an array of specs) reads:

export async function getWorkspaceSummary() {
  "use cache: private";
  const { db, session } = await bs.cached();
  bs.cacheTags(workspaceSummary);
  if (session.kind !== "user") return null;
  return db.$many(workspaceSummary, {}).orThrow();
}

Any mutation of those tables revalidates the entry. See Cache Components.

DoctorCopy Anchor Link

BS304 reports a read-sets file that no longer matches the sets in readSets. Run better-supabase gen (or better-supabase sql sync), then supabase db schema declarative sync.