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:
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, removemode. parseServerSpecchecks 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, andisNoneOfmatch it. Add anIS NULLtest 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.
Related
- Server rows example: the grid side of this route
- Server rows contract: the request and the answer
- Handle refused saves: the
onSaveround trip