How to Setup Laravel Database Read & Write Connections

There are times when your application is at a stage where one database for read & write is not enough and you might want to use separate databases for read & write. Luckily Laravel provides you with the flexibility to setup a separate database connection for read & write.

Let's say you have two MySQL databases with same credentials and you want to setup one for read and other for write. In you config/database.php file change the mysql configuration as follow:

'mysql' => [
    'read' => [
        'host' => '192.168.1.1',
    ],
    'write' => [
        'host' => '196.168.1.2',
    ],
    'sticky' => true,
    'driver' => 'mysql',
    'database' => 'database_name',
    'username' => 'root',
    'password' => 'secret',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

Let's say you want to setup two read databases, the configuration will be as follow:

'mysql' => [
    'read' => [
        'host' => [
            '192.168.1.1',
            '192.168.1.2',
        ],
    ],
    'write' => [
        'host' => [
            '196.168.1.3',
        ],
    ],
    'sticky' => true,
    'driver' => 'mysql',
    'database' => 'database_name',
    'username' => 'root',
    'password' => 'secret',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

Now, let's assume you have two different MySQL servers with different credentials then the configuration will be as follow:

'mysql' => [
    'read' => [
        'host' => '192.168.1.1',
        'username' => 'root',
        'password' => 'secret',
        'database' => 'database_name',
    ],
    'write' => [
        'host' => '192.168.1.2',
        'username' => 'root',
        'password' => 'secret',
        'database' => 'database_name',
    ],
    'sticky' => true,
    'driver' => 'mysql',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

Now, if you want to setup two read databases with different credentials and the configuration will be as follow:

'mysql' => [
    'read' => [
        [
            'host' => '192.168.1.1',
            'username' => 'root',
            'password' => 'secret',
            'database' => 'database_name',
         ],
         [
            'host' => '192.168.1.2',
            'username' => 'root',
            'password' => 'secret',
            'database' => 'database_name',
         ],
    ],
    'write' => [
        'host' => '192.168.1.2',
        'username' => 'root',
        'password' => 'secret',
        'database' => 'database_name',
    ],
    'sticky' => true,
    'driver' => 'mysql',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

For more configuration and options details you can refer to Laravel database connections.

Comments

[Gabriele Formenti]

This is what I was searching for! Thank you!!

[Ibrar Hussain]

I am glad that it was helpful for you.

[Wisam Saif]

How can I do the same for MS SQL SERVER with Asp .ner core?

[Ibrar Hussain]

Hi @wisam_saif I am not that familiar with Asp.net, however this might be of help: stackoverflow.com/a/48321008