Laravel MySQL System Variable Manager

A tiny extension of MySqlConnection that manages session system variables

Requirements

Package	Version	Mandatory
PHP	^8.0	✅
Laravel	^9.0 || ^10.0	✅
PHPStan	>=1.1

Installing

composer require mpyw/laravel-mysql-system-variable-manager

Basic Usage

Important

The default implementation is provided by MySqlConnectionServiceProvider, however, package discovery is not available. Be careful that you MUST register it in config/app.php by yourself.

<?php

return [

    /* ... */

    'providers' => [
        /* ... */

        Mpyw\LaravelMySqlSystemVariableManager\MySqlConnectionServiceProvider::class,

        /* ... */
    ],

];

<?php

use Illuminate\Support\Facades\DB;

// Assign an auto-recoverable system variable
// The variable is reassigned on accidental disconnections
DB::setSystemVariable('long_query_time', 10.0);

// Assign a system variable without auto-recovery
DB::setSystemVariable('long_query_time', 10.0, false);

// Assign multiple variables
DB::setSystemVariables(['long_query_time' => 10.0, 'transaction_isolation' => 'read-committed']);

// Assign a variable on a different connection
DB::connection('other_mysql_connection')->setSystemVariable('long_query_time', 10.0);

// Run callback temporarily assigning a variable
DB::usingSystemVariable('long_query_time', 10.0, function () {
    /* ... */
});

// Run callback temporarily assigning multiple variables
DB::usingSystemVariables(['long_query_time' => 10.0, 'transaction_isolation' => 'read-committed'], function () {
    /* ... */
});

// Run callback replacing current value
// NOTE: You MUST declare closure return types.
DB::usingSystemVariables(
    [
        'long_query_time' => function (float $currentValue): float {
             return $currentValue + 5.0;
        },
        'sql_mode' => function (string $currentValue): string {
             return str_replace('ONLY_FULL_GROUP_BY', '', $currentValue);
        },
    ],
    function () {
        /* ... */
    }
);

Caution

Don't use DB::disconnect() directly or auto-recovery won't be fired.
Use DB::connection()->disconnect() instead.

Advanced Usage

Tip

You can extend MySqlConnection with ManagesSystemVariables trait by yourself.

<?php

namespace App\Providers;

use App\Database\MySqlConnection;
use Illuminate\Database\Connection;
use Illuminate\Support\ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        Connection::resolverFor('mysql', function (...$parameters) {
            return new MySqlConnection(...$parameters);
        });
    }
}

<?php

namespace App\Database;

use Illuminate\Database\Connection as BaseMySqlConnection;
use Mpyw\LaravelMySqlSystemVariableManager\ManagesSystemVariables;

class MySqlConnection extends BaseMySqlConnection
{
    use ManagesSystemVariables;
    
    public function withoutForeignKeyChecks(callable $callback, ...$args)
    {
        return $this->usingSystemVariable('foreign_key_checks', false, $callback, ...$args);
    }
    
    public function allowingPartialGroupBy(callable $callback, ...$args)
    {
        return $this->usingSystemVariable('sql_mode', function (string $mode): string {
            return str_replace('ONLY_FULL_GROUP_BY', '', $mode);
        }, $callback, ...$args);
    }
}

<?php

use App\Post;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

$post = new Post();
$post->user()->associate(Auth::user());
$post->save();

DB::withoutForeignKeyChecks(function () use ($post) {
    $post->user()->associate(null);
    $post->save();
});