Hi All, I'm new to the Laravel ecosystem and need to know if there is an easy way to hook into on-database connect event. What I'm trying to achieve is that every time the db connection is established, I want to set a mysql user/session variable like so:
DB::statement('SET @user_id=23');
This is so that I can store who deleted a record in the DB in an audit log table. I know I could issue the SET statement before each DELETE statement, but it would be much more efficient and robust if I could create the mysql user var at the point where the connection is created.
Any advise would be appreciated.
Bretton
All red flags go off here. Your application should be the one to tell the audit log what to insert, not your database. In my opinion you should let your application handle this and pass the user from there to the database. You shouldn't use set on a database connection in my opinion.
Anyway, you can find all the database events here: https://laravel.com/api/5.5/Illuminate/Database/Events.html Based on this information I think you can only do this by using a transaction.
Handling this via the business logic is one way of doing it, but in our case we populate the audit log tables via database triggers, e.g. on-before-delete trigger. For updates and inserts triggers work perfectly because you can store the updating/inserting users id via the sql statement. With deletes there is no reference to the user id who is performing the action. Hence why we looking for an alternative solution. Yes, a transaction could handle this, but it adds complexity to the business logic, which we want to avoid.
I will leave a method here which allows you to listen to ConnectionEstablished event which triggers when a new database connection is established. Maybe it will help someone in the future.
# tested on laravel 10
use Illuminate\Database\Events\ConnectionEstablished;
Event::listen(function (ConnectionEstablished $event) {
logger("new database connection");
});
You can use the listener above in your EventServiceProvider.
Please or to participate in this conversation.