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.

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

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' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
...
...
],

Change above setting into below setting

'mysql' => [
'read' => [
'host' => [
env('DB_HOST_READONLY', '127.0.0.1'),
env('DB_HOST_READONLY_2', '127.0.0.1')
],
],
'write' => [
'host' => [
env('DB_HOST', '127.0.0.1'),
],
],
'sticky' => true,
'driver' => 'mysql',
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
...
...
],

Note that three keys have been added to the configuration array: read, write and sticky. The read and write keys have array values containing a single key: host. The rest of the database options for the read and write connections will be merged from the main mysql configuration array.

If you see “sticky” word there, if enabled, any "write" query operation performed against the database during the current request cycle, any further "read" operations will use the "write" connection. This ensures that any data written during the request cycle can be immediately read back from the database during that same request. I recommended you to enable the sticky option

For extra, incase, you have different credentials, you also can do like below…

'mysql' => [
'read' => [
'host' => [
env('DB_HOST_READONLY', '127.0.0.1'),
env('DB_HOST_READONLY_2', '127.0.0.1')
],
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
],
'write' => [
'host' => [
env('DB_HOST', '127.0.0.1'),
],
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
],
'sticky' => true,
'driver' => 'mysql',
'port' => env('DB_PORT', '3306'),
...
...
],

Then, you doesn’t need to create any extra class/trait or what so ever, just use your query like normal.

So now, you have Read Write database connections with your Laravel application. 😬

Thanks for your time

~~~Happy Working~~~

Software Engineer at Teratotech.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store