Skip to Content

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 after listSchema.
  • opts.where: a condition always applied, for limiting rows by role.
  • opts.search: the columns search looks in, matching LIKE '%…%' on any of them.
  • opts.filters: query field → column, compared for equality. Empty values are skipped.
  • opts.sort: name in sort → column. Only the names listed here can be sorted on.
  • opts.defaultSort: the order when the client picks none. Defaults to createdAt descending, or id when the table has no createdAt.
  • opts.maxPageSize: the largest page size, 100 if omitted.
  • opts.counts: the name of a key in filters, 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; check warns perf.order-unindexed when one is missing.
  • Joins, grouping, computed columns: write Drizzle with db. listOf is for the plain list.

Next steps

Last updated