Kysely Sort, Filter and Paginate

index.ts

import { ComparisonOperatorExpression, OperandValueExpressionOrList, ReferenceExpression, SelectQueryBuilder, Kysely, PostgresDialect, ColumnType, Generated, Insertable, Selectable, Updateable } from 'kysely'
import { OrderByDirection } from 'kysely/dist/cjs/parser/order-by-parser'
import { Pool } from 'pg'

// Types
export interface Database {
  person: PersonTable
  pet: PetTable
}

export interface PersonTable {
  id: Generated<number>
  first_name: string
  gender: 'man' | 'woman' | 'other'
  last_name: string | null
  created_at: ColumnType<Date, string | undefined, never>
}

export type Person = Selectable<PersonTable>
export type NewPerson = Insertable<PersonTable>
export type PersonUpdate = Updateable<PersonTable>

export interface PetTable {
  id: Generated<number>
  name: string
  owner_id: number
  species: 'dog' | 'cat'
}

export type Pet = Selectable<PetTable>
export type NewPet = Insertable<PetTable>
export type PetUpdate = Updateable<PetTable>

// Database
const dialect = new PostgresDialect({
  pool: async () => new Pool({
    database: 'test',
    host: 'localhost',
    user: 'admin',
    port: 5434,
    max: 10,
  })
})

export const db = new Kysely<Database>({
  dialect,
})


// Sort, Filter and Pagination
export type Sort<DB, TB extends keyof DB = keyof DB> = { column: ReferenceExpression<DB, TB>; direction: OrderByDirection }
export type Filter<DB, TB extends keyof DB = keyof DB> = {
	column: ReferenceExpression<DB, TB>
	op: ComparisonOperatorExpression
	value: OperandValueExpressionOrList<DB, TB, ReferenceExpression<DB, TB>>
}
export type Pagination = { limit: number; offset: number }

export interface Options<DB, TB extends keyof DB> {
	sort?: Sort<DB, TB>[]
	filter?: Filter<DB, TB>[]
	pagination?: Pagination
}

export const sortFilterPaginationQuery = <DB, TB extends keyof DB, O>(query: SelectQueryBuilder<DB, TB, O>, options?: Options<DB, TB>) => {
	if (options) {
		if (options.sort) {
			query = sortQuery(query, options.sort)
		}

		if (options.filter) {
			query = filterQuery(query, options.filter)
		}

		if (options.pagination) {
			query = paginateQuery(query, options.pagination)
		}
	}

	return query
}

export const sortQuery = <DB, TB extends keyof DB, O>(query: SelectQueryBuilder<DB, TB, O>, sort: Sort<DB, TB>[]) => {
	for (const s of sort) {
		query = query.orderBy(s.column, s.direction)
	}

	return query
}

export const filterQuery = <DB, TB extends keyof DB, O>(query: SelectQueryBuilder<DB, TB, O>, filter: Filter<DB, TB>[]) => {
	for (const f of filter) {
		query = query.where(f.column, f.op, f.value)
	}

	return query
}

export const paginateQuery = <DB, TB extends keyof DB, O>(query: SelectQueryBuilder<DB, TB, O>, pagination: Pagination) => {
	return query.offset(pagination.offset).limit(pagination.limit)
}


// Usage
const queryBase = db.selectFrom('person').select('id')

const sort: Sort<Database>[] = [{ column: 'first_name', direction: 'asc' }]
const filter: Filter<Database>[] = [{ column: 'last_name', op: '=', value: 'Jones' }]
const pagination = { limit: 10, offset: 0 }
const query = sortFilterPaginationQuery(queryBase, { sort, filter, pagination })

const { sql, parameters } = query.compile()

console.log({ sql, parameters })

db.destroy()