Sql Agent Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require knobik/sql-agent
   php artisan sql-agent:install
   

   This publishes config, migrations, and assets.

2. Configure LLM Provider (.env):

   SQL_AGENT_LLM_PROVIDER=openai  # or anthropic, ollama, etc.
   SQL_AGENT_LLM_MODEL=gpt-4o
   SQL_AGENT_LLM_API_KEY=your_key_here
   

3. First Query (in a controller/route):

   use Knobik\SqlAgent\Facades\SqlAgent;
   
   $response = SqlAgent::run('List all active users with their order count');
   dd($response->results); // Raw query results
   

4. Key Files to Review:

   • config/sql-agent.php (LLM settings, database connections)
   • database/migrations/ (for knowledge base tables)
   • resources/views/vendor/sql-agent/ (chat UI customization)

First Use Case: Ad-Hoc Reporting

Replace manual SQL writing with natural language:

// Instead of:
// DB::table('users')->where('active', true)->get();

// Use:
$response = SqlAgent::run('Show me users who signed up in Q1 2024 with their lifetime value');
$chartData = collect($response->results)->pluck('lifetime_value')->toArray();

Implementation Patterns

Core Workflows

1. Query Execution with Context

// With schema context (auto-injected)
$response = SqlAgent::run('Find customers from California with orders over $1000');

// With explicit context
$response = SqlAgent::run('Recent high-value transactions')
    ->withContext(['timeframe' => 'last_30_days', 'threshold' => 500]);

2. Chat UI Integration

// In a Blade view:
@sqlAgentChat(['initial_prompt' => 'Analyze our sales trends'])

• Customization: Override resources/views/vendor/sql-agent/chat.blade.php
• Styling: Extend resources/css/sql-agent.css

3. Knowledge Base Management

// Add custom business rules
SqlAgent::addKnowledge([
    'rule' => 'A "premium" customer has spent > $1000 in their lifetime',
    'sql' => 'SELECT * FROM customers WHERE lifetime_value > 1000'
]);

// Clear stale learnings (e.g., after schema changes)
SqlAgent::forgetLearnings();

4. Batch Processing

$queries = [
    'Top products by revenue',
    'Monthly active users trend',
    'Unpaid invoices older than 60 days'
];

$results = SqlAgent::batch($queries);
foreach ($results as $result) {
    // Process each response
}

Integration Tips

Database-Specific Optimizations

• PostgreSQL: Leverage jsonb columns for complex filtering:

  SqlAgent::run('Find users with metadata containing "premium"')
      ->withHint('Use jsonb_path_ops for metadata search');
  

• MySQL: Add table hints for performance:

  SqlAgent::run('Optimize this query for large tables')
      ->withHint('Add FORCE INDEX on orders(customer_id)');
  

API Wrapper Pattern

// app/Services/SqlAgentService.php
class SqlAgentService {
    public function getCustomerInsights(string $segment) {
        $response = SqlAgent::run("Insights for {$segment} customers")
            ->withContext(['segment' => $segment]);

        return $this->formatResponse($response);
    }
}

Testing Strategies

// Test with mocked LLM responses
$mock = Mockery::mock(SqlAgent::class);
$mock->shouldReceive('run')
    ->with('test query')
    ->andReturn((object)[
        'sql' => 'SELECT * FROM test',
        'results' => [['id' => 1]]
    ]);

Gotchas and Tips

Pitfalls

1. Schema Drift:

   • Issue: The agent’s knowledge base becomes stale after schema changes.
   • Fix: Run php artisan sql-agent:refresh-knowledge or manually update knowledge_base table.

2. LLM Token Limits:

   • Issue: Complex queries hit token limits, truncating context.
   • Fix:
     • Use shorter prompts.
     • Enable chunking: SQL_AGENT_CHUNK_SIZE=500 in config.
     • Simplify with ->withHint('Use minimal columns').

3. Permission Errors:

   • Issue: The agent generates SQL it can’t execute due to missing permissions.
   • Fix: Configure SQL_AGENT_SAFE_MODE=true to auto-sanitize queries or grant explicit permissions to the agent’s DB user.

4. Circular Learning:

   • Issue: The agent keeps "learning" the same failed query.
   • Fix: Manually review learnings table or set SQL_AGENT_MAX_LEARNING_ITERATIONS=3.

Debugging

Logs and Tracing

• Enable debug mode:

  SQL_AGENT_DEBUG=true
  

• View raw LLM interactions:

  $response = SqlAgent::run('...')->debug();
  // Inspect $response->llmDebug for prompt/response pairs
  

Common Errors

Extension Points

Custom LLM Providers

// Register a new provider (e.g., app/Providers/SqlAgentServiceProvider.php)
SqlAgent::extend('custom', function () {
    return new CustomLLMAdapter(config('sql-agent.custom_llm'));
});

Query Pre/Post-Processors

// Add a processor to modify SQL before execution
SqlAgent::addProcessor(function ($query, $prompt) {
    if (str_contains($prompt, 'export')) {
        $query->with('FOR EXPORT');
    }
    return $query;
});

UI Customization

• Override the chat UI:

  cp vendor/knobik/sql-agent/resources/views/vendor/sql-agent/* resources/views/vendor/sql-agent/
  

• Extend with plugins:

  // In a service provider
  SqlAgent::plugin('data-export', function () {
      return new DataExportPlugin();
  });
  

Performance Tuning

• Caching: Enable query caching for repeated prompts:

  SQL_AGENT_CACHE_ENABLED=true
  SQL_AGENT_CACHE_TTL=3600
  

• Parallel Queries: Use batch() for independent queries:

  $results = SqlAgent::batch([
      'Query 1',
      'Query 2'
  ], parallel: true);