Automatically add comments to all SQL queries made by Laravel

We’ve released a new package called spatie/laravel-sql-commenter. This one can add comments to SQL queries. These comments allow you to easily pinpoint the origin of a query when looking at your query log.

When Laravel executes an Eloquent query, it will transform it to SQL and send it to the database. Here’s what such a simple SQL query looks like.

select * from users

Using our package, you can easily add comments like this one.

select * from "users"/*controller='UsersController',action='index'*/;

We didn’t come up with that particular way of formatting a comment. We’re using sqlcommenter formatting. This formatting is supported by tools and services that allow you to inspect queries.

We are currently building Mailcoach Cloud, a service to easily send bulk mailings, drip campaigns and more via your own AWS SES, Mailgun, Postmark, Sendgrid, … account. The database of Mailcoach Cloud will be administered via PlanetScale .

On PlanetScale, you can get detailed information about the executed queries on the query insights screen.

Here’s a screenshot of a slow query (we intentionally made it slow for this example using sleep). The comments we added to the query end up as nicely formatted tags.

Using these tags, we can quickly pinpoint the source of the query and even for which client the query was executed.

Various other services, like Google Cloud Platform, will also use the comments in a similar way on their query insights pages.

You only need to install the package with Composer to add comments to all queries performed by Laravel.

composer require spatie/laravel-sql-commenter

All your queries will now have a comment that will tell you from which class, route, and/or job the query originated.

Publishing the config file can give you fine-grained control over what gets added.

php artisan vendor:publish --tag="sql-commenter-config"

Here’s what that published config file looks like.

return [
    /*
     * When set to true, comments will be added to all your queries
     */
    'enabled' => true,

    /*
     * These classes add comments to an executed query.
     */
    'commenters' => [
        Spatie\SqlCommenter\Commenters\ControllerCommenter::class => ['includeNamespace' => false],
        Spatie\SqlCommenter\Commenters\RouteCommenter::class,
        Spatie\SqlCommenter\Commenters\JobCommenter::class => ['includeNamespace' => false],
        Spatie\SqlCommenter\Commenters\FileCommenter::class => ['backtraceLimit' => 20],
        Spatie\SqlCommenter\Commenters\CurrentUserCommenter::class,
        // Spatie\SqlCommenter\Commenters\FrameworkVersionCommenter::class,
        // Spatie\SqlCommenter\Commenters\DbDriverCommenter::class,
    ],

    /*
     * If you need fine-grained control over the logging, you can extend
     * the SqlCommenter class and specify your custom class here
     */
    'commenter_class' => Spatie\SqlCommenter\SqlCommenter::class,
];

The classes in the commenters key are responsible for adding a comment to the query. By default, we’ll add where the query originated and who is the current user.

You can easily add commenters yourself to add a comment about an aspect that is important to you. A commentator is a class that returns zero, one or more comments. Let’s look at the FrameworkVersionCommenter that ships with the package.

namespace Spatie\SqlCommenter\Commenters;

use Illuminate\Database\Connection;
use Spatie\SqlCommenter\Comment;

class FrameworkVersionCommenter implements Commenter
{
    /** @return Comment|Comment[]|null */
    public function comments(string $query, Connection $connection): Comment|array|null
    {
        return Comment::make('framework',  "laravel-" . app()->version());
    }
}

When you create your own commenter, don’t forget the add its class name to the commenters key of the config file.

How it works under the hood

Laravel makes adding comments to queries that are about to be executed pretty easy. We can leverage the beforeExecuting method on the DB connection. In our package, we call that function in our service provider.

// in SqlCommenterServiceProvider

$this->app->get('db.connection')
    ->beforeExecuting(function (
        string &$query,
        array &$bindings,
        Connection $connection,
    ) {
        $sqlCommenter = app(SqlCommenter::class);

        $commenters = $this->instanciateCommenters(config('sql-commenter.commenters'));

        $query = $sqlCommenter->commentQuery($query, $connection, $commenters);
    });

You’ll notice that the query is preceded by &. This means that the query is passed by reference. This allows us to change the comment, the code that calls beforeExecuting will use the changed $query.

The comments are added to $query inside the commmentQuery method of the SqlCommenter class. This class will loop over all configured commenters to determine which comments should be added. If you’re interested in how this all works, head over the code of the SqlCommenter class.

In closing

We hope this package can help you make your query log more useful. spatie/laravel-sql-commenter was made by my colleague Rias and me. To learn more about the package, head to the the readme on GitHub.

To know more about Mailcoach Cloud, the service where we use this package ourselves, point your browser to the Mailcoach Cloud website.

This isn’t the first package that we’ve made. Our company website has a extensive list of packages our team has previously released. If you want to support us, consider picking up one of our paid products or premium courses.