skip to content

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()?

level: principalimportance: nice to knowfreq 24%

answer

  1. legacy, not removed
  2. what forces the move
  3. results that change silently
  4. freeze new use, convert on touch

basics

~20 s

Query 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 s

In 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

for a junior

Recall that session.query() is legacy but still works in SQLAlchemy 2.1, and that new code should use select() with the Session.

for a middle

Explain which Query behaviours disappear on conversion and why AsyncSession code queries with select() rather than Query.

for a senior

Show how you would convert a module safely: row-count tests, SQL comparison with echo, and explicit where() instead of filter_by() after joins.

for a principal

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.