Routing models to multiple databases with session binds

Pass binds={Product: catalogue_engine, Invoice: billing_engine} to async_sessionmaker and the session routes each statement by the model it targets — but a commit then commits each engine's transaction separately, so a unit of work that must be atomic has to stay inside one database. This guide belongs to dynamic schema and multi-tenant routing.

Quick Answer

A session can be bound to more than one engine, keyed by the models each engine owns.

Before — a session bound to one engine cannot see the other database:

from sqlalchemy import select
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

from shop.models import Invoice, Product

catalogue = create_async_engine("postgresql+asyncpg://shop:secret@catalogue/shop")
Session = async_sessionmaker(catalogue, expire_on_commit=False)

async with Session() as session:
    await session.execute(select(Invoice))
# sqlalchemy.exc.ProgrammingError: (asyncpg.exceptions.UndefinedTableError)
# relation "invoices" does not exist

After — one session, routed by model:

from sqlalchemy import select
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

from shop.models import Invoice, Product

catalogue = create_async_engine("postgresql+asyncpg://shop:secret@catalogue/shop", pool_size=10)
billing = create_async_engine("postgresql+asyncpg://shop:secret@billing/shop", pool_size=5)

Session = async_sessionmaker(
    binds={Product: catalogue, Invoice: billing},
    expire_on_commit=False,
)

async with Session() as session:
    products = (await session.scalars(select(Product))).all()   # catalogue
    invoices = (await session.scalars(select(Invoice))).all()    # billing

binds accepts mapped classes, Table objects, or a common base class — {CatalogueBase: catalogue, BillingBase: billing} routes whole model hierarchies, which is usually cleaner than listing every class.

What this does not give you is a transaction across both databases. await session.commit() commits the catalogue transaction and then the billing one, and the second can fail after the first has succeeded.

Execution Context & Async Workflow Integration

When a session executes a statement, it determines which mapper the statement targets and asks get_bind() which engine to use. The binds mapping is the declarative way to answer: SQLAlchemy looks up the mapper, then its base classes, then the table, and uses the first entry it finds. A bind= engine passed alongside acts as the fallback for anything unmapped — raw text() statements, for instance, which have no mapper to route by.

Each engine the session touches gets its own connection and its own transaction, begun lazily on first use. That is what makes routing work, and also what limits it: PostgreSQL has no distributed transaction across two servers that SQLAlchemy uses by default, so a session spanning two engines is a session with two transactions.

commit() therefore commits them in sequence. If the second fails — a constraint violation, a lost connection, a failover — the first is already durable. There is no rollback for it, and SQLAlchemy does not pretend otherwise. Two-phase commit exists (twophase=True with a suitable backend and max_prepared_transactions configured), and it is rarely worth the operational cost: prepared transactions that are never resolved hold locks and block vacuum indefinitely.

The practical rule follows: every unit of work should write to at most one database. Cross-database reads in one session are fine and useful. Cross-database writes need a design that tolerates partial failure — an idempotency key so the second attempt is safe, or an outbox table in the first database that a worker reads and applies to the second.

For read-replica routing, the more flexible hook is get_bind() on a Session subclass, because the decision depends on the statement rather than on the model:

from sqlalchemy.orm import Session


class RoutingSession(Session):
    def get_bind(self, mapper=None, clause=None, **kwargs):
        if self._flushing or self.info.get("wrote"):
            return primary_engine            # read your own writes
        return replica_engine

self._flushing is true while the session is writing, so flushes always go to the primary. Setting self.info["wrote"] = True on the first write — from an after_flush event — keeps the rest of that session's reads on the primary too, which is what prevents the "your order could not be found" failure. The full replica-routing treatment, including lag measurement, is in routing reads to replicas with async engines.

Using a custom Session class with async is one line: async_sessionmaker(sync_session_class=RoutingSession), because AsyncSession wraps a synchronous Session and that is the class to customise.

Resolving Warnings, Errors & Common Mistakes

The MetaData point is worth acting on before the first model is written, because it makes everything else clearer. One base per database means the binds mapping is two entries, table names cannot collide, and Alembic's target_metadata is unambiguous:

from sqlalchemy import MetaData
from sqlalchemy.orm import DeclarativeBase


class CatalogueBase(DeclarativeBase):
    metadata = MetaData(schema=None, naming_convention=NAMING_CONVENTION)


class BillingBase(DeclarativeBase):
    metadata = MetaData(schema=None, naming_convention=NAMING_CONVENTION)


Session = async_sessionmaker(
    binds={CatalogueBase: catalogue, BillingBase: billing},
    expire_on_commit=False,
)

Migrations then need one Alembic environment per base, which running Alembic across multiple databases and tenant schemas covers — a separate revision directory per database keeps each history linear and reviewable.

One more limitation to know: relationships cannot span engines. A ForeignKey from a catalogue table to a billing table is not enforceable, and relationship() between models on different engines cannot emit a join. Cross-database references are carried as plain id columns, with the lookup done in the application — which is a real loss of integrity and a good reason to be sure two databases are genuinely necessary.

Advanced: Cross-Database Writes Without Distributed Transactions

When a workflow genuinely has to write to two databases, the durable answer is an outbox: the first database records the intent in the same transaction as the work, and a worker delivers it to the second.

import datetime as dt

from sqlalchemy import JSON, DateTime, String, select, update
from sqlalchemy.orm import Mapped, mapped_column

from shop.models import CatalogueBase


class OutboxEvent(CatalogueBase):
    __tablename__ = "outbox_events"

    id: Mapped[int] = mapped_column(primary_key=True)
    topic: Mapped[str] = mapped_column(String(64))
    payload: Mapped[dict] = mapped_column(JSON)
    created_at: Mapped[dt.datetime] = mapped_column(DateTime(timezone=True))
    delivered_at: Mapped[dt.datetime | None] = mapped_column(
        DateTime(timezone=True), default=None, index=True
    )

The write path stays inside one database and one transaction:

async def publish_product(session, product: Product) -> None:
    async with session.begin():                     # catalogue only
        session.add(product)
        session.add(OutboxEvent(
            topic="product.published",
            payload={"sku": product.sku, "price_cents": product.price_cents},
            created_at=dt.datetime.now(dt.UTC),
        ))

Either both rows commit or neither does, because they are in the same database. A worker then reads undelivered events and applies them to the billing database, marking each delivered in its own transaction:

async def deliver_outbox(CatalogueSession, BillingSession, batch: int = 100) -> int:
    async with CatalogueSession() as read:
        events = list((await read.scalars(
            select(OutboxEvent)
            .where(OutboxEvent.delivered_at.is_(None))
            .order_by(OutboxEvent.id)
            .limit(batch)
            .with_for_update(skip_locked=True)
        )))
        if not events:
            return 0
        async with BillingSession.begin() as write:
            for event in events:
                await apply_to_billing(write, event)      # idempotent by event id
        await read.execute(
            update(OutboxEvent)
            .where(OutboxEvent.id.in_([e.id for e in events]))
            .values(delivered_at=dt.datetime.now(dt.UTC))
        )
        await read.commit()
    return len(events)

The delivery is at-least-once: a crash between the billing commit and the outbox update re-delivers, which is why apply_to_billing must be idempotent — keyed on the event id, with a unique constraint enforcing it. That is the same guarantee, and the same requirement, as in handling IntegrityError on concurrent inserts.

SKIP LOCKED lets several workers share the outbox without coordination — the job-queue pattern from building a job queue with SELECT FOR UPDATE SKIP LOCKED.

Compared with two-phase commit, the outbox is more code and far less operational risk: no prepared transactions to leak, no coordinator to run, and a failure mode — delayed delivery — that is visible in a table anyone can query.

Deciding Whether Two Databases Are Warranted

Session binds make multiple databases workable, which makes it worth asking whether they are warranted, because the costs are structural rather than incidental.

What you lose. Foreign keys cannot cross databases, so referential integrity between the two becomes application logic that will eventually be wrong. Joins cannot cross either, so a query that needs both does two round trips and combines in Python. Transactions cannot span them. And every operational concern doubles: two connection pools, two backup schedules, two migration histories, two sets of credentials.

What genuinely justifies it. A database owned by another team, where the boundary is organisational rather than technical. A legacy system being migrated away from, where both are live for a transition. A read replica, which is the same data and therefore none of these problems. Or a hard requirement — a contract that says a customer's data lives in a specific region.

What usually does not. "Separating concerns" between two services that are deployed together and change together; the boundary adds cost without buying independence. Performance, which is almost always better served by indexes, caching or a replica than by splitting the schema. And future-proofing, which pays the cost now for an option that may never be exercised.

For multi-tenancy specifically, the choice between a tenant column, a schema per tenant and a database per tenant is worth making on its own terms, and the trade-offs are laid out in the parent guide on dynamic schema and multi-tenant routing and in enforcing tenant isolation with Postgres row-level security. A database per tenant is the strongest isolation and multiplies every operational task by the tenant count; binds are how a session reaches the right one, not a reason to choose that layout.

If the answer is that two databases are warranted, two habits keep the cost contained. Keep each unit of work inside one database, with an outbox for anything that must reach the other. And make the boundary visible in the code — separate bases, separate modules, separate session factories where the caller should be choosing — so that a developer cannot accidentally write a query that assumes the two are one.

Frequently Asked Questions

Related

• Dynamic Schema and Multi-Tenant Routing — The parent guide: schemas, tenants and routing.
• Routing reads to replicas with async engines — Replica routing, lag and read-your-own-writes.
• Switching schemas per request with schema_translate_map — One database, many schemas — often the better boundary.
• Running Alembic across multiple databases and tenant schemas — Migrating each database on its own history.