listSchema, listOf
listSchema validates a list API’s query: page, search, filters, sort. listOf runs that query against a table and returns { data, total }.
Usage
app/orders/get.ts
import { z } from "@hono/zod-openapi";
import { listSchema } from "app/db";
import { OrderStatus } from "app/shared/order";
import { listOrders } from "./repository";
export const schema = {
query: listSchema({ filters: { status: z.enum(OrderStatus.values).optional() }, sort: ["createdAt", "total"] }),
};
export default (c, { user, query }) => listOrders(user, query);app/orders/repository.ts
import { eq, listOf } from "app/db";
import { orders } from "./orders.table";
export const listOrders = (user, query) =>
listOf(orders, query, {
search: [orders.customerName, orders.code],
filters: { status: orders.status },
sort: { createdAt: orders.createdAt, total: orders.total },
where: user.role === "sales" ? eq(orders.createdBy, user.id) : undefined,
});Signature
app/db.ts
function listSchema(opts?: {
filters?: Record<string, z.ZodType>;
sort?: string[];
pageSize?: number;
maxPageSize?: number;
}): z.ZodObject;
function listOf(table: SQLiteTable, query: object, opts?: {
where?: SQL;
search?: Column[];
filters?: Record<string, Column>;
sort?: Record<string, Column>;
defaultSort?: SQL;
maxPageSize?: number;
counts?: string;
}): Promise<{ data: Row[]; total: number; counts?: Record<string, number> }>;Parameters
listSchema(opts):
filters: extra filter fields, one zod schema each. Fields not declared here are dropped from the query.sort: the column names a client may sort by.pageSize: the default page size, 20 if omitted.maxPageSize: the largest page size, 100 if omitted.
listOf(table, query, opts):
table: the table to read.query: the query afterlistSchema.opts.where: a condition always applied, for limiting rows by role.opts.search: the columnssearchlooks in, matchingLIKE '%…%'on any of them.opts.filters: query field → column, compared for equality. Empty values are skipped.opts.sort: name insort→ column. Only the names listed here can be sorted on.opts.defaultSort: the order when the client picks none. Defaults tocreatedAtdescending, oridwhen the table has nocreatedAt.opts.maxPageSize: the largest page size, 100 if omitted.opts.counts: the name of a key infilters, usually"status". The result also counts rows per value of that field, with every other filter applied, for status tabs with counts.
Returns
listSchema returns a zod object that accepts page (or ProTable’s current), pageSize, search (up to 200 characters), order (asc or desc), sort (only the declared names) and the filters fields.
listOf returns Promise<{ data, total }>: data holds the rows of one page, total the number of rows matching the conditions. With counts it adds counts, such as { new: 3, done: 12 }.
Examples
A list page sends ProTable’s parameters straight to the API with tableRequest (or <ResourceList> from manh/shell). Calling it yourself:
app/orders/page.tsx
const { data, total } = await api.orders.list({ ...params, search: keyword });- Index the columns in
sort;checkwarnsperf.order-unindexedwhen one is missing. - Joins, grouping, computed columns: write Drizzle with db.
listOfis for the plain list.
Next steps
- tableRequest: the UI side of the same list page.
- Database: declare tables and repositories.
Last updated