DataTable Guide

Generic DataTable Guide

Overview

The DataTable component in packages/ui is fully generic and reusable across your entire application. It provides:

• Server-side pagination - Only loads the data needed for the current page
• Server-side sorting - Database handles sorting for optimal performance
• Server-side search - Global search across specified columns
• Server-side filtering - Column-specific filters (coming soon in UI)
• Virtualization - For large datasets (100+ rows)
• Type-safe - Full TypeScript support

---

Architecture

The system has 3 main parts:

1. Backend (API)

• Contract (packages/api/src/features/*/contracts/index.ts) - Defines API schema
• Router (packages/api/src/features/*/router/index.ts) - Implements API logic
• Column Metadata (packages/api/src/features/*/column-metadata.ts) - Defines searchable/sortable/filterable columns
• Query Builder (packages/api/src/features/*/query-builder.ts) - Generic utilities for building queries

2. Shared Types (packages/shared/src/*/filtering.ts)

• Type definitions shared between frontend and backend
• Column metadata interfaces
• Filter operators

3. Frontend (UI)

• DataTable (packages/ui/src/components/data-table.tsx) - Generic table component
• DataTableColumnHeader (packages/ui/src/components/data-table-column-header.tsx) - Sortable column headers
• Page Component (packages/features/src/routes/*/your-page.tsx) - Your page using the table

---

How to Add DataTable to a New Page

Step 1: Create Database Schema (if needed)

packages/db/src/schemas/your-feature.ts

import { pgTable, text, timestamp, integer } from 'drizzle-orm/pg-core';

export const yourTable = pgTable('your_table', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  age: integer('age'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

Step 2: Define Column Metadata (Backend)

This tells the backend which columns are searchable, sortable, and filterable:

packages/api/src/features/your-feature/column-metadata.ts

import type { ColumnMetadata } from '@repo/shared/your-feature/filtering';
import { yourTable } from '@repo/db/schema';
import type { Column } from 'drizzle-orm';

export const yourTableColumns: Record<
  string,
  {
    column: Column;
    metadata: ColumnMetadata;
  }
> = {
  name: {
    column: yourTable.name,
    metadata: {
      key: 'name',
      label: 'Name',
      dataType: 'string',
      filterable: true,
      searchable: true,  // Included in global search
      sortable: true,    // Can be sorted
    },
  },
  email: {
    column: yourTable.email,
    metadata: {
      key: 'email',
      label: 'Email',
      dataType: 'string',
      filterable: true,
      searchable: true,
      sortable: true,
    },
  },
  age: {
    column: yourTable.age,
    metadata: {
      key: 'age',
      label: 'Age',
      dataType: 'number',
      filterable: true,
      searchable: false,  // Not included in global search
      sortable: true,
    },
  },
  createdAt: {
    column: yourTable.createdAt,
    metadata: {
      key: 'createdAt',
      label: 'Created At',
      dataType: 'date',
      filterable: true,
      searchable: false,
      sortable: true,
    },
  },
};

Step 3: Create API Contract

packages/api/src/features/your-feature/contracts/index.ts

import { oc } from '@orpc/contract';
import * as v from 'valibot';

// Helper to coerce string to number (for query parameters)
const numberFromString = v.pipe(
  v.union([v.number(), v.pipe(v.string(), v.transform(Number))]),
  v.number(),
);

const tableQueryInputSchema = v.object({
  page: v.optional(v.pipe(numberFromString, v.integer(), v.minValue(1))),
  pageSize: v.optional(v.pipe(numberFromString, v.integer(), v.minValue(1), v.maxValue(100))),
  sortBy: v.optional(v.string()),
  sortOrder: v.optional(v.picklist(['asc', 'desc'])),
  search: v.optional(v.string()),
});

const yourFeatureContract = oc
  .prefix('/your-feature')
  .tag('your-feature')
  .router({
    listItems: oc
      .route({
        method: 'GET',
        path: '/items',
        summary: 'List all items',
        description: 'Retrieve a paginated list of items with filtering and sorting',
      })
      .input(v.optional(tableQueryInputSchema))
      .output(
        v.object({
          data: v.array(
            v.object({
              id: v.string(),
              name: v.string(),
              email: v.string(),
              age: v.number(),
              createdAt: v.date(),
              updatedAt: v.date(),
            }),
          ),
          total: v.number(),
          page: v.number(),
          pageSize: v.number(),
        }),
      ),
    // ... other routes (create, update, delete)
  });

export default yourFeatureContract;

Step 4: Implement API Router

packages/api/src/features/your-feature/router/index.ts

import { eq, count, asc, desc } from 'drizzle-orm';
import { yourTable } from '@repo/db/schema';
import { protectedProcedure } from '../../../server/orpc';
import { yourTableColumns } from '../column-metadata';
import { buildWhereClause, getSortColumn } from '../query-builder';
import type { ColumnFilter } from '@repo/shared/your-feature/filtering';

const yourFeatureRouter = {
  listItems: protectedProcedure.yourFeature.listItems.handler(
    async ({ context, input }) => {
      const page = input?.page ?? 1;
      const pageSize = input?.pageSize ?? 10;
      const sortBy = input?.sortBy ?? 'createdAt';
      const sortOrder = input?.sortOrder ?? 'desc';
      const search = input?.search;
      const filters = input?.filters as ColumnFilter[] | undefined;

      // Build where clause using generic query builder
      const whereClause = buildWhereClause(yourTableColumns, search, filters);

      // Get total count
      const [totalResult] = await context.db
        .select({ count: count() })
        .from(yourTable)
        .where(whereClause);

      const total = totalResult?.count ?? 0;

      // Get sort column using generic helper
      const orderByColumn = getSortColumn(
        yourTableColumns,
        sortBy,
        yourTable.createdAt,
      );

      // Fetch paginated data
      const itemsQuery = context.db
        .select()
        .from(yourTable)
        .where(whereClause);

      const items = await (sortOrder === 'asc'
        ? itemsQuery.orderBy(asc(orderByColumn))
        : itemsQuery.orderBy(desc(orderByColumn))
      )
        .limit(pageSize)
        .offset((page - 1) * pageSize);

      return {
        data: items,
        total,
        page,
        pageSize,
      };
    },
  ),
  // ... other routes
};

export default yourFeatureRouter;

Step 5: Use Generic Query Builder

The query builder (packages/api/src/features/cash-register/query-builder.ts) is already generic and can be copied or imported for other features:

packages/api/src/features/your-feature/query-builder.ts

// You can copy the entire query-builder.ts from cash-register
// OR import and re-export it if you want to share the same implementation
export * from '../cash-register/query-builder';

Step 6: Create Frontend Page Component

packages/features/src/routes/your-feature/items.tsx

import { Button } from '@repo/ui/components/button';
import { Card, CardContent, CardHeader, CardTitle } from '@repo/ui/components/card';
import { DataTable, DataTableColumnHeader } from '@repo/ui/components/data-table';
import { createColumnHelper } from '@repo/ui/lib/data-table-utils';
import type { createTanstackQueryAPIClient } from '@repo/api/client';
import { PlusIcon } from 'lucide-react';
import React from 'react';
import { useQuery, keepPreviousData } from '@tanstack/react-query';
import type { ColumnDef } from '@tanstack/react-table';

type APIClient = ReturnType<typeof createTanstackQueryAPIClient>;

interface YourPageProps {
  apiClient: APIClient;
}

interface Item {
  id: string;
  name: string;
  email: string;
  age: number;
  createdAt: Date;
  updatedAt: Date;
}

export function YourPage({ apiClient }: YourPageProps) {
  // State for pagination, search, and sorting
  const [page, setPage] = React.useState(1);
  const [pageSize, setPageSize] = React.useState(10);
  const [search, setSearch] = React.useState('');
  const [sorting, setSorting] = React.useState<{ id: string; desc: boolean } | null>(null);

  // Query with proper input wrapping
  const listItemsQuery = useQuery({
    ...apiClient.yourFeature.listItems.queryOptions({
      input: {  // IMPORTANT: Wrap parameters in "input"
        page,
        pageSize,
        search: search || undefined,
        sortBy: sorting?.id,
        sortOrder: sorting ? (sorting.desc ? 'desc' : 'asc') : undefined,
      },
    }),
    placeholderData: keepPreviousData,
  });

  const queryData = listItemsQuery.data as { data: Item[]; total: number; page: number; pageSize: number } | undefined;
  const items = queryData?.data ?? [];
  const total = queryData?.total ?? 0;
  const isLoading = listItemsQuery.isLoading;

  // Define columns
  const columnHelper = createColumnHelper<Item>();
  const columns: ColumnDef<Item, unknown>[] = [
    columnHelper.accessor('name', {
      enableSorting: true,  // Enable sorting
      header: ({ column }: { column: any }) => (
        <DataTableColumnHeader column={column} title="Name" />
      ),
      cell: ({ row }: { row: any }) => (
        <div className="font-medium">{row.getValue('name')}</div>
      ),
    }),
    columnHelper.accessor('email', {
      enableSorting: true,
      header: ({ column }: { column: any }) => (
        <DataTableColumnHeader column={column} title="Email" />
      ),
    }),
    columnHelper.accessor('age', {
      enableSorting: true,
      header: ({ column }: { column: any }) => (
        <DataTableColumnHeader column={column} title="Age" />
      ),
    }),
    // ... more columns
  ];

  return (
    <div className="container mx-auto py-8">
      <Card>
        <CardHeader>
          <CardTitle>Your Items</CardTitle>
        </CardHeader>
        <CardContent>
          <DataTable
            columns={columns}
            data={items}
            query={listItemsQuery}
            loading={isLoading}
            enablePagination={true}
            enableSorting={true}
            enableVirtualization={total > 100}
            pageSize={pageSize}
            searchValue={search}
            onSearchChange={(value) => {
              setSearch(value);
              setPage(1); // Reset to first page on search
            }}
            onPaginationChange={(newPage, newPageSize) => {
              setPage(newPage);
              setPageSize(newPageSize);
            }}
            onSortingChange={(newSorting) => {
              setSorting(newSorting);
              setPage(1); // Reset to first page on sort
            }}
            searchPlaceholder="Search items..."
            emptyMessage="No items found."
          />
        </CardContent>
      </Card>
    </div>
  );
}

---

Key Points

DO:

1. Wrap query parameters in input - ORPC requires this for proper query key generation
2. Use asc() and desc() functions - For proper Drizzle ORM sorting
3. Coerce string to number - Query parameters are always strings
4. Reset page to 1 - When search or sort changes
5. Mark columns as sortable - Use enableSorting: true and <DataTableColumnHeader>

DON’T:

1. Don’t forget to wrap parameters in input object
2. Don’t use inline SQL for sorting (sql\DESC`) - use desc()` function
3. Don’t forget numberFromString helper for pagination parameters
4. Don’t mix client-side and server-side sorting/pagination

---

DataTable Props Reference

interface DataTableProps<TData> {
  columns: ColumnDef<TData, unknown>[];  // Column definitions
  data: TData[];                         // Data to display
  query?: UseQueryResult;                // React Query result (enables server-side features)
  loading?: boolean;                     // Loading state
  error?: Error | null;                  // Error state
  enablePagination?: boolean;            // Enable pagination (default: true)
  enableSorting?: boolean;               // Enable sorting (default: true)
  enableVirtualization?: boolean;        // Enable virtualization (default: false)
  virtualizationThreshold?: number;      // When to virtualize (default: 100)
  pageSize?: number;                     // Page size (default: 10)
  searchValue?: string;                  // Current search value
  onSearchChange?: (value: string) => void;           // Search callback
  onPaginationChange?: (page: number, pageSize: number) => void;  // Pagination callback
  onSortingChange?: (sorting: { id: string; desc: boolean } | null) => void;  // Sorting callback
  searchPlaceholder?: string;            // Search input placeholder
  toolbarActions?: React.ReactNode;      // Custom toolbar actions
  emptyMessage?: string;                 // Empty state message
  className?: string;                    // Custom CSS class
}

---

Examples in Codebase

Look at these files for complete working examples:

1. Products/Articles - packages/features/src/routes/cash-register/articles.tsx
2. Invoices - packages/features/src/routes/cash-register/invoices.tsx
3. Backend - packages/api/src/features/cash-register/

---

Advanced: Column Filtering (Coming Soon)

The backend already supports column-specific filtering via the filters parameter. The UI component for this is not yet implemented but can be added:

// Backend already supports:
{
  filters: [
    { column: 'age', operator: 'gt', value: 18, dataType: 'number' },
    { column: 'name', operator: 'contains', value: 'John', dataType: 'string' },
  ]
}

To add filter UI, create a DataTableFilters component similar to DataTableToolbar.

---

Summary

The DataTable system is fully generic and can be used for any data type:

1. Define your database schema
2. Create column metadata (what’s searchable/sortable)
3. Create API contract with tableQueryInputSchema
4. Implement router using generic buildWhereClause and getSortColumn
5. Use <DataTable> component in your page
6. Wrap query parameters in input object

That’s it! The system handles pagination, sorting, searching, and filtering automatically.