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' },
  sorting: [{ id: 'createdAt', desc: true }],
  pagination: { pageIndex: 0, pageSize: 25 },
}

interface OverviewConfig<T extends Record<string, unknown> = Record<string, unknown>> {
  filters?: T
  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}%`),
  },
})

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