slop-bench.ts

export const GET = apiHandlers.withAdminAuth(async (request) => {
  const url = new URL(request.url);
  const sampleCount = Math.min(Number(url.searchParams.get('samples')) || 20, 100);
  const includeQueries = url.searchParams.get('queries')?.split(',') || Object.keys(standardTestQueries);

  // Create instances for both drivers
  const postgresDb = createKyselyWithDriver('postgres');
  const neonDb = createKyselyWithDriver('neon');

  try {
    const results: {
      postgres: PerformanceTestResult[];
      neon: PerformanceTestResult[];
      comparison: Record<
        string,
        {
          postgresMedian: number;
          neonMedian: number;
          difference: number;
          percentageDiff: number;
          winner: string;
        }
      >;
    } = {
      postgres: [],
      neon: [],
      comparison: {},
    };

    // Run tests for each query type
    for (const queryName of includeQueries) {
      const queryFn = standardTestQueries[queryName as keyof typeof standardTestQueries];
      if (!queryFn) continue;

      // Run tests in parallel for both drivers
      const [postgresResult, neonResult] = await Promise.all([
        runPerformanceTest(postgresDb, queryName, queryFn, sampleCount),
        runPerformanceTest(neonDb, queryName, queryFn, sampleCount),
      ]);

      // Set driver names
      postgresResult.driver = 'postgres';
      neonResult.driver = 'neon';

      results.postgres.push(postgresResult);
      results.neon.push(neonResult);

      // Calculate comparison
      const diff = neonResult.stats.median - postgresResult.stats.median;
      const percentDiff = (diff / postgresResult.stats.median) * 100;

      results.comparison[queryName] = {
        postgresMedian: postgresResult.stats.median,
        neonMedian: neonResult.stats.median,
        difference: Math.round(diff * 100) / 100,
        percentageDiff: Math.round(percentDiff * 10) / 10,
        winner: diff < 0 ? 'neon' : 'postgres',
      };
    }

    // Cleanup connections
    await postgresDb.destroy();
    await neonDb.destroy();

    return apiHandlers.json({
      results,
      metadata: {
        sampleCount,
        queriesRun: includeQueries,
        timestamp: new Date().toISOString(),
        environment: {
          isProduction: process.env.NODE_ENV === 'production',
          isVercel: !!process.env.VERCEL,
          isNeonHost: process.env.DATABASE_URL?.includes('neon.tech') || false,
        },
      },
    });
  } catch (error) {
    // Make sure to cleanup on error
    try {
      await postgresDb.destroy();
      await neonDb.destroy();
    } catch {
      // Ignore cleanup errors
    }

    throw error;
  }
});
                        
                        
                        
/**
 * Create a Kysely instance with a specific database driver
 * Used for performance testing and driver comparison
 */
export function createKyselyWithDriver(driverType: DriverType = 'auto'): Kysely<DB> {
  const DATABASE_URL = env.DATABASE_URL;

  if (!DATABASE_URL) {
    throw new Error('DATABASE_URL is required');
  }

  // Determine which driver to use
  const useNeon = driverType === 'neon' || (driverType === 'auto' && DATABASE_URL.includes('neon.tech'));

  if (useNeon) {
    // Use Neon HTTP driver
    return createNeonKysely(DATABASE_URL);
  } else {
    // Use postgres.js driver
    return createPostgresKysely(DATABASE_URL);
  }
}

/**
 * Create a Kysely instance using the Neon HTTP driver
 */
export function createNeonKysely(databaseUrl: string): Kysely<DB> {
  // Neon dialect handles the connection internally
  // We just need to provide the connection string
  return new Kysely<DB>({
    dialect: new NeonDialect({
      connectionString: databaseUrl,
    }),
    plugins: [new CamelCasePlugin()],
  });
}

/**
 * Create a Kysely instance using the postgres.js driver
 */
export function createPostgresKysely(databaseUrl: string): Kysely<DB> {
  const isNeonConnection = databaseUrl.includes('neon.tech');
  const isPoolerConnection = databaseUrl.includes('-pooler') || databaseUrl.includes(':6543');
  const isTransactionMode = isNeonConnection && isPoolerConnection;
  const isLocalhost = databaseUrl.includes('localhost') || databaseUrl.includes('127.0.0.1');
  const isServerless = env.VERCEL || env.AWS_LAMBDA_FUNCTION_NAME;

  // Create postgres.js client with optimized settings
  const sql = postgres(databaseUrl, {
    // Disable prepared statements for transaction pooling mode
    prepare: !isTransactionMode,

    // Connection pool settings
    max: isServerless ? 1 : 10,
    idle_timeout: isServerless ? 0.1 : 20,
    connect_timeout: 5,

    // SSL configuration
    ssl: isLocalhost ? false : 'require',

    // Transform settings
    transform: {
      undefined: null,
    },

    // Connection behavior
    connection: {
      application_name: 'sweetistics-driver-test',
    },

    // Force timestamp-like types to be returned as raw strings
    types: {
      timestamptz: { from: [1184], to: 1184, parse: (s: string) => s, serialize: (s: string) => s },
      timestamp: { from: [1114], to: 1114, parse: (s: string) => s, serialize: (s: string) => s },
      date: { from: [1082], to: 1082, parse: (s: string) => s, serialize: (s: string) => s },
      time: { from: [1083], to: 1083, parse: (s: string) => s, serialize: (s: string) => s },
      timetz: { from: [1266], to: 1266, parse: (s: string) => s, serialize: (s: string) => s },
    },

    // Debug mode (only in development)
    debug: env.NODE_ENV === 'development' && env.DEBUG_SQL === 'true',
  });

  return new Kysely<DB>({
    dialect: new PostgresJSDialect({ postgres: sql }),
    plugins: [new CamelCasePlugin()],
  });
}

/**
 * Performance test suite for comparing drivers
 */
export interface PerformanceTestResult {
  driver: string;
  query: string;
  samples: number[];
  stats: {
    min: number;
    max: number;
    median: number;
    p95: number;
    p99: number;
    mean: number;
    stdDev: number;
  };
}

/**
 * Run a performance test with a specific driver
 */
export async function runPerformanceTest(
  db: Kysely<DB>,
  queryName: string,
  queryFn: (db: Kysely<DB>) => Promise<unknown>,
  sampleCount: number = 20
): Promise<PerformanceTestResult> {
  const samples: number[] = [];

  // Warm-up run
  await queryFn(db);

  // Collect samples
  for (let i = 0; i < sampleCount; i++) {
    const start = performance.now();
    await queryFn(db);
    const end = performance.now();
    samples.push(end - start);
  }

  // Sort for percentile calculations
  samples.sort((a, b) => a - b);

  // Calculate statistics
  const mean = samples.reduce((a, b) => a + b, 0) / samples.length;
  const variance = samples.reduce((acc, val) => acc + (val - mean) ** 2, 0) / samples.length;

  return {
    driver: 'unknown',
    query: queryName,
    samples: samples.map((s) => Math.round(s * 100) / 100),
    stats: {
      min: Math.round((samples[0] ?? 0) * 100) / 100,
      max: Math.round((samples[samples.length - 1] ?? 0) * 100) / 100,
      median: Math.round((samples[Math.floor(samples.length * 0.5)] ?? 0) * 100) / 100,
      p95: Math.round((samples[Math.floor(samples.length * 0.95)] ?? 0) * 100) / 100,
      p99: Math.round((samples[Math.floor(samples.length * 0.99)] ?? 0) * 100) / 100,
      mean: Math.round(mean * 100) / 100,
      stdDev: Math.round(Math.sqrt(variance) * 100) / 100,
    },
  };
}

/**
 * Standard test queries for performance comparison
 */
export const standardTestQueries = {
  simple: (db: Kysely<DB>) => kyselySql`SELECT 1`.execute(db),

  timestamp: (db: Kysely<DB>) => kyselySql`SELECT NOW()`.execute(db),

  countUsers: (db: Kysely<DB>) => db.selectFrom('users').select(db.fn.count('id').as('count')).execute(),

  recentTweets: (db: Kysely<DB>) =>
    db.selectFrom('tweets').select(['id', 'text', 'createdAt']).orderBy('createdAt', 'desc').limit(10).execute(),

  complexJoin: (db: Kysely<DB>) =>
    db
      .selectFrom('tweets')
      .innerJoin('profiles', 'profiles.id', 'tweets.authorId')
      .select(['tweets.id', 'tweets.text', 'profiles.username', 'profiles.name'])
      .orderBy('tweets.createdAt', 'desc')
      .limit(20)
      .execute(),

  aggregation: (db: Kysely<DB>) =>
    db
      .selectFrom('tweets')
      .select(['authorId', db.fn.count('id').as('tweetCount'), db.fn.max('createdAt').as('lastTweet')])
      .groupBy('authorId')
      .having(db.fn.count('id'), '>', 10)
      .limit(10)
      .execute(),
};