DEV Community

Cover image for How to Setup Laravel Database Read & Write Connections
Ibrar Hussain
Ibrar Hussain

Posted on

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' => '',
],
Enter fullscreen mode Exit fullscreen mode

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' => '',
],
Enter fullscreen mode Exit fullscreen mode

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' => '',
],
Enter fullscreen mode Exit fullscreen mode

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' => '',
],
Enter fullscreen mode Exit fullscreen mode

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

Top comments (4)

Collapse
 
gabrielef profile image
Gabriele Formenti

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

Collapse
 
ibrarturi profile image
Ibrar Hussain

I am glad that it was helpful for you.

Collapse
 
wisam_saif profile image
Wisam Saif

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

Collapse
 
ibrarturi profile image
Ibrar Hussain

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