Savvi Studio

Database Cursor Patterns

Purpose: PostgreSQL-specific cursor implementation
Location: src/lib/db/api/ (consider renaming to src/lib/db/cursor/)
Last Updated: 2024-11-26

Overview

This documentation covers the PostgreSQL-specific implementation of the abstract cursor pattern. For the general cursor architecture, see Cursors Architecture.

Database cursors enable stateless pagination for PostgreSQL queries using token-based resumption. This implementation builds on the abstract cursor pattern defined in src/lib/cursor/.

Core principle: Database cursors implement the abstract Cursor<T> interface for PostgreSQL queries.

For complete examples, see Cursor Pagination Examples.

Relationship to Abstract Architecture

This is a concrete implementation of the abstract cursor pattern:

Design Philosophy

Why Cursors?

Problem: Traditional pagination is stateful

  • Server must remember query state
  • Session-dependent
  • Breaks in distributed systems
  • Difficult to scale

Solution: Cursor-based pagination

  • Encodes position in cursor string
  • Stateless - no server memory required
  • Works across multiple requests
  • Scalable to distributed systems
  • Resumable from any point

Use Cases

✅ Perfect for:

  • Large result sets (thousands+ rows)
  • API endpoints with pagination
  • Infinite scroll interfaces
  • Export operations with progress
  • Multi-request workflows

❌ Not ideal for:

  • Small result sets (< 100 rows)
  • Single-page displays
  • Real-time data (cursors can become stale)

Architecture

Components

1. Cursor Encoding (src/lib/cursor/encode.ts)

  • Converts position to base64 string
  • Includes query metadata
  • Tamper-resistant

2. Cursor Decoding (src/lib/cursor/decode.ts)

  • Extracts position from cursor
  • Validates cursor integrity
  • Handles corrupted cursors

3. Query Integration (src/lib/db/api/cursor.ts)

  • Applies cursor to queries
  • Manages page boundaries
  • Handles edge cases

Cursor Structure

interface CursorData {
  // Position tracking
  offset?: number;          // Offset-based position
  lastId?: bigint;          // ID-based position
  lastValue?: unknown;      // Value-based position
  
  // Query metadata
  direction: 'forward' | 'backward';
  limit: number;
  
  // Integrity
  timestamp: number;
  version: number;
}

Encoded cursor:

eyJvZmZzZXQiOjUwLCJsaW1pdCI6MjUsImRpcmVjdGlvbiI6ImZvcndhcmQifQ==

Implementation Patterns

For detailed implementation patterns, see Implementation Guide.

Pattern Types

Offset-Based: Simple pagination, order doesn't matter

  • Pros: Simple to implement
  • Cons: Poor performance with large offsets

ID-Based (Keyset): Large datasets, stable ordering

  • Pros: Constant O(log n) performance
  • Cons: Requires indexed column

Value-Based: Sorting by non-unique column

  • Pros: Works with any sortable column
  • Cons: Requires composite index

Quick Start

Basic Node Pagination

import { encodeCursor, decodeCursor } from '@/lib/cursor';
import { listNodes } from '@db/graph';

async function paginateNodes(client: PoolClient, cursor?: string) {
  const limit = 50;
  const cursorData = cursor ? decodeCursor(cursor) : null;
  
  const nodes = await listNodes(client, {
    limit: limit + 1,
    after_id: cursorData?.lastId
  });
  
  const hasMore = nodes.length > limit;
  const results = hasMore ? nodes.slice(0, limit) : nodes;
  
  const nextCursor = hasMore
    ? encodeCursor({
        lastId: results[results.length - 1].id,
        limit,
        direction: 'forward',
        timestamp: Date.now(),
        version: 1
      })
    : null;
  
  return { nodes: results, cursor: nextCursor, hasMore };
}

Documentation Structure

Next steps: See Implementation Guide for detailed patterns, or Examples for complete code.