react-datatable

Use this guide when your endpoint is wired up and the next job is turning raw OnlineQueryInput state into a safe backend plan.

This page stops at the planner output. It explains buildBackendQueryState() and buildBackendQueryPlan(). It does not cover the Drizzle execution layer; that lives in Server query execution.

Plan the query

The planner helpers split the request into two stages:

buildBackendQueryState() normalizes raw table state into one backend-friendly shape.
buildBackendQueryPlan() validates that shape against your server column map and returns either a flat plan or a grouped plan.

That split is the seam between UI state and backend execution.

Step 1: Normalize the request with `buildBackendQueryState()`

import { buildBackendQueryState } from "react-datatable-server"

const query = buildBackendQueryState({
  filters: input.filters,
  sorting: input.sorting,
  globalFilter: input.globalFilter,
  filterMode: "AND",
  grouping: input.grouping,
})

The returned BackendQueryState does four important cleanup jobs:

keeps filters as one typed list
preserves sorting in request order
trims and deduplicates grouping columns
normalizes grouped expansion state into a stable shape

That matters because saved views, persisted state, and live requests should all converge on the same backend query model.

Grouping columns are trimmed and deduplicated

export function normalizeGroupingColumns(columns: string[]): string[] {
  const seen = new Set<string>()
  const normalized: string[] = []

  for (const column of columns) {
    const trimmed = column.trim()
    if (!trimmed || seen.has(trimmed)) {
      continue
    }

    seen.add(trimmed)
    normalized.push(trimmed)
  }

  return normalized
}

So noisy input like this:

["status", " plan ", "status", ""]

becomes this:

["status", "plan"]

Group expansion becomes one stable override model

{
  defaultExpanded: true,
  overrides: {
    "status:inactive": false,
  },
}

That shape is what later grouped execution uses when it decides which groups emit only headers and which emit nested data rows.

Step 2: Define which server columns support which operations

buildBackendQueryPlan() can only validate the request if you declare what the backend actually supports.

That is the job of defineServerColumns().

import { defineServerColumns } from "react-datatable-server"

export const orderServerColumns = defineServerColumns({
  company: {
    id: "company",
    filter: {
      type: "text",
      expression: { kind: "column", columnId: "company" },
    },
    search: {
      expression: { kind: "column", columnId: "company" },
    },
    sort: {
      expression: { kind: "column", columnId: "company" },
    },
    group: {
      keyExpression: { kind: "column", columnId: "company" },
      orderExpressions: [{ kind: "column", columnId: "company" }],
    },
  },
  renewal: {
    id: "renewal",
    filter: {
      type: "date",
      expression: { kind: "derived", key: "renewalDate" },
    },
    sort: {
      expression: { kind: "derived", key: "renewalDate" },
    },
  },
})

Each column definition declares which planner operations are valid:

filter
search
sort
group

If a column omits one of those sections, that operation is unsupported for that column.

Step 3: Build the validated plan with `buildBackendQueryPlan()`

import { buildBackendQueryPlan } from "react-datatable-server"

const plan = buildBackendQueryPlan({
  navigationMode: input.mode,
  limit: input.limit,
  offset: input.offset,
  query,
  columns: orderServerColumns,
  tieBreakers: [
    { columnId: "createdAt", expression: { kind: "column", columnId: "created_at" } },
    { columnId: "id", expression: { kind: "column", columnId: "id" } },
  ],
})

The planner compiles four major concerns before execution starts:

compileFilters()
compileGlobalSearch()
compileSorting()
compileGrouping() when grouping is active

This is the point where table-owned state becomes a backend-owned execution contract.

What the planner validates

Window validation

The planner rejects invalid windows before the query layer runs:

if (!Number.isInteger(limit) || limit <= 0) {
  throw new QueryPlannerValidationError("Query limit must be a positive integer.")
}

if (!Number.isInteger(offset) || offset < 0) {
  throw new QueryPlannerValidationError("Query offset must be a non-negative integer.")
}

Filter validation

Each compiled filter becomes a QueryPlanFilter entry with:

columnId
the validated filter payload
the backend expression that execution should compile

If the frontend sends a filter type that the backend did not declare, the planner fails early instead of producing half-valid SQL.

grouping columns are unique
each requested grouping column is actually groupable
each grouped column exposes both a key expression and group ordering expressions

A grouped plan should either be valid or fail clearly. It should not degrade into a half-grouped response.

What the planner returns

The planner returns one of two executable contracts.

Flat plan

{
  kind: "flat_window",
  navigationMode: "pagination" | "infinite",
  limit,
  offset,
  filters,
  filterMode,
  sorting,
  globalSearch,
}

Grouped plan

{
  kind: "grouped_window",
  navigationMode: "pagination" | "infinite",
  limit,
  offset,
  filters,
  filterMode,
  sorting,
  globalSearch,
  grouping: {
    columns: [
      {
        columnId: "status",
        keyExpression: { kind: "column", columnId: "status" },
        orderExpressions: [{ kind: "column", columnId: "status" }],
      },
    ],
    showEmptyGroups: false,
    expansion: {
      defaultExpanded: true,
      overrides: {},
    },
  },
}

The grouped plan carries the exact grouping metadata that the executor must honor later.

What this page does not do

The planner does not:

fetch a flat page from the database
count grouped summaries
decide how showEmptyGroups becomes rendered group headers
insert loading rows for virtual scrolling
emit OnlineQueryResponse<TData>

Those are execution concerns.

In the example app, they live in separate execution modules. In the showcase app, those files are:

site/src/db/showcase-flat-executor.ts
site/src/db/showcase-grouped-summary.ts
site/src/db/showcase-grouped-executor.ts
site/src/db/showcase-online-query.ts

Verify planning before moving on

Before you call the planning layer done, confirm that:

invalid limit and offset values fail clearly
unsupported filter columns fail clearly
filter type mismatches fail clearly
search only uses declared search expressions
sorting appends deterministic tie-breakers
unsupported grouping columns fail clearly
grouped requests preserve expansion state in the returned plan

Then continue to Server query execution, where the real Drizzle and grouped-row complexity starts.

Server query planning