Read/Write Splitting in SQLAlchemy with Multiple Binds

Problem statement: Your SQLAlchemy application sends every query — including 90% read-only SELECTs — to the primary, and you want the ORM itself to send flushes and DML to a writer engine while SELECTs fan out to a read replica, without rewriting every call site.

Symptom Identification

These are the signals that push a team toward multiple-bind routing in SQLAlchemy 2.x:

  • pg_stat_activity on the primary shows a flood of SELECT statements from your application role, while the replica sits nearly idle — the ORM has one engine and no split.
  • Read-heavy endpoints saturate the primary’s connection pool (QueuePool limit of size X overflow Y reached) even though a replica is provisioned and healthy.
  • A naive fix — pointing the whole app at the replica — produces ERROR: cannot execute INSERT in a read-only transaction the moment a write path runs.
  • After adding a second engine by hand at some call sites, you get inconsistent results because developers forget to pick the right engine, and code review cannot catch a missing bind=.
  • Read-your-writes bugs appear: a POST creates a row, the follow-up GET reads a replica that has not yet applied the WAL, and the user sees “not found.”

Root Cause Analysis

SQLAlchemy’s Session acquires a connection lazily through Session.get_bind(). By default that method returns the single engine the Session was configured with, so there is no routing layer at all — every statement, read or write, uses the same Engine and therefore the same database. To split traffic you must override get_bind() so it inspects what the Session is about to do and returns a different Engine accordingly.

Two facts drive a correct override. First, during the unit-of-work flush the Session sets an internal flag, self._flushing, to True; any query issued while that flag is set is part of persisting dirty objects and must go to the writer. Second, when you call Session.execute() with a Core Update, Delete, or Insert construct, that construct arrives as the clause argument and can be pattern-matched. Reads — ORM select() loads and Core Select statements — arrive with _flushing unset and a non-DML clause, and are safe to route to a replica.

This is the same in-process routing decision described in the parent guide on ORM middleware for automatic query routing, specialized to SQLAlchemy’s get_bind() hook. The subtlety unique to SQLAlchemy is that a single Session can hold a transaction open on both engines simultaneously, which interacts with autoflush and replication lag in ways covered below.

Architecture: get_bind() as the Split Point

SQLAlchemy multiple-bind routing through get_bind() A Session dispatches each statement to get_bind, which checks the flushing flag, the clause type, and a force-writer ContextVar. Flushes and DML route to the writer engine and primary database; SELECTs route to the reader engine and replica; a set ContextVar overrides reads to the writer. RoutingSession execute / flush get_bind() _flushing? Update/Delete? force_writer var? writer_engine flush · DML · forced reads WRITE reader_engine SELECT · read-only READ Primary Replica

Step-by-Step Resolution

Step 1: Create the writer and reader engines

Build two engines. The writer points at the primary; the reader points at the replica endpoint (a DNS name or load-balancer VIP fronting one or more replicas). Mark the reader read-only so a stray write fails loudly instead of silently hitting a read-only backend.

python
# db.py
from sqlalchemy import create_engine

writer_engine = create_engine(
    "postgresql+psycopg://app:secret@primary.db.internal:5432/app",
    pool_size=10, max_overflow=20, pool_pre_ping=True,
)

reader_engine = create_engine(
    "postgresql+psycopg://app_ro:secret@replica-lb.internal:5432/app",
    pool_size=20, max_overflow=40, pool_pre_ping=True,
    # Fail fast if a write is misrouted here
    execution_options={"postgresql_readonly": True},
)

Inline verification: writer_engine.connect().execute(text("SELECT pg_is_in_recovery()")).scalar() returns False; the same on reader_engine returns True.

Step 2: Subclass Session and override get_bind()

This is the routing core. The official SQLAlchemy pattern checks self._flushing (set during the unit-of-work flush) and the clause type. Add a ContextVar so business logic can force a read onto the writer for read-your-writes.

python
# routing.py
from contextvars import ContextVar
from sqlalchemy import Update, Delete, Insert
from sqlalchemy.orm import Session
from db import writer_engine, reader_engine

force_writer: ContextVar[bool] = ContextVar("force_writer", default=False)

class RoutingSession(Session):
    def get_bind(self, mapper=None, clause=None, bind=None, **kw):
        # Explicit engine passed to execute(bind=...) always wins
        if bind is not None:
            return bind
        # Escape hatch: caller demanded the writer (read-your-writes)
        if force_writer.get() or kw.get("realm") == "writer":
            return writer_engine
        # Persisting dirty objects, or an explicit write construct
        if self._flushing or isinstance(clause, (Update, Delete, Insert)):
            return writer_engine
        return reader_engine

self._flushing guarantees that every INSERT/UPDATE/DELETE emitted by the unit of work reaches the writer even though the objects were loaded from the reader. The isinstance check covers Core DML executed directly via session.execute(update(...)).

Inline verification: with writer_engine and reader_engine created with echo=True, a session.execute(select(User)) logs on the reader; a session.add(User(...)); session.flush() logs on the writer.

Step 3: Wire the routing Session into a sessionmaker

Never instantiate RoutingSession directly in application code. Bind it through sessionmaker so pooling, expire_on_commit, and lifecycle are consistent.

python
# session.py
from sqlalchemy.orm import sessionmaker
from routing import RoutingSession

SessionLocal = sessionmaker(class_=RoutingSession, expire_on_commit=False)

Do not pass a bind= to the sessionmaker — get_bind() supplies the engine per statement. Passing a bind would make it the fallback but is unnecessary here.

Inline verification: SessionLocal().get_bind(clause=select(User)) is reader_engine; SessionLocal()._flushing context routes to writer_engine.

Step 4: Add scoped sessions for threaded frameworks

Classic threaded frameworks (Flask, Celery workers) want one Session per thread. scoped_session gives that, and the routing logic rides along unchanged.

python
# scoped.py
from sqlalchemy.orm import scoped_session
from session import SessionLocal

Session = scoped_session(SessionLocal)

# In a Flask teardown handler:
#   @app.teardown_appcontext
#   def remove_session(exc=None):
#       Session.remove()

Session.remove() at the end of each request rolls back and returns connections on both the writer and the reader — critical, because a request that both wrote and read holds an open transaction on each engine.

Inline verification: after Session.remove(), SHOW POOLS-equivalent writer_engine.pool.checkedout() returns to its idle baseline.

Step 5: Route correctly with async engines

For asyncio stacks (FastAPI, async workers), use create_async_engine and async_sessionmaker, and hand your RoutingSession in via sync_session_class. The AsyncSession wraps a synchronous Session, so your get_bind() runs unchanged under the hood.

python
# async_db.py
from contextvars import ContextVar
from sqlalchemy import Update, Delete, Insert
from sqlalchemy.orm import Session
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession

async_force_writer: ContextVar[bool] = ContextVar("async_force_writer", default=False)

async_writer = create_async_engine("postgresql+asyncpg://app@primary.db.internal/app", pool_size=10)
async_reader = create_async_engine("postgresql+asyncpg://app_ro@replica-lb.internal/app", pool_size=20)

class AsyncRoutingSession(Session):
    def get_bind(self, mapper=None, clause=None, bind=None, **kw):
        if async_force_writer.get():
            return async_writer.sync_engine
        if self._flushing or isinstance(clause, (Update, Delete, Insert)):
            return async_writer.sync_engine
        return async_reader.sync_engine

AsyncSessionLocal = async_sessionmaker(
    bind=async_writer, sync_session_class=AsyncRoutingSession, expire_on_commit=False,
)

Note that get_bind() must return the .sync_engine of each async engine because the wrapped session is synchronous. ContextVar is the correct scoping primitive here — a threading.local() would not follow await boundaries.

Inline verification: await session.execute(select(User)) opens a connection on async_reader; await session.commit() after an add() commits on async_writer. Confirm with application_name on each backend.

Step 6: Confirm the split end to end

Run a mixed workload and inspect both backends.

python
from sqlalchemy import text
from session import SessionLocal
from routing import force_writer

with SessionLocal() as s:
    # Read -> replica
    assert s.execute(text("SELECT pg_is_in_recovery()")).scalar() is True
    # Write -> primary (flush routes here)
    s.add(User(email="a@b.com")); s.flush()
    # Force a read-your-writes read onto the primary
    tok = force_writer.set(True)
    assert s.execute(text("SELECT pg_is_in_recovery()")).scalar() is False
    force_writer.reset(tok)
    s.commit()

Inline verification: the first assertion proves the reader is a standby; the second proves the forced read hit the primary.

Configuration Snippet

A minimal, production-shaped module tying it together:

python
# routing_complete.py
from contextvars import ContextVar
from sqlalchemy import create_engine, Update, Delete, Insert
from sqlalchemy.orm import Session, sessionmaker, scoped_session

force_writer: ContextVar[bool] = ContextVar("force_writer", default=False)

writer_engine = create_engine("postgresql+psycopg://app@primary.db.internal/app",
                              pool_size=10, max_overflow=20, pool_pre_ping=True)
reader_engine = create_engine("postgresql+psycopg://app_ro@replica-lb.internal/app",
                              pool_size=20, max_overflow=40, pool_pre_ping=True,
                              execution_options={"postgresql_readonly": True})

class RoutingSession(Session):
    def get_bind(self, mapper=None, clause=None, bind=None, **kw):
        if bind is not None:
            return bind
        if force_writer.get() or kw.get("realm") == "writer":
            return writer_engine
        if self._flushing or isinstance(clause, (Update, Delete, Insert)):
            return writer_engine
        return reader_engine

Session = scoped_session(sessionmaker(class_=RoutingSession, expire_on_commit=False))

Verification and Rollback

Confirm the routing is live and correct:

python
# Which backend served the last read?
Session.execute(text("SELECT inet_server_addr(), pg_is_in_recovery()")).one()

# Count checked-out connections per engine under load
writer_engine.pool.checkedout(), reader_engine.pool.checkedout()

To roll back to single-engine behavior without touching call sites, make get_bind() return the writer unconditionally, or point reader_engine at the primary URL and redeploy:

python
class RoutingSession(Session):
    def get_bind(self, mapper=None, clause=None, bind=None, **kw):
        return writer_engine   # temporary: everything on primary

This is a safe kill switch during a replica incident: all traffic collapses onto the primary, and no application code changes. Once the replica is healthy again, restore the routing method and redeploy.

Edge Cases and Gotchas

Autoflush routes a read to the writer mid-transaction

If you add() an object and then run a select() in the same Session before committing, SQLAlchemy’s autoflush fires first. During that flush self._flushing is True, so the flush lands on the writer — correct. But the subsequent select() runs with _flushing back to False and goes to the reader, which has not seen the uncommitted write. Inside an uncommitted unit of work, prefer reading through the writer (set force_writer) or commit before reading. This is the SQLAlchemy-specific face of the general read-your-writes consistency problem.

One Session, two open transactions

A Session that both reads and writes opens a transaction on the reader and the writer. They are independent: the reader transaction sees a snapshot from before your uncommitted writes. Session.commit() commits both; Session.rollback() rolls back both. Keep sessions short and always close them (with SessionLocal() as s: or scoped_session.remove()), or you leak an idle-in-transaction connection on each engine.

Session.begin() and explicit transactions still split

Wrapping work in with Session.begin(): does not pin everything to the writer. get_bind() is still consulted per statement, so reads inside the block go to the replica unless you force the writer. If you need a fully serializable read-write transaction, run it entirely through the writer by setting force_writer for the block, or use a dedicated writer-only sessionmaker. For hard consistency requirements, see how to force primary reads for critical user transactions.

FAQ

Does a single SQLAlchemy Session open transactions on both engines at once?

Yes. A Session with multiple binds maintains an independent transaction per engine it touches. The first SELECT starts a read transaction on the reader; the first flush starts a write transaction on the writer. Session.commit() commits both, so a mid-request write followed by a read leaves two open transactions until commit or rollback.

Why do my reads go to the writer inside a transaction?

During a flush the Session sets self._flushing, and the recommended get_bind checks that flag. If you have already flushed pending writes in the same Session, autoflush will run before each query and route the flush to the writer. Use expire_on_commit=False with short sessions, or force explicit reads through the reader with bind_arguments if you accept the staleness.

How do I force a read onto the writer for read-your-writes?

Set a request-scoped ContextVar such as force_writer=True immediately after a write, and have get_bind return the writer engine while it is set. Alternatively pass bind_arguments={"realm": "writer"} to Session.execute, which forwards the key to get_bind. Clear the flag after the causal window closes so later reads return to the replica.

← Back to ORM Middleware for Automatic Query Routing