Sessionmakers with multiple binds only use engines from first registered app

[brihall]

In an application that uses Werkzeug's Application Dispatcher middleware and vertical partitioning, SQLAlchemy.test_isolation was behaving inconsistently during testing with pytest. Tests would pass when run alone but fail when other tests were included, because rows inserted in earlier tests weren't being rolled back.

_make_sessionmaker uses a shallow copy of SQLAlchemy._session_options, so options["binds"] is the same object as ._session_options["binds"]. After the first app is registered, the sessionmakers for subsequent apps registered on the same instance will use the binds created for the first app, because they've been assigned to ._session_options.

Here's a script demonstrating this:

# /// script
# requires-python = ">=3.14"
# dependencies = [
#     "flask-sqlalchemy-lite",
# ]
# ///
import sqlalchemy.orm as orm
from flask import Flask
from flask_sqlalchemy_lite import SQLAlchemy


class DefaultBase(orm.DeclarativeBase):
    pass


class AltBase(orm.DeclarativeBase):
    pass


db = SQLAlchemy(session_options={"binds": {DefaultBase: "default", AltBase: "alt"}})


def create_app() -> Flask:
    app = Flask(__name__)
    app.config.update({
        "SQLALCHEMY_ENGINES": {
            "default": {"url": "sqlite://", "connect_args": {"autocommit": False}},
            "alt": {"url": "sqlite://", "connect_args": {"autocommit": False}},
        }
    })
    db.init_app(app)
    return app


if __name__ == "__main__":
    assert db._session_options["binds"] == {DefaultBase: "default", AltBase: "alt"}
    app1 = create_app()
    with app1.app_context():
        assert db._session_options["binds"] == {
            DefaultBase: db.engines["default"],
            AltBase: db.engines["alt"],
        }
    app2 = create_app()

    state1 = db._app_state[app1]
    state2 = db._app_state[app2]

    assert state2.sessionmaker.kw["binds"][DefaultBase] is state1.engines["default"]
    assert state2.sessionmaker.kw["binds"][AltBase] is state1.engines["alt"]
    assert (
        set(state2.sessionmaker.kw["binds"].values()) & set(state2.engines.values())
        == set()
    )

This only became an issue because of some niche (some would say "poor") design in my own project, but it still seems like unintended behavior.

[davidism]

Good catch, thanks for investigating! Would adding options["binds"] = base.options["binds"].copy() be enough? Happy to review a PR against the stable branch.

[brihall]

That change by itself actually causes issues while making the async_sessionmaker if the app doesn't have async config. Currently bind is an Engine after the first call to _make_sessionmaker, so isinstance(bind, str) == False during any subsequent calls. That also means that currently async_sessionmaker is initialized with sync engines if binds is passed as a session option