Table Server Query

What it solves

A TanStack Table v9 table in manual mode sends its sorting, filters, search text and page to the server, and the server has to turn that into a database query.

Done by hand, that means checking every column id and operator the client sends, binding values instead of writing them into SQL, escaping % and _ in search text, bounding the page size and offset, running a count with the same WHERE, and writing all of it again for each ORM.

defineTableQuery() takes a column allow-list (type string, enum, number, date or boolean, plus optional field name, sortable, filterable, allowed operators and default operator) and a set of limits. Its parse() reads Table state from a request body, URLSearchParams, a query string or a TanStack Router search object, in the same param format as tanstack-table-url-state, and returns a database-neutral query. Unknown columns, operators not allowed for the column and badly typed values throw a TableQueryError with status 400 and every issue listed with its path; with invalid: 'ignore' they are dropped and listed instead. A request over a limit, such as a page size above maxPageSize (100 by default), is rejected, or capped or dropped in ignore mode. The /drizzle, /prisma, /kysely and /supabase entries turn the query into WHERE, ORDER BY, LIMIT and OFFSET with values bound as parameters, and each has a helper that returns the rows, rowCount and pageCount. The Drizzle, Kysely and Prisma helpers run the rows and count queries concurrently, not in a transaction; run them inside a REPEATABLE READ transaction when the count must match the rows exactly. The definition is also a Standard Schema v1 validator, so a TanStack Start server function can take it in .validator() and receive the parsed query. Tested against real Postgres 17 and SQLite with Drizzle, Prisma 7 with Postgres, Kysely with Postgres, and postgrest-js with PostgREST 13 (214 tests), on the lowest peers drizzle-orm 0.36.4 and kysely 0.27.6, in a TanStack Start 1.169 app in the browser with all four adapters, and from a fresh npm install. An independent package, not made or endorsed by TanStack, Drizzle, Prisma, Kysely or Supabase.

• defineTableQuery(): one column allow-list with a type per column (string, enum, number, date, boolean), the database field name, sortable, filterable, allowed operators and default operator
• parse() reads Table state, URLSearchParams, a query string or a TanStack Router search object; the params (sort, q, page, size, f.<columnId>) match tanstack-table-url-state
• Rejects unknown columns, operators a column does not allow and values of the wrong type with a TableQueryError (status 400, every issue with its path); safeParse() and invalid: 'ignore' for softer handling
• Operators eq, ne, lt, lte, gt, gte, between, in, notIn, contains, startsWith, endsWith, isNull, isNotNull; number and date arrays are ranges with open and swapped ends, and a date-only value means the whole UTC day
• Global search: a case-insensitive contains over the string columns you list, OR-ed, trimmed, 200 characters at most by default
• Values are bound as parameters and column names come only from the allow-list; %, _ and \ in search text are escaped
• Limits with defaults: page size 100, page index 10000, 20 filters, 3 sort columns, 100 values in an in list, 200 characters per string value; going over one is rejected, or capped or dropped with invalid: 'ignore'
• defaultSorting, plus a tiebreaker field added to every ORDER BY so pages stay stable when sort values tie
• Adapters for Drizzle, Prisma, Kysely and Supabase/PostgREST, each with a helper that returns rows, rowCount and pageCount, plus tested REPEATABLE READ recipes for a count that matches the rows
• Standard Schema v1 validator, usable as a TanStack Start server function .validator()
• ESM only, marked side-effect free, no runtime dependencies; drizzle-orm and kysely are optional peers; Node 20 or later

Install

Requires TanStack Table v9.

npm install @amjadiqbal/tanstack-table-server-query

Usage

import { createServerFn } from '@tanstack/react-start';
import { defineTableQuery } from '@amjadiqbal/tanstack-table-server-query';
import { queryDrizzle } from '@amjadiqbal/tanstack-table-server-query/drizzle';

export const ordersQuery = defineTableQuery({
  columns: {
    customer: { type: 'string' },
    status: { type: 'enum', values: ['paid', 'pending', 'refunded'] },
    total: { type: 'number' },
    placedAt: { type: 'date' },
    paid: { type: 'boolean' },
  },
  defaultSorting: [{ id: 'placedAt', desc: true }],
  tiebreaker: 'id',
  globalFilter: { columns: ['customer'] },
  maxPageSize: 50,
});

// ?sort=-total&f.status=paid&q=acme&page=3&size=20
export const getOrders = createServerFn({ method: 'GET' })
  .validator(ordersQuery)
  .handler(({ data }) => queryDrizzle(db, orders, data));
// => { rows, rowCount, pageCount, pageIndex, pageSize }

Setup

drizzle-orm >=0.36.0 and kysely >=0.27.0 are optional peer dependencies, needed only by the /drizzle and /kysely entries. The /prisma and /supabase entries import nothing from their clients: they build plain arguments and call the client you pass in. There are no runtime dependencies and no TanStack Table peer, because the core works on plain state objects; the table itself runs in your client code. The package is ESM only, marked side-effect free, and needs Node 20 or later.

1. 1 npm install @amjadiqbal/tanstack-table-server-query
2. 2 Install the ORM or client you use as usual (drizzle-orm, kysely, @prisma/client, or supabase-js / @supabase/postgrest-js).
3. 3 Define the allow-list once with defineTableQuery() and export it.
4. 4 In the request handler or server function, call parse() (or use the definition as the validator), then pass the result to the adapter helper.
5. 5 On the client, set manualSorting, manualFiltering and manualPagination on the table and pass rowCount from the response.

The column allow-list

Every column the client may sort or filter on is listed under columns, keyed by the Table column id. Anything not listed is rejected. Each column has a type and these optional settings.

• field: the database column or ORM field the adapter uses. It defaults to the column id. For Drizzle and Prisma it is the property name on the table or model; for Kysely and PostgREST it is the database column, and Kysely accepts a qualified name such as o.total.
• sortable and filterable: both true by default.
• ops: the operators the column accepts. Defaults by type: string eq, ne, contains, startsWith, endsWith, in, notIn, isNull, isNotNull; enum eq, ne, in, notIn, isNull, isNotNull; number eq, ne, lt, lte, gt, gte, between, in, notIn, isNull, isNotNull; date the same as number without in and notIn; boolean eq, ne, isNull, isNotNull.
• defaultOp: the operator for a plain value such as f.customer=acme. contains for string columns, eq for the rest.
• values: the allowed values of an enum column, required for that type.
• defaultSorting applies when the request has no sorting. tiebreaker names a field added to the end of every ORDER BY, ascending, unless the query already sorts on it.
• Mistakes in the definition itself (an unknown type, an enum without values, an operator that does not fit the column type, an unknown or non-string column in globalFilter, an unknown column in defaultSorting) throw a plain Error when defineTableQuery() runs.

export const ordersQuery = defineTableQuery({
  columns: {
    customer: { type: 'string' },
    status: { type: 'enum', values: ['paid', 'pending', 'refunded'] },
    total: { type: 'number' },
    placedAt: { type: 'date', field: 'placed_at' },
    paid: { type: 'boolean' },
    email: { type: 'string', ops: ['eq'], defaultOp: 'eq', sortable: false },
  },
  defaultSorting: [{ id: 'placedAt', desc: true }],
  tiebreaker: 'id',
  globalFilter: { columns: ['customer', 'email'] },
  maxPageSize: 50,
});

What parse() reads

parse(input) accepts Table state ({ sorting, columnFilters, globalFilter, pagination }), URLSearchParams, a query string, or a search object such as TanStack Router's search. An object that has any of the four state keys is read as Table state; any other object is read as search params. The result is a database-neutral description: sorting with field names, a list of AND-ed filters, the global filter (or null), pagination with offset and limit, and an issues list.

• Param names are sort, q, page, size and f.<columnId>, the same as tanstack-table-url-state. keys renames them and prefix puts a string in front of each, for two tables on one page. Params with other names are ignored.
• sort is a comma-separated list of column ids, - in front for descending (sort=-total,customer). A column listed twice is used once, at its first position. Duplicates are removed before maxSorts is checked, so sort=a,a,a,b counts as two columns.
• page is 1-based (page=3 is pageIndex 2) and must be a whole number of 1 or more; size must be a whole number of 1 or more.
• URL values are decoded by column type. The global search and string and enum filters keep exactly what was typed, so q=1.50, q=007, q=true and q=null search for those characters. For them a JSON-quoted string (q="42", as tanstack-table-url-state writes it) is unquoted, and string and enum filters also parse a JSON array (f.status=["paid","pending"] is an in filter) or an { op, value } object. number, date and boolean filters parse JSON numbers, true, false and arrays as before, so f.total=[100,500] is a range.
• TanStack Router JSON-parses search params itself before a loader or server function sees them, so a value typed by hand into the address bar can arrive changed, and the package cannot undo that (seen with Router 1.170: ?q=17.0 reaches the server as 17, ?q=null as no search). Numbers and booleans the router hands over are used as text for the global search and string columns. Text typed into a table that uses tanstack-table-url-state is written quoted (q="17.0") and arrives exactly as typed, and a plain query string or URLSearchParams from the request URL is always read exactly.
• With no pagination in the input, the page is the first and the size is defaultPageSize (10).

Filters

A column filter value is either a plain value, read by the column type, or an explicit { op, value }. All filters are AND-ed.

• Plain value: the column's defaultOp. A plain array is a range (between) for number and date columns and in for the others.
• between takes [min, max]. A blank end is open and reversed ends are swapped, as in Table's inNumberRange and inDateRange; it becomes gte and lte.
• Dates are ISO strings or timestamps. A date-only value such as 2026-03-01 is that UTC day: eq matches the whole day, and a date-only upper bound (lte, or the end of a range) includes the whole day by using less than the next day. gt on a date-only value starts at the next day, gte and lt use the start of that day, and ne with a date-only value is refused.
• Numbers are read from numbers or numeric strings, booleans from true, false, "true" or "false", enum values must be one of values, and string columns also take numbers and booleans, as text.
• Empty plain values ('', null, [], [null, null]) are ignored, as Table drops them. An explicit { op } without a value, other than isNull and isNotNull, is an error.
• contains, startsWith and endsWith need a string and are case-insensitive in every adapter.

ordersQuery.parse({
  columnFilters: [
    { id: 'status', value: ['paid', 'pending'] },          // in
    { id: 'total', value: [100, null] },                   // >= 100
    { id: 'placedAt', value: ['2026-01-05', '2026-01-09'] }, // whole end day
    { id: 'customer', value: { op: 'startsWith', value: 'ac' } },
  ],
  globalFilter: 'acme',
  pagination: { pageIndex: 0, pageSize: 25 },
});

Global search

globalFilter: { columns: [...] } turns on global search for the listed columns, which must be string columns. The search text is trimmed and becomes one case-insensitive contains per column, OR-ed together and AND-ed with the column filters. In a URL it is kept exactly as typed (q=1.50 searches "1.50", q=007 "007"); a number or boolean, as a router may hand over, is searched as its text. An empty or all-space search means no search. It is limited to 200 characters by default (globalFilter.maxLength). A search on a table without globalFilter configured is an error.

• Drizzle and Kysely: lower(column) LIKE lower(pattern) ESCAPE '\', with the pattern bound as a parameter. They do not use ILIKE. Tested on PostgreSQL (both) and SQLite (Drizzle).
• Prisma: contains with mode: 'insensitive' by default. Prisma supports that mode on PostgreSQL and MongoDB only, so pass { insensitive: false } elsewhere.
• PostgREST: an or() of ilike filters, each pattern double-quoted so commas, parentheses and quotes in the text cannot change the filter.
• %, _ and \ in the text are escaped with \ in all four adapters, so 50% finds "50%". Prisma passes these to LIKE unescaped, so its adapter escapes them itself (escapeWildcards, on by default). Prisma's query has no ESCAPE clause, so this relies on \ being the database's default LIKE escape character, as it is on PostgreSQL, the only database the Prisma adapter is tested on.
• PostgREST reads * in an ilike pattern as a wildcard, so a * typed into the search box matches anything in that position on the Supabase adapter.

Errors and limits

parse() collects every problem, not only the first, and throws one TableQueryError. Its status is 400, issues lists each problem with its path (for example columnFilters.0: unknown column "note"), and the message joins them. safeParse() returns { success: false, error } instead of throwing. With invalid: 'ignore' nothing is thrown: bad parts are dropped and listed in query.issues, which suits hand-edited URLs.

• maxPageSize, default 100: a larger page size is rejected; in ignore mode it is capped at the maximum.
• maxPageIndex, default 10000: bounds OFFSET; rejected, or capped in ignore mode.
• maxFilters 20 and maxSorts 3: more is rejected; in ignore mode the extra ones are left out. Duplicate sort columns are removed before maxSorts is counted.
• maxListLength 100 for in and notIn, maxStringLength 200 for string values, globalFilter.maxLength 200: more is rejected; in ignore mode that filter or search is dropped.
• defaultPageSize is 10, like TanStack Table.
• Adapters throw too: the Drizzle adapter when a field is not a column of the table or field map, and querySupabase() with the PostgREST error when the request fails.

import { TableQueryError } from '@amjadiqbal/tanstack-table-server-query';

try {
  const query = ordersQuery.parse(new URL(request.url).searchParams);
} catch (e) {
  if (e instanceof TableQueryError) {
    // e.status === 400
    // e.issues: [{ path: ['columnFilters', 0], message: 'unknown column "note"' }]
  }
}

Adapters

Each adapter has its own entry point. The helpers return { rows, rowCount, pageCount, pageIndex, pageSize }, with pageCount computed as rowCount divided by pageSize, rounded up. The Drizzle, Kysely and Prisma helpers run the page query and a count with the same WHERE as two concurrent queries, not in a transaction (see Consistent counts). The Supabase helper gets the count in the same request, except for a page past the end.

• Drizzle (/drizzle): queryDrizzle(db, table, query) selects all columns of a table. toDrizzle(query, source) returns where, orderBy, limit and offset for a select you write yourself, and source may be a map of field names to columns or SQL expressions for joins and computed columns. drizzleWhere() returns only the WHERE. Field names are the table's property names.
• Prisma (/prisma): queryPrisma(prisma.order, query, options) runs findMany and count({ where }); options.extra (select, include) is merged into findMany. toPrisma() returns { where, orderBy, skip, take } as plain objects and prismaWhere() only the where. Options: insensitive (default true) and escapeWildcards (default true).
• Kysely (/kysely): queryKysely(db, 'orders', query) selects all columns of a table with a countAll() beside it. applyKysely(qb, query) adds WHERE, ORDER BY, LIMIT and OFFSET to any select, applyKyselyWhere() only the WHERE, and kyselyWhere(eb, query) returns the expression. Fields are referenced with sql.ref, so qualified names work.
• Supabase / PostgREST (/supabase): querySupabase(supabase.from('orders'), query, columns) selects with count: 'exact' and applies filters, order and range(). PostgREST answers a page past the end with 416; the helper then sends a second, head-only count request and returns no rows with the right count. applySupabase() and applySupabaseFilters() work on a select builder you create. notIn values are quoted by the adapter; in values go through the client's in() method.

import { toDrizzle } from '@amjadiqbal/tanstack-table-server-query/drizzle';

const { where, orderBy, limit, offset } = toDrizzle(query, {
  total: orders.total,
  customer: customers.name,
});
const rows = await db.select().from(orders)
  .innerJoin(customers, eq(orders.customerId, customers.id))
  .where(where).orderBy(...orderBy).limit(limit).offset(offset);

Consistent counts

queryDrizzle(), queryKysely() and queryPrisma() run the rows query and the count query at the same time (Promise.all), not in a transaction. If rows are written in between, rowCount can disagree with the rows by a few. When that matters, call the same helper inside a REPEATABLE READ transaction, so both queries read one snapshot.

• Tested on PostgreSQL 17 for all three: inside the transaction, a row inserted from another connection between two reads does not change the count or the rows. A control test without a transaction sees the new row.
• querySupabase() gets rows and count from one PostgREST request, except for a page past the end, where it sends a second, head-only count request.

// Drizzle (node-postgres)
const page = await db.transaction(
  (tx) => queryDrizzle(tx, orders, query),
  { isolationLevel: 'repeatable read' },
);

// Kysely
const page = await db.transaction().setIsolationLevel('repeatable read')
  .execute((trx) => queryKysely(trx, 'orders', query));

// Prisma
const page = await prisma.$transaction(
  (tx) => queryPrisma(tx.order, query),
  { isolationLevel: 'RepeatableRead' },
);

TanStack Start

The object defineTableQuery() returns implements Standard Schema v1: its validate() runs safeParse() and returns the parsed query, or the issues. A TanStack Start server function can therefore use the definition as its validator and receive the parsed query as data; there is no separate Start helper. The newest Start on npm on 2026-10-23, @tanstack/react-start 1.169.4, names that method .validator(). How a failed validation reaches the client and with which HTTP status is decided by Start; if you need a 400 response, take the raw input and call safeParse() in the handler.

• Pair it with tanstack-table-url-state to keep the table state in the URL: both packages use the same params, so the route's search can be passed to the server function from the loader without mapping.
• On the client, run the table with manualSorting, manualFiltering and manualPagination, and set rowCount from the response.

export const getOrders = createServerFn({ method: 'GET' })
  .validator(ordersQuery)
  .handler(({ data }) => queryPrisma(prisma.order, data));

export const Route = createFileRoute('/orders')({
  validateSearch: validateTableSearch(),
  loaderDeps: ({ search }) => ({ search }),
  loader: ({ deps }) => getOrders({ data: deps.search }),
  component: Orders,
});

Compatibility and testing

Tested against real Postgres 17 and SQLite with Drizzle, Prisma 7 with Postgres, Kysely with Postgres, and postgrest-js with PostgREST 13 (214 tests), on the lowest peers drizzle-orm 0.36.4 and kysely 0.27.6, in a TanStack Start 1.169 app in the browser with all four adapters, and from a fresh npm install. Table Server Query is an independent package and is not made or endorsed by TanStack, Drizzle, Prisma, Kysely or Supabase.

• Peer ranges: drizzle-orm >=0.36.0 and kysely >=0.27.0, both optional. No runtime dependencies.
• The 214 tests are 30 unit tests of the parser and helpers, plus 184 adapter tests: the same 34 cases run on each of the five database setups and are compared row by row with a reference implementation, along with a few adapter-specific checks and the three REPEATABLE READ recipes with their control.
• The adapter cases cover sorting, multi-sort with the tiebreaker, paging including a page past the end, each operator, ranges, dates, booleans, nulls, global search, and search text and in / notIn values with %, _, \, quotes, apostrophes, commas and parentheses.
• Supabase was tested through postgrest-js against a local PostgREST, not a hosted Supabase project.
• MySQL is not tested and not supported yet. The Drizzle and Kysely adapters write ESCAPE '\', and in MySQL's default SQL mode a backslash inside a string literal is itself an escape, so that clause is likely to fail there.
• Not tested: Prisma on SQLite, Kysely on SQLite, and Solid, Vue or Svelte tables.

Support

Found a bug or want a feature? Open an issue on GitHub. For help adding it to your own project, use the quote form above.