# Read sets and $many

> Run several reads as one round trip, a single GET to a stable function or one SQL transaction.

Source: https://bettersupabase.com/docs/repository/read-sets

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 reads [#ad-hoc-reads]

Pass an array of [query specs](/docs/concepts/caching#query-specs). The result is a tuple, typed
per entry:

```ts
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](/docs/testing#database-budget).
Over [`better-supabase/postgres`](/docs/auth/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 sets [#registered-read-sets]

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

```ts title="src/lib/read-sets.ts"
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:

```ts title="better-supabase.config.ts"
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](/docs/blocks/sql), then you create a migration with
`supabase db schema declarative sync` (`supabase db diff` on the legacy migra
engine):

```sql
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:

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

| Executor                   | What runs                                                                            |
| -------------------------- | ------------------------------------------------------------------------------------ |
| PostgREST                  | One GET to `rpc/rs_<name>`. The function is `stable`, so a read replica can serve it |
| `better-supabase/postgres` | The same reads with the values bound, in one transaction. The function isn't needed  |
| Anything else              | The reads in parallel                                                                |

Rows come back exactly as `db.$run(spec)` would return them: same casing,
codecs and [aggregates](/docs/repository/aggregates).

### Parameters [#parameters]

`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.

| Type                                                  | TypeScript value              |
| ----------------------------------------------------- | ----------------------------- |
| `uuid`, `text`, `date`, `timestamp`, `timestamptz`    | `string`                      |
| `int2`, `int4`, `int8`, `float4`, `float8`, `numeric` | `number`                      |
| `bool`                                                | `boolean`                     |
| `<type>[]`                                            | `readonly` array of the above |
| `schema.type`, for example `public.note_kind`         | `string`                      |

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 id [#the-callers-id]

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.

### Security [#security]

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.

### Limits [#limits]

* 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`.

## Caching [#caching]

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

```ts
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](/docs/frameworks/next-cache-components).

## Doctor [#doctor]

[BS304](/docs/cli/doctor#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`.