# List queries

> Search, facets, sorting and pagination from one definition, safe from the URL to SQL.

Source: https://bettersupabase.com/docs/platform/list

Most apps have a lot of list pages that each hand-roll the same things: read
`?q=&status=&page=`, validate it, escape the search text, turn "no value" into
`is null`, and document the endpoint. `defineListQuery` does all of that once
per table.

```ts title="lib/lists.ts"
import { defineListQuery } from "better-supabase/list";
import { betterSupabase } from "./supabase";

export const customerList = defineListQuery(betterSupabase, "customers", {
  search: ["name", "kvk"],
  facets: { status: "status", kvk: "kvk" },
  sorts: {
    name: [{ name: "asc" }, { id: "asc" }],
    newest: [{ createdAt: "desc" }, { id: "asc" }],
  },
  defaultSort: "newest",
  pageSize: 25, // default 50, capped by maxPageSize (default 200)
});
```

Column names, sort keys and facet keys are all typed. `search` only accepts
text columns. `defaultSort` must be one of the `sorts`.

## Parse and run [#parse-and-run]

`parse` accepts `URLSearchParams`, a Next.js `searchParams` record, or typed
input. Facet values can be comma-separated or repeated
(`?status=active,lead` or `?status=active&status=lead`).

```ts title="app/customers/page.tsx"
export default async function Page({ searchParams }: PageProps<'/customers'>) {
  const { db } = await bs.context();
  const query = customerList.parse(await searchParams);
  if (!query.ok) return <InvalidFilters issues={query.issues} />;

  const page = await customerList
    .run(db, query.value, { select: ['id', 'name', 'status'], where: { archivedAt: null } })
    .orThrow();
  // page.items: { id: string; name: string; status: CustomerStatus }[]
}
```

`run` calls `paginate` and ANDs your own `where` with the list filters. Plugins
such as tenant scoping and soft delete still apply. `include` takes the same
relation map as `findMany`. If you only want the arguments, use
`customerList.args(query)`.

The total uses `count: 'planned'` by default: the planner's row estimate,
which costs nothing extra but can be off after bulk changes until the table is
analyzed. Set `count: 'exact'` in the config when the UI needs the precise
total (a "page 3 of 7" footer on a small table), `'estimated'` for exact
counts below PostgREST's `db-max-rows` and the estimate above, or pass `count`
to one `run`; see [Pagination](/docs/repository/pagination) for what each mode costs.

## Cursor pagination [#cursor-pagination]

Set `pagination: 'cursor'` and the list takes `after` instead of `page`:

```ts
export const customerFeed = defineListQuery(betterSupabase, "customers", {
  sorts: { newest: [{ createdAt: "desc" }, { id: "desc" }] },
  defaultSort: "newest",
  pagination: "cursor",
});

const page = await customerFeed.run(db, query).orThrow();
// page: { items, nextCursor, hasMore }
customerFeed.toSearchParams({ ...query, after: page.nextCursor! });
```

A cursor page skips the count and reads the same number of rows however
deep it is (see [Pagination](/docs/repository/pagination#cursors)). `page`
in the input is rejected with an issue, as is `after` on an offset list.
`size` is still capped by `maxPageSize`, and the OpenAPI parameters and JSON
Schema describe `after`.

## Facet counts [#facet-counts]

Filter UIs usually show how many rows each facet value would match
("Active (12)"). Set `facetCounts: true` and `run` also returns
`page.facetCounts`:

```ts
export const customerList = defineListQuery(betterSupabase, "customers", {
  facets: { status: "status", kvk: "kvk" },
  sorts: { name: { name: "asc" } },
  defaultSort: "name",
  facetCounts: true,
});

const page = await customerList.run(db, query).orThrow();
page.facetCounts.status; // { active: 12, lead: 4, archived: 0 }
page.facetCounts.kvk; // { '1001': 3, __unset__: 13 }
```

Each facet gets its own grouped aggregate, filtered by the search, your own
`where` and every other facet's selection but not its own, so picking `active`
doesn't turn `lead` into 0. Enum and CHECK values with no rows show up as 0.
Empty values are keyed `UNSET`.

Each aggregate counts at most `facetLimit` values (100 by default), the most
frequent first, with ties in column order. A facet with more values keeps the
`facetLimit` values that match the most rows, and its key is listed in
`page.facetCountsTruncated`, so the UI can say the list is cut:

```ts
page.facetCountsTruncated; // [] when every facet was counted in full
```

This needs PostgREST aggregates (`pgrst.db_aggregates_enabled`); doctor warns
with [BS210](/docs/cli/doctor) when they are off. The counts
[sort by `_count`](/docs/repository/aggregates#sort-groups-by-an-aggregate),
which PostgREST can't do on a table with a column named `count`. If an
aggregate fails, the whole `run` fails with its `DbError`.

## Request budget [#request-budget]

A list is one request, plus one per facet when `facetCounts` is on, and always
one wave: the page and the aggregates run in parallel. Assert it with
[`db.$stats()`](/docs/repository):

```ts
await customerList.run(db, query);
expect(db.$stats()).toMatchObject({ calls: 3, waves: 1 });
```

## What it compiles to [#what-it-compiles-to]

| Input                | Filter                                      |
| -------------------- | ------------------------------------------- |
| `q=road`             | `name ilike '%road%' or kvk ilike '%road%'` |
| `status=active,lead` | `status in ('active', 'lead')`              |
| `kvk=__unset__`      | `kvk is null`                               |
| `kvk=__unset__,1001` | `kvk in ('1001') or kvk is null`            |

Search text is trimmed, capped at `maxSearchLength` (200) and escaped: `%`,
`_` and `\` match literally, and commas, parentheses and quotes can't break out
of a PostgREST `or=(...)` filter. The same query gives identical results
through PostgREST and the direct [Postgres executor](/docs/auth/postgres).

For full-text search, point `search` at a `tsvector` or text column:

```ts
search: { fts: 'searchVector', config: 'dutch' } // websearch_to_tsquery
```

## Validation [#validation]

Facet values are checked against enum and CHECK-constraint values from the
generated schema. `__unset__` (exported as `UNSET`) is only allowed on
nullable columns. Every problem is reported with a path:

```ts
customerList.parse(new URLSearchParams("sort=oldest&page=0&status=bogus"))
  .issues;
// [{ path: ['sort'], ... }, { path: ['page'], ... }, { path: ['facets', 'status'], ... }]
```

`customerList.schema` is a [Standard Schema](/docs/standards), so it plugs into
any router or form library that accepts one. Pass it to `bs.action({ input })`
or an oRPC procedure without writing a validator.

## URLs and nuqs [#urls-and-nuqs]

`toSearchParams` writes a query back to the URL, leaving out defaults, so
links stay short and round-trip exactly:

```ts
customerList.toSearchParams({ ...query, page: 2 }).toString(); // 'q=road&page=2'
```

For client-side state with [nuqs](https://nuqs.dev), pass its `createParser`.
better-supabase does not depend on nuqs:

```ts
"use client";
import { createParser, useQueryStates } from "nuqs";

const parsers = customerList.nuqs(createParser);
const [state, setState] = useQueryStates(parsers);
```

`customerList.parsers` exposes the same `{ parse, serialize }` pairs for
other routers.

## OpenAPI, MCP and filter UIs [#openapi-mcp-and-filter-uis]

The definition describes itself:

* `customerList.openapi`: OpenAPI 3.1 query parameters, with facets as
  `style: form, explode: false` string arrays that list their enum values.
* `customerList.jsonSchema`: JSON Schema for the typed input. Use it for MCP
  tool arguments or JSON APIs.
* `customerList.facets`: `{ key, column, nullable, values? }` for rendering
  filter menus.