gridcn2.0.0
Server and data examplesORM examples

Supabase example

Build a route handler that maps the sort, filter, search text, and row range of a server grid onto a Supabase query.

This recipe needs a server and is not a registry item. A server grid sends its spec (sorts, filters, searchText) and a row range (start, end) to your route. The route checks the request with parseServerSpec, builds one query, and answers { rows, totalRowCount }. The route reads a users table with an id column and the text columns name, email, and role.

The route

The route follows the server rows contract. It writes every filter as a PostgREST condition string, and every filter group as and(...) or or(...). Each top-level node goes into one .or() call. It rejects a request that fails the check with status 400. It adds id as the last sort, so no two rows tie and a page never repeats a row:

app/api/grid/route.ts
import {
  isFilterGroup,
  type FilterNode,
} from "@/components/data-grid/data-grid";
import {
  parseServerSpec,
} from "@/components/data-grid-server/data-grid-server";
import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
);

const COLUMNS = new Set<string>(["name", "email", "role"]);

// A condition string splits on commas and parentheses, so quote every value.
const quote = (value: string) =>
  `"${value.replace(/\\/g, "\\\\").replace(/"/g, '\\"')}"`;
const escapeLike = (text: string) => text.replace(/[\\%_]/g, "\\$&");
const same = (v: string) => `ilike.${quote(escapeLike(v))}`;
const group = (c: string, values: string[], not: string) =>
  values.map((v) => `${c}.${not}${same(v)}`).join(",");

const CLAUSES: Record<string, (c: string, v: any) => string> = {
  contains: (c, v) => `${c}.ilike.${quote(`%${escapeLike(v)}%`)}`,
  notContains: (c, v) => `${c}.not.ilike.${quote(`%${escapeLike(v)}%`)}`,
  startsWith: (c, v) => `${c}.ilike.${quote(`${escapeLike(v)}%`)}`,
  endsWith: (c, v) => `${c}.ilike.${quote(`%${escapeLike(v)}`)}`,
  equals: (c, v) => `${c}.${same(v)}`,
  notEquals: (c, v) => `${c}.not.${same(v)}`,
  gt: (c, v) => `${c}.gt.${quote(v)}`,
  gte: (c, v) => `${c}.gte.${quote(v)}`,
  lt: (c, v) => `${c}.lt.${quote(v)}`,
  lte: (c, v) => `${c}.lte.${quote(v)}`,
  empty: (c) => `${c}.eq.""`,
  notEmpty: (c) => `${c}.neq.""`,
  isAnyOf: (c, v) =>
    v.length ? `or(${group(c, v, "")})` : `${c}.in.()`,
  isNoneOf: (c, v) =>
    v.length ? `and(${group(c, v, "not.")})` : `${c}.not.in.()`,
};

const columnRules = Object.fromEntries(
  [...COLUMNS].map((id) => [id, { operators: Object.keys(CLAUSES) }]),
);

function readRange(body: any): { start: number; end: number } | null {
  const { start, end } = body ?? {};
  const valid =
    Number.isSafeInteger(start) && start >= 0 && start <= 10_000_000 &&
    Number.isSafeInteger(end) && end > start;
  return valid ? { start, end } : null;
}

// "" matches every row, as an empty group does in the grid.
function condition(node: FilterNode): string {
  if (!isFilterGroup(node)) {
    return CLAUSES[node.operator](node.columnId, node.value);
  }
  const parts: string[] = node.filters.map(condition);
  if (node.join === "or" && parts.includes("")) return "";
  const kept = parts.filter(Boolean);
  if (kept.length <= 1) return kept[0] ?? "";
  return `${node.join}(${kept.join(",")})`;
}

export async function POST(request: Request) {
  const body = await request.json().catch(() => null);
  const range = readRange(body);
  if (!range) return new Response("Invalid request", { status: 400 });
  const { start, end } = range;
  let spec;
  try {
    spec = parseServerSpec(body.spec, { columns: columnRules });
  } catch {
    return new Response("Invalid request", { status: 400 });
  }
  const limit = Math.min(end - start, 500);
  let query = supabase.from("users").select("*", { count: "exact" });
  if (spec.searchText) {
    const pattern = quote(`%${escapeLike(spec.searchText)}%`);
    query = query.or(`name.ilike.${pattern},email.ilike.${pattern}`);
  }
  // Each top-level node is one .or() call; the calls combine with "and".
  for (const node of spec.filters) {
    const text = condition(node);
    if (text) query = query.or(text);
  }
  for (const sort of spec.sorts) {
    query = query.order(sort.columnId, { ascending: sort.direction === "asc" });
  }
  query = query.order("id");

  const { data, count, error } = await query.range(start, start + limit - 1);
  if (error) throw new Error(error.message);
  return Response.json({ rows: data, totalRowCount: count });
}

About the sample:

  • range(from, to) includes both ends, hence the - 1.
  • isFilterGroup(node) is true for a group and false for a filter row.
  • The grid compares text without case. The sample uses ilike for every text operator, with the value escaped and no wildcard for the equality operators.
  • escapeLike stops %, _, and \ in the user text from acting as wildcards. PostgREST also reads * in an ilike pattern as a wildcard, and no escape removes that.
  • parseServerSpec checks the column ids, the operators, the value shapes, and the tree size. Its default caps are 64 sorts, 500 search characters, 64 filters, 16 groups, 1,000 characters per value, and 1,000 list values. A request over a cap gets status 400.
  • Number and date columns also get isBetween, with a [min, max] pair in which either bound can be "". The sample refuses it. Map it before you add such a column.
  • A null cell reads as empty text, so empty, notContains, notEquals, and isNoneOf match it. Add an IS NULL test to these four for a nullable column.
  • The route caps a page at 500 rows. Keep the page size at or below 500. A larger page returns fewer rows than the grid asked for.

Keep the key on the server

The service role key bypasses row-level security, so read it only in the route. Cap the body size at the framework or proxy, because the tree limits do not bound a large JSON text.

The client is the one in the Drizzle example, and the Shareable state section of that page applies as is. Edits go through onSave. Save edits shows the round trip.

On this page