Table Utilities & Database Helpers

Overview

PadawanForge implements a comprehensive set of table utilities and database helper functions that provide efficient data manipulation, pagination, filtering, and query building capabilities. These utilities streamline database operations and provide consistent data handling patterns.

Architecture

Core Components

• Table Utilities: Pagination, filtering, and sorting helpers
• Query Builders: Dynamic SQL query construction
• Data Transformers: Result formatting and transformation
• Connection Management: Database connection utilities
• Performance Optimizers: Query optimization helpers

Utility Features

• Pagination Support: Offset and cursor-based pagination
• Dynamic Filtering: Flexible filter application
• Sorting: Multi-column sorting with direction
• Query Optimization: Efficient query construction
• Data Validation: Input validation and sanitization

Implementation

Table Utilities

import { 
  createPaginationQuery, 
  applyFilters, 
  applySorting,
  buildWhereClause,
  sanitizeInput,
  transformResults 
} from '@/lib/table-utils';

// Create paginated query
const paginationQuery = createPaginationQuery({
  table: 'players',
  select: ['uuid', 'username', 'display_name', 'created_at'],
  page: 1,
  limit: 20,
  filters: { status: 'active', level: { min: 5 } },
  sort: { field: 'created_at', direction: 'desc' }
});

// Apply filters to query
const filteredQuery = applyFilters(baseQuery, {
  username: { like: 'john%' },
  level: { gte: 10, lte: 50 },
  status: 'active',
  created_at: { gte: '2024-01-01' }
});

// Apply sorting
const sortedQuery = applySorting(filteredQuery, [
  { field: 'level', direction: 'desc' },
  { field: 'username', direction: 'asc' }
]);

Pagination Interface

interface PaginationOptions {
  page?: number;                   // Page number (1-based)
  limit?: number;                  // Items per page
  cursor?: string;                 // Cursor for cursor-based pagination
  offset?: number;                 // Offset for offset-based pagination
}

interface PaginationResult<T> {
  data: T[];                       // Paginated data
  pagination: {
    page: number;                  // Current page
    limit: number;                 // Items per page
    total: number;                 // Total items
    totalPages: number;            // Total pages
    hasNext: boolean;              // Has next page
    hasPrev: boolean;              // Has previous page
    nextCursor?: string;           // Next cursor
    prevCursor?: string;           // Previous cursor
  };
}

Usage Examples

Basic Pagination

import { createPaginationQuery, executePagination } from '@/lib/table-utils';

// Simple pagination
async function getPlayers(db: D1Database, page: number = 1, limit: number = 20) {
  const query = createPaginationQuery({
    table: 'players',
    select: ['uuid', 'username', 'display_name', 'level', 'created_at'],
    page,
    limit,
    sort: { field: 'created_at', direction: 'desc' }
  });

  return await executePagination(db, query);
}

// Usage
const result = await getPlayers(db, 1, 20);
console.log(result);
// {
//   data: [...],
//   pagination: {
//     page: 1,
//     limit: 20,
//     total: 150,
//     totalPages: 8,
//     hasNext: true,
//     hasPrev: false
//   }
// }

Advanced Filtering

// Complex filtering
async function getFilteredPlayers(db: D1Database, filters: any) {
  const baseQuery = {
    table: 'players',
    select: ['uuid', 'username', 'display_name', 'level', 'status', 'created_at'],
    joins: [
      { table: 'player_profiles', on: 'players.uuid = player_profiles.player_uuid' }
    ]
  };

  // Apply filters
  const filteredQuery = applyFilters(baseQuery, {
    username: { like: filters.username },
    level: { 
      gte: filters.minLevel, 
      lte: filters.maxLevel 
    },
    status: filters.status,
    created_at: { 
      gte: filters.startDate,
      lte: filters.endDate 
    },
    'player_profiles.location': filters.location
  });

  // Apply sorting
  const sortedQuery = applySorting(filteredQuery, [
    { field: 'level', direction: 'desc' },
    { field: 'username', direction: 'asc' }
  ]);

  // Execute with pagination
  return await executePagination(db, sortedQuery, {
    page: filters.page || 1,
    limit: filters.limit || 20
  });
}

// Usage
const filters = {
  username: 'john',
  minLevel: 10,
  maxLevel: 50,
  status: 'active',
  startDate: '2024-01-01',
  endDate: '2024-12-31',
  location: 'US',
  page: 1,
  limit: 25
};

const result = await getFilteredPlayers(db, filters);

Cursor-Based Pagination

// Cursor-based pagination for better performance
async function getPlayersCursor(db: D1Database, cursor?: string, limit: number = 20) {
  const query = createPaginationQuery({
    table: 'players',
    select: ['uuid', 'username', 'display_name', 'created_at'],
    cursor: cursor,
    limit: limit,
    sort: { field: 'created_at', direction: 'desc' }
  });

  return await executePagination(db, query);
}

// Usage
const firstPage = await getPlayersCursor(db, undefined, 20);
const nextPage = await getPlayersCursor(db, firstPage.pagination.nextCursor, 20);

Dynamic Query Building

// Build dynamic queries
class QueryBuilder {
  private query: any = {
    table: '',
    select: [],
    joins: [],
    where: [],
    orderBy: [],
    groupBy: [],
    having: []
  };

  from(table: string): QueryBuilder {
    this.query.table = table;
    return this;
  }

  select(...columns: string[]): QueryBuilder {
    this.query.select.push(...columns);
    return this;
  }

  join(table: string, on: string): QueryBuilder {
    this.query.joins.push({ table, on });
    return this;
  }

  where(condition: string, params: any[] = []): QueryBuilder {
    this.query.where.push({ condition, params });
    return this;
  }

  orderBy(field: string, direction: 'asc' | 'desc' = 'asc'): QueryBuilder {
    this.query.orderBy.push({ field, direction });
    return this;
  }

  groupBy(...fields: string[]): QueryBuilder {
    this.query.groupBy.push(...fields);
    return this;
  }

  having(condition: string, params: any[] = []): QueryBuilder {
    this.query.having.push({ condition, params });
    return this;
  }

  build(): { sql: string; params: any[] } {
    let sql = `SELECT ${this.query.select.join(', ')} FROM ${this.query.table}`;
    const params: any[] = [];

    // Add joins
    for (const join of this.query.joins) {
      sql += ` JOIN ${join.table} ON ${join.on}`;
    }

    // Add where clauses
    if (this.query.where.length > 0) {
      sql += ' WHERE ' + this.query.where.map(w => w.condition).join(' AND ');
      this.query.where.forEach(w => params.push(...w.params));
    }

    // Add group by
    if (this.query.groupBy.length > 0) {
      sql += ` GROUP BY ${this.query.groupBy.join(', ')}`;
    }

    // Add having
    if (this.query.having.length > 0) {
      sql += ' HAVING ' + this.query.having.map(h => h.condition).join(' AND ');
      this.query.having.forEach(h => params.push(...h.params));
    }

    // Add order by
    if (this.query.orderBy.length > 0) {
      const orderClauses = this.query.orderBy.map(o => `${o.field} ${o.direction}`);
      sql += ` ORDER BY ${orderClauses.join(', ')}`;
    }

    return { sql, params };
  }
}

// Usage
const query = new QueryBuilder()
  .from('players')
  .select('uuid', 'username', 'level')
  .join('player_profiles', 'players.uuid = player_profiles.player_uuid')
  .where('level >= ?', [10])
  .where('status = ?', ['active'])
  .orderBy('level', 'desc')
  .build();

const result = await db.prepare(query.sql).bind(...query.params).all();

Data Transformation

// Transform database results
function transformPlayerData(rawData: any[]): Player[] {
  return rawData.map(row => ({
    uuid: row.uuid,
    username: row.username,
    displayName: row.display_name,
    level: row.level,
    status: row.status,
    createdAt: new Date(row.created_at),
    profile: {
      bio: row.bio,
      location: row.location,
      avatarUrl: row.avatar_url
    }
  }));
}

// Transform with pagination
async function getTransformedPlayers(db: D1Database, options: PaginationOptions) {
  const query = createPaginationQuery({
    table: 'players',
    select: [
      'players.uuid',
      'players.username', 
      'players.level',
      'players.status',
      'players.created_at',
      'profiles.bio',
      'profiles.location',
      'profiles.avatar_url'
    ],
    joins: [
      { table: 'player_profiles profiles', on: 'players.uuid = profiles.player_uuid' }
    ],
    page: options.page,
    limit: options.limit
  });

  const result = await executePagination(db, query);
  
  return {
    ...result,
    data: transformPlayerData(result.data)
  };
}

Filter Application

// Apply complex filters
function applyAdvancedFilters(query: any, filters: any) {
  const whereConditions: string[] = [];
  const params: any[] = [];

  // Text search
  if (filters.search) {
    whereConditions.push('(username LIKE ? OR display_name LIKE ?)');
    params.push(`%${filters.search}%`, `%${filters.search}%`);
  }

  // Range filters
  if (filters.levelRange) {
    whereConditions.push('level BETWEEN ? AND ?');
    params.push(filters.levelRange.min, filters.levelRange.max);
  }

  // Date filters
  if (filters.dateRange) {
    whereConditions.push('created_at BETWEEN ? AND ?');
    params.push(filters.dateRange.start, filters.dateRange.end);
  }

  // Status filters
  if (filters.statuses && filters.statuses.length > 0) {
    const placeholders = filters.statuses.map(() => '?').join(',');
    whereConditions.push(`status IN (${placeholders})`);
    params.push(...filters.statuses);
  }

  // Boolean filters
  if (filters.hasProfile !== undefined) {
    if (filters.hasProfile) {
      whereConditions.push('profiles.player_uuid IS NOT NULL');
    } else {
      whereConditions.push('profiles.player_uuid IS NULL');
    }
  }

  if (whereConditions.length > 0) {
    query.where = whereConditions.join(' AND ');
    query.params = params;
  }

  return query;
}

// Usage
const filters = {
  search: 'john',
  levelRange: { min: 10, max: 50 },
  dateRange: { start: '2024-01-01', end: '2024-12-31' },
  statuses: ['active', 'verified'],
  hasProfile: true
};

const query = applyAdvancedFilters(baseQuery, filters);

Performance Optimization

// Optimize queries for performance
class QueryOptimizer {
  // Add indexes hint
  static addIndexHint(query: string, table: string, index: string): string {
    return query.replace(
      `FROM ${table}`,
      `FROM ${table} INDEXED BY ${index}`
    );
  }

  // Limit result set
  static addLimit(query: string, limit: number): string {
    if (!query.includes('LIMIT')) {
      return `${query} LIMIT ${limit}`;
    }
    return query;
  }

  // Add query timeout
  static addTimeout(query: string, timeoutMs: number): string {
    return `${query} /* timeout: ${timeoutMs}ms */`;
  }

  // Optimize for specific use case
  static optimizeForRead(query: string): string {
    return query.replace('SELECT', 'SELECT /* read optimization */');
  }

  static optimizeForCount(query: string): string {
    return query.replace('SELECT *', 'SELECT COUNT(*) as count');
  }
}

// Usage
let query = 'SELECT * FROM players WHERE level > 10';
query = QueryOptimizer.addIndexHint(query, 'players', 'idx_level');
query = QueryOptimizer.addLimit(query, 100);
query = QueryOptimizer.addTimeout(query, 5000);

Connection Management

// Database connection utilities
class DatabaseManager {
  private db: D1Database;
  private connectionPool: Map<string, any> = new Map();

  constructor(db: D1Database) {
    this.db = db;
  }

  // Execute query with retry logic
  async executeWithRetry<T>(
    queryFn: () => Promise<T>,
    maxRetries: number = 3,
    delayMs: number = 1000
  ): Promise<T> {
    let lastError: Error;

    for (let attempt = 1; attempt <= maxRetries; attempt++) {
      try {
        return await queryFn();
      } catch (error) {
        lastError = error as Error;
        
        if (attempt === maxRetries) {
          throw lastError;
        }

        // Wait before retry
        await new Promise(resolve => setTimeout(resolve, delayMs * attempt));
      }
    }

    throw lastError!;
  }

  // Execute transaction
  async executeTransaction<T>(operations: (db: D1Database) => Promise<T>): Promise<T> {
    return await this.db.batch([
      this.db.prepare('BEGIN TRANSACTION'),
      // Execute operations
      this.db.prepare('COMMIT')
    ]);
  }

  // Health check
  async healthCheck(): Promise<boolean> {
    try {
      await this.db.prepare('SELECT 1').first();
      return true;
    } catch {
      return false;
    }
  }
}

Integration Examples

API Route Integration

import { createPaginationQuery, executePagination } from '@/lib/table-utils';

export async function GET(request: Request, locals: any) {
  const url = new URL(request.url);
  const page = parseInt(url.searchParams.get('page') || '1');
  const limit = parseInt(url.searchParams.get('limit') || '20');
  const search = url.searchParams.get('search') || '';
  const status = url.searchParams.get('status') || '';

  try {
    const query = createPaginationQuery({
      table: 'players',
      select: ['uuid', 'username', 'display_name', 'level', 'status', 'created_at'],
      page,
      limit,
      filters: {
        username: search ? { like: `%${search}%` } : undefined,
        status: status || undefined
      },
      sort: { field: 'created_at', direction: 'desc' }
    });

    const result = await executePagination(locals.runtime.env.DB, query);

    return new Response(JSON.stringify(result), {
      status: 200,
      headers: { 'Content-Type': 'application/json' }
    });
  } catch (error) {
    return new Response(JSON.stringify({ error: 'Failed to fetch players' }), {
      status: 500,
      headers: { 'Content-Type': 'application/json' }
    });
  }
}

Component Integration

import { useState, useEffect } from 'react';
import { createPaginationQuery, executePagination } from '@/lib/table-utils';

function PlayersTable() {
  const [players, setPlayers] = useState<any[]>([]);
  const [pagination, setPagination] = useState<any>({});
  const [loading, setLoading] = useState(false);
  const [filters, setFilters] = useState({
    search: '',
    status: '',
    page: 1,
    limit: 20
  });

  const loadPlayers = async () => {
    setLoading(true);
    try {
      const query = createPaginationQuery({
        table: 'players',
        select: ['uuid', 'username', 'display_name', 'level', 'status'],
        page: filters.page,
        limit: filters.limit,
        filters: {
          username: filters.search ? { like: `%${filters.search}%` } : undefined,
          status: filters.status || undefined
        }
      });

      const result = await executePagination(db, query);
      setPlayers(result.data);
      setPagination(result.pagination);
    } catch (error) {
      console.error('Failed to load players:', error);
    } finally {
      setLoading(false);
    }
  };

  useEffect(() => {
    loadPlayers();
  }, [filters]);

  return (
    <div className="players-table">
      <div className="filters">
        <input
          type="text"
          placeholder="Search players..."
          value={filters.search}
          onChange={(e) => setFilters({ ...filters, search: e.target.value, page: 1 })}
        />
        <select
          value={filters.status}
          onChange={(e) => setFilters({ ...filters, status: e.target.value, page: 1 })}
        >
          <option value="">All Status</option>
          <option value="active">Active</option>
          <option value="inactive">Inactive</option>
        </select>
      </div>

      {loading ? (
        <div>Loading...</div>
      ) : (
        <>
          <table>
            <thead>
              <tr>
                <th>Username</th>
                <th>Display Name</th>
                <th>Level</th>
                <th>Status</th>
              </tr>
            </thead>
            <tbody>
              {players.map(player => (
                <tr key={player.uuid}>
                  <td>{player.username}</td>
                  <td>{player.display_name}</td>
                  <td>{player.level}</td>
                  <td>{player.status}</td>
                </tr>
              ))}
            </tbody>
          </table>

          <div className="pagination">
            <button
              disabled={!pagination.hasPrev}
              onClick={() => setFilters({ ...filters, page: filters.page - 1 })}
            >
              Previous
            </button>
            <span>Page {pagination.page} of {pagination.totalPages}</span>
            <button
              disabled={!pagination.hasNext}
              onClick={() => setFilters({ ...filters, page: filters.page + 1 })}
            >
              Next
            </button>
          </div>
        </>
      )}
    </div>
  );
}

Testing

Table Utilities Testing

describe('Table Utilities', () => {
  it('should create pagination query correctly', () => {
    const query = createPaginationQuery({
      table: 'players',
      select: ['uuid', 'username'],
      page: 2,
      limit: 10
    });

    expect(query.sql).toContain('SELECT uuid, username FROM players');
    expect(query.sql).toContain('LIMIT 10 OFFSET 10');
  });

  it('should apply filters correctly', () => {
    const baseQuery = { table: 'players', select: ['*'] };
    const filters = { status: 'active', level: { gte: 10 } };

    const filteredQuery = applyFilters(baseQuery, filters);

    expect(filteredQuery.where).toContain('status = ?');
    expect(filteredQuery.where).toContain('level >= ?');
  });

  it('should apply sorting correctly', () => {
    const baseQuery = { table: 'players', select: ['*'] };
    const sort = { field: 'username', direction: 'desc' };

    const sortedQuery = applySorting(baseQuery, [sort]);

    expect(sortedQuery.orderBy).toContain('username DESC');
  });
});

This comprehensive table utilities system provides efficient database operations with pagination, filtering, and optimization capabilities.