As the lead of a SQLAlchemy 2.1 codebase with hundreds of legacy session.query() calls, how do you decide whether, when and how to move them to select()?
answer
- legacy, not removed
- what forces the move
- results that change silently
- freeze new use, convert on touch
basics
~20 sQuery still runs in SQLAlchemy 2.1 without warnings, so migrating is a cost decision, not a deadline. Ban new session.query() use, convert code when touched or moved to AsyncSession, which has no query(), and test conversions for changed results.
solid answer
~40 sIn SQLAlchemy 2.1 `Query` is a legacy API that translates itself into `select()` and still works; `Session.query()` emits no warning, and the 2.0 notes say it stays but is no longer documented beyond its reference or supported for the most part. So nothing forces a big-bang rewrite. What pushes migration is concrete: `AsyncSession` has no `query()`, documentation and examples are written for `select()`, and new behaviour such as 2.1's `Select.filter_by()` never reaches `Query`. Against that, conversion changes results quietly: lost deduplication, no LIMIT in `first()`, a different `count()`, a new `filter_by()` target. My plan: forbid new `session.query()` by lint rule, convert modules when they change or move to async, give converted queries row-count tests, and leave stable, cold code alone until it is touched.
go deeper
Recall that session.query() is legacy but still works in SQLAlchemy 2.1, and that new code should use select() with the Session.
Explain which Query behaviours disappear on conversion and why AsyncSession code queries with select() rather than Query.
Show how you would convert a module safely: row-count tests, SQL comparison with echo, and explicit where() instead of filter_by() after joins.
Own the trade between consistency and conversion risk: freeze new legacy use, prioritise async paths and removed methods, and decide openly whether the cold tail is worth converting.
## What staying on Query costs, and what it does not SQLAlchemy 2.0 made `select()` executed through the `Session` the standard way to query the ORM and turned **`Query`** into a legacy API. In SQLAlchemy 2.1 the situation is unchanged: - `Query` still exists and **translates itself into a `select()`** at execution time, so it is a thin adapter over the same machinery. - `Session.query()` itself emits **no deprecation warning**; only a few individual legacy methods, such as `Query.get()`, emit `LegacyAPIWarning`. - The 2.0 migration notes state that `Query` will stay, but will no longer be part of the documentation beyond its API reference nor be supported for the most part. So a codebase with hundreds of `session.query()` calls is not broken and has no removal date. The real costs of staying are slower: new team members learn two query styles, and new features and documentation target `select()`. Typing is not one of them: `Session.query()` is typed per column in 2.1 too, returning for example `RowReturningQuery[Ticket, str]`. ## Forces that make migration worth paying for | Force | Why it matters | |---|---| | Moving to `AsyncSession` | `AsyncSession` has no `query()` method; async code uses `await session.execute(select(...))` or `await session.scalars(...)`, and legacy `Query` code can run only inside `AsyncSession.run_sync()` | | Documentation and examples | the 2.0 notes say `Query` is no longer part of the documentation beyond its reference; guides and new examples use `select()` | | New behaviour | 2.1's improved `Select.filter_by()` was deliberately not applied to `Query.filter_by()`, because `Query` is legacy | | One style for Core and ORM | the same `select()` construct serves both, so helpers and reviews are shared | | Removed features | `Query.from_self()`, `select_entity_from()` and string join targets are already gone; code using them must change anyway | If none of these applies to a module, it can wait. ## Stopgaps while the migration runs - **Async code that still needs an old query**: `AsyncSession.run_sync()` runs a plain function against the underlying sync `Session`, so a legacy `Query` helper can be called there until it is rewritten. Calling the sync session directly from async code fails with `MissingGreenlet`. - **Shared helpers**: wrap frequently used queries in functions that return lists of objects, so call sites do not care which style builds the statement and the helper can be converted once. ## Risks of converting A mechanical rewrite can change results, mostly without raising anything: 1. **Deduplication**: `Query` deduplicated full-entity results by primary key; 2.0-style results keep every joined row. 2. **`first()`**: `Query.first()` added a LIMIT of one; `Result.first()` sends the statement as written. 3. **`count()`**: `Query.count()` counted a subquery of the whole query; the 2.0 form counts directly. 4. **`filter_by()` after `join()`**: legacy uses the last joined entity; 2.1's `Select.filter_by()` searches every FROM entity and raises `AmbiguousColumnError` on shared names. 5. **Joined eager collections** need an explicit `.unique()` on the result. These are exactly the places where tests that check only the first returned object stay green while users see wrong totals or duplicates. ## A migration plan a lead can defend 1. **Stop the inflow.** Add a lint or review rule that rejects new `session.query()` calls, so the legacy surface only shrinks. 2. **Convert on touch.** When a module changes for other reasons, convert its queries in the same change, with tests that assert row counts and, for hot paths, the emitted SQL. 3. **Convert by force where needed.** Code moving to `AsyncSession` and code using removed `Query` methods goes first, because it has to change anyway. 4. **Track the remainder.** Count the remaining `session.query()` calls per module so the backlog is visible, and decide later whether the cold tail is worth converting at all. 5. **Write the conversion rules down**: `filter_by()` after a join becomes explicit `where()`; `first()` gets `limit(1)`; joined collections get `.unique()`. ## When to leave code alone - Stable reporting or admin code that nobody changes and that has no async pressure. - Queries whose behaviour depends on `Query`'s deduplication and that lack tests; converting them blind trades a style problem for a correctness risk. - Periods when the team is mid-upgrade of SQLAlchemy itself: change one thing at a time. ## The judgement in short The decision is a trade between **consistency and future features** on one side and **conversion risk and review time** on the other. Because `Query` is not going away on a fixed date, a lead does not need a big-bang rewrite; freezing new use, converting on touch and prioritising async paths and code that uses removed methods captures most of the benefit at a fraction of the risk.
- How do you stop new session.query() calls from entering the codebase while the old ones remain?Make the rule mechanical: a lint check or a code-review rule that flags `.query(` on a `Session`, with an allowlist of existing files that shrinks as modules are converted. Pair it with a short conversion guide so authors know the `select()` form, and track the count of remaining calls so the trend is visible.
- Which converted queries deserve tests first?Those whose results can change without an error: queries that join collections and return parents, call `first()` on large tables, use `count()` for user-facing totals, or call `filter_by()` after a join. Assert on row counts and identities, and compare emitted SQL with `echo=True` for hot paths.
- session.query() raises deprecation warnings in 2.1, so every call must be removed now.
- AsyncSession.query() works the same way as Session.query(), just awaited.
- Converting Query to select() is purely syntactic and cannot change results.
- Legacy Query code automatically picks up new 2.1 behaviour such as the filter_by() lookup.
- The safest plan is one big-bang rewrite of every query in a single release.
Assumes SQLAlchemy 2.1, where Query remains a legacy API that emits no warning from Session.query() and does not receive 2.1 changes such as the new Select.filter_by() lookup.