Overview Queries

Utilities for building paginated, sortable, filterable list views on top of Drizzle.

OverviewConfig

The configuration object that describes the current view state.

import type { OverviewConfig } from '@repo/db'

const config: OverviewConfig<{ name: string; status: string }> = {
  filters: { name: 'alice', status: 'active' },
  grouping: ['status'],
  sorting: [{ id: 'createdAt', desc: true }],
  pagination: { pageIndex: 0, pageSize: 25 },
}

interface OverviewConfig<T extends Record<string, unknown> = Record<string, unknown>> {
  filters?: T
  grouping?: string[]
  groupingPath?: { columnId: string; value: unknown }[]
  sorting: { id: string; desc: boolean }[]
  pagination: { pageIndex: number; pageSize: number }
}

overviewToQuery

Applies filters, sorting, and pagination to a Drizzle $dynamic() query builder. Returns { data, count }.

Use countSelect in your select to get the total count via a window function.

import { overviewToQuery, countSelect } from '@repo/db'
import { eq } from 'drizzle-orm'

const query = db
  .select({ ...getTableColumns(users), count: countSelect })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .$dynamic()

const { data, count } = await overviewToQuery(query, users, overviewConfig)

Filter Behavior

String values use ilike (case-insensitive partial match)
Enum columns use eq (exact match)
All other types use eq
Columns not found on the table log an error and are skipped

Custom Filter Functions

Override the default filter behavior per field with filterFns:

const { data, count } = await overviewToQuery(query, users, overviewConfig, {
  filterFns: {
    role: (value) => eq(users.role, value),
    search: (value) => ilike(users.name, `%${value}%`),
  },
})

Server-side Grouping

Grouping is opt-in through a server allowlist:

const result = await overviewToQuery(query, users, overviewConfig, {
  groupingColumns: {
    status: users.status,
    roleName: roles.name,
  },
})

When grouping is active, the database returns one hierarchy level using GROUP BY. The response is { grouped: true, data, count, leafCount }; count is the number of nodes at that level and leafCount is the number of filtered leaves below it. Each group contains its full leaf count and a path. Send the path as groupingPath to fetch the next grouped level. Once the path covers all grouping fields, the helper returns paginated leaf rows.

queryWithCount

Runs a data query and a separate count(*) query in parallel. Useful with the relational query API where countSelect cannot be embedded.

import { queryWithCount } from '@repo/db'

const getData = db.query.users.findMany(queryConfig)
const result = await queryWithCount(getData, db, users, queryConfig)
// result.data -- the rows
// result.count -- total matching rows

Parameters

Parameter	Type	Description
`getData`	`Promise<T>`	The data query promise
`db`	`PostgresJsDatabase`	Drizzle db instance (for the count query)
`table`	`PgTableWithColumns`	Table to count from
`queryConfig`	`QueryConfig`	Config containing `where` clause to reuse