`src/lib/db.ts`

65 lines
// Database Connection Singleton
// Prevents SQL injection, connection leaks, and improves performance

import Database from 'better-sqlite3'
import path from 'path'

let dbInstance: Database.Database | null = null

/**
 * Get singleton database connection
 * Reuses connection across requests for better performance
 */
export function getDb(): Database.Database {
  if (!dbInstance) {
    // Use environment variable or fallback to project path
    const dbPath = process.env.DATABASE_PATH ||
      path.join(process.cwd(), 'data', 'handbags.db')

    try {
      dbInstance = new Database(dbPath, {
        readonly: true,
        fileMustExist: true
      })

      // Optimize for performance
      dbInstance.pragma('journal_mode = WAL')
      dbInstance.pragma('synchronous = NORMAL')

      console.log(`[DB] Connected to database: ${dbPath}`)
    } catch (error) {
      console.error('[DB] Failed to connect to database:', error)
      throw new Error('Database connection failed')
    }
  }

  return dbInstance
}

/**
 * Close database connection (call on app shutdown)
 */
export function closeDb(): void {
  if (dbInstance) {
    dbInstance.close()
    dbInstance = null
    console.log('[DB] Database connection closed')
  }
}

/**
 * Validate brand filter to prevent SQL injection
 */
export function validateBrandFilter(brand: string | null): string | null {
  if (!brand || brand === 'all') return null

  const validBrands = ['hermes', 'chanel', 'lv', 'dior', 'prada', 'fendi']
  const normalized = brand.toLowerCase().trim()

  if (!validBrands.includes(normalized)) {
    throw new Error(`Invalid brand filter: ${brand}`)
  }

  return normalized
}