Have you ever wanted to see how the final database query looks like before getting its result? Do you need to see how where conditions are grouped or you just need to implement pretty queries log? Me too, and here’s how I do it.
Preface
Personally, I don’t recommend using this solution for production. Not because it’s unstable or badly designed. In my opinion, this is not the proper way of logging database queries.
I’d rather recommend you to look for a dedicated solution for this task. Nevertheless, you’ll be fine if you utilize my library for development, learning or fun.
The “need”
Let’s start with asking ourselves, why do we even need to see a database query before execution? We are using Eloquent’s Query Builder, right? Who would ever need it if query construction is abstracted?
Of course, I did! I needed to see it when I was learning Laravel’s fundamentals. My curiosity about how it exactly works won.
Another situation was when I had an application deployed on the weird remote server and I couldn’t get to my personal environment. The only thing I had was SSH access and no tools and services.
I had to improvise.
How Eloquent fires queries?
Eloquent, it’s internal QueryBuilder, is basically a wrapper over PHP Data Objects. Nothing particularly complicated or overwhelming (despite the fact Eloquent is fancy ORM).
If you are aware of SQL Injection consequences and know to protect yourself from them, you are, with no doubts, familiar with the prepared statements.
This approach isn’t foreign for Eloquent too. If you look deep inside Laravel’s package, you might find \Illuminate\Database\Connection::select method.
/**
* Run a select statement against the database.
*
* @param string $query
* @param array $bindings
* @param bool $useReadPdo
* @return array
*/
public function select($query, $bindings = [], $useReadPdo = true)
{
return $this->run($query, $bindings, function ($query, $bindings) use ($useReadPdo) {
if ($this->pretending()) {
return [];
}
// For select statements, we'll simply execute the query and return an array
// of the database result set. Each element in the array will be a single
// row from the database table, and will either be an array or objects.
$statement = $this->prepared($this->getPdoForSelect($useReadPdo)
->prepare($query));
$this->bindValues($statement, $this->prepareBindings($bindings));
$statement->execute();
return $statement->fetchAll();
});
}
Simple, isn’t it? Did you notice binding values to the statement? This means bindings aren’t processed manually but securely by PDO.
Let’s find out, if we could print them anyway.
Extending Eloquent
The main purpose of doing this might be logging. You might have already been familiar with \Illuminate\Support\Facades\DB facade, and its listen method.
I must admit, this is a good direction. You’re in the right place if you want to peek on the query details. But it’s not that flexible, and it’s boring.
DB::listen(function (\Illuminate\Database\Events\QueryExecuted $query) {
dd($query->sql, $query->bindings, $query->time);
});
Look, we have access to everything we need! This method is fired every time we execute a query. What we can’t do here, is previewing query if it crashed.
The first thing we can do, to make it more flexible, is extracting “logging” logic to separate construction, inject it here and utilise. Here’s how our provider’s boot method could look like.
public function boot(\App\Services\MyDumper $dumper)
{
DB::listen(function (\Illuminate\Database\Events\QueryExecuted $query) use ($dumper) {
$dumper->magic($query);
});
}
A lot cleaner, right? But I still don’t like that \Illuminate\Support\Facades\DB::listen method. Now let’s see how this could work in a real-life application. This might be your routes/web.php file. I’d love to do something like this. Is this possible?
use Illuminate\Database\Query\Builder;
Route::get('/', function () {
$query = DB::query()
->from('users')
->where('active', true)
->where(function (Builder $builder) {
$builder
->orWhere('email', 'like', '%gmail.com')
->orWhere('email', 'like', '%example.com');
})
->orderByDesc('id')
->limit(10);
dd($query->dump());
});
Are you REPL fan? Here you are. Here’s use Tinker version, if you prefer.
Psy Shell v0.9.9 (PHP 7.3.2-3+ubuntu18.04.1+deb.sury.org+1 — cli) by Justin Hileman
>>> DB::query() \
... ->from('users') \
... ->where('active', true) \
... ->where(function ($builder) { \
... $builder \
... ->orWhere('email', 'like', '%gmail.com') \
... ->orWhere('email', 'like', '%example.com'); \
... }) \
... ->orderByDesc('id') \
... ->limit(10) \
... ->dump()
=> "select * from `users` where `active` = 1 and (`email` like '%gmail.com' or `email` like '%example.com') order by `id` desc limit 10"
You probably already guessed how this can be done. You’re right – it’s \Illuminate\Database\Query\Builder::macro. Check out how it’s done now.
public function boot(\App\Services\MyDumper $dumper)
{
\Illuminate\Database\Query\Builder::macro('dump', function () use ($dumper) {
return $dumper->magic($this);
});
}
Magic
Okay, now let’s see what’s that magic. Our dumping method will be called a dump. How convenient, right?
Simple POC logic would simply grab query string and replace every binding inside with corresponding value. Let’s do this.
public function dump(\Illuminate\Database\Query\Builder $builder): string
{
$sql = $builder->toSql();
foreach ($builder->getBindings() as $binding) {
$sql = Str::replaceFirst('?', (is_numeric($binding) ? $binding : sprintf('"%s"', $binding)), $sql);
}
return $sql;
}
And this works like charm!
This might seem to be finished, but I wouldn’t be that sure. Think of more complex values. DateTime for example. I wish there was something I could reuse from the vendor, that does exactly what I need?
I found it for you. There is a tiny method \Illuminate\Database\ConnectionInterface::prepareBindings, that basically decorates value properly.
All you need to do now is to add my package to your’s project dependencies. For more information see the readme.md file.
Summary
As mentioned in the introduction, I encourage you to play with it a bit. If you want logging feature, maybe you should consider a different approach.
Maybe you didn’t know about this feature, or didn’t think about using it this way? Profit. Get the code and see what you can do with it. Maybe you can implement a caching mechanism? Sound’s like a challenge?
Did you like this article? Was it helpful? Please let me know in the comments section below!
