Filtering

ColumnsCopy Anchor Link

A value means equality; null means is null:

where: { status: 'active', kvk: null }

Operators depend on the column type:

contains, startsWith and endsWith are case-insensitive and escape %, _ and * in your input. Use like or ilike when you want wildcards (% and _; a * in the pattern is a literal character, as in Postgres), and match (~) or imatch (~*, case-insensitive) for a POSIX regular expression. SQLite (PowerSync) returns unsupported for match and imatch.

On a jsonb column, contains sends its value as a JSON document, arrays included, so an array matches a JSON array that holds every given element:

where: {
  attachments: {
    contains: [{ kind: "image" }];
  }
}

path compares the text at a key inside a jsonb column, as ->> does. It takes eq, neq, in, notIn, isNull, gt, gte, lt, lte, like and ilike:

where: {
  metadata: { path: ['owner', 'id'], eq: userId },
  AND: [{ metadata: { path: ['replacedBy'], isNull: true } }],
}

This sends metadata->owner->>id=eq.<id>. Numbers and booleans compare as their text, so gt and lt order strings; isNull: true matches a missing key and a JSON null. Combine several paths on one column with AND or OR. SQLite (PowerSync) returns unsupported for path filters.

A null in an in list matches rows where the column is null, and a null in a notIn list leaves them out, so the list behaves like the values it holds rather than like SQL's in (..., null). A Date is sent as ISO 8601 text.

Enum and CHECK columns only accept their allowed values:

where: { status: { in: ['lead', 'active'] } }
// @ts-expect-error: 'deleted' is not a CustomersStatus
where: { status: 'deleted' }

Long in listsCopy Anchor Link

in lists send uuids, numbers, dates and other plain values without quotes, as supabase-js does. A read whose query string is still longer than urlLengthLimit (6000 characters by default, below the 8 KB request line the Supabase API gateway accepts, or the client's own urlLengthLimit when that is lower) is split along its longest top-level in list into reads that fit; they run in parallel, and the rows come back merged:

await db.employees.findMany({ where: { id: { in: managerIds } } });

Splitting needs a read without limit, offset, count or a page, since the parts can't share those, and an orderBy on number, uuid, date or time columns, which are sorted again after the merge (an order column you don't select is fetched for the sort and left out of the rows). Other reads that are too long return invalid_request, and so do updateMany, deleteMany and the other writes, which can't be split: page through a shorter list, or pass the list to a function. Set the limit with defineSupabase(schema, { urlLengthLimit }).

Building a filter step by stepCopy Anchor Link

WhereInput is read-only, like every argument type. The generated module exports WhereOf<"table">, the same shape with writable keys, for a filter you assemble one condition at a time:

import type { WhereOf } from "@/lib/supabase/generated";

const where: WhereOf<"customers"> = {};
if (status) where.status = status;
if (search) where.name = { ilike: `%${search}%` };
await db.customers.findMany({ where });

MutableWhere<Models, T> from better-supabase is the generic form.

OrderByOf<"table"> types a sort you build outside the call, one term or a list:

import type { OrderByOf } from "@/lib/supabase/generated";

const orderBy: OrderByOf<"customers"> =
  sort === "newest" ? { createdAt: "desc" } : [{ name: "asc" }, { id: "asc" }];
await db.customers.findMany({ where, orderBy });

OrderTermOf<"table"> is one term of that sort, for a tiebreak or a list you assemble from parts:

import type { OrderByOf, OrderTermOf } from "@/lib/supabase/generated";

const tiebreak: OrderTermOf<"customers"> = { id: "asc" };
const orderBy: OrderByOf<"customers"> = [{ name: "asc" }, tiebreak];

OrderByArg<Models, T> and OrderByInput<Models, T> from better-supabase are the generic forms.

CombiningCopy Anchor Link

where: {
  OR: [{ name: { contains: 'acme' } }, { kvk: '1001' }],
  NOT: { status: 'archived' },
}

Keys in one object are combined with AND. AND also accepts an array.

IndexesCopy Anchor Link

Equality, ranges and startsWith on a plain btree index work as you would expect. The other operators need a different kind of index, or every query reads the whole table:

search on a text column compiles to to_tsvector(column) @@ websearch_to_tsquery(...), which an index can't serve, because the configuration is a parameter. Store the vector in a generated column instead, index it, and search that column:

alter table customers add column search_vector tsvector
  generated always as (to_tsvector('simple', coalesce(name, '') || ' ' || coalesce(kvk, ''))) stored;
create index on customers using gin (search_vector);

where: {
  searchVector: {
    search: "acme";
  }
}

jsonb_path_ops makes the index smaller and faster for @>, which is all contains uses; leave it out if SQL elsewhere uses ?. Doctor's BS219 flags containment filters on columns without a GIN index.

RelationsCopy Anchor Link

To-many relations take some, none and every:

where: {
  notes: { some: { kind: 'call' } },      // at least one call note
  locations: { none: {} },                // no locations at all
  customerTags: { every: { tag: { color: 'green' } } },
}

To-one relations take a filter directly, null when the relation is nullable, or is and isNot:

where: { organization: { slug: 'acme' }, primaryContact: null }

Relation filters nest to any depth and work inside OR and NOT. They compile to filter-only embeds (!inner for some, anti-joins for none and every), so they run as a single request under the caller's RLS.

Limits of the PostgREST pathCopy Anchor Link

updateMany and deleteMany cannot filter through relations over PostgREST, and return an invalid_request error. Use the direct Postgres executor for those, or select the ids first.