DebugKit SQL Log for dynamically created db connection

[nshiddqui]

Hello,

When I create database connection dynamically in my application with setConfig() (i.e. datasource setting not written in app.php or written but reconfigured) DebugKit SQL Log is not working for this connection.

Can I enable DebugKit SQL Log for this kind of connection?

in app.php

‘Datasources’ => [ ‘connectionA’ => […] ],

… SQL log works

in controller

ConnectionManager::setConfig(‘connectionB’, $new_config);

… SQL log doesn’t works

‘log’ => true did not affect this issue.

Thanks

[ndm2]

Can I enable DebugKit SQL Log for this kind of connection?

Not really, no. The loggers are being registered in the middleware layer (and Debug Kit tries to be the first middleware in line), if you cannot configure your connections before that point, then the pretty much only thing you could do is borrow an existing logger from another connection, where Debug Kit slid its logger into, but that comes with all sorts of caveats, I really wouldn't recommend it.

In a perfect world, Debug Kit could react to connections being configured on the fly, and enable logging for them, but the connection manager likes to be secretive about its doings. The next best thing might be making Debug Kit's internal functionality for enabling logging public.

That being said, if you explain why you seemingly need to configure the connection in the controller, then maybe someone can come up with an idea to refactor this into/before the middleware layer, so that Debug Kit can pick it up.

[nshiddqui]

I need to switch the database according to the logged in user.

[ndm2]

In your example it looks like you add a connection. When you say switch, do you mean that the new connection actually replaces an already existing connection? In that case using the logger of the old connection could possibly be fine, if there are no possibly conflicting query loggers configured (apart from Debug Kit) for any of the two connections.

[nshiddqui]

it means I'm using both connections. by using this connection public static function defaultConnectionName(): string { return 'group'; }

[markstory]

You could also use connection aliasing to give a predefined connection the name required for the active user database.

[ndm2]

So its actually a known static name, group, and it needs to use a specific database per current user?

I'm not sure how exactly aliasing could maybe help in that situation, but you should at least be able to predefine a connection as Mark suggested. With the predefined connection available, it would be possible to take its logger, and replace the connection with a new config and the old logger, something along the lines of this:

$oldConnection = ConnectionManager::get('group');

$config = $oldConnection->config();
$config['database'] = $currentUserDatabase;

ConnectionManager::drop('group');
ConnectionManager::setConfig('group', $config);

if ($oldConnection->isQueryLoggingEnabled()) {
    ConnectionManager::get('group')->setLogger($oldConnection->getLogger());
}

Should work, though it's kinda hack-ish. Mark might have something better in mind that utilizes aliasing!?

If you don't mind seeing a different connection name in Debug Kit, then you could give the predefined connection a different name.

[markstory]

I wrote a blog post a few years back on doing something similar for a horizontally sharded database that uses connection aliasing http://mark-story.com/posts/view/using-cakephp-and-a-horizontally-sharded-database

The blog post uses a DispatchFilter which has been replaced with Middleware, but otherwise the connection aliasing parts are still relevant.

[ndm2]

Ah, I see. I understood the issue so that every user has its own database, ie dozens and dozens of databases. But if it's actually that for some users it's databaseA, and for some users it's databaseB, then switching connections like that would certainly be the way to go.

[markstory]

Ah, I see. I understood the issue so that every user has its own database, ie dozens and dozens of databases.

If there are 100s to 1000s of databases then your solution to create databases dynamically would be better as having those all defined in configuration could get complicated. Having that many databases would also complicate life in many other ways though.