Laravel Read And Write Database Connections

Out of the box Laravel always you to have read/write database connections. This will allow you 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 read queries you need to add a new read item to the config.

'mysql' => [
    'write' => [
      'host' => [
        '127.0.0.1'
      ]
    ],
    'read' => [
      'host' => [
        '127.0.0.2'
      ]
    ],
    'driver' => 'mysql',
    '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,
],

Now this config will use 127.0.0.2 for SELECT queries and 127.0.0.1 for write queries.

#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 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 they're are on different databases the data may not be there in the read connection. This will cause your application to fail with a model not found 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.

#Using Multiple Read Connections

When you have an application with data replication across READ and WRITE connections. Then you may have multiple read connections. With the above configuration Laravel can handle this out of the by adding more elements to the read.host IPs.

'mysql' => [
    'write' => [
      'host' => [
        '127.0.0.1'
      ]
    ],
    'read' => [
      'host' => [
        '127.0.0.2', '127.0.0.3', '127.0.0.4', '127.0.0.5',
      ]
    ],
    'driver' => 'mysql',
    '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,
],

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.