Multiple connection support — read/write splitting
Concept
Multiple connection support and read/write splitting allow a framework to route queries to the appropriate database server. Write operations (INSERT, UPDATE, DELETE) go to the primary (write) server. Read operations (SELECT) go to one of the replica (read) servers.
Why read/write splitting:
- Database replicas handle read traffic — scale reads horizontally.
- Replicas are constantly updated from the primary.
- Write performance isn't affected by read load.
- Common in high-traffic applications.
Replication lag: The replica may be slightly behind the primary. After writing data, reading it immediately from a replica might not return the fresh data. Solutions: after a write, use the primary for subsequent reads in the same request (Laravel does this with $this->recordsModified).
DatabaseManager: A class that holds multiple named connections and a default connection. $db->connection('analytics') returns a different Connection than the default.
Read/write connection config:
'mysql' => [
'read' => ['host' => ['replica1', 'replica2']],
'write' => ['host' => 'primary'],
// ... shared config
]Framework randomly picks one replica for reads.
Connection::usingReadPdo() (internal): The connection uses the write PDO if a write has occurred in the request; otherwise uses the read PDO.
Code Example
<?php
namespace Framework\Database;
class DatabaseManager
{
private array $connections = [];
public function __construct(private readonly array $config) {}
public function connection(string $name = 'default'): Connection
{
if (!isset($this->connections[$name])) {
$this->connections[$name] = $this->makeConnection($name);
}
return $this->connections[$name];
}
private function makeConnection(string $name): Connection
{
$config = $this->config['connections'][$name]
?? throw new \InvalidArgumentException("Connection [{$name}] not configured.");
return ConnectionFactory::make($config);
}
}
class ReadWriteConnection extends Connection
{
private ?\PDO $readPdo = null;
private ?\PDO $writePdo = null;
private bool $recordsModified = false;
public function __construct(
private readonly array $readConfig,
private readonly array $writeConfig,
) {}
public function select(string $sql, array $bindings = []): array
{
// After a write, use the write connection to avoid replication lag
$pdo = $this->recordsModified ? $this->getWritePdo() : $this->getReadPdo();
$stmt = $pdo->prepare($sql);
$stmt->execute($bindings);
return $stmt->fetchAll();
}
public function insert(string $sql, array $bindings = []): bool
{
$this->recordsModified = true; // flag: use write for reads in this request
$stmt = $this->getWritePdo()->prepare($sql);
return $stmt->execute($bindings);
}
public function update(string $sql, array $bindings = []): int
{
$this->recordsModified = true;
$stmt = $this->getWritePdo()->prepare($sql);
$stmt->execute($bindings);
return $stmt->rowCount();
}
private function getReadPdo(): \PDO
{
if ($this->readPdo === null) {
// Pick a random replica
$host = $this->readConfig['host'][array_rand($this->readConfig['host'])];
$this->readPdo = $this->createPdoFromConfig(array_merge($this->readConfig, ['host' => $host]));
}
return $this->readPdo;
}
private function getWritePdo(): \PDO
{
if ($this->writePdo === null) {
$this->writePdo = $this->createPdoFromConfig($this->writeConfig);
}
return $this->writePdo;
}
private function createPdoFromConfig(array $config): \PDO
{
$dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset=utf8mb4";
return new \PDO($dsn, $config['username'], $config['password'], [
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
]);
}
}