List queries

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.

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 runCopy Anchor Link

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

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 for what each mode costs.

Cursor paginationCopy Anchor Link

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

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). 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 countsCopy Anchor Link

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

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:

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

This needs PostgREST aggregates (pgrst.db_aggregates_enabled); doctor warns with BS210 when they are off. The counts sort by _count, 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 budgetCopy Anchor Link

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():

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

What it compiles toCopy Anchor Link

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.

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

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

ValidationCopy Anchor Link

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:

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

customerList.schema is a Standard Schema, 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 nuqsCopy Anchor Link

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

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

For client-side state with nuqs, pass its createParser. better-supabase does not depend on nuqs:

"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 UIsCopy Anchor Link

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.