Skip to content

About

High-performance, database-agnostic keyset pagination and universal search library for Drizzle ORM on Node.js (18+) and Bun.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

12 Commits

Folders and files

Repository files navigation

KeysetEngine for Drizzle ORM

License: MIT Runtime ORM

High-performance, database-agnostic keyset pagination and universal search library for Drizzle ORM on Node.js (18+) and Bun.

Direct TypeScript port of the .NET KeysetEngine, preserving all core mathematical guarantees, bidirectional navigation, and cryptographic token security.


Why Keyset Pagination?

Keyset pagination eliminates the severe O(N) query degradation inherent to SQL OFFSET on large datasets by establishing stable, indexed boundaries using composite keys and opaque cursors:

  • True O(1) Keyset Pagination: No slow OFFSET scans; uses nested boolean comparison trees compatible with PostgreSQL, SQLite, and MySQL.
  • Bidirectional Traversal: Seamless forward and backward page navigation with automated in-memory ordering reversal.
  • Tamper-Proof Opaque Cursors: Cryptographic protection via authenticated encryption (AES-256-GCM via node:crypto) with purpose and entity isolation.
  • Dynamic Multi-Column Search: Search across multiple properties without breaking keyset boundaries or client-side evaluation.
  • Zero External Runtime Dependencies: Uses native node:crypto and peer-depends strictly on drizzle-orm.

Installation

# Using bun
bun add keysetengine-drizzle drizzle-orm

# Using pnpm
pnpm add keysetengine-drizzle drizzle-orm

# Using npm
npm install keysetengine-drizzle drizzle-orm

Quickstart

1. Define Drizzle Table

import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core';
// Or pgTable from 'drizzle-orm/pg-core'

export const orders = sqliteTable('orders', {
  id: integer('id').primaryKey(),
  customerName: text('customer_name').notNull(),
  orderNumber: text('order_number').notNull(),
  createdAt: text('created_at').notNull(),
  amount: integer('amount').notNull(),
});

2. Configure Keyset Boundaries

import { createKeysetConfig } from 'keysetengine-drizzle';
import { orders } from './schema.js';

export const orderKeyset = createKeysetConfig(orders, {
  sortBy: orders.createdAt,
  sortDirection: 'desc', // default 'asc'
  tieBreaker: orders.id,  // Must be unique and non-nullable
  searchIn: [orders.customerName, orders.orderNumber],
  entityPurpose: 'orders',
});

3. Query Paginated Data

import { paginateKeyset, CryptoCursorProtector } from 'keysetengine-drizzle';
import { db } from './db.js';
import { orderKeyset } from './keyset.js';

// Production cryptographic protector (AES-256-GCM + HKDF)
const protector = new CryptoCursorProtector(process.env.CURSOR_ENCRYPTION_SECRET!);

// In your route handler (Express, Hono, Elysia, Fastify, Next.js):
export async function getOrders(req) {
  const { cursor, direction, limit, search } = req.query;

  const page = await paginateKeyset(
    db.select().from(orders).$dynamic(),
    orderKeyset,
    {
      cursor,
      direction: direction === 'backward' ? 'backward' : 'forward',
      limit: Number(limit) || 20,
      search,
    },
    { protector }
  );

  return {
    items: page.items,
    startCursor: page.startCursor,
    endCursor: page.endCursor,
    hasNextPage: page.hasNextPage,
    hasPreviousPage: page.hasPreviousPage,
  };
}

Search Strategies

KeysetEngine includes multiple search strategies:

  1. Universal Strategy (Default):
    searchStrategy: 'like' // lower(column) LIKE lower('%term%')
  2. PostgreSQL Strategy:
    searchStrategy: 'ilike' // ILIKE '%term%'
  3. PostgreSQL TsVector (Full-Text Search):
    searchStrategy: 'tsvector' // to_tsvector @@ plainto_tsquery
  4. Custom Expression Strategy:
    import { sql } from 'drizzle-orm';
    
    createKeysetConfig(orders, {
      // ...
      searchStrategy: (column, term) => sql`${column} LIKE ${'%' + term + '%'}`,
    });

Cryptographic Security & Purpose Isolation

CryptoCursorProtector implements authenticated encryption with purpose-scoped keys:

  • AES-256-GCM: Authenticated payload encryption ensures cursors cannot be read or tampered with by clients.
  • HKDF Purpose Isolation: Tokens generated for an 'orders' endpoint cannot be decrypted or accepted by a 'customers' endpoint.
  • Graceful Fault Tolerance: Corrupt, expired, or tampered tokens safely evaluate to null and return the first page instead of raising 500 errors.

License

MIT © Paul Abib Camano — Aebibtech

About

High-performance, database-agnostic keyset pagination and universal search library for Drizzle ORM on Node.js (18+) and Bun.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages