Changing the database connection on Laravel's Query Builder

While working for a client whose codebase had a lot of legacy code I faced an interesting challenge, I needed to change the database connection on an already instantiated query builder, and I couldn't do it the Laravel way. 

Our client introduced the Master Slave Replication into an existing code base that was fairly complex. Since the service which was altered was written with the Method Chaining pattern, by the time we were at the method that would actually fetch from the database a lot of things would already be set upstream, so instead of a Model or a Connection instance I would get an Eloquent or Query Builder instance.

The first thing that came to my mind was to set the connection in the constructor of the service depending on some conditions, but in the end that actually caused a bug where we would try to do write statements on the slave database. So, that was a no go.

I couldn't go with the approach that Laravel offers out of the box, a read and write config, because that would force us to always read from the slave database, and we wanted to be able to manually control when to read from which database. But wait?! Doesn't Laravel provide a method for that?! Well, yes it does, and it's called useWritePdo(), but in our case, once again, we couldn't go the Laravel way because it would mean a lot of query building methods would need to have that added, and that's a boring and tedious work.

'mysql' => array(
    'read' => array(
        'host' => '192.168.1.1',
    ),
    'write' => array(
        'host' => '196.168.1.2'
    ),
    'driver'    => 'mysql',
    'database'  => 'database',
    'username'  => 'root',
    'password'  => '',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
),

Rewriting the service was out of the question because it would affect its public API and it was used all over the codebase, not to say that it was huge, so I had to get creative. 

I know some people hate traits but I love them, they provide us with so much flexibility, so I created one and named it Slavable, almost went with Enslavable, but that could come across as a bit racist nowadays. * ba dum ts * Now that the awkward silence has passed after this bad joke, let's take a look at the code.

<?php

use DB;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
use Illuminate\Database\ConnectionInterface;

trait Slavable
{
    /**
     * @param $name
     * @param $query
     * @return EloquentBuilder|QueryBuilder
     */
    protected function onConnection($name, $query)
    {
        $conn = DB::connection(config("database.connections.$name") ?? 'mysql');

        if ($query instanceof QueryBuilder) {

            $newQuery = $this->transferQuery($conn, $query);

            return $newQuery;
        }

        if ($query instanceof EloquentBuilder) {

            $model = $query->getModel();

            $newEloquentQuery = new EloquentBuilder(
                $this->transferQuery($conn, $query->getQuery())
            );

            $newEloquentQuery->setModel($model);

            if ($relations = $model->getRelations()) {
                $newEloquentQuery->with($relations);
            }

            return $newEloquentQuery;
        }
    }

    /**
     * @param ConnectionInterface $conn
     * @param Builder $oldQuery
     * @return QueryBuilder
     */
    private function transferQuery(ConnectionInterface $conn, QueryBuilder $oldQuery)
    {
        $queryComponents = get_object_vars($oldQuery);

        $newQuery = new QueryBuilder(
            $conn, $oldQuery->getGrammar(), $oldQuery->getProcessor()
        );

        foreach ($queryComponents as $k => $v) {
            $newQuery->$k = $v;
        }

        $newQuery->mergeBindings($oldQuery);

        return $newQuery;
    }
}

Worked like a charm! What it does is it takes the old query instance, takes all its SQL string components and bindings, and finally transfers it to a new query with a new connection. Pretty nifty isn't it?

And for all those Trait haters here's a Class based approach.

<?php

use DB;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
use Illuminate\Database\ConnectionInterface;

class SlavableConnection
{
    protected $oldQuery;

    protected $newQuery;

    public function __construct($query)
    {
        $this->oldQuery = $query;
    }

    /**
     * @param $name
     * @return EloquentBuilder|QueryBuilder
     */
    public function change($name)
    {
        return $this->onConnection($name, $this->oldQuery);
    }

    /**
     * @return EloquentBuilder|QueryBuilder
     */
    public function getOldQuery()
    {
        return $this->oldQuery;
    }

     /**
     * @param $name
     * @param $query
     * @return EloquentBuilder|QueryBuilder
     */
    protected function onConnection($name, $query)
    {
        $conn = DB::connection(config("database.connections.$name") ?? 'mysql');

        if ($query instanceof QueryBuilder) {

            $newQuery = $this->transferQuery($conn, $query);

            return $newQuery;
        }

        if ($query instanceof EloquentBuilder) {

            $model = $query->getModel();

            $newEloquentQuery = new EloquentBuilder(
                $this->transferQuery($conn, $query->getQuery())
            );

            $newEloquentQuery->setModel($model);

            if ($relations = $model->getRelations()) {
                $newEloquentQuery->with($relations);
            }

            return $newEloquentQuery;
        }
    }

    /**
     * @param ConnectionInterface $conn
     * @param Builder $oldQuery
     * @return QueryBuilder
     */
    private function transferQuery(ConnectionInterface $conn, QueryBuilder $oldQuery)
    {
        $queryComponents = get_object_vars($oldQuery);

        $newQuery = new QueryBuilder(
            $conn, $oldQuery->getGrammar(), $oldQuery->getProcessor()
        );

        foreach ($queryComponents as $k => $v) {
            $newQuery->$k = $v;
        }

        $newQuery->mergeBindings($oldQuery);

        return $newQuery;
    }
}

Last, but not the least, a handy helper function.

<?php

if (!function_exists('connection')) {

    /**
     * Change Database connections on runtime on Query Builder instances
     *
     * @param $name
     * @param $query
     * @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder
     */
    function connection($name, $query) {
        return (new SlavableConnection($query))->change($name);
    }
}

If you liked reading this blog post, please share it!

'Till next time :)