create-read-write-proxy

name: create-read-write-proxy description: Generates Read/Write Connection Proxy for PHP 8.4. Creates connection wrapper with query routing logic, transaction-aware routing, and replica health checks. Includes unit tests.

Read/Write Connection Proxy Generator

Creates database connection proxy infrastructure for read/write splitting and replica routing.

When to Use

Component Characteristics

ReadWriteConnectionInterface

• Connection contract abstracting primary/replica routing
• Query, execute, transaction methods
• Transparent to application code

ReadWriteConnectionProxy

• Routes SELECT queries to replicas automatically
• Sends INSERT/UPDATE/DELETE to primary
• Transaction-aware: all queries in transaction go to primary
• Sticky connection after write within same request

ConnectionConfig

• Configuration Value Object for primary and replica DSNs
• Supports multiple replica endpoints
• Immutable with validation

ReplicaHealthChecker

• Monitors replica availability
• Removes unhealthy replicas from rotation
• Periodic health check with configurable interval

Generation Process

Step 1: Generate Core Components

Path: src/Infrastructure/Database/

1. ReadWriteConnectionInterface.php — Connection contract
2. ConnectionConfig.php — Configuration value object
3. ConnectionRole.php — Enum (Primary, Replica)

Step 2: Generate Proxy

Path: src/Infrastructure/Database/

1. ReadWriteConnectionProxy.php — Query routing proxy

Step 3: Generate Health Checker

Path: src/Infrastructure/Database/

1. ReplicaHealthChecker.php — Replica availability monitor

Step 4: Generate Tests

1. ConnectionConfigTest.php — Configuration validation tests
2. ReadWriteConnectionProxyTest.php — Routing behavior tests

File Placement

Naming Conventions

Quick Template Reference

ReadWriteConnectionInterface

interface ReadWriteConnectionInterface
{
    /** @return list<array<string, mixed>> */
    public function query(string $sql, array $params = []): array;
    public function execute(string $sql, array $params = []): int;
    public function beginTransaction(): void;
    public function commit(): void;
    public function rollback(): void;
    public function inTransaction(): bool;
}

ConnectionConfig

final readonly class ConnectionConfig
{
    /** @param list<string> $replicaDsns */
    public function __construct(
        public string $primaryDsn,
        public array $replicaDsns = [],
        public bool $stickyAfterWrite = true
    ) {}
}

ReadWriteConnectionProxy

final class ReadWriteConnectionProxy implements ReadWriteConnectionInterface
{
    public function query(string $sql, array $params = []): array;
    public function execute(string $sql, array $params = []): int;
    public function beginTransaction(): void;
    public function commit(): void;
    public function rollback(): void;
}

Usage Example

$config = new ConnectionConfig(
    primaryDsn: 'mysql:host=primary.db;dbname=app',
    replicaDsns: [
        'mysql:host=replica1.db;dbname=app',
        'mysql:host=replica2.db;dbname=app',
    ],
    stickyAfterWrite: true
);

$connection = new ReadWriteConnectionProxy($config, $pdoFactory, $healthChecker);

// Routed to replica
$users = $connection->query('SELECT * FROM users WHERE active = ?', [1]);

// Routed to primary
$connection->execute('UPDATE users SET name = ? WHERE id = ?', ['John', 1]);

// After write, reads also go to primary (sticky)
$user = $connection->query('SELECT * FROM users WHERE id = ?', [1]);

Routing Logic

query(SQL) ──→ Is SELECT?
                │          │
               YES         NO
                │          │
         In transaction? ──→ Primary
                │
         Sticky after write? ──→ Primary
                │
         Pick healthy replica ──→ Replica
                │
         No healthy replicas? ──→ Primary (fallback)

Anti-patterns to Avoid

References

For complete PHP templates and examples, see:

• references/templates.md — ConnectionInterface, Proxy, Config, HealthChecker templates
• references/examples.md — Repository integration examples and tests