Laravel Sql Commenter Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require spatie/laravel-sql-commenter
   

   Publish the config file (optional):

   php artisan vendor:publish --provider="Spatie\SqlCommenter\SqlCommenterServiceProvider"
   

2. First Use Case: Enable the package in config/sql-commenter.php:

   'enabled' => env('SQL_COMMENTER_ENABLED', false),
   

   Set SQL_COMMENTER_ENABLED=true in your .env and restart your app.

   Verify: Run a query (e.g., User::all()) and check your database logs or PlanetScale Query Insights for comments like:

   select * from "users"/*controller='App\Http\Controllers\UserController',action='index'*/
   

Implementation Patterns

Core Workflows

1. Automatic Commenting: The package hooks into Laravel’s query builder (DB::query(), Eloquent, Query Builder) and appends comments in the format:

   /*controller='{Controller}',action='{Method}',model='{Model}'*/
   

   • Example: A UserController@show query becomes:

     select * from "users" where "id" = ?/*controller='UserController',action='show',model='App\Models\User'*/
     

2. Customizing Comments: Override the default comment format via config/sql-commenter.php:

   'comment_format' => '/*source={controller}.{action},model={model},user={user_id}*/',
   

   • Use placeholders: {controller}, {action}, {model}, {user_id} (requires middleware).

3. Conditional Commenting: Disable for specific queries:

   DB::connection()->disableCommenting();
   User::where('active', true)->get(); // No comment added
   

4. Middleware Integration: Attach user IDs to comments via middleware:

   use Spatie\SqlCommenter\Middleware\AddUserIdToSqlComments;
   
   protected $middleware = [
       AddUserIdToSqlComments::class,
   ];
   

Integration Tips

• Logging: Pair with laravel-debugbar or monolog to log commented queries for debugging.
• PlanetScale: Enable Query Insights to visualize commented queries in the dashboard.
• Testing: Mock comments in tests:

  $this->app->make('Spatie\SqlCommenter\SqlCommenter')->shouldReceive('commentQuery')->andReturn('/*test*/');
  

Gotchas and Tips

Pitfalls

1. Performance Overhead:

   • Commenting adds minimal overhead (~1-2ms per query), but disable in production if unused:

     'enabled' => app()->environment('production') ? false : true,
     

2. Query Builder vs. Raw SQL:

   • Raw SQL queries (DB::select("SELECT ...")) won’t be commented unless wrapped in a builder:

     DB::select(DB::raw("SELECT * FROM users")); // Commented
     DB::select("SELECT * FROM users"); // Not commented
     

3. Anonymous Controllers:

   • Closure-based routes (e.g., Route::get('/', fn() => ...)) may show as controller='__lambda'. Rename via middleware:

     use Spatie\SqlCommenter\Middleware\SetLambdaControllerName;
     
     protected $middleware = [SetLambdaControllerName::class];
     

4. Nested Transactions:

   • Comments persist across transactions. Disable temporarily if needed:

     DB::beginTransaction();
     DB::connection()->disableCommenting();
     // ...
     DB::commit();
     

Debugging

• Missing Comments:

  • Check config/sql-commenter.php for enabled: false.
  • Verify the Spatie\SqlCommenter\SqlCommenterServiceProvider is registered in config/app.php.

• Incorrect Formatting:

  • Clear cached config:

    php artisan config:clear
    

  • Check for typos in comment_format placeholders.

Extension Points

1. Custom Comment Logic: Extend the commenter via a binding:

   $this->app->bind('Spatie\SqlCommenter\Contracts\Commenter', function () {
       return new CustomCommenter();
   });
   

2. Dynamic Comment Data: Add custom metadata via events:

   use Spatie\SqlCommenter\Events\CommentingQuery;
   
   CommentingQuery::listen(function ($event) {
       $event->commentData['custom_key'] = 'custom_value';
   });
   

3. Database-Specific Quirks:

   • MySQL/PostgreSQL: Works out-of-the-box.
   • SQLite: Test thoroughly—some drivers may strip comments.
   • SQL Server: Use /* */ syntax (default) or adjust comment_format to use -- for line comments if needed.