Laravel Read / Write Connections use multi-database

image from google

Why do you use it?

Sometimes you may wish to use backup database and live database for handling maximum user interaction

Out of the box Laravel always you to have read/write database connections. This will allow you to have different database connections for SELECT queries and INSERT, UPDATE and DELETE queries.

The default laravel connection config looks like this.

'mysql' => [
    'driver' => 'mysql',
    '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', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

To have a different connection used for reading queries you need to add a new read item to the config.

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'read' => [
                'host' => [
                    '172.17.0.1',
                ],
            ],
            'write' => [
                'host' => [
                    env('DB_HOST', '127.0.0.1'),
                ],
            ],
            // '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', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            
        ],

if you want to add a multi-read or Write server then you can use the below conf:

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'read' => [
                'host' => [
                    '127.0.0.2',
                    '127.0.0.3',
                    '127.0.0.4',
                    '127.0.0.5',
                ],
            ],
            'write' => [
                'host' => [
                    env('DB_HOST', '127.0.0.1'),
                ],
            ],
            // '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', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            
        ],

Replication Lag

With database setups like this, there will need to be some database replication that will copy the database records from the write database to the read database.

The problem you might face with different read/write connections is replication lag. This is the time it takes for the database to be mirrored to the read connection database. Typically this is 10/100ms it’s dictated by the network speed available to you, therefore, can change depending on bandwidth traffic.

You could face the problem of inserting a new record in the database, then querying the database to fetch the inserted record, but because there are different databases the data may not be there in the read connection. This will cause your application to fail with a model not found as an exception.

One way that you can get round this in Laravel is to use the sticky option in your database config.

'sticky' => true

When this config is set to true it will enable the application to use the write connection, when a read connection is used directly after a write connection during the current request cycle. This will ensure the same data inserted with the write connection will exist when it tries to be fetched in later read connections.

Then when you need to force a select using the write connection, you can do this:

DB::connection('mysql::write')->select(...);

Or if you are using Eloquent:

User::on('mysql::write')->find(1);
User::on('mysql::read')->get();
User::on('mysql::read')->all();

or

User::on('mysql::read')->find(1);

The above config will use 5 database connections, one write and 4 read connections.

If you look inside the laravel database connection factory in the method createPdoResolverWithHosts.

\Illuminate\Database\Connectors\ConnectionFactory::createPdoResolverWithHosts

You will see the Laravel will perform an array_shuffle on the hosts to pick a read connection at random.

Now you can have multiple database connections with your Laravel application.

My Publis Article List

Multi-threading in PHP

Multi-threading in PH

Laravel Read / Write Connections use multi-database

Laravel Read / Write Connections use multi-database

Socket Programming in PHP

Socket Programming in PHP