gridcn2.0.0
Server and data examplesORM examples

Prisma example

Build a route handler that maps the sort, filter, search text, and row range of a server grid onto a Prisma 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 User model with a unique id and the text fields name, email, and role.

The route

Each grid operator maps to a function that builds a Prisma where object. The route follows the server rows contract. 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 type { Prisma } from "@prisma/client";
import { prisma } from "~/lib/prisma";

const COLUMNS = new Set<string>(["name", "email", "role"]);
const text = (
  id: string,
  op: string,
  value: string,
): Prisma.UserWhereInput => ({
  [id]: { [op]: value, mode: "insensitive" as const },
});

const anyOf = (id: string, values: string[]) =>
  values.map((value) => text(id, "equals", value));

type Build = (id: string, v: any) => Prisma.UserWhereInput;

const OPERATORS: Record<string, Build> = {
  contains: (id, v) => text(id, "contains", v),
  notContains: (id, v) => ({ NOT: text(id, "contains", v) }),
  startsWith: (id, v) => text(id, "startsWith", v),
  endsWith: (id, v) => text(id, "endsWith", v),
  equals: (id, v) => text(id, "equals", v),
  notEquals: (id, v) => ({ NOT: text(id, "equals", v) }),
  gt: (id, v) => ({ [id]: { gt: v } }),
  gte: (id, v) => ({ [id]: { gte: v } }),
  lt: (id, v) => ({ [id]: { lt: v } }),
  lte: (id, v) => ({ [id]: { lte: v } }),
  empty: (id) => ({ [id]: "" }),
  notEmpty: (id) => ({ NOT: { [id]: "" } }),
  isAnyOf: (id, v) => ({ OR: anyOf(id, v) }),
  isNoneOf: (id, v) => ({ NOT: { OR: anyOf(id, v) } }),
};

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

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;
}

function toWhere(node: FilterNode): Prisma.UserWhereInput {
  if (!isFilterGroup(node)) {
    return OPERATORS[node.operator](node.columnId, node.value);
  }
  const parts = node.filters.map(toWhere);
  if (parts.length === 0) return {};
  return node.join === "or" ? { OR: parts } : { AND: parts };
}

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 search = ["name", "email"].map((id) =>
    text(id, "contains", spec.searchText),
  );
  const where: Prisma.UserWhereInput = {
    AND: [
      spec.searchText ? { OR: search } : {},
      ...spec.filters.map(toWhere),
    ],
  };

  const [totalRowCount, rows] = await Promise.all([
    prisma.user.count({ where }),
    prisma.user.findMany({
      where,
      orderBy: [
        ...spec.sorts.map((s) => ({ [s.columnId]: s.direction })),
        { id: "asc" },
      ],
      skip: start,
      take: Math.min(end - start, 500),
    }),
  ]);
  return Response.json({ rows, totalRowCount });
}

About the sample:

  • isFilterGroup(node) is true for a group and false for a filter row.
  • The grid sends every value as text. A numeric field needs Number(value) in its operator.
  • The grid compares text without case. The sample sets mode: "insensitive" on the text operators, which Prisma supports on PostgreSQL, CockroachDB, and MongoDB. On other databases, remove mode.
  • 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.
  • An empty group becomes {}, because the grid treats an empty group as a match for every row.

Cap the request body size at the framework or proxy. The limits of parseServerSpec 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