Kysely Cursor Paginator

pagination.ts

import { SelectQueryBuilder } from "kysely";
import { DB } from "db";

/*
 // From kysely-codegen
 export type Generated<T> = T extends ColumnType<infer S, infer I, infer U>
  ? ColumnType<S, I | undefined, U>
  : ColumnType<T, T | undefined, T>;

 export type Int8 = ColumnType<string, bigint | number | string, bigint | number | string>;
 export interface Account {
  accessToken: string | null;
  expiresAt: Int8 | null;
  id: Generated<string>;
  idToken: string | null;
  provider: string;
  providerAccountId: string;
  refreshToken: string | null;
  scope: string | null;
  sessionState: string | null;
  tokenType: string | null;
  type: string;
  userId: string;
}
 export interface DB {
  account: Account;
 }
*/

export function isValidDate(date: any) {
  return date && Object.prototype.toString.call(date) === "[object Date]" && !isNaN(date);
}

export function isValidPositiveInteger(str: any, allowZero = false) {
  if (str === null || str === undefined) return false;
  if (typeof str !== 'string') str = String(str).trim();
  else str = str.trim();

  const num = Number(str)  // may return NaN
  if (!Number.isInteger(num)) return false  // handles NaN
  return allowZero ? num >= 0 : num > 0;
}


type ValidPositionTypes = string | number
type ValidCursorType = "date" | "string"
type Cursor = { o: number, p: ValidPositionTypes, t: ValidCursorType }

type ConvertedPositionT = Date | string
interface DecodedCursor {
  offset: number,
  position: ConvertedPositionT | null,
  serializedPosition: ValidPositionTypes | null,
  cursorType: ValidCursorType | null
}

export function decodeCursor(cursor: string | null): DecodedCursor {
  let position: ConvertedPositionT | null = null
  let offset = 0
  let cursorType: ValidCursorType | null = null
  let serializedPosition: ValidPositionTypes | null = null
  if (cursor) {
    let tokens: Cursor
    try {
      tokens = JSON.parse(Buffer.from(cursor, "base64").toString("utf-8"))
    } catch (e) {
      throw new Error("Invalid cursor")
    }
    if (!isValidPositiveInteger(tokens.o, true)) {
      throw new Error("Invalid offset")
    }
    if (!tokens.p) {
      throw new Error("Invalid position")
    }
    if (tokens.t === "date") {
      const convertedDate = new Date(tokens.p)
      if (!isValidDate(new Date(tokens.p))) {
        throw new Error("Invalid position")
      }
      position = convertedDate
    } else if (tokens.t === "string") {
      if (typeof tokens.p !== "string") {
        throw new Error("Invalid position")
      }
      position = tokens.p
    } else {
      throw new Error("Invalid cursor type")
    }
    cursorType = tokens.t
    offset = tokens.o
    serializedPosition = tokens.p
  }
  return { offset, position, cursorType, serializedPosition }
}

type KeysWithToString<T> = {
  [K in keyof T]: T[K] extends ConvertedPositionT ? K : never;
}[keyof T]

interface CreateNextCursorProps<ResultType> {
  results: ResultType[]
  pageSize: number
  ordering: KeysWithToString<ResultType>
  cursor: string | null
}

export function createNextCursor<ResultType>(
  { results, ordering, pageSize, cursor }: CreateNextCursorProps<ResultType>
): string | null {
  let nextCursor: Cursor | null = null
  if (!cursor) {
    if (results.length > pageSize) {
      const firstResult = results[0][ordering]
      // Always generate a position; otherwise, if a new record comes in, the offset
      // will include the new record and the next pagination will be shifted by 1
      let position: ValidPositionTypes
      let type: ValidCursorType
      if (isValidDate(firstResult)) {
        position = (firstResult as Date).getTime()
        type = "date"
      } else {
        position = (firstResult as unknown as { toString: () => string }).toString()
        type = "string"
      }
      nextCursor = { o: pageSize, p: position, t: type }
    }
  } else {
    const tokens = decodeCursor(cursor)
    nextCursor = { o: tokens.offset + pageSize, p: tokens.serializedPosition!, t: tokens.cursorType! }
  }
  return nextCursor ? Buffer.from(JSON.stringify(nextCursor)).toString("base64") : null
}

type ExtractRowType<Q> = Q extends SelectQueryBuilder<DB, any, infer R> ? R : never;
type QualifiedColumn<T extends keyof DB> = `${T}.${Extract<keyof DB[T], string>}`;
type ValidPositionColumn<TB extends keyof DB> = keyof DB[TB] | QualifiedColumn<TB>;

interface CursorPaginateProps<
  Q extends SelectQueryBuilder<DB, any, any>,
  R = ExtractRowType<Q>,
  O extends KeysWithToString<R> = KeysWithToString<R>,
  TB extends keyof DB = keyof DB,
> {
  query: Q
  cursor: string | null
  pageSize: number
  ordering: O
  positionColumn: ValidPositionColumn<TB>
}

export async function cursorPaginate<
  Q extends SelectQueryBuilder<DB, any, any>,
  R = ExtractRowType<Q>,
  O extends KeysWithToString<R> = KeysWithToString<R>,
  TB extends keyof DB = keyof DB,
>(
  { query, cursor, pageSize, ordering, positionColumn }: CursorPaginateProps<Q, R, O, TB>
): Promise<{ results: ExtractRowType<Q>[]; nextCursor: string | null }> {
  const { offset, position } = decodeCursor(cursor)

  const rawResults = await query
    .$if(
      !!position,
      (query) => (
        query.where((qb) => qb.ref(positionColumn as string), "<", position!)
      )
    )
    .$if(!!offset, (query) => query.offset(offset))
    // Fetch one more than the page size to determine if there's a next page
    .limit(pageSize + 1)
    .execute()

  const results = rawResults as ExtractRowType<Q>[]

  const nextCursor = createNextCursor<typeof results[number]>(
    { results, ordering, pageSize, cursor }
  )

  return { results: results.slice(0, pageSize), nextCursor }
}

usage.ts

const query = db.selectFrom("account")
  .select([
    "account.id",
    "account.emailVerified", // Type: Date | null
  ])
  .$narrowType<{ emailVerified: Date }>()
  .orderBy("account.emailVerified", "desc")

I tried to base the implementation off Django rest framework and kysely-paginate, but a design difference is that each token from the cursor in the aforementioned libraries are separate url parameters in the URL whereas this implementation encodes everything in base64url as a JSON encapsulation.