Sql Query Formatter Laravel Package

## Getting Started

### Minimal Steps
1. **Installation**
   Add the package via Composer:
   ```bash
   composer require nilportugues/sql-query-formatter:^1.2.2

Note: This version supports PHP 5.5 (temporarily) but downgrades PHPUnit to v4.x. Ensure your project's composer.json allows this version range:

"require": {
    "php": ">=5.5.0",
    "phpunit/phpunit": "^4.0"
}

No additional configuration is required—autoload the NilPortugues\SqlFormatter\SqlFormatter class.

2. First Use Case Format a raw SQL query string for debugging or logging:

   use NilPortugues\SqlFormatter\SqlFormatter;
   
   $sql = "SELECT * FROM users WHERE id=1 AND status='active' ORDER BY created_at DESC";
   $formatter = new SqlFormatter();
   $formatted = $formatter->format($sql);
   
   // Outputs:
   // SELECT
   //     *
   // FROM
   //     users
   // WHERE
   //     id = 1
   //     AND status = 'active'
   // ORDER BY
   //     created_at DESC
   

3. Where to Look First

   • Class Docs: Check the SqlFormatter class for available methods (e.g., format(), setLineLength()).
   • Tests: Browse the GitHub repo for edge cases (note: tests may use PHPUnit v4.x).
   • Default Behavior: Understand that the formatter prioritizes readability over strict SQL validation.
   • PHP Version: Confirm compatibility with your project’s PHP version (5.5+ for this release; future versions may drop 5.5 support).

Implementation Patterns

Usage Patterns

1. Debugging Queries Log formatted queries in Laravel’s app/Exceptions/Handler.php:

   public function report(Throwable $exception)
   {
       if ($exception instanceof QueryException) {
           $formatted = (new SqlFormatter())->format($exception->getQuery());
           Log::error("Failed Query: " . $formatted);
       }
   }
   

2. Middleware for API Responses Format SQL in API error responses (e.g., app/Http/Middleware/LogFailedQueries.php):

   public function handle($request, Closure $next)
   {
       try {
           return $next($request);
       } catch (\Exception $e) {
           if (app()->bound('db')) {
               $lastQuery = app('db')->getQueryLog()[0] ?? null;
               if ($lastQuery) {
                   $e->getMessage() .= "\nFormatted SQL:\n" . (new SqlFormatter())->format($lastQuery['query']);
               }
           }
           throw $e;
       }
   }
   

3. Service Layer Logging Wrap Eloquent queries in a service class:

   class UserService {
       public function findActiveUsers()
       {
           $query = User::where('status', 'active')->toSql();
           $formatted = (new SqlFormatter())->format($query);
           Log::debug("Executing query:\n" . $formatted);
   
           return User::where('status', 'active')->get();
       }
   }
   

Workflows

1. Dynamic Formatting Use the setLineLength() method to control output width (default: 80 chars):

   $formatter = new SqlFormatter();
   $formatter->setLineLength(120); // Wider lines for logs
   $formatted = $formatter->format($sql);
   

2. Batch Processing Format multiple queries in a loop (e.g., for migration files or seeders):

   $queries = [
       "SELECT * FROM posts WHERE user_id = 1",
       "UPDATE users SET votes = votes + 1 WHERE id = ?"
   ];
   
   foreach ($queries as $query) {
       echo (new SqlFormatter())->format($query) . "\n\n";
   }
   

3. Integration with Laravel Debugbar Extend LaravelDebugbar to show formatted SQL:

   // In a Debugbar extension
   $data['sql'] = (new SqlFormatter())->format($query);
   

Integration Tips

• Query Logging: Pair with Laravel’s DB::enableQueryLog() for real-time formatting.
• Artisan Commands: Add a custom command to format SQL files:

  // app/Console/Commands/FormatSql.php
  public function handle()
  {
      $sql = file_get_contents($this->argument('file'));
      $this->info((new SqlFormatter())->format($sql));
  }
  

  Register it in app/Console/Kernel.php:

  protected $commands = [
      \App\Console\Commands\FormatSql::class,
  ];
  

  Usage:

  php artisan format-sql:format path/to/query.sql
  

  Note: Ensure your composer.json allows PHPUnit v4.x if running tests for this command.

Gotchas and Tips

Pitfalls

1. PHP Version Limitations

   • PHP 5.5 Support: This version supports PHP 5.5 but may introduce compatibility issues with modern Laravel versions (e.g., Laravel 8+ requires PHP 7.3+).
   • Future Deprecation: The package may drop PHP 5.5 support in future releases. Plan to upgrade your PHP version if this is a concern.
   • Workaround: Pin the package version to ^1.2.2 in composer.json to avoid automatic upgrades that might drop 5.5 support.

2. No SQL Validation

   • The formatter assumes valid SQL. Malformed queries may break formatting or produce unexpected output.
   • Workaround: Validate SQL first (e.g., with Doctrine\DBAL\Connection).

3. Line Wrapping Quirks

   • Long table/column names may wrap unpredictably. Use setLineLength() to adjust.
   • Tip: Set a higher line length (e.g., 120) for complex queries.

4. Parameter Placeholders

   • Named/positional parameters (e.g., ?, :id) are not parsed—only literal values are formatted.
   • Example: WHERE id = ? remains unchanged (no type inference for ?).

5. Case Sensitivity

   • SQL keywords (e.g., SELECT, FROM) are lowercase in output, regardless of input case.
   • Note: This is intentional for consistency but may mislead if your team uses mixed case.

6. PHPUnit Version Conflict

   • This release downgrades PHPUnit to v4.x, which may conflict with other packages in your project requiring PHPUnit v9.x.
   • Workaround: Use a project-specific phpunit/phpunit version in composer.json or isolate tests for this package.

Debugging

1. Edge Cases

   • Subqueries: Nested queries may not align perfectly. Test with complex joins.
   • Comments: SQL comments (--, /* */) are stripped. Add them back manually if needed.
   • Example:

     -- Get active users
     SELECT * FROM users WHERE status = 'active'
     

     Output will omit the comment.

2. Logging Formatted Queries

   • Use Laravel’s tap() to inspect formatted SQL:

     DB::table('users')->where('active', 1)->toSql()
         ->tap(fn($sql) => Log::debug((new SqlFormatter())->format($sql)));
     

3. Custom Formatting

   • Extend the class for project-specific rules (e.g., preserve comments):

     class CustomFormatter extends SqlFormatter {
         protected function preProcess($sql) {
             // Add logic to handle comments or other edge cases
             return parent::preProcess($sql);
         }
     }
     

Config Quirks

• No Configuration File: All settings are method-based (e.g., setLineLength()).
• Default Behavior: Relies on PHP’s wordwrap() for line breaks. Override if needed:

  $formatter->setLineLength(100);
  $formatter->setLineBreak("\n"); // Customize line endings
  

Extension Points

1. Pre/Post Processing Override preProcess() or postProcess() to modify input/output:

   class ExtendedFormatter extends SqlFormatter {
       protected function postProcess($sql) {
           return str_replace('WHERE', '/* FILTER */ WHERE', $sql);
       }
   }
   

2. Syntax Highlighting Combine with a lexer (e.g., Spatie\SqlFormatter) for colored output in CLI tools.

3. Database-Specific Dialects

   • The formatter is agnostic to DBMS (MySQL, PostgreSQL, etc.). Test with your dialect’s quirks (e.g., LIMIT vs. FETCH FIRST).