react-datatable

Use this guide when online.query should call your server instead of reading rows from the browser.

This page is about the endpoint boundary. It should authenticate, normalize input, choose the right execution path, and return OnlineQueryResponse<TData>. It should not hide planner or SQL complexity inside the route handler.

Start with the request and response contract

import type {
  OnlineQueryInput,
  OnlineQueryResponse,
  OnlineTableRow,
} from "react-datatable-server"

The endpoint receives table interaction state, not auth state:

mode, limit, and offset
filters, sorting, and globalFilter
optional grouping

It returns backend-owned row output:

rows
totalDataRows
totalRenderedRows
hasMore
optional grouping
optional facets

OnlineQueryInput leaves filterMode, tenant scope, and permissions to the server so you can choose them in your own backend policy layer.

Keep the route thin

A good route does five jobs in order:

authenticate and resolve workspace scope
parse OnlineQueryInput
normalize and plan the query
delegate execution by plan kind
serialize OnlineQueryResponse<TData>

import { NextRequest, NextResponse } from "next/server"
import type { OnlineQueryInput } from "react-datatable-server"
import { buildBackendQueryPlan, buildBackendQueryState } from "react-datatable-server"
import { orderServerColumns } from "@/server/datatable/order-columns"
import { executeOrderQuery } from "@/server/datatable/order-query-service"
import { requireWorkspaceAccess } from "@/server/auth"

export async function POST(request: NextRequest) {
  const session = await requireWorkspaceAccess(request)
  const input = (await request.json()) as OnlineQueryInput

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

  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" } },
    ],
  })

  const response = await executeOrderQuery({
    db: session.db,
    workspaceId: session.workspaceId,
    plan,
  })

  return NextResponse.json(response)
}

Normalize first, then plan, then execute

Do not compile SQL directly from the raw request body.

buildBackendQueryState() gives you one stable backend query shape. buildBackendQueryPlan() validates that shape against your supported server columns. Only then should your execution layer touch Drizzle or SQL.

That separation matters because the same backend state model also powers:

live online requests
saved-view replay
persisted-state replay
grouped expansion restoration

Split execution by plan kind

Grouped requests need their own counting, ordering, row shaping, and continuity rules.

import type { BackendQueryPlan, OnlineQueryResponse } from "react-datatable-server"

export async function executeOrderQuery(args: {
  db: Database
  workspaceId: string
  plan: BackendQueryPlan
}): Promise<OnlineQueryResponse<OrderRow>> {
  if (args.plan.kind === "grouped_window") {
    return executeGroupedOrderQuery(args)
  }

  return executeFlatOrderQuery(args)
}

The next two guides cover those inner layers separately on purpose:

Return the exact row shape the table expects

Flat mode returns only data rows:

return {
  rows: pageRows.map((row) => ({
    type: "data",
    rowId: row.id,
    item: row,
    groupPath: [],
  })),
  totalDataRows,
  totalRenderedRows: totalDataRows,
  hasMore,
}

Grouped mode returns structural headers too:

return {
  rows: [
    {
      type: "group-header",
      groupId: "status:active",
      columnId: "status",
      value: "active",
      depth: 0,
      count: 42,
      groupPath: [],
    },
    {
      type: "data",
      rowId: order.id,
      item: order,
      groupPath: ["status:active"],
    },
  ],
  totalDataRows: 420,
  totalRenderedRows: 431,
  hasMore: true,
  grouping: {
    groups: {
      "status:active": {
        total: 42,
        renderedRowCount: 43,
      },
    },
  },
}

In grouped mode, totalRenderedRows includes headers and grouping describes the full grouped result across the dataset.

Keep tenant scope outside the datatable request

The client should control table interactions. The server should control data access.

Apply tenant, workspace, and permission scope before search, filters, and sorting run:

const baseWhere = eq(orders.workspaceId, workspaceId)

Connect the endpoint to `online.query`

import type { OnlineQueryInput, OnlineQueryResponse } from "react-datatable-server"

async function queryOrders(input: OnlineQueryInput): Promise<OnlineQueryResponse<OrderRow>> {
  const res = await fetch("/api/orders/datatable-query", {
    method: "POST",
    headers: { "content-type": "application/json" },
    body: JSON.stringify(input),
  })

  if (!res.ok) {
    throw new Error("Failed to query orders")
  }

  return res.json()
}

This example uses POST so the table can send structured query input as JSON. React Query can still cache this response by queryKey, as the landing page demo does. Use GET instead if you need normal browser or CDN cache semantics.

Verify the endpoint boundary before you move on

Before you call the endpoint done, confirm that:

auth and workspace scope are server-owned
raw input is normalized before planning
flat and grouped plans go through different executors
grouped responses include grouping and correct totalRenderedRows
facets are returned when your filter UI depends on them
the route handler stays thin enough that planner and SQL logic are testable outside HTTP

Server query endpoint