# Database read replicas Read replicas let Bootgly send safe read queries to replica pools while keeping writes on the primary database. This is useful when a worker handles many read-heavy HTTP requests and the primary should stay focused on writes, locks and transactions. Bootgly keeps one routing path inside `SQL::query()`: each query is normalized, classified as read or write, then assigned to the primary pool or one of the replica pools. ## Configure replicas In code, pass a `routing.sticky` window and a `replicas` list to `SQL`: ```php use Bootgly\ADI\Databases\SQL; $Database = new SQL([ 'host' => 'primary.db.local', 'routing' => [ 'sticky' => 5.0, ], 'replicas' => [ [ 'host' => 'replica-1.db.local', 'pool' => [ 'min' => 0, 'max' => 8, ], ], [ 'host' => 'replica-2.db.local', 'statements' => 128, ], ], ]); ``` Replica configs inherit the primary connection fields unless a replica overrides them. Set a replica `host` to enable that endpoint. Optional fields include `driver`, `port`, `database`, `username`, `password`, `timeout`, `secure`, `pool` and `statements`. `routing.sticky` is the best-effort read-after-write window in seconds. After a write, reads inside the same logical scope stay on primary until the window expires. ## Project config The demo HTTP project exposes the same options through `configs/database/database.Config.php`. Add `Routing->Sticky` and one or more `Replicas` nodes under the selected connection: ```php use Bootgly\ADI\Databases\SQL\Config as ADIConfig; use Bootgly\API\Environment\Configs\Config; use Bootgly\API\Environment\Configs\Config\Types; return new Config(scope: 'database') ->Default->bind(key: 'DB_CONNECTION', default: ADIConfig::DEFAULT_DRIVER) ->Connections ->PostgreSQL ->Host->bind(key: 'DB_HOST', default: ADIConfig::DEFAULT_HOST) ->Routing ->Sticky->bind(key: 'DB_ROUTING_STICKY', default: ADIConfig::DEFAULT_ROUTING_STICKY, cast: Types::Float) ->up() ->Replicas ->Replica1 ->Host->bind(key: 'DB_REPLICA_1_HOST', default: null) ->Port->bind(key: 'DB_REPLICA_1_PORT', default: null, cast: Types::Integer) ->Database->bind(key: 'DB_REPLICA_1_NAME', default: null) ->Username->bind(key: 'DB_REPLICA_1_USER', default: null) ->Password->bind(key: 'DB_REPLICA_1_PASS', default: null) ->Timeout->bind(key: 'DB_REPLICA_1_TIMEOUT', default: null, cast: Types::Float) ->Statements->bind(key: 'DB_REPLICA_1_STATEMENTS', default: null, cast: Types::Integer); ``` When using `DatabaseConfig`, the API adapter maps `Routing->Sticky` to `routing.sticky` and each enabled replica node to the ADI-native `replicas` list. The demo project accepts `DB_ROUTING_STICKY` and the following keys for `Replica1` and `Replica2`: ```text DB_REPLICA_1_HOST DB_REPLICA_1_PORT DB_REPLICA_1_NAME DB_REPLICA_1_USER DB_REPLICA_1_PASS DB_REPLICA_1_TIMEOUT DB_REPLICA_1_STATEMENTS DB_REPLICA_1_SSLMODE DB_REPLICA_1_SSLVERIFY DB_REPLICA_1_SSLPEER DB_REPLICA_1_SSLCAFILE DB_REPLICA_1_POOL_MIN DB_REPLICA_1_POOL_MAX ``` Use the same suffixes for `DB_REPLICA_2_*`. A replica without `*_HOST` is ignored. ## Routing rules Builder queries are the most precise path. `Builder::compile()` marks a query as readable only when it is a plain `SELECT` without a lock. `SELECT ... FOR UPDATE` and other locking reads go to primary. Raw SQL uses a conservative classifier: - `SELECT`, `SHOW` and non-analyzing `EXPLAIN` can use replicas. - Read-only `WITH ... SELECT` CTEs can use replicas. - `INSERT`, `UPDATE`, `DELETE`, `MERGE`, DDL and unknown statements go to primary. - `EXPLAIN ANALYZE` goes to primary because it executes the query. Writes, DDL, transactions and locked builders always use the primary pool. ### Security verdicts The SQL-backed security stores do not make authentication or revocation decisions from potentially stale replicas. `Users` credential lookups, `Tokens` action-token lookups and `Trust` trusted-device lookups are forced to the primary. This includes a remember-token rotation verdict: replica lag must not turn the current validator into a false theft incident or revive a token already revoked on primary. These forced reads use a transient routing scope. They do not start or extend read-after-write stickiness for unrelated queries later in the same worker. When a security store is backed by a `Transaction`, the transaction remains pinned to its primary connection and obtains the verdict on that connection. MySQL and PostgreSQL use `SELECT ... FOR UPDATE`: MySQL reads the latest row, while PostgreSQL Read Committed reads the current row and Repeatable Read or Serializable can reject a row changed after the snapshot with SQLSTATE `40001`. That rejection is fail-closed; roll back and retry the entire transaction. SQLite does not support `FOR UPDATE`. The stores first execute a zero-row DML barrier on the same transaction, which reserves the writer before the SELECT or fails closed with `database is locked` when a WAL snapshot is already stale. The barrier and row locks remain until commit/rollback and read-only transactions cannot acquire them. Keep these transactions short and handle database rollback/retry. The constructor signatures and public store APIs do not change. ## Read-after-write scope Bootgly tracks read-after-write stickiness with a logical scope object. In WPI, the built-in Database Response Resource binds that scope to the current response request lifecycle, so normal and deferred response work share the same stickiness scope without leaking it to later keep-alive requests. When using `SQL` directly across Fibers, pass the same scope object to related writes and reads: ```php use stdClass; $Scope = new stdClass; $Database->query('UPDATE users SET name = $1 WHERE id = $2', ['Ada', 7], $Scope); $Read = $Database->query('SELECT name FROM users WHERE id = $1', [7], $Scope); ``` The sticky window is timing-based. It is not a causal LSN or GTID wait. If your application needs strict replica freshness, keep that read on primary or add a database-specific causal wait before reading from replicas. ## Failover and quarantine Replica reads fall back to primary once when the replica operation fails. Connection, handshake and socket-establishment failures count against the replica pool health. Query timeouts fall back but do not quarantine the replica. After `Pool::DEFAULT_FAILURES` consecutive health-affecting failures, the replica is skipped until its jittered retry window expires or the pool recovers after a successful operation. ## Next references - **[Database DBAL](/guide/database-dbal/overview/)** - register the Database Response Resource in HTTP projects. - **[Database queries](/guide/database-queries/overview/)** - build read and write statements. - **[Database transactions](/guide/database-transactions/overview/)** - pin writes to one primary connection.