# Drizzle ORM

> Step-by-step walkthrough for building a server-side data table with Drizzle ORM and PostgreSQL

Source: https://data-table.openstatus.dev/docs/drizzle-orm · Docs index: https://data-table.openstatus.dev/llms.txt · Full docs: https://data-table.openstatus.dev/llms-full.txt

A step-by-step guide to wiring up the data table with [Drizzle ORM](https://orm.drizzle.team) and PostgreSQL. See the [live example](/drizzle) and the [Database](/docs/data-layer) page for the concepts behind the implementation.

### File Checklist

Every file you need to create:

| File                            | Purpose                                                          |
| ------------------------------- | ---------------------------------------------------------------- |
| `db/drizzle/schema.ts`          | Drizzle table definition (enums, columns)                        |
| `db/drizzle/index.ts`           | Database client                                                  |
| `app/[route]/column-mapping.ts` | Maps schema keys → Drizzle columns                               |
| `app/[route]/table-schema.tsx`  | UI column config (display, filters, sorting)                     |
| `app/[route]/schema.ts`         | Zod validation + ColumnSchema type                               |
| `app/[route]/search-params.ts`  | URL search param parsers (derived from schema)                   |
| `app/[route]/query-options.ts`  | React Query infinite query config                                |
| `app/[route]/api/route.ts`      | API handler with `createDrizzleHandler`                          |
| `app/[route]/api/ai/route.ts`   | AI filter handler (optional, see [AI Filters](/docs/ai-filters)) |
| `app/[route]/client.tsx`        | Client component                                                 |
| `app/[route]/page.tsx`          | Page wrapper                                                     |

### Step 1: Define Your Drizzle Table

Define your PostgreSQL table with Drizzle's schema builder:

```ts
// db/drizzle/schema.ts
import {
  integer,
  jsonb,
  pgEnum,
  pgTable,
  text,
  timestamp,
  uuid,
} from "drizzle-orm/pg-core";

export const levelEnum = pgEnum("level", ["success", "warning", "error"]);
export const methodEnum = pgEnum("method", ["GET", "POST", "PUT", "DELETE"]);

export const logs = pgTable("logs", {
  uuid: uuid("uuid").defaultRandom().primaryKey(),
  level: levelEnum("level").notNull(),
  method: methodEnum("method").notNull(),
  host: text("host").notNull(),
  pathname: text("pathname").notNull(),
  status: integer("status").notNull(),
  latency: integer("latency").notNull(),
  regions: text("regions").array().notNull(),
  date: timestamp("date", { withTimezone: true }).notNull(),
  timingDns: integer("timing_dns").notNull(),
  timingConnection: integer("timing_connection").notNull(),
  timingTls: integer("timing_tls").notNull(),
  timingTtfb: integer("timing_ttfb").notNull(),
  timingTransfer: integer("timing_transfer").notNull(),
  headers: jsonb("headers").$type<Record<string, string>>().notNull(),
  message: text("message"),
});
```

Set up the database client using the `node-postgres` driver:

```ts
// db/drizzle/index.ts
import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "./schema";

const connectionString = process.env.DATABASE_URL;

if (!connectionString) {
  throw new Error("DATABASE_URL environment variable is not set");
}

export const db = drizzle(connectionString, { schema });
```

> **Note:** If you're using a connection pooler (Supabase transaction mode, PgBouncer, Neon), make sure to use the transaction pooler URL (port `6543` for Supabase). Install `pg` as a dependency: `pnpm add pg @types/pg`.

### Step 2: Column Mapping

The column mapping bridges your table schema keys to Drizzle columns. This is the **only file that couples UI to DB** — one file per table:

```ts
// app/drizzle/column-mapping.ts
import { logs } from "@/db/drizzle/schema";
import type { ColumnMapping } from "@/lib/drizzle";

export const columnMapping = {
  level: logs.level,
  date: logs.date,
  status: logs.status,
  latency: logs.latency,
  method: logs.method,
  host: logs.host,
  pathname: logs.pathname,
  regions: logs.regions,
  "timing.dns": logs.timingDns,
  "timing.connection": logs.timingConnection,
  "timing.tls": logs.timingTls,
  "timing.ttfb": logs.timingTtfb,
  "timing.transfer": logs.timingTransfer,
} satisfies ColumnMapping;
```

Keys can use dot notation (`"timing.dns"`) to map nested UI names to flat database columns (`timingDns`).

### Step 3: Table Schema

Define how each column appears in the UI — labels, display format, filter type, sizing:

```tsx
// app/drizzle/table-schema.tsx
import { col, createTableSchema } from "@/lib/table-schema";

export const tableSchema = createTableSchema({
  // Checkbox filter with enum options
  level: col
    .enum(["success", "warning", "error"])
    .label("Level")
    .filterable("checkbox", {
      options: [
        { label: "success", value: "success" },
        { label: "warning", value: "warning" },
        { label: "error", value: "error" },
      ],
    })
    .size(80),

  // Timerange filter for date column
  date: col
    .timestamp()
    .label("Date")
    .display("timestamp")
    .defaultOpen()
    .size(200)
    .sortable(),

  // Checkbox filter with number options
  status: col
    .number()
    .label("Status")
    .filterable("checkbox", {
      options: [
        { label: "200", value: 200 },
        { label: "400", value: 400 },
        { label: "404", value: 404 },
        { label: "500", value: 500 },
      ],
    })
    .size(60),

  // Slider filter with range
  latency: col
    .number()
    .label("Latency")
    .filterable("slider", { min: 0, max: 5000, unit: "ms" })
    .size(110)
    .sortable(),

  // Text input filter
  host: col.string().label("Host").filterable("input").size(125),

  // ... add more columns as needed
});
```

The filter type you choose here (`checkbox`, `slider`, `input`, `timerange`) determines how the [three-pass filtering strategy](/docs/data-layer#three-pass-filtering) handles each column.

Additional methods: `hidden()` to hide by default, `sortable()` for sort support, `sheet()` for the row detail drawer, `resizable()` for resizable columns.

### Step 4: API Route

The API route uses `createDrizzleHandler` which implements the [three-pass filtering strategy](/docs/data-layer#three-pass-filtering), faceted search, counts, and cursor pagination:

```ts
// app/drizzle/api/route.ts
import { db } from "@/db/drizzle";
import { logs } from "@/db/drizzle/schema";
import { createDrizzleHandler } from "@/lib/drizzle";
import { NextRequest } from "next/server";
import SuperJSON from "superjson";
import { columnMapping } from "../column-mapping";
import { searchParamsCache } from "../search-params";
import { tableSchema } from "../table-schema";

export const dynamic = "force-dynamic";

const handler = createDrizzleHandler({
  db,
  table: logs,
  schema: tableSchema.definition, // Auto-derives slider/facet/date keys
  columnMapping,
  cursorColumn: "date",
  defaultSize: 40,
});

export async function GET(req: NextRequest): Promise<Response> {
  const _search: Map<string, string> = new Map();
  req.nextUrl.searchParams.forEach((value, key) => _search.set(key, value));
  const search = searchParamsCache.parse(Object.fromEntries(_search));

  const result = await handler.execute(search as Record<string, unknown>);

  // Map DB rows to your ColumnSchema shape
  const data = result.data.map((row) => ({
    uuid: row.uuid,
    level: row.level,
    status: row.status,
    latency: row.latency,
    host: row.host,
    date: row.date,
    // ... map all columns
  }));

  return Response.json(
    SuperJSON.stringify({
      data,
      meta: {
        totalRowCount: result.totalRowCount,
        filterRowCount: result.filterRowCount,
        chartData: [], // Add chart data query if needed
        facets: result.facets,
      },
      prevCursor: result.prevCursor,
      nextCursor: result.nextCursor,
    }),
  );
}
```

`createDrizzleHandler` accepts either `schema: tableSchema.definition` (auto-derives filter types) or explicit `sliderKeys`, `facetKeys`, and `dateKeys` arrays when the table schema can't be imported on the server.

> To add AI-powered filtering, create a second route at `app/[route]/api/ai/route.ts` using `createAIFilterHandler` from `@/lib/ai`. It reuses the same `tableSchema` to generate the AI prompt and output schema. See [AI Filters — API Route Setup](/docs/ai-filters#api-route-setup).

### Step 5: Client Component

Wire up `useInfiniteQuery` to `DataTableInfinite`. The key pattern is populating filter fields dynamically from the facet data returned by the API:

```tsx
// app/drizzle/client.tsx
"use client";

import { DataTableInfinite } from "@/components/data-table/data-table-infinite";
import { DataTableStoreProvider, useFilterState } from "@/lib/store";
import { useNuqsAdapter } from "@/lib/store/adapters/nuqs";
import {
  generateColumns,
  generateFilterFields,
  getDefaultColumnVisibility,
} from "@/lib/table-schema";
import { useInfiniteQuery } from "@tanstack/react-query";
import * as React from "react";
import { dataOptions } from "./query-options";
import type { ColumnSchema, FilterState } from "./schema";
import { filterSchema } from "./schema";
import { tableSchema } from "./table-schema";

const columns = generateColumns<ColumnSchema>(tableSchema.definition);
const filterFields = generateFilterFields<ColumnSchema>(tableSchema.definition);
const defaultColumnVisibility = getDefaultColumnVisibility(
  tableSchema.definition,
);

export function Client() {
  const adapter = useNuqsAdapter(filterSchema.definition, { id: "drizzle" });

  return (
    <DataTableStoreProvider adapter={adapter}>
      <ClientInner />
    </DataTableStoreProvider>
  );
}

function ClientInner() {
  const search = useFilterState<FilterState>();

  const { data, isFetching, isLoading, fetchNextPage, hasNextPage, refetch } =
    useInfiniteQuery(dataOptions(search));

  const flatData = React.useMemo(
    () => data?.pages?.flatMap((page) => page.data ?? []) ?? [],
    [data?.pages],
  );

  const lastPage = data?.pages?.[data?.pages.length - 1];
  const facets = lastPage?.meta?.facets;

  // Populate filter options from server-side facet data
  const dynamicFilterFields = React.useMemo(() => {
    return filterFields.map((field) => {
      const facetsField = facets?.[field.value as string];
      if (!facetsField) return field;
      if (field.options && field.options.length > 0) return field;

      const options = facetsField.rows.map(({ value }) => ({
        label: `${value}`,
        value,
      }));

      if (field.type === "slider") {
        return {
          ...field,
          min: facetsField.min ?? field.min,
          max: facetsField.max ?? field.max,
          options,
        };
      }

      return { ...field, options };
    });
  }, [facets]);

  const { sort, size, uuid, cursor, direction, live, ...filter } = search;

  const defaultColumnFilters = React.useMemo(() => {
    return Object.entries(filter)
      .map(([key, value]) => ({ id: key, value }))
      .filter(({ value }) => {
        if (value === null || value === undefined) return false;
        if (Array.isArray(value) && value.length === 0) return false;
        return true;
      });
  }, [filter]);

  return (
    <DataTableInfinite
      columns={columns}
      data={flatData}
      totalRows={lastPage?.meta?.totalRowCount}
      filterRows={lastPage?.meta?.filterRowCount}
      totalRowsFetched={flatData?.length}
      defaultColumnFilters={defaultColumnFilters}
      defaultColumnSorting={sort ? [sort] : undefined}
      defaultColumnVisibility={defaultColumnVisibility}
      filterFields={dynamicFilterFields}
      isFetching={isFetching}
      isLoading={isLoading}
      fetchNextPage={fetchNextPage}
      hasNextPage={hasNextPage}
      refetch={refetch}
      schema={filterSchema.definition}
      adapterType="nuqs"
      meta={{}}
    />
  );
}
```

### Step 6: Page Wrapper

A minimal server component with `HydrationBoundary` for React Query:

```tsx
// app/drizzle/page.tsx
import { getQueryClient } from "@/providers/get-query-client";
import { dehydrate, HydrationBoundary } from "@tanstack/react-query";
import { Client } from "./client";

export const dynamic = "force-dynamic";

export default function Page() {
  return (
    <HydrationBoundary state={dehydrate(getQueryClient())}>
      <Client />
    </HydrationBoundary>
  );
}
```

### Helper Functions Reference

The `@/lib/drizzle` module exports four functions that `createDrizzleHandler` uses internally. You can also use them directly for custom query logic:

```ts
import {
  buildWhereConditions,
  buildOrderBy,
  buildCursorPagination,
  computeFacets,
} from "@/lib/drizzle";
```

- **`buildWhereConditions(mapping, filters, options?)`** — converts filter state to SQL WHERE clauses. Handles `ilike` for strings, `between` for slider ranges, `inArray` for checkboxes, date ranges, and PostgreSQL array overlap (`&&`) for array columns.
- **`buildOrderBy(mapping, sort)`** — returns an `asc` or `desc` SQL clause from a sort descriptor.
- **`buildCursorPagination({ cursor, direction, size, cursorColumn })`** — returns cursor condition, order clause, and a `needsReverse` flag for bidirectional infinite scroll.
- **`computeFacets(db, table, mapping, conditions, facetKeys, options?)`** — computes grouped counts (or min/max for slider keys) via parallel SQL queries.