Read and Write Database Connections in Laravel

Separating database into read and write connections is recommended when you can every heavy-read application. Thus, it will reduce the pressure connection flood to database when having single database.

Why recommended R/W database ? This architecture serves the purpose of safeguarding site reliability. If a site receives a lot of traffic and only single database available, it will be overloaded with reading and writing requests and leads the entire system tend to slowing down.

In I will show you 2 way you can do to configure read/write connection in Laravel.

But, please remind that, you will be facing replication lag where sync between read and write might be differ in millisecond depending on network speed and bandwidth.

Method 1 : Create new connections in database.php

This is the hard way which i used before when i too lazy to read Laravel documentation 😂.

All you need to do is duplicate the mysql connection.

'mysql' => [
     ....
],
'mysql_readonly' => [
     ....
],

Then, i created the traits and put in the Model for easy switch. It looks like something this,

trait DBQuery 
{
    public static function readonly()
    {
         return self::on('mysql_readonly');
    } 
}

and use like this

User::readonly()->get();

The bad thing about this method is where when you use readonly() function, you are unable to write using the result. You need to query again without readonly and perform update/create/delete. By default, all the connection will goes to “mysql”. This is very tons of work to customize

Method 2: Modify current connection in database.php (Recommended)

The Laravel framework database setting makes this read/write configuration easy. You may refer here. Below is the default mysql setting,

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' =>…