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

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 { 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.

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, start, 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(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.
Data LayerFeatures
GitHubXBluesky

Powered by OpenStatus