How to log every query from multiple connections in Eloquent (outside laravel)

Question

I use multiple database connections in my app, one SQLServ, and another MySQL. I want to debug every query from both servers sequentially. therefore rather using Manager::getQueryLog() i need to use Event::listen. I use SlimFramework, with PHP-DI.

index.php

// Create container & database
$containerBuilder = new DI\ContainerBuilder(App\Lib\Container::class);
$containerBuilder->useAnnotations(false);
$containerBuilder->addDefinitions(__DIR__ . '/../config/settings.php');
$container = $containerBuilder->build();
$app = \DI\Bridge\Slim\Bridge::create($container);

// Register database
$capsule = new \Illuminate\Database\Capsule\Manager();
foreach ($container->get('database') as $con => $config) {
    $capsule->addConnection($config, $con);
}

$capsule->setEventDispatcher(new \Illuminate\Events\Dispatcher()); 
// Throw error A facade root has not been set. pretty sure it 
// was because i use it outside laravel

$capsule->setAsGlobal();
$capsule->bootEloquent();
$container->set('connection', $capsule);

// Listen
DB::listen(function($query) {
    Log::info(
       $query->sql,
       $query->bindings,
       $query->time
    );
});

App\Lib\Container::class

namespace App\Lib;

use DI\Container as DIContainer;

class Container extends DIContainer
{
    public function __get($key)
    {
        if ($this->has($key)) {
            return $this->get($key);
        }
    }
}

How to log every query from multiple connections, but in sequential order, something like the following.

select * from tableInMySQL limit 0,10;
select TOP 10 * from [tableInMSSQL];
update tableInMySQL set field='value';

EDIT

like I said earlier, I use SlimFramework, with PHP-DI.
So, I'm not use LARAVEL as a whole. (not using service provider)

the DB::listen throw error, $capsule->getConnection('con_name')->getEventDispatcher() return null

Answer 1

To log queries of multiple db connections in sequential order, we'll need to enableQueryLog first, have separate Logger configured.

$capsule->setAsGlobal();
$capsule->bootEloquent();
$container->set('connection', $capsule);

$capsule->connection('<MySqlConnectionName>')->enableQueryLog();
$capsule->connection('<SqlServerConnectionName>')->enableQueryLog();
//$capsule->connection('<MongoConnectionName>')->enableQueryLog(); 

// Listen
\Illuminate\Database\Capsule\Manager::listen(function($query) {
    if($query->connectionName == 'mysql') {
        $mysqlLogger->debug('mysql', [
            'query' => $query->sql,
            'bindings' => $query->bindings
        ]);
    } elseif($query->connectionName == 'sqlserver') {
        $sqlServerLogger->debug('mongodb', [
            'query' => $query->sql,
            'bindings' => $query->bindings
        ]);
    } /*elseif($query->connectionName == 'mongodb') {
        $mongoDbLogger->debug('mongodb', [
            'query' => $query->sql,
            'bindings' => $query->bindings
        ]);
    }*/
});

Answer 2

you should write DB::listen event in boot() function of app/Providers/AppServiceProvider.php

DB::listen(function ($query) {
    $qry = str_replace(['?'], ['\'%s\''], $query->sql);
    $qry = vsprintf($qry, $query->bindings);
    Log::info('query_log', [
        'query' => $qry,
        'bindings' => $query->bindings,
    ]);
});

Answer 3

Please add this to the Providers/AppServiceProvider.php file and check them in the laravel log file

use Illuminate\Support\Facades\Event;
use Illuminate\Support\Facades\Log;

public function register() {
    Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {
        Log::debug($query->sql . ' - ' . serialize($query->bindings));
    });
}

Answer 4

for anyone who's landing here, we need to attach listener on every connection

setting.php

return [
    'maintenance' => false,
    'base_path'   => empty($_ENV['SUBDIR']) ? '' : '/' . $_ENV['SUBDIR'],
    'database'    => [
        'default'  => [ // sql server
            "driver"   => env('DB_MSSQL_DRIVER'),
            "host"     => env('DB_MSSQL_HOST'),
            "port"     => env('DB_MSSQL_PORT'),
            "database" => env('DB_MSSQL_DATABASE'),
            "username" => env('DB_MSSQL_USERNAME'),
            'password' => env('DB_MSSQL_PASSWORD'),
        ],
        'mysql'  => [
            "driver"   => env('DB_MYSQL_DRIVER'),
            "host"     => env('DB_MYSQL_HOST'),
            "port"     => env('DB_MYSQL_PORT'),
            "database" => env('DB_MYSQL_DATABASE'),
            "username" => env('DB_MYSQL_USERNAME'),
            'password' => env('DB_MYSQL_PASSWORD'),
        ],
    ],
    "template"    => [
        "view"  => __DIR__ . "/../module",
        "cache" => __DIR__ . "/../cache",
    ],
];

index.php

// build PHP-DI container 
$container = $containerBuilder->build();

//register database
$capsule = new \Illuminate\Database\Capsule\Manager();
foreach ($container->get('database') as $con => $config) {
    $capsule->addConnection($config, $con);
    $capsule->getConnection($con)->setEventDispatcher(new \Illuminate\Events\Dispatcher());
    $capsule->getConnection($con)->listen(function ($q) {
        // or use logger
        $d = str_replace('?', "'?'", "[$q->connectionName]\t" . $q->sql);
        $d = vsprintf(str_replace('?', '%s', $d), $q->bindings);
        file_put_contents(__DIR__ . '/../log/sql.log', $d . PHP_EOL, FILE_APPEND);
    });

}
$capsule->setAsGlobal();
$capsule->bootEloquent();
$container->set('connection', $capsule);

Answer 5

I put there code into boot() method on AppServiceProvider.php

       if (\App::environment('local')) {
            \DB::listen(function ($query) {
                \Log::info($query->sql);
            });
        }

So, the log will be write only for development environment.