Skip to content

Read Replicas and Session Routing

Send reads to a reader pool while keeping writes on the primary. Start with an explicit read session. Add request scopes or a routing session when one unit of work needs both reads and writes.

Start with One Reader

A reader endpoint is enough for a PostgreSQL config:

from sqlspec.adapters.asyncpg import AsyncpgConfig

config = AsyncpgConfig(
    connection_config={"dsn": "postgresql://app:secret@writer/app"},
    replicas="postgresql://app:secret@reader/app",
)

Use async with config.provide_replica_session() as session: for reads and config.provide_session() for writes. The read provider yields the same concrete driver type as the ordinary provider. SQLSpec.provide_replica_session(config) provides the same choice through a registry. Without a reader group, the generic provider uses the primary.

Try a Local WAL Reader

SQLite can open a read-only pool over the same WAL database:

from pathlib import Path
from tempfile import TemporaryDirectory

from sqlspec.adapters.sqlite import SqliteConfig

with TemporaryDirectory() as directory:
    config = SqliteConfig(
        connection_config={"database": str(Path(directory) / "orders.db")},
        replicas=True,
    )
    try:
        with config.provide_session() as writer:
            writer.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY)")
            writer.execute("INSERT INTO orders (id) VALUES (:id)", {"id": 1})
        with config.provide_replica_session() as reader:
            assert reader.select_value("SELECT count(*) FROM orders") == 1
    finally:
        config.close_pool()

The reader uses mode=ro and query_only=1. This gives the OS and database a chance to reject writes. A reader on another server also needs a suitable role and server policy. A read provider is a routing choice; generic primary fallback is not a client-side write sandbox.

Choose a Topology

Topology Adapters Read path
Separate endpoints or native read intent asyncpg, psycopg, psqlpy, adbc, oracledb, mssql-python, pymssql, Db2, asyncmy, aiomysql, PyMySQL, mysql-connector, SQL Anywhere, Sybase Derived pools with reader endpoints or vendor read intent
Shared consensus pool Spanner, cockroach-asyncpg, cockroach-psycopg A read snapshot or follower-read session with bounds
Local WAL readers sqlite, aiosqlite A read-only pool over a file-backed WAL database
Compute or analytical isolation Snowflake, Teradata, BigQuery, Arrow ODBC, DuckDB A reader workload, separate compute, or a native read-only config

Adapter guides describe accepted replicas= values. True has vendor-specific meaning and is rejected where an explicit endpoint is required. It does not provision a replica or copy data.

Groups, Weights, and Failover

Use a sequence for several readers, or a mapping for named groups. Weights belong to a ReplicaSpec:

from sqlspec.adapters.asyncpg import AsyncpgConfig
from sqlspec.core.routing import ReplicaSpec

config = AsyncpgConfig(
    connection_config={"dsn": "postgresql://app:secret@writer/app"},
    replicas={
        "default": [
            ReplicaSpec({"host": "reader-a"}, weight=3),
            ReplicaSpec({"host": "reader-b"}, weight=1),
        ],
        "reporting": [{"host": "reporting-reader"}],
    },
)

Request a group with replica_group="reporting". ReplicaGroup accepts explicit configs and exposes strategy="round_robin" or "random". The first uses smooth weighted round robin; the second uses weighted random selection. A group can set failure/recovery limits, a causal validator, and fallback_to_primary=False. The default permits primary fallback after eligible readers fail checkout. Failover never retries SQL after a session has been yielded.

Owned replica pools share the primary's startup, shutdown, and observability setup. Registry telemetry gives each reader a distinct key, such as orders:replica:0 or orders:replica:reporting:0. A separately registered config is counted once.

Choose the Read Interface

Interface Use it for
provide_replica_session() A query whose read intent is explicit at the call site
SQLSpecSyncService or SQLSpecAsyncService Shared read helpers, lists, pagination, and primary write blocks
Framework read dependency/accessor A route that only needs a reader
provide_routing_session() An opt-in session that chooses a physical driver lazily for each eligible operation

Service helpers such as get_one, exists, and pagination use the reader unless an explicit or already-bound session owns the work. read_config= can select a separate reader config. begin_transaction() keeps all statements on one primary driver.

A routing session sends only parsed, read-only statements to readers. Writes, locking reads, scripts, and transactions pin it to the primary. Parsing failures also choose the primary. SELECT statements that invoke write-performing user-defined functions (UDFs) still parse as read-only statements and must run inside RoutingContext.primary(). The facade owns its physical checkouts; it is not a replacement for every adapter-specific driver method. Request a concrete driver for vendor APIs.

Scope a Consistency Choice

from sqlspec.utils.routing import RoutingContext

with RoutingContext.scope():
    with RoutingContext.replica(replica_group="reporting"):
        pass
    with RoutingContext.primary():
        pass

An explicit RoutingContext.replica() choice requires a reader. It never falls back to the primary; missing replica groups or exhausted readers raise ReplicaUnavailableError. Automatic read providers retain the group's configured primary fallback. Outside an active transaction, an explicit replica scope can override a historical primary pin; normal automatic routing remains pinned.

Each scope works with with or async with and restores its caller's state. primary, replica, and auto can target a bind_key. A write pins only the active logical bind in the active request scope. An explicit conflicting replica choice during a primary transaction fails rather than splitting that transaction across sockets.

Frameworks expose db_replica_session, get_db_replica_session, or FastAPI's provide_replica_session dependency factory. The integration guides show each form. For lag, failures, tenant policies, and request headers, continue with replica operations.