Back

Search Docs

Search through documentation...

OpenStatus Logo

Drizzle ORM

A step-by-step guide to wiring up the data table with Drizzle ORM and PostgreSQL. See the live example and the Database page for the concepts behind the implementation.

File Checklist

Every file you need to create:

FilePurpose
db/drizzle/schema.tsDrizzle table definition (enums, columns)
db/drizzle/index.tsDatabase client
app/[route]/column-mapping.tsMaps schema keys → Drizzle columns
app/[route]/table-schema.tsxUI column config (display, filters, sorting)
app/[route]/schema.tsZod validation + ColumnSchema type
app/[route]/search-params.tsURL search param parsers (derived from schema)
app/[route]/query-options.tsReact Query infinite query config
app/[route]/api/route.tsAPI handler with createDrizzleHandler
app/[route]/api/ai/route.tsAI filter handler (optional, see AI Filters)
app/[route]/client.tsxClient component
app/[route]/page.tsxPage wrapper

Step 1: Define Your Drizzle Table

Define your PostgreSQL table with Drizzle's schema builder:

// 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:

// 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:

// 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).

This mapping is also the projection: the handler selects with it, so rows come back keyed by your schema keys. No caller ever sees timingDns. A key that is missing from the mapping throws at handler construction with the exact snippet to paste — it used to make the filter silently stop filtering.

Step 3: Table Schema

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

// 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 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, faceted search, counts, and cursor pagination:

// app/drizzle/api/route.ts
import { db } from "@/db/drizzle";
import { logs } from "@/db/drizzle/schema";
import { createDrizzleHandler } from "@/lib/drizzle";
import { defineFilters } from "@/lib/filters";
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,
  filters: defineFilters(tableSchema.definition), // declared filter semantics
  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>);
 
  // No remap needed — rows are already keyed by schema keys.
  const data = result.data as unknown as ColumnSchema[];
 
  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,
    }),
  );
}

filters carries the declared filter semantics — the slider, facet, and date key groupings are derived from it. When tableSchema lives in a "use client" file and can't be imported on the server, pass the serialized schema instead: defineFilters(schemaJson). The declaration crosses the boundary as data, so the server still gets real semantics rather than three untyped string[]s.

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.

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:

// 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:

// 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:

import {
  buildWhereConditions,
  buildOrderBy,
  buildCursorPagination,
  computeFacets,
} from "@/lib/drizzle";
  • buildWhereConditions(filters, values, mapping, selection?) — converts filter state to SQL WHERE clauses. It never inspects the shape of a filter value: filters.plan() turns declared semantics into a closed set of six operations, and each compiles to exactly one SQL form (ilike, =, IN, &&, BETWEEN, or a date range).
  • 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.
  • evaluateIntervalMs(durationMs) — the bucket-size ladder used for time-series aggregation.

Writing your own aggregate SQL

result.scope is one typed handle onto the query the handler just ran, so a chart or rollup query does not have to re-derive anything:

const { db, table, where, range, bucketMs } = result.scope;
fieldwhat it is
wherethe fully filtered set
whereWithoutSlidersthe set slider bounds are computed over
rangeresolved from an explicit date filter, else MIN/MAX of the set
bucketMsbucket size for range, interval ladder already applied
columnsthe column mapping

This replaces the older result.allConditions, which exposed just enough to make every caller rebuild the range, the bucket size, and the WHERE composition by hand.

Data LayerFeatures