How to track every user database interaction in laravel 5?

Question

If any logged in user accesses any service then I want to track all queries run by this user.

Example:

If user1 logs in and wants to delete his profile details and user2 wants to update his profile details, then I want to get the data in table like below

user  |   user_id  |   service     |   query  
user1 |     1      | deleteProfile |   "DELETE FROM Users WHERE id=1"
user2 |     2      | updateProfile |   "UPDATE Users SET lastname='Thaper' WHERE id=2"

For doing so I have tried to use activity log like antonioribeiro/tracker and laravel-activitylog etc. but it did not match my requirements.

How can I do this in Laravel 5.2?

Answer 1

You can do this using middleware:

use DB;

class DatabaseLogger {

     public function handle($request, $next) {
          DB::connection()->enableQueryLog();
          return $next($request);
     }

     public function terminate($request, $response) {
         $queries = DB::getQueryLog();
         $id = Auth::check()?Auth::id():null;
         collect($queries)->each(function ($query) use ($id) { 
               DB::table("querylogtable")->insert(["user_id"=>$id,"query"=>$query]);
         });
     }

}

Add this middleware in your Kernel.php

protected $middleware = [  
    CheckForMaintenanceMode::class,
    DatabaseLogger::class           
];

Be aware that keeping all queries in memory can be taxing if there's a lot of queries being done.

More info at https://laravel.com/docs/5.0/database#query-logging

As an alternative you can register the following in a service provider:

   DB::listen(function ($query) {
        //code to log the query
    });

However beware that logging the query will also trigger this event leading in an infinite recursive logging so if you go this route be sure you don't log the logging event.

More info on: https://laravel.com/docs/5.2/database#running-queries